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] ...
    [COMMENT 'comment_string' | ATTRIBUTE 'json_object']

ALTER USER [IF EXISTS]
    USER() user_func_auth_option

ALTER USER [IF EXISTS]
    user [registration_option]

ALTER USER [IF EXISTS]
    USER() [registration_option]

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

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

auth_option: {
    IDENTIFIED BY 'auth_string'
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED BY RANDOM PASSWORD
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
  | DISCARD OLD PASSWORD
  | ADD factor factor_auth_option [ADD factor factor_auth_option]
  | MODIFY factor factor_auth_option [MODIFY factor factor_auth_option]
  | DROP factor [DROP factor]
}

user_func_auth_option: {
    IDENTIFIED BY 'auth_string'
        [REPLACE 'current_auth_string']
        [RETAIN CURRENT PASSWORD]
  | DISCARD OLD PASSWORD
}

factor_auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

registration_option: {
    factor INITIATE REGISTRATION
  | factor FINISH REGISTRATION SET CHALLENGE_RESPONSE AS 'auth_string'
  | factor UNREGISTER
}

factor: {2 | 3} FACTOR

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 [DEFAULT | NEVER | INTERVAL N DAY]
  | PASSWORD HISTORY {DEFAULT | N}
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
  | FAILED_LOGIN_ATTEMPTS N
  | PASSWORD_LOCK_TIME {N | UNBOUNDED}
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

For each affected account, ALTER USER modifies the corresponding row in the mysql.user system table to reflect the properties specified in the statement. Unspecified properties retain their current values.

Each account name uses the format described in Section 8.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.

In one case 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. (The syntax also supports the REPLACE, RETAIN CURRENT PASSWORD, and DISCARD OLD PASSWORD clauses described at ALTER USER Authentication Options.)

For ALTER USER syntax that permits 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 accounts named in the statement.

Example: 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: Modify an account to use the caching_sha2_password authentication plugin and the given password. Require that a new password be chosen every 180 days, and enable failed-login tracking, such that three consecutive incorrect passwords cause temporary account locking for two days:

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'new_password'
  PASSWORD EXPIRE INTERVAL 180 DAY
  FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;

Example: Lock or unlock an account:

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

Example: 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;

Example: Alter multiple accounts, specifying some per-account properties and some global properties:

ALTER USER
  'jeffrey'@'localhost'
    IDENTIFIED BY 'jeffrey_new_password',
  'jeanne'@'localhost',
  'josh'@'localhost'
    IDENTIFIED BY 'josh_new_password'
    REPLACE 'josh_current_password'
    RETAIN CURRENT PASSWORD
  REQUIRE SSL WITH MAX_USER_CONNECTIONS 2
  PASSWORD HISTORY 5;

The IDENTIFIED BY value following jeffrey applies only to its immediately preceding account, so it changes the password to 'jeffrey_new_password' only for jeffrey. For jeanne, there is no per-account value (thus leaving the password unchanged). For josh, IDENTIFIED BY establishes a new password ('josh_new_password'), REPLACE is specified to verify that the user issuing the ALTER USER statement knows the current password ('josh_current_password'), and that current password is also retained as the account secondary password. (As a result, josh can connect with either the primary or secondary password.)

The remaining properties apply globally to all accounts named in the statement, so for both accounts:

Example: Discard the secondary password for josh, leaving the account with only its primary password:

ALTER USER 'josh'@'localhost' DISCARD OLD PASSWORD;

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.

An account name may be followed by an auth_option authentication option that specifies the account authentication plugin, credentials, or both. It may also include a password-verification clause that specifies the account current password to be replaced, and clauses that manage whether an account has a secondary password.

ALTER USER permits these auth_option syntaxes:

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

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

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

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

Example: Like the preceding example, but in addition, specify the current password as a cleartext value to satisfy any account requirement that the user making the change knows that password:

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

The preceding statement fails unless the current user is jeffrey because REPLACE is permitted only for changes to the current user's password.

