Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.1Mb
PDF (A4) - 38.1Mb
PDF (RPM) - 33.0Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 132.7Kb
Man Pages (Zip) - 189.0Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

13.7.1.10 SET PASSWORD Syntax

SET PASSWORD [FOR user] = 'auth_string' [REPLACE 'current_auth_string']

The SET PASSWORD statement assigns a password to a MySQL user account. 'auth_string' represents a cleartext (unencrypted) password.

If given, the REPLACE clause (available as of MySQL 8.0.13), must specify the current account password to be replaced:

  • The clause must be given if password changes for the account are required to specify the current password, as verification that the user attempting to make the change actually knows the current password.

  • The clause is optional if password changes for the account may but need not specify the current password.

  • The statement fails if the clause is given but does not match the current password, even if the clause is optional.

  • REPLACE can be specified only when changing the account password for the current user.

For more information about password verification by specifying the current password, see Section 6.3.8, “Password Management”.

Note

Rather than using SET PASSWORD ... = 'auth_string' syntax, ALTER USER syntax is the preferred statement for account alterations, including assigning passwords. For example:

ALTER USER user IDENTIFIED BY 'auth_string';
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”.

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' = 'auth_string';
  • With no FOR user clause, the statement sets the password for the current 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 the statement is changing the current user's password, the REPLACE clause can be given to satisfy any account requirement that the user making the change knows the current password. If an account is named but is not that of the current user, the REPLACE clause cannot be given.

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 CONNECTION_ADMIN or 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.4, “Specifying Account Names”. For example:

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

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

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 mysql.user account row. (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.)

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


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.