Good afternoon, ladies and gentlemen!
My problem is that I can not to create an account in MySQL. I use to create an account the following code:
CREATE USER [username] IDENTIFIED BY PASSWORD ‘password’;,
but MySQL shows the following: ERROR 1372 (HY000); Password hash should be 41-digit hexadecimal number.
Please, help me!
If you use IDENTIFIED BY PASSWORD ‘password’, that signifies that you will be using a hashed hexidecimal value as the password.
If you use IDENTIFIED BY ‘password’, that signifies that you will be using a plain text password.
Good aftetnoon, Mr Force Flow!
I read the article from your link, but I would like to ask you something else. I understand that the option “IDENTIFIED BY PASSWORD ‘password’” is better because it is more secure than option “IDENTIFIED BY ‘password’” - is this correct? Still I have some questions: 1) I should point out to the host when creating an account, for example, “localhost” or not? 2) I should specify the privileges of the user using the operator “GRANT” or not? If Yes, what should be specifically specify to fully use MySQL: edit and much more (have the right to full administration site)? Thanks in advance for your help!
Please help me someone to understand my previous questions on this topic! Thanks in advance for your help!
Sorry, it’s a Holiday and I’m not quite on the same page yet. Maybe this will help
CREATE USER user [IDENTIFIED BY [PASSWORD] ‘password’]
[, user [IDENTIFIED BY [PASSWORD] ‘password’]] …
…
The CREATE USER statement was added in MySQL 5.0.2. This statement creates new MySQL accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database.
…
The account can be given a password with the optional IDENTIFIED BY clause. The user value and the password are given the same way as for the GRANT statement. In particular, to specify the password in plain text, omit the PASSWORD keyword. To specify the password as the hashed value as returned by the PASSWORD() function, include the PASSWORD keyword.
So first questionn, you have the global CREATE USER privilege?
Yes, BUT (and I haven’t fully tested this, so please notify me if it errors), you should be able to do this:
CREATE USER [username] IDENTIFIED BY PASSWORD PASSWORD('password');
Worse case, you just need to perform it in two or more steps.
SELECT PASSWORD('password');
Copy the output of the above and then use that hash in your CREATE USER statement
CREATE USER [username] IDENTIFIED BY PASSWORD 'password';
Granted, both are stored securely within MySQL the only concern is if mysql_history is going to log the query and thus have the password in plain text. If you have your new user change their password immediately, then this becomes a moot point as the history will no longer refer to the new updated password.
No, they shouldn’t care. If they gave you the privileges for creating an account, then you have the right to use them. If you were denied those privileges and needed them, then you’d want to contact your host.
If the user is to have Full Admin rights, then you’d GRANT ALL PRIVILEGES, however, if the user does not need full administration, only grant what they need.
Thank you very much, Mitti neague and cpradio for your help!
But I would like something clarified. You see I am a beginner programmer. I have created a website using HTML and CSS. I installed a local server, MySQL and PHP. PHP and MySQL I need to create a registration system for users and baskets buyers on my website. I have a book on MySQL, but lacking the precision of instructions how to create a full account and full administrator rights with all privileges. I connect to MySQL while only using the [mysql-u root], so I do not have the global CREATE USER privilege, and the like. I will appreciate if You tell me step-by-step instructions on creating a proper and full account in MySQL.
root should have full privileges (including create user).
So if you want to create a new account with similar privileges, run your CREATE USER statement, then grant the privileges:
GRANT ALL ON database.table TO 'username'@'connection_name';
Okay, so the above seems confusing, and it is on some level, which is why I typically recommend using phpMyAdmin. That makes it much easier as it is GUI based. However, it isn’t too difficult to figure out.
So the first part, “database.table”: You can specify which database and a specific table to grant permissions to for a given account. You can also specify ALL tables within a database using database.*, or you can specify ALL databases and ALL tables using .
The latter part, “‘username’@‘connection_name’”. You can specify which user is getting these privileges and what privileges they get based on how they connected to the server. The username is self explanatory, put the new account name there. The connection name is a bit trickier, but you can think of it this way: There are a variety of connection strings you can use to connect to MySQL, you can use “localhost”, or 127.0.0.1, or the physical IP of your server 124.35.43.12, or even the host name of the server hosting your MySQL mysql.mydomain.com, or % to denote all possible ways of connecting.
You can grant specific privileges based on which of those connection strings the user uses. So for example, if you are hosting your own web application and MySQL and Apache are installed on the same server, I’d personally start with “localhost”, so you’d grant the permission to username@Localhost; however, future proofing would incline me to tell you to setup the DNS entry for mysql.mydomain.com and use that. This way, if you ever move MySQL to a different server, your code doesn’t change (as it is already using that connection string).
So why does MySQL let you assign privileges based on a connection? I’m not sure this is the correct answer, but I imagine it is from a security perspective. In short, I can give greater privileges to those using that account within my network, as I can define a local IP or localhost and thus detect they are working inside my network. I can then give out less privileges to those using mysql.mydomain.com or the external IP 124.35.43.12 so if the user gets compromised, it can’t take down the database/server.
Personally, I think this is a crappy implementation. I would never personally use the same username/credentials for an external application and an internal application. They would either 1) go through a RESTful service or 2) use different accounts. I just don’t see the usefulness in MySQL working this way with its privileges…
I know a lot of that probably went over your head (I apologize for that – so feel free to ask questions if I just made you concerned with anything else), but hopefully this gave you some insight to the privilege system of MySQL.
Tell me, please, I correctly described the following 4 steps to create an account in MySQL or not:
At the moment I have no personal account information databases and data tables in MySQL.
Thanks in advance.
Yes, those are the steps. Be careful of you fourth step as you missed the keyword TO between . and ‘user’@‘localhost’
Please explain how to record the password in the hexadecimal system during account creation using the CREATE USER. Or the system issues a password, which then must be entered to log in? And if You were told how to actually work with passwords in MySQL.
Thanks in advance.
Sorry for the fingerprint in the word “explain” in the previous post :).
I’d use the following approach:
SELECT PASSWORD('password i want to use');
That should produce an output showing the hexadecimal value, take that output, and use it in your CREATE USER statement.
I’m not sure you can have the system generate a password, and this is just how I’d do it. Wasn’t taught one way or another, but in all honesty, you are putting to much emphasis on this. Sure using the hexadecimal is more secure, however, for it to be a “real” issue, your server must be compromised to where the attacker has access to your logs. That means they have full (or at least significant) access to the machine and that is a much bigger issue than your MySQL password.
Thank you for reply!
So, to create your account should be written down so: CREATE USER ‘user’@‘localhost’ without IDENTIFIED BY ‘password’ and then after that write SELECT PASSWORD (‘password i want to use’)? Between these two statements CREATE USER and SELECT PASSWORD need to press the ENTER button or not? I correctly written summary to create an account? Correct me please if I don’t understand! And how to properly solve the issue of security in MySQL? So the password is not necessary to use the hexadecimal system?
Thanks in advance.
No. If you want to use the hexadecimal format, run the SELECT statement first. Yes, press enter, as you need it to execute. Copy the output (should give you a hexadecimal value).
Next write the CREATE USER statement with the IDENTIFIED BY PASSWORD ‘paste hexadecimal value from the select statement here’, press enter to execute it, then run a GRANT ALL … for your new user.
Don’t let your server get compromised. The issue is, if someone has access to the logs, they may be able to view the password created for that user. Prevent the server from being breached and limit who has access to the physical server and the password is secure (without using the hexadecimal system).
Correct. You technically don’t need to use the hexadecimal version for creating a user. The whole issue is the password gets stored in the mysql history log. If you keep your server secure, or wipe out that file after creating your user, you have nothing to worry about. Quite frankly, if you server is breached, if you have worse things to worry about than a mysql password.