13.7.1.7 SET PASSWORD Statement
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. '
represents a cleartext (unencrypted) password. auth_string
'
SET PASSWORD ... = PASSWORD('
syntax is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.auth_string
')SET PASSWORD ... = '
syntax is not deprecated, butauth_string
'ALTER USER
is the preferred statement for account alterations, including assigning passwords. For example:ALTER USER user IDENTIFIED BY 'auth_string';
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:
With a
FOR
clause, the statement sets the password for the named account, which must exist:user
SET PASSWORD FOR 'jeffrey'@'localhost' = 'auth_string';
With no
FOR
clause, the statement sets the password for the current user: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
CURRENT_USER()
function:SELECT CURRENT_USER();
If a FOR
clause is given, the account name uses the format described in Section 6.2.4, “Specifying Account Names”. For example: user
SET PASSWORD FOR 'bob'@'%.example.org' = 'auth_string';
The host name part of the account name, if omitted, defaults to '%'
.
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. When the read_only
system variable is enabled, SET PASSWORD
requires the SUPER
privilege in addition to any other required privileges.
The password can be specified in these ways:
Use a string without
PASSWORD()
SET PASSWORD FOR 'jeffrey'@'localhost' = 'password';
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 themysql.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.)Use the
PASSWORD()
function (deprecated as of MySQL 5.7.6)SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('password');
The
PASSWORD()
argument is the cleartext (unencrypted) password.PASSWORD()
hashes the password and returns the encrypted password string for storage in the account row in themysql.user
system table.The
PASSWORD()
function hashes the password using the hashing method determined by the value of theold_passwords
system variable value. Be sure thatold_passwords
has the value corresponding to the hashing method expected by the authentication plugin associated with the account. For example, if the account uses themysql_native_password
plugin, theold_passwords
value must be 0:SET old_passwords = 0; SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('password');
If the
old_passwords
value differs from that required by the authentication plugin, the hashed password value returned byPASSWORD()
will not by usable by the plugin and correct authentication of client connections will not occur.
The following table shows, for each password hashing method, the permitted value of old_passwords
and which authentication plugins use the hashing method.
Password Hashing Method | old_passwords Value | Associated Authentication Plugin |
---|---|---|
MySQL 4.1 native hashing | 0 | mysql_native_password |
SHA-256 hashing | 2 | sha256_password |
For additional information about setting passwords and authentication plugins, see Section 6.2.10, “Assigning Account Passwords”, and Section 6.2.13, “Pluggable Authentication”.