8.2.15 Password Management

MySQL supports these password-management capabilities:

• Password expiration, to require passwords to be changed periodically.

• Password reuse restrictions, to prevent old passwords from being chosen again.

• Password verification, to require that password changes also specify the current password to be replaced.

• Dual passwords, to enable clients to connect using either a primary or secondary password.

• Password strength assessment, to require strong passwords.

• Random password generation, as an alternative to requiring explicit administrator-specified literal passwords.

• Password failure tracking, to enable temporary account locking after too many consecutive incorrect-password login failures.

The following sections describe these capabilities, except password strength assessment, which is implemented using the validate_password component and is described in Section 8.4.3, “The Password Validation Component”.

• Internal Versus External Credentials Storage

• Password Expiration Policy

• Password Reuse Policy

• Password Verification-Required Policy

• Dual Password Support

• Random Password Generation

• Failed-Login Tracking and Temporary Account Locking

Important

MySQL implements password-management capabilities using tables in the mysql system database. If you upgrade MySQL from an earlier version, your system tables might not be up to date. In that case, the server writes messages similar to these to the error log during the startup process (the exact numbers may vary):

[ERROR] Column count of mysql.user is wrong. Expected
49, found 47. The table is probably corrupted
[Warning] ACL table mysql.password_history missing.
Some operations may fail.

To correct the issue, perform the MySQL upgrade procedure. See Chapter 3, Upgrading MySQL. Until this is done, password changes are not possible.

Internal Versus External Credentials Storage

Some authentication plugins store account credentials internally to MySQL, in the mysql.user system table:

• caching_sha2_password

• mysql_native_password (deprecated)

• sha256_password (deprecated)

Most discussion in this section applies to such authentication plugins because most password-management capabilities described here are based on internal credentials storage handled by MySQL itself. Other authentication plugins store account credentials externally to MySQL. For accounts that use plugins that perform authentication against an external credentials system, password management must be handled externally against that system as well.

The exception is that the options for failed-login tracking and temporary account locking apply to all accounts, not just accounts that use internal credentials storage, because MySQL is able to assess the status of login attempts for any account no matter whether it uses internal or external credentials storage.

For information about individual authentication plugins, see Section 8.4.1, “Authentication Plugins”.

Password Expiration Policy

MySQL enables database administrators to expire account passwords manually, and to establish a policy for automatic password expiration. Expiration policy can be established globally, and individual accounts can be set to either defer to the global policy or override the global policy with specific per-account behavior.

To expire an account password manually, use the ALTER USER statement:

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

This operation marks the password expired in the corresponding row in the mysql.user system table.

Password expiration according to policy is automatic and is based on password age, which for a given account is assessed from the date and time of its most recent password change. The mysql.user system table indicates for each account when its password was last changed, and the server automatically treats the password as expired at client connection time if its age is greater than its permitted lifetime. This works with no explicit manual password expiration.

To establish automatic password-expiration policy globally, use the default_password_lifetime system variable. Its default value is 0, which disables automatic password expiration. If the value of default_password_lifetime is a positive integer N, it indicates the permitted password lifetime, such that passwords must be changed every N days.

Examples:

• To establish a global policy that passwords have a lifetime of approximately six months, start the server with these lines in a server my.cnf file:

  [mysqld]
  default_password_lifetime=180

• To establish a global policy such that passwords never expire, set default_password_lifetime to 0:

  [mysqld]
  default_password_lifetime=0

• default_password_lifetime can also be set and persisted at runtime:

  SET PERSIST default_password_lifetime = 180;
  SET PERSIST default_password_lifetime = 0;

  SET PERSIST sets a value for the running MySQL instance. It also saves the value to carry over to subsequent server restarts; see Section 15.7.6.1, “SET Syntax for Variable Assignment”. To change the value for the running MySQL instance without having it carry over to subsequent restarts, use the GLOBAL keyword rather than PERSIST.

The global password-expiration policy applies to all accounts that have not been set to override it. To establish policy for individual accounts, use the PASSWORD EXPIRE option of the CREATE USER and ALTER USER statements. See Section 15.7.1.3, “CREATE USER Statement”, and Section 15.7.1.1, “ALTER USER Statement”.

Example account-specific statements:

