Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.7Mb
PDF (A4) - 35.7Mb
PDF (RPM) - 34.8Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 203.5Kb
Man Pages (Zip) - 308.9Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

14.7.1.1 ALTER USER Syntax

ALTER USER syntax for MySQL 5.7.6 and higher:

ALTER USER [IF EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

ALTER USER [IF EXISTS]
    USER() IDENTIFIED BY 'auth_string'

user:
    (see Section 7.2.3, “Specifying Account Names”)

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'hash_string'
}

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

ALTER USER syntax before MySQL 5.7.6:

ALTER USER
    user [password_option] [, user [password_option]] ...

password_option: {
    PASSWORD EXPIRE
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY
}

The ALTER USER statement modifies MySQL accounts. It provides control over account password expiration. As of MySQL 5.7.6, it also provides control over authentication, SSL/TLS, and resource-limit properties, and account locking and unlocking.

To use ALTER USER, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database. When the read_only system variable is enabled, ALTER USER additionally requires the SUPER privilege.

An error occurs if you try to modify an account that does not exist.

As of MySQL 5.7.8, the IF EXISTS clause can be used, which causes the statement to produce a warning for each named account that does not exist, rather than an error.

ALTER USER modifies the mysql.user table row for each affected account according to the options specified in the statement. Unspecified properties retain their current values.

Example 1: Change an account's password and expire it. As a result, the user must connect with the named password and choose a new one at the next connection:

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED BY 'new_password' PASSWORD EXPIRE;

Example 2: Modify an account to use the sha256_password authentication plugin and the given password. Require that a new password be chosen every 180 days:

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED WITH sha256_password BY 'new_password'
  PASSWORD EXPIRE INTERVAL 180 DAY;

Example 3: Lock or unlock an account:

ALTER USER 'jeffrey'@'localhost' ACCOUNT LOCK;
ALTER USER 'jeffrey'@'localhost' ACCOUNT UNLOCK;

Example 4: Require an account to connect using SSL and establish a limit of 20 connections per hour:

ALTER USER 'jeffrey'@'localhost'
  REQUIRE SSL WITH MAX_CONNECTIONS_PER_HOUR 20;

Because the capabilities of this statement were expanded considerably in MySQL 5.7.6, this section first describes current syntax, and then the more limited pre-5.7.6 syntax.

Important

Under some circumstances, ALTER USER 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 7.1.2.3, “Passwords and Logging”. For similar information about client-side logging, see Section 5.5.1.3, “mysql Logging”.

ALTER USER as of MySQL 5.7.6

Each account name uses the format described in Section 7.2.3, “Specifying Account Names”. The host name part of the account name, if omitted, defaults to '%'. It is also possible to specify CURRENT_USER or CURRENT_USER() to refer to the account associated with the current session.

For one syntax only, the account may be specified with the USER() function:

ALTER USER USER() IDENTIFIED BY 'auth_string';

This syntax enables changing your own password without naming your account literally.

For ALTER USER syntaxes that permit an auth_option value to follow a user value, auth_option begins with IDENTIFIED and indicates how the account authenticates by specifying an account authentication plugin, credentials (password), or both. Each auth_option value applies only to the user named immediately preceding it.

Following the user specifications, the statement may include options for SSL/TLS, resource-limit, password-expiration, and locking properties. All these options are global to the statement and apply to all named users.

Example: This statement changes the password for jeffrey but leaves that for jeanne unchanged. For both accounts, connections are required to use SSL and each account can be used for a maximum of two simultaneous connections:

ALTER USER
  'jeffrey'@'localhost' IDENTIFIED BY 'new_password',
  'jeanne'@'localhost'
  REQUIRE SSL WITH MAX_USER_CONNECTIONS 2;

In the absence of a particular type of option, the account remains unchanged in that respect. For example, with no locking option, the locking state of the account is not changed.

There are several aspects to the ALTER USER statement, described under the following topics in this section:

Authentication Options

An account name may be followed by an authentication option that specifies the account authentication plugin, credentials, or both:

  • auth_plugin names an authentication plugin. The plugin name can be a quoted string literal or an unquoted name. Plugin names are stored in the plugin column of the mysql.user table.

  • 'auth_string' or 'hash_string' specifiy account credentials, either as cleartext or hashed in the format expected by the authentication plugin, respectively. Credentials are stored in the authentication_string column of the mysql.user table.

