MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
SET PASSWORD [FORuser
] =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 in MySQL 5.7 and is
removed in MySQL 8.0.
auth_string
')
SET PASSWORD
... = '
syntax is not deprecated, but auth_string
'ALTER
USER
is the preferred statement for account
alterations, including assigning passwords. For example:
ALTER USERuser
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 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.)
Use the PASSWORD()
function
(deprecated in MySQL 5.7)
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 the
mysql.user
system table.
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('password
');
If the old_passwords
value
differs from that required by the authentication plugin, the
hashed password value returned by
PASSWORD()
cannot be used by
the plugin and correct authentication of client connections
cannot 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”.