• Require the password to be changed every 90 days:

  CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
  ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

  This expiration option overrides the global policy for all accounts named by the statement.

• Disable password expiration:

  CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
  ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;

  This expiration option overrides the global policy for all accounts named by the statement.

• Defer to the global expiration policy for all accounts named by the statement:

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

When a client successfully connects, the server determines whether the account password has expired:

• The server checks whether the password has been manually expired.

• Otherwise, the server checks whether the password age is greater than its permitted lifetime according to the automatic password expiration policy. If so, the server considers the password expired.

If the password is expired (whether manually or automatically), 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:

mysql> SELECT 1;
ERROR 1820 (HY000): You must reset your password using ALTER USER
statement before executing this statement.

mysql> ALTER USER USER() IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

After the client resets the password, the server restores normal access for the session, as well as for subsequent connections that use the account. It is also possible for an administrative user to reset the account password, but any existing restricted sessions for that account remain restricted. A client using the account must disconnect and reconnect before statements can be executed successfully.

Note

Although it is possible to “reset” an expired password by setting it to its current value, it is preferable, as a matter of good policy, to choose a different password. DBAs can enforce non-reuse by establishing an appropriate password-reuse policy. See Password Reuse Policy.

Password Reuse Policy

MySQL enables restrictions to be placed on reuse of previous passwords. Reuse restrictions can be established based on number of password changes, time elapsed, or both. Reuse policy can be established globally, and individual accounts can be set to either defer to the global policy or override the global policy with specific per-account behavior.

The password history for an account consists of passwords it has been assigned in the past. MySQL can restrict new passwords from being chosen from this history:

• If an account is restricted on the basis of number of password changes, a new password cannot be chosen from a specified number of the most recent passwords. For example, if the minimum number of password changes is set to 3, a new password cannot be the same as any of the most recent 3 passwords.

• If an account is restricted based on time elapsed, a new password cannot be chosen from passwords in the history that are newer than a specified number of days. For example, if the password reuse interval is set to 60, a new password must not be among those previously chosen within the last 60 days.

Note

The empty password does not count in the password history and is subject to reuse at any time.

To establish password-reuse policy globally, use the password_history and password_reuse_interval system variables.

Examples:

• To prohibit reusing any of the last 6 passwords or passwords newer than 365 days, put these lines in the server my.cnf file:

  [mysqld]
  password_history=6
  password_reuse_interval=365

• To set and persist the variables at runtime, use statements like this:

  SET PERSIST password_history = 6;
  SET PERSIST password_reuse_interval = 365;

  SET PERSIST sets a value for the running MySQL instance. It also saves the value to carry over to subsequent server restarts; see Section 15.7.6.1, “SET Syntax for Variable Assignment”. To change the value for the running MySQL instance without having it carry over to subsequent restarts, use the GLOBAL keyword rather than PERSIST.

The global password-reuse policy applies to all accounts that have not been set to override it. To establish policy for individual accounts, use the PASSWORD HISTORY and PASSWORD REUSE INTERVAL options of the CREATE USER and ALTER USER statements. See Section 15.7.1.3, “CREATE USER Statement”, and Section 15.7.1.1, “ALTER USER Statement”.

Example account-specific statements:

• Require a minimum of 5 password changes before permitting reuse:

  CREATE USER 'jeffrey'@'localhost' PASSWORD HISTORY 5;
  ALTER USER 'jeffrey'@'localhost' PASSWORD HISTORY 5;

  This history-length option overrides the global policy for all accounts named by the statement.

• Require a minimum of 365 days elapsed before permitting reuse:

  CREATE USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
  ALTER USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;

  This time-elapsed option overrides the global policy for all accounts named by the statement.

• To combine both types of reuse restrictions, use PASSWORD HISTORY and PASSWORD REUSE INTERVAL together:

  CREATE USER 'jeffrey'@'localhost'
    PASSWORD HISTORY 5
    PASSWORD REUSE INTERVAL 365 DAY;
  ALTER USER 'jeffrey'@'localhost'
    PASSWORD HISTORY 5
    PASSWORD REUSE INTERVAL 365 DAY;

  These options override both global policy reuse restrictions for all accounts named by the statement.