ALTER USER permits these auth_option syntaxes:

  • IDENTIFIED BY 'auth_string'

    Sets the account authentication plugin to the default plugin, hashes the cleartext 'auth_string' value, and stores the result in the mysql.user account row.

  • IDENTIFIED WITH auth_plugin

    Sets the account authentication plugin to auth_plugin, clears the credentials to the empty string (the credentials are associated with the old authentication plugin, not the new one), and stores the result in the mysql.user account row.

    In addition, the password is marked expired. The user must choose a new one when next connecting.

  • IDENTIFIED WITH auth_plugin BY 'auth_string'

    Sets the account authentication plugin to auth_plugin, hashes the cleartext 'auth_string' value, and stores the result in the mysql.user account row.

  • IDENTIFIED WITH auth_plugin AS 'hash_string'

    Sets the account authentication plugin to auth_plugin, takes the hashed 'hash_string' value as is, and stores the result in the mysql.user account row. The string is assumed to be already hashed in the format required by the plugin.

The default plugin is mysql_native_password unless the default_authentication_plugin system variable is set otherwise. For descriptions of each plugin, see Section 7.5.1, “Authentication Plugins”.

Example 1: Specify the password as cleartext; the default plugin is used:

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED BY 'mypass';

Example 2: Specify the authentication plugin, along with a cleartext password value:

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED WITH mysql_native_password
             BY 'mypass';

Example 3: Specify the authentication plugin, along with a hashed password value:

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED WITH mysql_native_password
             AS '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';
SSL/TLS Options

MySQL can check X509 certificate attributes in addition to the usual authentication that is based on the user name and credentials. For background information on the use of SSL with MySQL, see Section 7.4, “Using Secure Connections”.

To specify SSL-related options for a MySQL account, use a REQUIRE clause that specifies one or more tls_option values.

ALTER USER permits these tls_option values:

  • NONE

    Indicates that the account has no SSL or X509 requirements. Unencrypted connections are permitted if the user name and password are valid. However, encrypted connections can also be used, at the client's option, if the client has the proper certificate and key files.

    As of MySQL 5.7.3, a client need specify only the --ssl option to obtain an encrypted connection. The connection attempt fails if SSL is not available. Before MySQL 5.7.3, the client must specify either the --ssl-ca option, or all three of the --ssl-ca, --ssl-key, and --ssl-cert options.

  • SSL

    Tells the server to permit only encrypted connections for the account.

    ALTER USER 'jeffrey'@'localhost' REQUIRE SSL;
    

    As of MySQL 5.7.3, a client need specify only the --ssl option to obtain an encrypted connection. The connection attempt fails if SSL is not available. Before MySQL 5.7.3, the client must specify the --ssl-ca option to authenticate the server certificate, and may additionally specify the --ssl-key and --ssl-cert options. If neither the --ssl-ca option nor --ssl-capath option is specified, the client does not authenticate the server certificate.

  • X509

    Requires that the client must have a valid certificate but the exact certificate, issuer, and subject do not matter. The only requirement is that it should be possible to verify its signature with one of the CA certificates. Use of X509 certificates always implies encryption, so the SSL option is unnecessary in this case.

    ALTER USER 'jeffrey'@'localhost' REQUIRE X509;
    

    The client must specify the --ssl-key and --ssl-cert options to connect. (It is recommended but not required that --ssl-ca also be specified so that the public certificate provided by the server can be verified.) This is true for ISSUER and SUBJECT as well because those REQUIRE options imply the requirements of X509.

  • ISSUER 'issuer'

    Places the restriction on connection attempts that the client must present a valid X509 certificate issued by CA 'issuer'. If the client presents a certificate that is valid but has a different issuer, the server rejects the connection. Use of X509 certificates always implies encryption, so the SSL option is unnecessary in this case.

    Because ISSUER implies the requirements of X509, the client must specify the --ssl-key and --ssl-cert options to connect. (It is recommended but not required that --ssl-ca also be specified so that the public certificate provided by the server can be verified.)

    ALTER USER 'jeffrey'@'localhost'
      REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/
        O=MySQL/CN=CA/emailAddress=ca@example.com';
    
  • SUBJECT 'subject'

    Places the restriction on connection attempts that the client must present a valid X509 certificate containing the subject subject. If the client presents a certificate that is valid but has a different subject, the server rejects the connection. Use of X509 certificates always implies encryption, so the SSL option is unnecessary in this case.

    Because SUBJECT implies the requirements of X509, the client must specify the --ssl-key and --ssl-cert options to connect. (It is recommended but not required that --ssl-ca also be specified so that the public certificate provided by the server can be verified.)

    ALTER USER 'jeffrey'@'localhost'
      REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/
        O=MySQL demo client certificate/
        CN=client/emailAddress=client@example.com';
    

    MySQL does a simple string comparison of the 'subject' value to the value in the certificate, so lettercase and component ordering must be given exactly as present in the certificate.

    Note

    Regarding emailAddress, see the note in the description of REQUIRE ISSUER.

  • CIPHER 'cipher'

    Requests a specific cipher method for connections. This option is needed to ensure that ciphers and key lengths of sufficient strength are used. SSL itself can be weak if old algorithms using short encryption keys are used.

    ALTER USER 'jeffrey'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';
    

