Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.9Mb
PDF (A4) - 33.9Mb
PDF (RPM) - 31.9Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 145.9Kb
Man Pages (Zip) - 206.8Kb
Info (Gzip) - 3.1Mb
Info (Zip) - 3.1Mb


Pre-General Availability Draft: 2017-11-24

13.7.1.1 ALTER USER Syntax

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'

ALTER USER [IF EXISTS]
    user DEFAULT ROLE
    {NONE | ALL | role [, role ] ...}

user:
    (see Section 6.2.4, “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
  | PASSWORD HISTORY DEFAULT
  | PASSWORD HISTORY N
  | PASSWORD REUSE INTERVAL DEFAULT
  | PASSWORD REUSE INTERVAL N DAY
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

The ALTER USER statement modifies MySQL accounts. It enables authentication, SSL/TLS, resource-limit, and password-management properties to be modified for existing accounts, and enables account locking and unlocking.

Except for the DEFAULT ROLE syntax, ALTER USER requires the global CREATE USER privilege, or the UPDATE privilege for the mysql database. For DEFAULT ROLE, ALTER USER requires these privileges:

  • Setting the default roles for another user requires the global CREATE USER privilege, or the UPDATE privilege for the mysql.default_roles system table.

  • Setting the default roles for yourself requires no special privileges, as long as the roles you want as the default have been granted to you.

When the read_only system variable is enabled, ALTER USER additionally requires the CONNECTION_ADMIN or SUPER privilege.

By default, an error occurs if you try to modify a user that does not exist. If the IF EXISTS clause is given, the statement produces a warning for each named user that does not exist, rather than an error.

The statement is written to the binary log if it succeeds, but not if it fails; in that case, rollback occurs and no changes are made. A statement written to the binary log includes all named users. If the IF EXISTS clause is given, this includes even users that do not exist and were not altered.

If the original statement changes the credentials for a user, the statement written to the binary log specifies the applicable authentication plugin for that user, determined as follows:

  • The plugin named in the original statement, if one was specified.

  • Otherwise, the plugin associated with the user account if the user exists, or the default authentication plugin if the user does not exist. (If the statement written to the binary log must specify a particular authentication plugin for a user, include it in the original statement.)

If the server adds the default authentication plugin for any users in the statement written to the binary log, it writes a warning to the error log naming those users.

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

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

ALTER USER Overview

For each affected account, ALTER USER modifies the corresponding mysql.user table row to reflect the properties 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;

Each account name uses the format described in Section 6.2.4, “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 indicates how the account authenticates by specifying an account authentication plugin, credentials (for example, a password), or both. Each auth_option value applies only to the account named immediately preceding it.

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

Example: This statement alters two accounts, specifying some per-account properties and some global properties:

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

The auth_option value following jeffrey (IDENTIFIED BY) applies only to its immediately preceding account, so it changes the password only for jeffrey. For jeanne, there is no per-account value (thus leaving the password unchanged). The remaining properties apply globally to the statement. For both accounts, connections are required to use SSL and each account can be used for a maximum of two simultaneous connections:

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.

ALTER USER Authentication Options

An account name may be followed by an auth_option 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 system table.

    For auth_option syntaxes that do not specify an authentication plugin, the default plugin is indicated by the value of the default_authentication_plugin system variable. For descriptions of each plugin, see Section 6.5.1, “Authentication Plugins”.

  • Credentials are stored in the authentication_string column of the mysql.user system table. An 'auth_string' or 'hash_string' value specifies account credentials, either as a cleartext (unencrypted) string or hashed in the format expected by the authentication plugin associated with the account, respectively:

    • For syntaxes that use 'auth_string', the string is cleartext and is passed to the authentication plugin for possible hashing. The result returned by the plugin is stored in the authentication_string column. A plugin may use the value as specified, in which case no hashing occurs.

    • For syntaxes that use 'hash_string', the string is assumed to be already hashed in the format required by the authentication plugin. If the hash format is inappropriate for the plugin, it will not be usable and correct authentication of client connections will not occur.

ALTER USER permits these auth_option syntaxes:

  • IDENTIFIED BY 'auth_string'

    Sets the account authentication plugin to the default plugin, passes the cleartext 'auth_string' value to the plugin for hashing, 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, passes the cleartext 'auth_string' value to the plugin for hashing, 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 and stores the hashed 'hash_string' value as is in the mysql.user account row. The string is assumed to be already hashed in the format required by the plugin.

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

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

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

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

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

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED WITH mysql_native_password
             AS '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';

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

ALTER USER 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/TLS with MySQL, see Section 6.4, “Using Encrypted Connections”.

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

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

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. Encrypted connections can be used, at the client's option, if the client has the proper certificate and key files.

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

    Clients attempt to establish a secure connection by default. For clients that have REQUIRE NONE, the connection attempt falls back to an unencrypted connection if a secure connection cannot be established. To require an encrypted connection, a client need specify only the --ssl-mode=REQUIRED option; the connection attempt fails if a secure connection cannot be established.

  • SSL

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

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

    Clients attempt to establish a secure connection by default. For accounts that have REQUIRE SSL, the connection attempt fails if a secure connection cannot be established.

  • X509

    Requires that clients present 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;

    For accounts with REQUIRE X509, clients 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'

    Requires that clients present a valid X509 certificate issued by CA 'issuer'. If a 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.

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

    Because ISSUER implies the requirements of X509, clients 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.)

  • SUBJECT 'subject'

    Requires that clients present a valid X509 certificate containing the subject subject. If a 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.

    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.

    Because SUBJECT implies the requirements of X509, clients 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.)

  • CIPHER 'cipher'

    Requires a specific cipher method for encrypting connections. This option is needed to ensure that ciphers and key lengths of sufficient strength are used. Encryption 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';
