Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Assigning Account Passwords

6.3.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. Client authentication occurs using plugins; see Section 6.3.8, “Pluggable Authentication”.

MySQL stores passwords in the user table in the mysql database. Operations that assign or modify passwords are permitted only to users with the CREATE USER privilege, or, alternatively, privileges for the mysql database (INSERT to create new accounts, UPDATE to modify existing accounts). If the read_only system variable is enabled, use of account-modification statements such as CREATE USER or SET PASSWORD additionally requires the SUPER privilege.

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

mysql> CREATE USER 'jeffrey'@'localhost'
    -> IDENTIFIED BY 'mypass';

CREATE USER also supports syntax for specifying the account authentication plugin. See Section, “CREATE USER Syntax”.

As of MySQL 5.7.6, to assign or change a password for an existing account, use the ALTER USER statement:

mysql> ALTER USER 'jeffrey'@'localhost'
    -> IDENTIFIED BY 'mypass';

If you are not connected as an anonymous user, you can change your own password without naming your own account literally:

    -> IDENTIFIED BY 'mypass';

Before MySQL 5.7.6, use SET PASSWORD:

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

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');

The old_passwords system variable value determines the hashing method used by PASSWORD(). If you specify the password using that function and SET PASSWORD rejects the password as not being in the correct format, it may be necessary to set old_passwords to change the hashing method. See Section, “SET PASSWORD Syntax”.

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';

Use of SET PASSWORD or GRANT for password modification is deprecated as of MySQL 5.7.6. Use ALTER USER instead.

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

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

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.

During authentication when a client connects to the server, MySQL treats the password in the user table as an encrypted hash value. When assigning a password to an account, it is important to store an encrypted value, not the cleartext password. Use the following guidelines:

  • When you assign a password using CREATE USER or ALTER USER, GRANT with an IDENTIFIED BY clause, or the mysqladmin password command, they encrypt the password for you. Specify the literal cleartext password. For example:

    mysql> CREATE USER 'jeffrey'@'localhost'
        -> IDENTIFIED BY 'mypass';
  • For CREATE USER or GRANT, you can avoid sending the cleartext password if you know the hash value that PASSWORD() would return for the password. Specify the hash value preceded by the keyword PASSWORD:

    mysql> CREATE USER 'jeffrey'@'localhost'
        -> IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689';

    IDENTIFIED BY PASSWORD syntax is deprecated as of MySQL 5.7.6.

  • Before MySQL 5.7.6, when you assign an account a nonempty password using SET PASSWORD, you must use the PASSWORD() function to encrypt the password, otherwise the password is stored as cleartext. Suppose that you assign a password like this:

        -> '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.

    As of MySQL 5.7.6, if you specify the password without PASSWORD(), SET PASSWORD interprets the string as a cleartext string and hashes it appropriately for the account authentication plugin before storing it in the user account row.


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

Although it is preferable to modify passwords using SET PASSWORD, GRANT, or mysqladmin, 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.

Download this Manual
User Comments
Sign Up Login You must be logged in to post a comment.