4.3.7 Setting Up Passwords

In most cases you should use GRANT to set up your users/passwords, so the following only applies for advanced users. See section 4.3.1 GRANT and REVOKE Syntax.

The examples in the preceding sections illustrate an important principle: when you store a non-empty password using INSERT or UPDATE statements, you must use the PASSWORD() function to encrypt it. This is because the user table stores passwords in encrypted form, not as plaintext. If you forget that fact, you are likely to attempt to set passwords like this:

shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('%','jeffrey','biscuit');
mysql> FLUSH PRIVILEGES;

The result is that the plaintext value 'biscuit' is stored as the password in the user table. When the user jeffrey attempts to connect to the server using this password, the mysql client encrypts it with PASSWORD(), generates an authentification vector based on encrypted password and a random number, obtained from server, and sends the result to the server. The server uses the password value in the user table (that is not encrypted value 'biscuit') to perform the same calculations, and compares results. The comparison fails and the server rejects the connection:

shell> mysql -u jeffrey -pbiscuit test
Access denied

Passwords must be encrypted when they are inserted in the user table, so the INSERT statement should have been specified like this instead:

mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('%','jeffrey',PASSWORD('biscuit'));

You must also use the PASSWORD() function when you use SET PASSWORD statements:

mysql> SET PASSWORD FOR jeffrey@"%" = PASSWORD('biscuit');

If you set passwords using the GRANT ... IDENTIFIED BY statement or the mysqladmin password command, the PASSWORD() function is unnecessary. They both take care of encrypting the password for you, so you would specify a password of 'biscuit' like this:

mysql> GRANT USAGE ON *.* TO jeffrey@"%" IDENTIFIED BY 'biscuit';

or

shell> mysqladmin -u jeffrey password biscuit

Note: PASSWORD() is different from Unix password encryption. See section 4.3.2 MySQL User Names and Passwords.

User Comments

Listen up kids, 'cause this doesn't seem to be
anywhere in the documentation and it's really
important to know if you're planning on matching
plaintext strings against password() encrypted fields
in your tables: Apparently password() creates a
garbled alpha-numeric string that is ALWAYS 16
CHARACTERS LONG. If your field is less than 16
characters long, the string will get truncated and
you'll be out in the wind when the time comes to
compare your plaintext candidates. I can't tell you
how long it took me to figure this out, so I hope this
note saves someone else the agrivation I went
through to end up writing it. Additionally, if someone
from mySQL is reading this, you might want to add
this fact to the manual officially. It seems to me like
a pretty vital scrap of information to omit.

Okay I got a question and a problem with Mysql and
passwords:). I tried to use several of the options
and most of them have not worked.

However one soloution did work and I tested it out
twice and it was solid. Of course I lost the little piece
of paper I wrote it out on and I can't seem to find
this soloution anywhere, as if it did not exist or
maybe I imagined it.

The soloution that worked for me, before I lost the
little slip of paper I wrote it down on goes something
like this.....

Insert into user root
Password "my password"
and then something with 'Y', 'Y', 'Y', (about a dozen
or 15 times or so)
However, I can not find this soloution anywhere can
someone help me out here?

here's what it should look like.
write your update statement accordingly.



mysql> select * from user \G
*************************** 1. row ***************************
Host: localhost
User: root
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
*************************** 2. row ***************************
Host: localhost.localdomain
User: root
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
*************************** 3. row ***************************
Host: localhost
User:
Password:
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
*************************** 4. row ***************************
Host: localhost.localdomain
User:
Password:
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N

Here's a time-saver: If you're hand-editing these tables, don't forget to run "FLUSH PRIVILEGES;" afterwards, or your changes won't actually take effect.

Well, password creation in MySQL has always driven me insane because it never worked satisfyingly or only with dozens of workarounds.

I've got a fresh install of 4.0.13 now, and there's no user table at all in contrary to 3.23.xx.

Actually, it *SHOULD* be possible to create a new password 'summer123' for the user 'mysql' using mysqladmin by typing

> mysqladmin -u mysql password 'summer123'

but this doesn't work on my windows installation.
User 'mysql' can enter the mysql.exe command-line application without having to enter a password. Funny, really.
So I have to do everything by hand. *sigh*

Ugly, ugly.
Found it:
http://www.mysql.com/doc/en/Windows_running.html

You have to delete the entry for global rights BY HAND (!) from the user table to inject some security into your database.
Oh please ... let this be the default setting in future.

Add your own comment.