ALTER USER Resource-Limit Options

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

Order of WITH options does not matter, except that if a given resource limit is specified multiple times, the last instance takes precedence.

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 how many queries, updates, and connections to the server are permitted to this account during any given one-hour period. 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;
ALTER USER Password-Management Options

ALTER USER supports several password_option values for password management:

  • Password expiration options: You can expire an account password manually and establish its password expiration policy. Policy options do not expire the password. Instead, they determine how the server applies automatic expiration to the account based on password age, which is assessed from the date and time of the most recent account password change.

  • Password reuse options: You can restrict password reuse based on number of password changes, time elapsed, or both.

Password expiration and reuse-restriction options apply to accounts that use a MySQL built-in authentication plugin (mysql_native_password, sha256_password, or caching_sha2_password). For accounts that use plugins that perform authentication against an external credential system, password expiration and reuse restrictions must be handled externally as well. For information about password management policy, see Section 6.3.8, “Password Management”.

A client has an expired password if the account password was expired manually or the password age is considered greater than its permitted lifetime per the automatic expiration policy. In this case, the server either disconnects the client or restricts the operations permitted to it (see Section 6.3.9, “Server Handling of Expired Passwords”). Operations performed by a restricted client result in an error until the user establishes a new account password.

ALTER USER permits these password_option values for password restriction:

  • PASSWORD EXPIRE

    Immediately 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;

ALTER USER permits these password_option values for controlling reuse of previous passwords based on required minimum number of password changes:

  • PASSWORD HISTORY DEFAULT

    Sets the account so that the global policy about password history length applies, to prohibit reuse of passwords before the number of changes specified by the password_history system variable.

    ALTER USER 'jeffrey'@'localhost' PASSWORD HISTORY DEFAULT;
  • PASSWORD HISTORY N

    Sets the account password history length to N passwords, to prohibit reusing any of the N most recently chosen passwords. This statement prohibits reuse of any of the previous 6 passwords:

    ALTER USER 'jeffrey'@'localhost' PASSWORD HISTORY 6;

ALTER USER permits these password_option values for controlling reuse of previous passwords based on time elapsed:

  • PASSWORD REUSE INTERVAL DEFAULT

    Sets the account so that the global policy about time elapsed applies, to prohibit reuse of passwords newer than the number of days specified by the password_reuse_interval system variable.

    ALTER USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL DEFAULT;
  • PASSWORD REUSE INTERVAL N DAY

    Sets the account password reuse interval to N days, to prohibit reuse of passwords newer than that many days. This statement prohibits password reuse for 360 days:

    ALTER USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL 360 DAY;

If multiple password-management options of a given type (PASSWORD EXPIRE, PASSWORD HISTORY, PASSWORD REUSE INTERVAL) are specified, the last one takes precedence.

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. DBAs can enforce non-reuse by establishing an appropriate password-reuse policy. See Password Reuse Policy.

ALTER USER 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 6.3.12, “User Account Locking”.

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

ALTER USER Role Options

ALTER USER ... DEFAULT ROLE defines which roles become active when the user connects to the server and authenticates, or when the user executes the SET ROLE DEFAULT statement during a session.

ALTER USER ... DEFAULT ROLE is alternative syntax for SET DEFAULT ROLE (see Section 13.7.1.9, “SET DEFAULT ROLE Syntax”). However, ALTER USER can set the default for only a single user, whereas SET DEFAULT ROLE can set the default for multiple users. On the other hand, you can specify CURRENT_USER as the user name for the ALTER USER statement, whereas you cannot for SET DEFAULT ROLE.

Each user account name uses the format described previously.

Each role name uses the format described in Section 6.2.5, “Specifying Role Names”. For example:

ALTER USER 'joe'@'10.0.0.1' DEFAULT ROLE administrator, developer;

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

The clause following the DEFAULT ROLE keywords permits these values:

  • NONE: Set the default to NONE (no roles).

  • ALL: Set the default to all roles granted to the account.

  • role [, role ] ...: Set the default to the named roles, which need not exist or be granted to the account at the time ALTER USER ... DEFAULT ROLE is executed.


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