MySQL 3.23, 4.0, 4.1 Reference Manual :: 5 MySQL Server Administration :: 5.6 MySQL User Account Management :: 5.6.5 Assigning Account Passwords

5.6.6 Using SSL for Secure Connections »

Section Navigation [Toggle]

5.6.5. Assigning Account Passwords

Required credentials for clients that connect to the MySQL server can include a password. This section describes how to assign passwords for MySQL accounts.

To assign a password when you create a new account with GRANT, include an IDENTIFIED BY clause:

mysql> GRANT ALL ON db.* TO 'jeffrey'@'localhost'
    -> IDENTIFIED BY 'mypass';

To assign or change a password for an existing account, one way is to issue a SET PASSWORD statement:

mysql> SET PASSWORD FOR
    -> 'jeffrey'@'localhost' = PASSWORD('mypass');

MySQL stores passwords in the user table in the mysql database. 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('mypass');

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'@'localhost'
    -> IDENTIFIED BY 'mypass';

To assign a password from the command line, use the mysqladmin command:

shell> mysqladmin -u user_name -h host_name password "newpwd"

The account for which this command sets 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.

It is preferable to assign passwords using one of the preceding methods, but it is also possible to modify the user table directly. In this case, you must also use FLUSH PRIVILEGES to cause the server to reread the grant tables. Otherwise, the change remains unnoticed by the server until you restart it.

To establish a password for a new account, provide a value for the Password column:

mysql> INSERT INTO mysql.user (Host,User,Password)
    -> VALUES('localhost','jeffrey',PASSWORD('mypass'));
mysql> FLUSH PRIVILEGES;

To change the password for an existing account, use UPDATE to set the Password column value:

mysql> UPDATE mysql.user SET Password = PASSWORD('bagel')
    -> WHERE Host = 'localhost' AND User = 'francis';
mysql> FLUSH PRIVILEGES;

During authentication when a client connects to the server, MySQL treats the password in the user table as an encrypted hash value (the value that PASSWORD() would return for the password). When assigning a password to an account, it is important to store an encrypted value, not the plaintext password. Use the following guidelines:

When you assign a password using GRANT with an IDENTIFIED BY clause or with the mysqladmin password command, they encrypt the password for you. Specify the literal plaintext password:
```
mysql> GRANT USAGE ON *.* TO 'jeffrey'@'localhost'
    -> IDENTIFIED BY 'mypass';
```
For GRANT, you can avoid sending the plaintext password if you know the hashed value that PASSWORD() would return for the password. Specify the hashed value preceded by the keyword PASSWORD:
```
mysql> GRANT USAGE ON *.* TO 'jeffrey'@'localhost'
    -> IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689';
```
When you assign an account a nonempty password using SET PASSWORD, INSERT, or UPDATE, you must use the PASSWORD() function to encrypt the password, otherwise the password is stored as plaintext. Suppose that you assign a password like this:
```
mysql> SET PASSWORD FOR
    -> 'jeffrey'@'localhost' = 'mypass';
```
The result is that the literal value 'mypass' 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 'mypass', so the comparison fails and the server rejects the connection with an Access denied error.

Note

PASSWORD() encryption differs from Unix password encryption. See Section 5.6.1, “User Names and Passwords”.

Previous / Next / Up / Table of Contents

User Comments

Add your own comment.

Top / Previous / Next / Up / Table of Contents