Example: Establish a new primary password and retain the existing password as the secondary password:

ALTER USER 'jeffrey'@'localhost'
  IDENTIFIED BY 'new_password'
  RETAIN CURRENT PASSWORD;

Example: Discard the secondary password, leaving the account with only its primary password:

ALTER USER 'jeffery'@'localhost' DISCARD OLD PASSWORD;

Example: 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 8.2.14, “Assigning Account Passwords”, and Section 8.2.17, “Pluggable Authentication”.

ALTER USER has ADD, MODIFY, and DROP clauses that enable authentication factors to be added, modified, or dropped. In each case, the clause specifies an operation to perform on one authentication factor, and optionally an operation on another authentication factor. For each operation, the factor item specifies the FACTOR keyword preceded by the number 2 or 3 to indicate whether the operation applies to the second or third authentication factor. (1 is not permitted in this context. To act on the first authentication factor, use the syntax described in ALTER USER Authentication Options.)

ALTER USER multifactor authentication clause constraints are defined by the authentication_policy system variable. For example, the authentication_policy setting controls the number of authentication factors that accounts may have, and for each factor, which authentication methods are permitted. See Configuring the Multifactor Authentication Policy.

When ALTER USER adds, modifies, or drops second and third factors in a single statement, operations are executed sequentially, but if any operation in the sequence fails the entire ALTER USER statement fails.

For ADD, each named factor must not already exist or it cannot be added. For MODIFY and DROP, each named factor must exist to be modified or dropped. If a second and third factor are defined, dropping the second factor causes the third factor to take its place as the second factor.

This statement drops authentication factors 2 and 3, which has the effect of converting the account from 3FA to 1FA:

ALTER USER 'user' DROP 2 FACTOR 3 FACTOR;

For additional ADD, MODIFY, and DROP examples, see Getting Started with Multifactor Authentication.

For information about factor-specific rules that determine the default authentication plugin for authentication clauses that do not name a plugin, see The Default Authentication Plugin.

ALTER USER has clauses that enable FIDO/FIDO2 devices to be registered and unregistered. For more information, see Using WebAuthn Authentication, Device Unregistration for WebAuthn, and the mysql client --register-factor option description.

The mysql client --register-factor option, used for FIDO/FIDO2 device registration, causes the mysql client to generate and execute INITIATE REGISTRATION and FINISH REGISTRATION statements. These statements are not intended for manual execution.

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 15.7.1.9, “SET DEFAULT ROLE Statement”). 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 8.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:

MySQL can check X.509 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 8.3, “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:

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

It is possible to place limits on use of server resources by an account, as discussed in Section 8.2.21, “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:

Example:

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

ALTER USER supports several password_option values for password management:

This section describes the syntax for password-management options. For information about establishing policy for password management, see Section 8.2.15, “Password Management”.

If multiple password-management options of a given type are specified, the last one takes precedence. For example, PASSWORD EXPIRE DEFAULT PASSWORD EXPIRE NEVER is the same as PASSWORD EXPIRE NEVER.

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 8.2.16, “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 controlling password expiration:

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

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

ALTER USER permits these password_option values for controlling whether attempts to change an account password must specify the current password, as verification that the user attempting to make the change actually knows the current password:

ALTER USER permits these password_option values for controlling failed-login tracking:

For failed-login tracking and temporary locking to occur, an account's FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME options both must be nonzero. The following statement modifies an account such that it remains locked for two days after four consecutive password failures:

ALTER USER 'jeffrey'@'localhost'
  FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 2;

MySQL 8.3 supports user comments and user attributes, as described in Section 15.7.1.3, “CREATE USER Statement”. These can be modified employing ALTER USER by means of the COMMENT and ATTRIBUTE options, respectively. You cannot specify both options in the same ALTER USER statement; attempting to do so results in a syntax error.