• Defer to the global policy for both types of reuse restrictions:

  CREATE USER 'jeffrey'@'localhost'
    PASSWORD HISTORY DEFAULT
    PASSWORD REUSE INTERVAL DEFAULT;
  ALTER USER 'jeffrey'@'localhost'
    PASSWORD HISTORY DEFAULT
    PASSWORD REUSE INTERVAL DEFAULT;

Password Verification-Required Policy

It is possible to require that attempts to change an account password be verified by specifying the current password to be replaced. This enables DBAs to prevent users from changing a password without proving that they know the current password. Such changes could otherwise occur, for example, if one user walks away from a terminal session temporarily without logging out, and a malicious user uses the session to change the original user's MySQL password. This can have unfortunate consequences:

• The original user becomes unable to access MySQL until the account password is reset by an administrator.

• Until the password reset occurs, the malicious user can access MySQL with the benign user's changed credentials.

Password-verification policy can be established globally, and individual accounts can be set to either defer to the global policy or override the global policy with specific per-account behavior.

For each account, its mysql.user row indicates whether there is an account-specific setting requiring verification of the current password for password change attempts. The setting is established by the PASSWORD REQUIRE option of the CREATE USER and ALTER USER statements:

• If the account setting is PASSWORD REQUIRE CURRENT, password changes must specify the current password.

• If the account setting is PASSWORD REQUIRE CURRENT OPTIONAL, password changes may but need not specify the current password.

• If the account setting is PASSWORD REQUIRE CURRENT DEFAULT, the password_require_current system variable determines the verification-required policy for the account:

  • If password_require_current is enabled, password changes must specify the current password.

  • If password_require_current is disabled, password changes may but need not specify the current password.

In other words, if the account setting is not PASSWORD REQUIRE CURRENT DEFAULT, the account setting takes precedence over the global policy established by the password_require_current system variable. Otherwise, the account defers to the password_require_current setting.

By default, password verification is optional: password_require_current is disabled and accounts created with no PASSWORD REQUIRE option default to PASSWORD REQUIRE CURRENT DEFAULT.

The following table shows how per-account settings interact with password_require_current system variable values to determine account password verification-required policy.

Table 8.10 Password-Verification Policy

Per-Account Setting	password_require_current System Variable	Password Changes Require Current Password?
PASSWORD REQUIRE CURRENT	OFF	Yes
PASSWORD REQUIRE CURRENT	ON	Yes
PASSWORD REQUIRE CURRENT OPTIONAL	OFF	No
PASSWORD REQUIRE CURRENT OPTIONAL	ON	No
PASSWORD REQUIRE CURRENT DEFAULT	OFF	No
PASSWORD REQUIRE CURRENT DEFAULT	ON	Yes

Note

Privileged users can change any account password without specifying the current password, regardless of the verification-required policy. A privileged user is one who has the global CREATE USER privilege or the UPDATE privilege for the mysql system database.

To establish password-verification policy globally, use the password_require_current system variable. Its default value is OFF, so it is not required that account password changes specify the current password.

Examples:

• To establish a global policy that password changes must specify the current password, start the server with these lines in a server my.cnf file:

  [mysqld]
  password_require_current=ON

• To set and persist password_require_current at runtime, use a statement such as one of these:

  SET PERSIST password_require_current = ON;
  SET PERSIST password_require_current = OFF;

  SET PERSIST sets a value for the running MySQL instance. It also saves the value to carry over to subsequent server restarts; see Section 15.7.6.1, “SET Syntax for Variable Assignment”. To change the value for the running MySQL instance without having it carry over to subsequent restarts, use the GLOBAL keyword rather than PERSIST.

The global password verification-required policy applies to all accounts that have not been set to override it. To establish policy for individual accounts, use the PASSWORD REQUIRE options of the CREATE USER and ALTER USER statements. See Section 15.7.1.3, “CREATE USER Statement”, and Section 15.7.1.1, “ALTER USER Statement”.

Example account-specific statements:

• Require that password changes specify the current password:

  CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT;
  ALTER USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT;

  This verification option overrides the global policy for all accounts named by the statement.

• Do not require that password changes specify the current password (the current password may but need not be given):

  CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT OPTIONAL;
  ALTER USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT OPTIONAL;

  This verification option overrides the global policy for all accounts named by the statement.

