(mysql.info.gz) Passwords
Info Catalog
(mysql.info.gz) User resources
(mysql.info.gz) User Account Management
(mysql.info.gz) Password security
5.6.5 Assigning Account Passwords
---------------------------------
Passwords may be assigned from the command line by using the
`mysqladmin' command:
shell> mysqladmin -u USER_NAME -h HOST_NAME password "NEWPWD"
The account for which this command resets the password is the one with a
`user' table record that matches USER_NAME in the `User' column and the
client host _from which you connect_ in the `Host' column.
Another way to assign a password to an account is to issue a `SET
PASSWORD' statement:
mysql> SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');
Only users such as `root' with update access to the `mysql' database
can change the password for other users. If you are not connected as
an anonymous user, you can change your own password by omitting the
`FOR' clause:
mysql> SET PASSWORD = PASSWORD('biscuit');
You can also use a `GRANT USAGE' statement at the global level (`ON
*.*') to assign a password to an account without affecting the
account's current privileges:
mysql> GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit';
Although it is generally preferable to assign passwords using one of the
preceding methods, you can also do so by modifying the `user' table
directly:
* To establish a password when creating a new account, provide a
value for the `Password' column:
shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('%','jeffrey',PASSWORD('biscuit'));
mysql> FLUSH PRIVILEGES;
* To change the password for an existing account, use `UPDATE' to
set the `Password' column value:
shell> mysql -u root mysql
mysql> UPDATE user SET Password = PASSWORD('bagel')
-> WHERE Host = '%' AND User = 'francis';
mysql> FLUSH PRIVILEGES;
When you assign an account a password using `SET PASSWORD', `INSERT',
or `UPDATE', you must use the `PASSWORD()' function to encrypt it.
(The only exception is that you need not use `PASSWORD()' if the
password is empty.) `PASSWORD()' is necessary because the `user' table
stores passwords in encrypted form, not as plaintext. If you forget
that fact, you are likely 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 literal value `'biscuit'' is stored as the
password in the `user' table, not the encrypted value. When `jeffrey'
attempts to connect to the server using this password, the value is
encrypted and compared to the value stored in the `user' table.
However, the stored value is the literal string `'biscuit'', so the
comparison fails and the server rejects the connection:
shell> mysql -u jeffrey -pbiscuit test
Access denied
If you set passwords using the `GRANT ... IDENTIFIED BY' statement or
the `mysqladmin password' command, they both take care of encrypting
the password for you. The `PASSWORD()' function is unnecessary.
* `PASSWORD()' encryption is different from Unix password
encryption. User names.
Info Catalog
(mysql.info.gz) User resources
(mysql.info.gz) User Account Management
(mysql.info.gz) Password security
automatically generated byinfo2html