Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.3Mb
PDF (A4) - 33.3Mb
PDF (RPM) - 31.3Mb
HTML Download (TGZ) - 7.9Mb
HTML Download (Zip) - 8.0Mb
HTML Download (RPM) - 6.8Mb
Man Pages (TGZ) - 144.9Kb
Man Pages (Zip) - 205.9Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb


Pre-General Availability Draft: 2017-09-21

13.7.1.10 SET PASSWORD Syntax

SET PASSWORD [FOR user] = password_option

password_option: {
    'auth_string'
  | PASSWORD('auth_string')
}

The SET PASSWORD statement assigns a password to a MySQL user account. 'auth_string' represents a cleartext (unencrypted) password.

Note
Important

Under some circumstances, SET PASSWORD may be recorded in server logs or on the client side in a history file such as ~/.mysql_history, which means that cleartext passwords may be read by anyone having read access to that information. For information about the conditions under which this occurs for the server logs and how to control it, see Section 6.1.2.3, “Passwords and Logging”. For similar information about client-side logging, see Section 4.5.1.3, “mysql Logging”.

SET PASSWORD can be used with or without a FOR clause that explicitly names a user account:

  • With a FOR user clause, the statement sets the password for the named account, which must exist:

    SET PASSWORD FOR 'jeffrey'@'localhost' = 'auth_string';
  • With no FOR user clause, the statement sets the password for the current user:

    SET PASSWORD = 'auth_string';

    Any client who connects to the server using a nonanonymous account can change the password for that account. To see which account the server authenticated you as, invoke the CURRENT_USER() function:

    SELECT CURRENT_USER();

Setting the password for a named account (with a FOR clause) requires the UPDATE privilege for the mysql database. Setting the password for yourself (for a nonanonymous account with no FOR clause) requires no special privileges. When the read_only system variable is enabled, SET PASSWORD requires the CONNECTION_ADMIN or SUPER privilege in addition to any other required privileges.

If a FOR user clause is given, the account name uses the format described in Section 6.2.4, “Specifying Account Names”. For example:

SET PASSWORD FOR 'bob'@'%.example.org' = 'auth_string';

The host name part of the account name, if omitted, defaults to '%'.

The password can be specified in these ways:

  • Use a string without PASSWORD()

    SET PASSWORD FOR 'jeffrey'@'localhost' = 'mypass';

    SET PASSWORD interprets the string as a cleartext string, passes it to the authentication plugin associated with the account, and stores the result returned by the plugin in the mysql.user account row. (The plugin is given the opportunity to hash the value into the encryption format it expects. The plugin may use the value as specified, in which case no hashing occurs.)

  • Use the PASSWORD() function (deprecated as of MySQL 5.7.6)

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

    The PASSWORD() argument is the cleartext (unencrypted) password. PASSWORD() hashes the password and returns the encrypted password string for storage in the mysql.user account row.

    The PASSWORD() function hashes the password using the hashing method determined by the value of the old_passwords system variable value. Be sure that old_passwords has the value corresponding to the hashing method expected by the authentication plugin associated with the account. For example, if the account uses the mysql_native_password plugin, the old_passwords value must be 0:

    SET old_passwords = 0;
    SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');

    If the old_passwords value differs from that required by the authentication plugin, the hashed password value returned by PASSWORD() will not by usable by the plugin and correct authentication of client connections will not occur.

The following table shows the permitted values of old_passwords, the password hashing method for each value, and which authentication plugins use passwords hashed with each method.

ValuePassword Hashing MethodAssociated Authentication Plugin
0MySQL native hashingmysql_native_password
2SHA-256 hashingsha256_password

For additional information about setting passwords and authentication plugins, see Section 6.3.7, “Assigning Account Passwords”, and Section 6.3.10, “Pluggable Authentication”.


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