Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.2Mb
PDF (RPM) - 26.1Mb
HTML Download (TGZ) - 6.5Mb
HTML Download (Zip) - 6.5Mb
HTML Download (RPM) - 5.6Mb
Man Pages (TGZ) - 170.8Kb
Man Pages (Zip) - 279.3Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

13.7.1.6 SET PASSWORD Syntax

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.

Important

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 password logging in the server logs, see Section 6.1.2.3, “Passwords and Logging”. For similar information about client-side logging, see Section 4.5.1.3, “mysql Logging”.

SET PASSWORD can be used with or without a FOR clause that explicitly names a 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;
  • 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();

Setting the password for a named account (with a FOR clause) requires the UPDATE privilege for the mysql 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.

If a FOR user clause is given, the account name uses the format described in Section 6.2.3, “Specifying Account Names”. For example:

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

The host name part of the account name, if omitted, defaults to '%'.

The password can be specified in these ways:

  • Use the PASSWORD() function

    The PASSWORD() 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. It should be set to a value compatible with the hash format required by the account authentication plugin. For example, if the account uses the mysql_native_password authentication plugin, old_passwords should be 0 for PASSWORD() to produce a hash value in the correct format. For mysql_old_password, old_passwords should be 1.

    Permitted old_passwords values are described later in this section.

  • Use the OLD_PASSWORD() function:

    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.

  • 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 Methodold_passwords ValueAssociated Authentication Plugin
MySQL 4.1 native hashing0 or OFFmysql_native_password
Pre-4.1 (old) hashing1 or ONmysql_old_password
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.

For additional information about setting passwords and authentication plugins, see Section 6.3.5, “Assigning Account Passwords”, and Section 6.3.6, “Pluggable Authentication”.


User Comments
Sign Up Login You must be logged in to post a comment.