5.8.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 row 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 that have 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 non-empty password using
SET PASSWORD, INSERT, or
UPDATE, you must use the
PASSWORD() function to encrypt it.
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 assign passwords using the GRANT ... IDENTIFIED
BY statement or the mysqladmin
password command, they both take care of encrypting
the password for you. In these cases, using
PASSWORD() function is unnecessary.
Note:
PASSWORD() encryption is different from Unix
password encryption. See Section 5.8.1, “MySQL Usernames and Passwords”.
