MySQL 8: Account Locking

As part of my ongoing focus on MySQL 8 user and password management, I’ve covered how the new dual passwords feature can reduce the overall DBA workload and streamline the management process (see MySQL 8: Dual Passwords). This wasn’t the only change to user/password management in MySQL 8; one of the more security-focused changes was the implementation of temporary account locking, first introduced in MySQL 8.0.19. With this feature, database administrators can now configure user accounts so that too many consecutive login failures can temporarily lock the account.

The account locking feature only applies to the failure of a client to provide a correct password during the connection attempt. It does not apply to failure to connect for other reasons (network issues, unknown user account, etc.). In the case of dual passwords, either of the account passwords that have been set would count as correct during successful authentication.

Usage of MySQL 8 Account Locking

Configurable options are FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME, both used with the CREATE USER and ALTER USER statements. A couple of usage examples are:

Once a user has been set up with these options, too many consecutive login failures will result in an error:

FAILED_LOGIN_ATTEMPTS N

This option determines whether or not to track account login attempts with an incorrect password. The number N specifies how many consecutive wrong password attempts will lock the account.

PASSWORD_LOCK_TIME (N | UNBOUNDED)

This option indicates how long an account will remain locked after too many consecutive incorrect password attempts. The number N specifies the number of days the account will remain locked. For a more permanent account lockout, setting this to UNBOUNDED stipulates that the duration of the locked state is now unbounded and does not end until the account is manually unlocked.

• For both options, permitted values for N are between 0 and 32,767. Note that if setting the value to 0, the option is disabled. 
• Note that for failed login tracking and locking to occur for an account, both of the above options must be set to a non-zero value. 
• When creating a new user, not specifying either of the above options, the implicit default value is 0 for any accounts named in the statement. 
  • In other words, failed login tracking and temporary account locking are disabled when these options are not specified. 
  • This also applies to any accounts created before the introduction of this feature. 
• When altering a user, not specifying either of the above options means the existing values will remain unchanged for all accounts named by the statement. 
• For temporary account locking to occur, the password failures must be consecutive. 
  • Any successful login before reaching the FAILED_LOGIN_ATTEMPTS value for that user will reset the failure counting. 
• Once an account has been temporarily locked, it is impossible to log in even with the correct password until either the lock duration has passed or the account is unlocked by one of the account reset methods below. 

Resetting Account Locks

State information for each account regarding failed login tracking, and account lock status, happens every time the server reads the grant tables. An account’s state information can be reset, resetting the failed-login count and unlocking the account if it is already locked. Account resets can be global for all accounts or limited to a single account. 

Global Reset

• A global reset of all accounts occur for any of the following conditions:
  • MySQL server restart.
  • Execution of FLUSH PRIVILEGES.

Single Account Reset

• A per-account reset occurs for any of the following conditions:
  • Successful login for the account.
  • Expiration of the lock duration.
    • Failed login counting resets and resumes at the time of the next login attempt.
  • Execution of an ALTER USER statement for the account that sets either of the above options (or both) to any value, or the execution of an ALTER USER … UNLOCK statement for the account.
    • No other ALTER USER statements have any effect on the failed-login counter or the account lock state.

Account Locking Behavior

The account locking state is recorded in the ‘account_locked’ column of the mysql.user system table. The output from SHOW CREATE USER indicates whether an account is locked or unlocked.

If a client attempts to connect to an account that is locked, the attempt will fail. In this case, the server also increments the ‘Locked_connects’ status variable that indicates the number of attempts to connect to a locked account, an error message is displayed, and the attempt is logged to the MySQL error log:

Locking an account does not affect connecting using a proxy user that assumes the identity of the locked account. It also does not affect the ability to execute stored procedures or views with the DEFINER attribute set to the locked account.

In Closing

While this is another relatively simple feature, it can significantly impact how your company manages the security aspects of failed login attempts. This leads to a more secure database environment and better client management overall. Having this ability has also proven helpful during maintenance operations to disable access from specific accounts or as a security measure to lock privileged accounts and unlock them only during application maintenance.

Not running MySQL 8 yet? A colleague pointed out that a similar functionality was available in earlier versions of MySQL by utilizing the Connection-Control Plugin. With this plugin, too many consecutive failed attempts would increase the delay in the server response to help avoid or minimize brute force attacks. If there is interest, I may cover this plugin in more detail in a future blog post. Let me know!

Complete the 2021 Percona Open Source Data Management Software Survey

Have Your Say!