SET PASSWORD [FOR user] auth_option
[RETAIN CURRENT PASSWORD]
| TO RANDOM
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.
each represent a cleartext (unencrypted) password.
ALTER USER user IDENTIFIED BY 'auth_string';
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 a plugin that performs 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 8.2.15, “Password Management”.
clause performs password verification and is available as of
MySQL 8.0.13. If given:
REPLACEspecifies the account current password to be replaced, as a cleartext (unencrypted) string.
The clause must be given if password changes for the account are required to specify the current password, as verification that the user attempting to make the change actually knows the current password.
The clause is optional if password changes for the account may but need not specify the current password.
The statement fails if the clause is given but does not match the current password, even if the clause is optional.
REPLACEcan be specified only when changing the account password for the current user.
For more information about password verification by specifying the current password, see Section 8.2.15, “Password Management”.
RETAIN CURRENT PASSWORD clause implements
dual-password capability and is available as of MySQL 8.0.14. If
RETAIN CURRENT PASSWORDretains an account current password as its secondary password, replacing any existing secondary password. The new password becomes the primary password, but clients can use the account to connect to the server using either the primary or secondary password. (Exception: If the new password specified by the
SET PASSWORDstatement is empty, the secondary password becomes empty as well, even if
RETAIN CURRENT PASSWORDis given.)
If you specify
RETAIN CURRENT PASSWORDfor an account that has an empty primary password, the statement fails.
If an account has a secondary password and you change its primary password without specifying
RETAIN CURRENT PASSWORD, the secondary password remains unchanged.
For more information about use of dual passwords, see Section 8.2.15, “Password Management”.
SET PASSWORD permits these
Assigns the account the given literal password.
Assigns the account a password randomly generated by MySQL. The statement also returns the cleartext password in a result set to make it available to the user or application executing the statement.
For details about the result set and characteristics of randomly generated passwords, see Random Password Generation.
Random password generation is available as of MySQL 8.0.18.
Under some circumstances,
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 18.104.22.168, “Passwords and Logging”. For similar
information about client-side logging, see
Section 22.214.171.124, “mysql Client Logging”.
SET PASSWORD can be used with or
FOR clause that explicitly names a
FORclause, the statement sets the password for the named account, which must exist:
SET PASSWORD FOR 'jeffrey'@'localhost' = 'auth_string';
FORclause, 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. (In particular, you can change your own password.) To see which account the server authenticated you as, invoke the
clause is given, the account name uses the format described in
Section 8.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 schema. 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:
APPLICATION_PASSWORD_ADMINprivilege is required to use the
RETAIN CURRENT PASSWORDclause for
SET PASSWORDstatements that apply to your own account. The privilege is required to manipulate your own secondary password because most users require only one password.