SET PASSWORD [FOR user] = 'auth_string' [REPLACE 'current_auth_string'] [RETAIN CURRENT PASSWORD]
SET PASSWORD statement
assigns a password to a MySQL user account. It 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.
Clauses for password verification and secondary passwords
apply only to accounts that store credentials internally in
mysql.user system table
caching_sha2_password). For accounts that
use plugins that perform authentication against an external
credential system, password management must be handled
externally against that system as well.
clause 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 6.3.8, “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 6.3.8, “Password Management”.
ALTER USER user IDENTIFIED BY 'auth_string';
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 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:
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.