MySQL :: MySQL 5.7 Reference Manual :: 13.7.1.7 SET PASSWORD Syntax

MySQL 5.7 Reference Manual / ... / SET PASSWORD Syntax

13.7.1.7 SET PASSWORD Syntax

SET PASSWORD syntax for MySQL 5.7.6 and higher:

SET PASSWORD [FOR user] = password_option

password_option: {
    PASSWORD('auth_string')
  | 'auth_string'
}

SET PASSWORD syntax before MySQL 5.7.6:

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:

'auth_string' represents a cleartext password.
'hash_string' represents an encrypted password.

Note

SET PASSWORD ... = PASSWORD('auth_string') syntax is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.
SET PASSWORD ... = 'auth_string' syntax is not deprecated, but ALTER USER is now the preferred statement for assigning passwords. For example:
```
ALTER USER user IDENTIFIED BY 'auth_string';
```

SET PASSWORD can be used with or without an explicitly named user account:

With a FOR user clause, the statement sets the password for the named account, which must exist:
```
SET PASSWORD FOR 'jeffrey'@'localhost' = password_option;
```
In this case, you must have the UPDATE privilege for the mysql database.
With no FOR user clause, the statement sets the password for the current user:
```
SET PASSWORD = password_option;
```
Any client who connects to the server using a nonanonymous account can change the password for that account. To see which account the server authenticated you as, invoke the CURRENT_USER() function:
```
SELECT CURRENT_USER();
```

When the read_only system variable is enabled, SET PASSWORD requires the SUPER privilege in addition to any other required privileges.

If a FOR user clause is given, the account name uses the format described in Section 6.2.3, “Specifying Account Names”. The user value should be given as 'user_name'@'host_name', where 'user_name' and 'host_name' are exactly as listed in the User and Host columns of the account's mysql.user table row. The host name part of the account name, if omitted, defaults to '%'. For example, to set the password for an account with User and Host column values of 'bob' and '%.example.org', write the statement like this:

SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('auth_string');

The password can be specified in these ways:

Using the PASSWORD() function (deprecated as of MySQL 5.7.6)
The 'auth_string' function argument is the cleartext (unencrypted) password. PASSWORD() hashes the password and returns the encrypted password string for storage in the mysql.user account row.
The PASSWORD() function hashes the password using the hashing method determined by the value of the old_passwords system variable value. If SET PASSWORD rejects the hashed password value returned by PASSWORD() as not being in the correct format, it may be necessary to change old_passwords to change the hashing method. 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('mypass');
```
If the old_passwords value differs from that required by the authentication plugin, the hashed password value returned by PASSWORD() 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('mypass');
ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number
```
Permitted old_passwords values are described later in this section.
Using the OLD_PASSWORD() function (permitted before MySQL 5.7.5 only):
The 'auth_string' function argument is the cleartext (unencrypted) password. OLD_PASSWORD() hashes the password using pre-4.1 hashing and returns the encrypted password string for storage in the mysql.user account row. This hashing method is appropriate only for accounts that use the mysql_old_password authentication plugin.

Note

Passwords that use the pre-4.1 hashing method are less secure than passwords that use the native password hashing method and should be avoided. Pre-4.1 passwords are deprecated and support for them is removed in MySQL 5.7.5. Consequently, OLD_PASSWORD() is deprecated and is removed in MySQL 5.7.5. For account upgrade instructions, see Section 6.5.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.
Using a string without PASSWORD() or OLD_PASSWORD()
For this syntax, the meaning differs in MySQL 5.7.6 and higher from earlier versions:
- As of MySQL 5.7.6, SET PASSWORD interprets the string as a cleartext string and hashes it appropriately for the account authentication plugin before storing it in the mysql.user account row.
- Before MySQL 5.7.6, SET PASSWORD interprets the string as a hashed password value to be stored directly. The string must be hashed in the format required by the account authentication plugin. A string not hashed appropriately causes client connections for the account to fail with an Access denied error.

For more information about setting passwords, see Section 6.3.6, “Assigning Account Passwords”.

The following table shows the permitted values of old_passwords, the password hashing method for each value, and which authentication plugins use passwords hashed with each method.

Value	Password Hashing Method	Associated Authentication Plugin
0	MySQL 4.1 native hashing	`mysql_native_password`
1	Pre-4.1 (“old”) hashing	`mysql_old_password`
2	SHA-256 hashing	`sha256_password`

Note

Passwords that use the pre-4.1 hashing method are less secure than passwords that use the native password hashing method and should be avoided. Pre-4.1 passwords are deprecated and support for them is removed in MySQL 5.7.5. Consequently, old_passwords=1, which causes PASSWORD() to generate pre-4.1 password hashes, is not permitted as of 5.7.5. For account upgrade instructions, see Section 6.5.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.

Important

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 Logging”.

If you are using MySQL Replication, be aware that, currently, a password used by a replication slave as part of a CHANGE MASTER TO statement is effectively limited to 32 characters in length; if the password is longer, any excess characters are truncated. This is not due to any limit imposed by the MySQL Server generally, but rather is an issue specific to MySQL Replication. (For more information, see Bug #43439.)

PREV HOME UP NEXT

User Comments