MySQL Security – Failed-Login Tracking and Temporary Account Locking

May 12, 2020

When thinking about security within a MySQL installation, you can consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications.

MySQL provides many tools / features / plugins or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE)Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration Policy, Password Reuse Policy, Password Verification-Required Policy, Dual Password Support, Connection-Control Plugins, Password Validation Component, etc…

MySQL Security

Basic password policy practices teaches us :

  • Each user must have a password
  • A user’s password should be changed periodically

However, often this is unfortunately not enough.
Good news, MySQL 8.0 provide an easy way to increase database security with its failed-login tracking and temporary account locking feature.



TL;DR

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


Temporary Account Locking in MySQL


After a number of consecutive time when the client failed to provide a correct password during a connection attempt, the user account can be temporary locked.

The required number of failures and the lock time are configurable per account, using the FAILED_LOGIN_ATTEMPTS (track consecutive login failures) and PASSWORD_LOCK_TIME (how many days to lock the account).

Both are options of the CREATE USER and ALTER USER statements.


Let’s have a quick look using MySQL 8.0


Account lock

Create a user that would have his account locked for 1 day after 1 consecutive failed logins :

FAILED_LOGIN_ATTEMPTS : how many consecutive incorrect passwords cause temporary account locking.
A value of 0 disables the option.

PASSWORD_LOCK_TIME : number of days the account remains locked or UNBOUNDED (ie the duration of that state does not end until the account is unlocked).
A value of 0 disables the option.


We can see the user account details with mysql.user table :

If login failed a “FAILED_LOGIN_ATTEMPTS” number of time (1 time in this example), the account will be locked :


Tracking and locking could also be set up after the user creation :

In this example this user account will be locked (until the account is unlocked – more on that later) after 2 consecutive failed attempts.


You can also lock an account explicitly using ACCOUNT LOCK clause :

In this example I created a user account with a random password generated by MySQL. This account is created locked.

Details are visible with mysql.user table :

Any connection to this account will raised error 3118 :

This account can be activate with something like :

Again mysql.user table will give you some information :


Account unlock

Account can be unlocked with an ALTER USERACCOUNT UNLOCK statement :


Other possibilities to unlock an account are :

  • Execution of an ALTER USER statement for the account that sets either FAILED_LOGIN_ATTEMPTS or PASSWORD_LOCK_TIME (or both) to any value.
    e.g.
  • Obviously when the lock duration passes.
    In this case, failed-login counting resets at the time of the next login attempt.
  • Execution of FLUSH PRIVILEGES
  • A server restart



To Go Further

Reference Manual



MySQL Security Serie (1st edition)



Thanks for using MySQL!

Follow me on twitter

Watch my videos on my YouTube channel.

4 Responses to “MySQL Security – Failed-Login Tracking and Temporary Account Locking”

  1. […] If you are using MySQL 8.0, I would recommend you to read : MySQL Security – Failed-Login Tracking and Temporary Account Locking […]

  2. […] Firewall, Password Expiration Policy, Password Reuse Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control Plugins, Password Validation Component, […]

  3. […] Firewall, Random Password Generation, Password Reuse Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control Plugins, Password Validation Component, […]

  4. […] Random Password Generation, Password Expiration Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control Plugins, Password Validation Component, […]

Leave a Reply