The user comment and user attribute are stored in the Information Schema USER_ATTRIBUTES table as a JSON object; the user comment is stored as the value for a comment key in the ATTRIBUTE column of this table, as shown later in this discussion. The COMMENT text can be any arbitrary quoted text, and replaces any existing user comment. The ATTRIBUTE value must be the valid string representation of a JSON object. This is merged with any existing user attribute as if the JSON_MERGE_PATCH() function had been used on the existing user attribute and the new one; for any keys that are re-used, the new value overwrites the old one, as shown here:

mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
    ->     WHERE USER='bill' AND HOST='localhost';
+------+-----------+----------------+
| USER | HOST      | ATTRIBUTE      |
+------+-----------+----------------+
| bill | localhost | {"foo": "bar"} |
+------+-----------+----------------+
1 row in set (0.11 sec)

mysql> ALTER USER 'bill'@'localhost' ATTRIBUTE '{"baz": "faz", "foo": "moo"}';
Query OK, 0 rows affected (0.22 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
    ->     WHERE USER='bill' AND HOST='localhost';
+------+-----------+------------------------------+
| USER | HOST      | ATTRIBUTE                    |
+------+-----------+------------------------------+
| bill | localhost | {"baz": "faz", "foo": "moo"} |
+------+-----------+------------------------------+
1 row in set (0.00 sec)

To remove a key and its value from the user attribute, set the key to JSON null (must be lowercase and unquoted), like this:

mysql> ALTER USER 'bill'@'localhost' ATTRIBUTE '{"foo": null}';
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
    ->     WHERE USER='bill' AND HOST='localhost';
+------+-----------+----------------+
| USER | HOST      | ATTRIBUTE      |
+------+-----------+----------------+
| bill | localhost | {"baz": "faz"} |
+------+-----------+----------------+
1 row in set (0.00 sec)

To set an existing user comment to an empty string, use ALTER USER ... COMMENT ''. This leaves an empty comment value in the USER_ATTRIBUTES table; to remove the user comment completely, use ALTER USER ... ATTRIBUTE ... with the value for the column key set to JSON null (unquoted, in lower case). This is illustrated by the following sequence of SQL statements:

mysql> ALTER USER 'bill'@'localhost' COMMENT 'Something about Bill';
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
    ->     WHERE USER='bill' AND HOST='localhost';
+------+-----------+---------------------------------------------------+
| USER | HOST      | ATTRIBUTE                                         |
+------+-----------+---------------------------------------------------+
| bill | localhost | {"baz": "faz", "comment": "Something about Bill"} |
+------+-----------+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER USER 'bill'@'localhost' COMMENT '';
Query OK, 0 rows affected (0.09 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
    ->     WHERE USER='bill' AND HOST='localhost';
+------+-----------+-------------------------------+
| USER | HOST      | ATTRIBUTE                     |
+------+-----------+-------------------------------+
| bill | localhost | {"baz": "faz", "comment": ""} |
+------+-----------+-------------------------------+
1 row in set (0.00 sec)

mysql> ALTER USER 'bill'@'localhost' ATTRIBUTE '{"comment": null}';
Query OK, 0 rows affected (0.07 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
    ->     WHERE USER='bill' AND HOST='localhost';
+------+-----------+----------------+
| USER | HOST      | ATTRIBUTE      |
+------+-----------+----------------+
| bill | localhost | {"baz": "faz"} |
+------+-----------+----------------+
1 row in set (0.00 sec)

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 8.2.20, “Account Locking”.

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

ALTER USER ... ACCOUNT UNLOCK unlocks any account named by the statement that is temporarily locked due to too many failed logins. See Section 8.2.15, “Password Management”.

ALTER USER 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:

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.

If the original statement specifies the FAILED_LOGIN_ATTEMPTS or PASSWORD_LOCK_TIME option, the statement written to the binary log includes the option.

ALTER USER statements with clauses that support multifactor authentication (MFA) are written to the binary log with the exception of ALTER USER user factor INITIATE REGISTRATION statements.