SET PASSWORD [FOR user] = password_option
password_option: {
PASSWORD('auth_string')
| OLD_PASSWORD('auth_string')
| 'hash_string'
}
The SET PASSWORD
statement
assigns a password to a MySQL user account, specified as either
a cleartext (unencrypted) or encrypted value:
'
represents a cleartext password.auth_string
''
represents an encrypted password.hash_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' = password_option;
With no
FOR
clause, the statement sets the password for the current user:user
SET PASSWORD = password_option;
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' = PASSWORD('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 the
PASSWORD()
functionThe
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. IfSET PASSWORD
rejects the hashed password value returned byPASSWORD()
as not being in the correct format, it may be necessary to changeold_passwords
to change the hashing method. 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()
is not acceptable for that plugin, and attempts to set the password produce an error. For example:mysql> SET old_passwords = 1; mysql> SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('password'); ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number
Permitted
old_passwords
values are described later in this section.Use the
OLD_PASSWORD()
function:The
'
function argument is the cleartext (unencrypted) password.auth_string
'OLD_PASSWORD()
hashes the password using pre-4.1 hashing and returns the encrypted password string for storage in the account row in themysql.user
system table. This hashing method is appropriate only for accounts that use themysql_old_password
authentication plugin.Use an already encrypted password string
The password is specified as a string literal. It must represent the already encrypted password value, in the hash format required by the authentication method used for the account.
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 |
Pre-4.1 (“old”) hashing | 1 | mysql_old_password |
SHA-256 hashing | 2 | sha256_password |
If you are connecting to a MySQL 4.1 or later server using a pre-4.1 client program, do not change your password without first reading Section 6.1.2.4, “Password Hashing in MySQL”. The default password hashing format changed in MySQL 4.1, and if you change your password, it might be stored using a hashing format that pre-4.1 clients cannot generate, thus preventing you from connecting to the server afterward.
For additional information about setting passwords and authentication plugins, see Section 6.2.9, “Assigning Account Passwords”, and Section 6.2.11, “Pluggable Authentication”.