dasini.net – Diary of a MySQL expert

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…

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.

---

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 USER … ACCOUNT 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

• Failed-Login Tracking and Temporary Account Locking

MySQL Security Serie (1st edition)

• Password Validation Plugin
• Password Management
• User Account Locking
• The Connection-Control Plugins
• Enterprise Audit
• Enterprise Transparent Data Encryption (TDE)
• Enterprise Firewall
• Enterprise Data Masking and De-Identification

Thanks for using MySQL!

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

Filed under:
MySQL, Security, Tuto by Olivier DASINI