The SUBJECT, ISSUER, and CIPHER options can be combined in the REQUIRE clause:

ALTER USER 'jeffrey'@'localhost'
  REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/
    O=MySQL demo client certificate/
    CN=client/emailAddress=client@example.com'
  AND ISSUER '/C=SE/ST=Stockholm/L=Stockholm/
    O=MySQL/CN=CA/emailAddress=ca@example.com'
  AND CIPHER 'EDH-RSA-DES-CBC3-SHA';

The order of the options does not matter, but no option can be specified twice. The AND keyword is optional between REQUIRE options.

Resource-Limit Options

It is possible to place limits on use of server resources by an account, as discussed in Section 7.3.4, “Setting Account Resource Limits”. To do so, use a WITH clause that specifies one or more resource_option values:

ALTER USER permits these resource_option values:

  • MAX_QUERIES_PER_HOUR count, MAX_UPDATES_PER_HOUR count, MAX_CONNECTIONS_PER_HOUR count

    These options restrict the number of queries, updates, and connections to the server permitted to this account during any given one-hour period. (Queries for which results are served from the query cache do not count against the MAX_QUERIES_PER_HOUR limit.) If count is 0 (the default), this means that there is no limitation for the account.

  • MAX_USER_CONNECTIONS count

    Restricts the maximum number of simultaneous connections to the server by the account. A nonzero count specifies the limit for the account explicitly. If count is 0 (the default), the server determines the number of simultaneous connections for the account from the global value of the max_user_connections system variable. If max_user_connections is also zero, there is no limit for the account.

Example:

ALTER USER 'jeffrey'@'localhost'
  WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;

If a given resource limit is specified multiple times, the last instance takes precedence.

Password-Expiration Options

ALTER USER supports several password_option values for password expiration management, to either expire an account password or establish its password expiration policy. Policy options do not expire the password; instead, they determine how the server applies automatic expiration to the account (see Section 7.3.6, “Password Expiration Policy”).

The lifetime of a password is assessed from the date and time it was last changed.

ALTER USER permits these password_option values:

  • PASSWORD EXPIRE

    Expires the account password.

    ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;
    
  • PASSWORD EXPIRE DEFAULT

    Sets the account so that the global expiration policy applies, as specified by the default_password_lifetime system variable.

    ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;
    
  • PASSWORD EXPIRE NEVER

    Disables password expiration for the account so that its password never expires.

    ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
    
  • PASSWORD EXPIRE INTERVAL N DAY

    Sets the account password lifetime to N days. This statement requires the password to be changed every 180 days:

    ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;
    

If multiple password-expiration options are specified, the last one takes precedence.

A client session operates in restricted mode if the account password was expired manually or if the password is considered past its lifetime per the automatic expiration policy. In restricted mode, operations performed within the session result in an error until the user establishes a new account password. See Section 7.3.6, “Password Expiration Policy”.

Note

It is possible to reset a password by setting it to its current value. As a matter of good policy, it is preferable to choose a different password.

Account-Locking Options

MySQL supports account locking and unlocking using the ACCOUNT LOCK and ACCOUNT UNLOCK options, which specify the locking state for an account. For additional discussion, see Section 7.3.10, “User Account Locking”.

If multiple account-locking options are specified, the last one takes precedence.

ALTER USER Before MySQL 5.7.6

Each account name uses the format described in Section 7.2.3, “Specifying Account Names”. The host name part of the account name, if omitted, defaults to '%'. It is also possible to specify CURRENT_USER or CURRENT_USER() to refer to the account associated with the current session.

Each user value naming an account is followed by a password_option value that specifies the action to take for the account, to either expire an account password or establish its password expiration policy. Policy options do not expire the password; instead, they determine how the server applies automatic expiration to the account (see Section 7.3.6, “Password Expiration Policy”).

The password_option values are as described earlier in this section. The DEFAULT, NEVER, and INTERVAL variants of PASSWORD EXPIRE are not available before MySQL 5.7.4.

Before MySQL 5.7.3, it was possible to use ALTER USER to expire the password for anonymous-user accounts. This is no longer permitted because an anonymous user cannot reset the account password to lift the expiration.


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