Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.0Mb
PDF (A4) - 35.1Mb
PDF (RPM) - 34.1Mb
EPUB - 8.6Mb
HTML Download (TGZ) - 8.3Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.2Mb
Eclipse Doc Plugin (TGZ) - 9.2Mb
Eclipse Doc Plugin (Zip) - 11.3Mb
Man Pages (TGZ) - 200.1Kb
Man Pages (Zip) - 305.4Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

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

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

MySQL stores passwords in the user table in the mysql system 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 privilege to create new accounts, UPDATE privilege 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.

The discussion here summarizes syntax only for the most common password-assignment statements. For complete details on other possibilities, see Section 14.7.1.2, “CREATE USER Syntax”, Section 14.7.1.1, “ALTER USER Syntax”, Section 14.7.1.4, “GRANT Syntax”, and Section 14.7.1.7, “SET PASSWORD Syntax”.

MySQL hashes passwords stored in the mysql.user table to obfuscate them. For most statements described here, MySQL automatically hashes the password specified. An exception is SET PASSWORD ... = PASSWORD('auth_string'), for which you use the PASSWORD() function explicitly to hash the password. There are also syntaxes for CREATE USER, ALTER USER, GRANT, and SET PASSWORD that permit hashed values to be specified literally; for details, see the descriptions of those statements.

MySQL uses plugins to perform client authentication; see Section 7.3.8, “Pluggable Authentication”. The authentication plugin associated with an account determines the algorithm used to hash passwords for that account.

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

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

For this CREATE USER syntax, MySQL automatically hashes the password before storing it in the mysql.user table.

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

To assign or change a password for an existing account, use one of the following methods:

  • Use the ALTER USER statement with an IDENTIFIED BY clause:

    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:

    mysql> ALTER USER USER()
        -> IDENTIFIED BY 'mypass';
    

    For these ALTER USER syntaxes, MySQL automatically hashes the password before storing it in the mysql.user table.

    ALTER USER syntax for changing passwords is available as of MySQL 5.7.6.

  • Use SET PASSWORD with the PASSWORD() function:

    mysql> SET PASSWORD FOR
        -> '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 PASSWORD() function hashes the password using the hashing method determined by the value of the old_passwords system variable value. If SET PASSWORD rejects the hashed password value returned by PASSWORD() as not being in the correct format, it may be necessary to change old_passwords to change the hashing method. See Section 14.7.1.7, “SET PASSWORD Syntax”.

    Note

    Use of SET PASSWORD ... = PASSWORD('auth_string') for password modification is deprecated as of MySQL 5.7.6. Use ALTER USER instead.

  • Use SET PASSWORD without the PASSWORD() function:

    For this syntax, the meaning differs in MySQL 5.7.6 and higher from earlier versions:

    • As of MySQL 5.7.6, SET PASSWORD interprets the string as a cleartext string and hashes it appropriately for the account authentication plugin before storing it in the mysql.user account row.

      mysql> SET PASSWORD FOR
          -> 'jeffrey'@'localhost' = 'mypass';
      
    • Before MySQL 5.7.6, SET PASSWORD interprets the string as a hashed password value to be stored directly.

      mysql> SET PASSWORD FOR
          -> 'jeffrey'@'localhost' = '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';
      

      The string must be hashed in the format required by the account authentication plugin. A string not hashed appropriately causes client connections for the account to fail with an Access denied error.

  • Use a GRANT USAGE statement at the global level (ON *.*) to change an account password without affecting the account's current privileges:

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

    For this GRANT syntax, MySQL automatically hashes the password before storing it in the mysql.user table.

    Note

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

  • To change an account 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 mysql.user table row that matches user_name in the User column and the client host from which you connect in the Host column.

    For password changes made using mysqladmin, MySQL automatically hashes the password before storing it in the mysql.user table.


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