Skip navigation links
13.7.2 Table Maintenance Statements »
Section Navigation      [Toggle]

13.7.1.7 SET PASSWORD Syntax

SET PASSWORD [FOR user] =
    {
        PASSWORD('cleartext password')
      | OLD_PASSWORD('cleartext password')
      | 'encrypted password'
    }

The SET PASSWORD statement assigns a password to a MySQL user account:

  • With no FOR user clause, this statement sets the password for the current user: 

    SET PASSWORD = PASSWORD('cleartext password');

    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 for, invoke the CURRENT_USER() function: 

    SELECT CURRENT_USER();

  • With a FOR user clause, this statement sets the password for the named account, which must exist: 

    SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('cleartext password');

    In this case, you must have the UPDATE privilege for the mysql database.

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. (If you specify only a user name, a host name of '%' is used.) 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('cleartext password');

The password can be specified in these ways:

  • Using the PASSWORD() function

    The function argument is the cleartext (unencrypted) password. PASSWORD() hashes the password and returns the encrypted password string.

    The old_passwords system variable value determines the hashing method used by PASSWORD(). If SET PASSWORD rejects the 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, hashed password values returned by PASSWORD() are 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

  • Using the OLD_PASSWORD() function:

    The function argument is the cleartext (unencrypted) password. OLD_PASSWORD() hashes the password using pre-4.1 hashing and returns the encrypted password string. This hashing method is appropriate only for accounts that use the mysql_old_password authentication plugin.

  • Using 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 the permitted values of old_passwords, the password hashing method for each value, and which authentication plugins use passwords hashed with each method. These values are permitted as of MySQL 5.6.6. Before 5.6.6, the permitted values are 0 (or OFF) and 1 (or ON).

ValuePassword Hashing MethodAssociated Authentication Plugin
0MySQL 4.1 native hashingmysql_native_password
1Pre-4.1 (“old”) hashingmysql_old_password
2SHA-256 hashingsha256_password

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

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

Caution

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.

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.)


User Comments