• Defer to the global password verification-required policy for all accounts named by the statement:

  CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;
  ALTER USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;

Verification of the current password comes into play when a user changes a password using the ALTER USER or SET PASSWORD statement. The examples use ALTER USER, which is preferred over SET PASSWORD, but the principles described here are the same for both statements.

In password-change statements, a REPLACE clause specifies the current password to be replaced. Examples:

• Change the current user's password:

  ALTER USER USER() IDENTIFIED BY 'auth_string' REPLACE 'current_auth_string';

• Change a named user's password:

  ALTER USER 'jeffrey'@'localhost'
    IDENTIFIED BY 'auth_string'
    REPLACE 'current_auth_string';

• Change a named user's authentication plugin and password:

  ALTER USER 'jeffrey'@'localhost'
    IDENTIFIED WITH caching_sha2_password BY 'auth_string'
    REPLACE 'current_auth_string';

The REPLACE clause works like this:

• REPLACE 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.

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

• If REPLACE is specified, it must specify the correct current password, or an error occurs. This is true even if REPLACE is optional.

• REPLACE can be specified only when changing the account password for the current user. (This means that in the examples just shown, the statements that explicitly name the account for jeffrey fail unless the current user is jeffrey.) This is true even if the change is attempted for another user by a privileged user; however, such a user can change any password without specifying REPLACE.

• REPLACE is omitted from the binary log to avoid writing cleartext passwords to it.

Dual Password Support

User accounts are permitted to have dual passwords, designated as primary and secondary passwords. Dual-password capability makes it possible to seamlessly perform credential changes in scenarios like this:

• A system has a large number of MySQL servers, possibly involving replication.

• Multiple applications connect to different MySQL servers.

• Periodic credential changes must be made to the account or accounts used by the applications to connect to the servers.

Consider how a credential change must be performed in the preceding type of scenario when an account is permitted only a single password. In this case, there must be close cooperation in the timing of when the account password change is made and propagated throughout all servers, and when all applications that use the account are updated to use the new password. This process may involve downtime during which servers or applications are unavailable.

With dual passwords, credential changes can be made more easily, in phases, without requiring close cooperation, and without downtime:

1. For each affected account, establish a new primary password on the servers, retaining the current password as the secondary password. This enables servers to recognize either the primary or secondary password for each account, while applications can continue to connect to the servers using the same password as previously (which is now the secondary password).

2. After the password change has propagated to all servers, modify applications that use any affected account to connect using the account primary password.

3. After all applications have been migrated from the secondary passwords to the primary passwords, the secondary passwords are no longer needed and can be discarded. After this change has propagated to all servers, only the primary password for each account can be used to connect. The credential change is now complete.

MySQL implements dual-password capability with syntax that saves and discards secondary passwords:

• The RETAIN CURRENT PASSWORD clause for the ALTER USER and SET PASSWORD statements saves an account current password as its secondary password when you assign a new primary password.

• The DISCARD OLD PASSWORD clause for ALTER USER discards an account secondary password, leaving only the primary password.

Suppose that, for the previously described credential-change scenario, an account named 'appuser1'@'host1.example.com' is used by applications to connect to servers, and that the account password is to be changed from 'password_a' to 'password_b'.

To perform this change of credentials, use ALTER USER as follows:

1. On each server that is not a replica, establish 'password_b' as the new appuser1 primary password, retaining the current password as the secondary password:

   ALTER USER 'appuser1'@'host1.example.com'
     IDENTIFIED BY 'password_b'
     RETAIN CURRENT PASSWORD;

2. Wait for the password change to replicate throughout the system to all replicas.

3. Modify each application that uses the appuser1 account so that it connects to the servers using a password of 'password_b' rather than 'password_a'.

4. At this point, the secondary password is no longer needed. On each server that is not a replica, discard the secondary password:

   ALTER USER 'appuser1'@'host1.example.com'
     DISCARD OLD PASSWORD;

5. After the discard-password change has replicated to all replicas, the credential change is complete.

The RETAIN CURRENT PASSWORD and DISCARD OLD PASSWORD clauses have the following effects:

