MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

13.7.1.10 SET PASSWORD Syntax

SET PASSWORD [FOR user] auth_option
    [REPLACE 'current_auth_string']
    [RETAIN CURRENT PASSWORD]

auth_option: {
    = 'auth_string'
  | TO RANDOM
}

The SET PASSWORD statement assigns a password to a MySQL user account. The password may be either explicitly specified in the statement or randomly generated by MySQL. The statement may also include a password-verification clause that specifies the account current password to be replaced, and a clause that manages whether an account has a secondary password. 'auth_string' and 'current_auth_string' each represent a cleartext (unencrypted) password.

Note

Rather than using SET PASSWORD to assign passwords, ALTER USER is the preferred statement for account alterations, including assigning passwords. For example:

ALTER USER user IDENTIFIED BY 'auth_string';
Note

Clauses for random password generation, password verification, and secondary passwords apply only to accounts that use an authentication plugin that stores credentials internally to MySQL. For accounts that use plugins that perform authentication against a credentials system that is external to MySQL, password management must be handled externally against that system as well. For more information about internal credentials storage, see Section 6.2.15, “Password Management”.

The REPLACE 'current_auth_string' clause performs password verification and is available as of MySQL 8.0.13. If given:

For more information about password verification by specifying the current password, see Section 6.2.15, “Password Management”.

The RETAIN CURRENT PASSWORD clause implements dual-password capability and is available as of MySQL 8.0.14. If given:

For more information about use of dual passwords, see Section 6.2.15, “Password Management”.

SET PASSWORD permits these auth_option syntaxes:

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 Client Logging”.

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

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

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 account row in the mysql.user system table. (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.)

Setting the password for a named account (with a FOR clause) requires the UPDATE privilege for the mysql system database. Setting the password for yourself (for a nonanonymous account with no FOR clause) requires no special privileges.

Statements that modify secondary passwords require these 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.

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