• RETAIN CURRENT PASSWORD retains an account current password as its secondary password, replacing any existing secondary password. The new password becomes the primary password, but clients can use the account to connect to the server using either the primary or secondary password. (Exception: If the new password specified by the ALTER USER or SET PASSWORD statement is empty, the secondary password becomes empty as well, even if RETAIN CURRENT PASSWORD is given.)

• If you specify RETAIN CURRENT PASSWORD for an account that has an empty primary password, the statement fails.

• If an account has a secondary password and you change its primary password without specifying RETAIN CURRENT PASSWORD, the secondary password remains unchanged.

• For ALTER USER, if you change the authentication plugin assigned to the account, the secondary password is discarded. If you change the authentication plugin and also specify RETAIN CURRENT PASSWORD, the statement fails.

• For ALTER USER, DISCARD OLD PASSWORD discards the secondary password, if one exists. The account retains only its primary password, and clients can use the account to connect to the server only with the primary password.

Statements that modify secondary passwords require these privileges:

• The APPLICATION_PASSWORD_ADMIN privilege is required to use the RETAIN CURRENT PASSWORD or DISCARD OLD PASSWORD clause for ALTER USER and SET PASSWORD statements that apply to your own account. The privilege is required to manipulate your own secondary password because most users require only one password.

• If an account is to be permitted to manipulate secondary passwords for all accounts, it should be granted the CREATE USER privilege rather than APPLICATION_PASSWORD_ADMIN.

Random Password Generation

The CREATE USER, ALTER USER, and SET PASSWORD statements have the capability of generating random passwords for user accounts, as an alternative to requiring explicit administrator-specified literal passwords. See the description of each statement for details about the syntax. This section describes the characteristics common to generated random passwords.

By default, generated random passwords have a length of 20 characters. This length is controlled by the generated_random_password_length system variable, which has a range from 5 to 255.

For each account for which a statement generates a random password, the statement stores the password in the mysql.user system table, hashed appropriately for the account authentication plugin. The statement also returns the cleartext password in a row of a result set to make it available to the user or application executing the statement. The result set columns are named user, host, generated password, and auth_factor indicating the user name and host name values that identify the affected row in the mysql.user system table, the cleartext generated password, and the authentication factor the displayed password value applies to.

mysql> CREATE USER
       'u1'@'localhost' IDENTIFIED BY RANDOM PASSWORD,
       'u2'@'%.example.com' IDENTIFIED BY RANDOM PASSWORD,
       'u3'@'%.org' IDENTIFIED BY RANDOM PASSWORD;
+------+---------------+----------------------+-------------+
| user | host          | generated password   | auth_factor |
+------+---------------+----------------------+-------------+
| u1   | localhost     | iOeqf>Mh9:;XD&qn(Hl} |           1 |
| u2   | %.example.com | sXTSAEvw3St-R+_-C3Vb |           1 |
| u3   | %.org         | nEVe%Ctw/U/*Md)Exc7& |           1 |
+------+---------------+----------------------+-------------+
mysql> ALTER USER
       'u1'@'localhost' IDENTIFIED BY RANDOM PASSWORD,
       'u2'@'%.example.com' IDENTIFIED BY RANDOM PASSWORD;
+------+---------------+----------------------+-------------+
| user | host          | generated password   | auth_factor |
+------+---------------+----------------------+-------------+
| u1   | localhost     | Seiei:&cw}8]@3OA64vh |           1 |
| u2   | %.example.com | j@&diTX80l8}(NiHXSae |           1 |
+------+---------------+----------------------+-------------+
mysql> SET PASSWORD FOR 'u3'@'%.org' TO RANDOM;
+------+-------+----------------------+-------------+
| user | host  | generated password   | auth_factor |
+------+-------+----------------------+-------------+
| u3   | %.org | n&cz2xF;P3!U)+]Vw52H |           1 |
+------+-------+----------------------+-------------+

A CREATE USER, ALTER USER, or SET PASSWORD statement that generates a random password for an account is written to the binary log as a CREATE USER or ALTER USER statement with an IDENTIFIED WITH auth_plugin AS 'auth_string', clause, where auth_plugin is the account authentication plugin and 'auth_string' is the account hashed password value.

If the validate_password component is installed, the policy that it implements has no effect on generated passwords. (The purpose of password validation is to help humans create better passwords.)

Failed-Login Tracking and Temporary Account Locking

Administrators can configure user accounts such that too many consecutive login failures cause temporary account locking.

“Login failure” in this context means failure of the client to provide a correct password during a connection attempt. It does not include failure to connect for reasons such as unknown user or network issues. For accounts that have dual passwords (see Dual Password Support), either account password counts as correct.

The required number of login failures and the lock time are configurable per account, using the FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME options of the CREATE USER and ALTER USER statements. Examples:

CREATE USER 'u1'@'localhost' IDENTIFIED BY 'password'
  FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 3;

ALTER USER 'u2'@'localhost'
  FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME UNBOUNDED;

When too many consecutive login failures occur, the client receives an error that looks like this:

ERROR 3957 (HY000): Access denied for user user.
Account is blocked for D day(s) (R day(s) remaining)
due to N consecutive failed logins.

Use the options as follows:

• FAILED_LOGIN_ATTEMPTS N

  This option indicates whether to track account login attempts that specify an incorrect password. The number N specifies how many consecutive incorrect passwords cause temporary account locking.

• PASSWORD_LOCK_TIME {N | UNBOUNDED}

  This option indicates how long to lock the account after too many consecutive login attempts provide an incorrect password. The value is a number N to specify the number of days the account remains locked, or UNBOUNDED to specify that when an account enters the temporarily locked state, the duration of that state is unbounded and does not end until the account is unlocked. The conditions under which unlocking occurs are described later.

Permitted values of N for each option are in the range from 0 to 32767. A value of 0 disables the option.

Failed-login tracking and temporary account locking have these characteristics:

• For failed-login tracking and temporary locking to occur for an account, its FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME options both must be nonzero.

• For CREATE USER, if FAILED_LOGIN_ATTEMPTS or PASSWORD_LOCK_TIME is not specified, its implicit default value is 0 for all accounts named by the statement. This means that failed-login tracking and temporary account locking are disabled.

• For ALTER USER, if FAILED_LOGIN_ATTEMPTS or PASSWORD_LOCK_TIME is not specified, its value remains unchanged for all accounts named by the statement.

• For temporary account locking to occur, password failures must be consecutive. Any successful login that occurs prior to reaching the FAILED_LOGIN_ATTEMPTS value for failed logins causes failure counting to reset. For example, if FAILED_LOGIN_ATTEMPTS is 4 and three consecutive password failures have occurred, one more failure is necessary for locking to begin. But if the next login succeeds, failed-login counting for the account is reset so that four consecutive failures are again required for locking.

• Once temporary locking begins, successful login cannot occur even with the correct password until either the lock duration has passed or the account is unlocked by one of the account-reset methods listed in the following discussion.

When the server reads the grant tables, it initializes state information for each account regarding whether failed-login tracking is enabled, whether the account is currently temporarily locked and when locking began if so, and the number of failures before temporary locking occurs if the account is not locked.

An account's state information can be reset, which means that failed-login counting is reset, and the account is unlocked if currently temporarily locked. Account resets can be global for all accounts or per account:

• A global reset of all accounts occurs for any of these conditions:

  • A server restart.

  • Execution of FLUSH PRIVILEGES. (Starting the server with --skip-grant-tables causes the grant tables not to be read, which disables failed-login tracking. In this case, the first execution of FLUSH PRIVILEGES causes the server to read the grant tables and enable failed-login tracking, in addition to resetting all accounts.)

• A per-account reset occurs for any of these conditions:

  • Successful login for the account.

  • The lock duration passes. In this case, failed-login counting resets at the time of the next login attempt.

  • Execution of an ALTER USER statement for the account that sets either FAILED_LOGIN_ATTEMPTS or PASSWORD_LOCK_TIME (or both) to any value (including the current option value), or execution of an ALTER USER ... UNLOCK statement for the account.

    Other ALTER USER statements for the account have no effect on its current failed-login count or its locking state.

Failed-login tracking is tied to the login account that is used to check credentials. If user proxying is in use, tracking occurs for the proxy user, not the proxied user. That is, tracking is tied to the account indicated by USER(), not the account indicated by CURRENT_USER(). For information about the distinction between proxy and proxied users, see Section 8.2.19, “Proxy Users”.