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

MySQL SQL> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.19    |
+-----------+

Account lock

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

$ mysqlsh root@localhost:3306 --sql
...


MySQL localhost:3306 ssl SQL> 
CREATE USER aUser@localhost IDENTIFIED BY 'pAssw0rD' FAILED_LOGIN_ATTEMPTS 1 PASSWORD_LOCK_TIME 1;

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 :

MySQL localhost:3306 ssl SQL> 
SELECT user, host, User_attributes FROM mysql.user WHERE user = 'aUser'\G
*************************** 1. row ***************************
           user: aUser
           host: localhost
User_attributes: {"Password_locking": {"failed_login_attempts": 1, "password_lock_time_days": 1}}

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

MySQL localhost:3306 ssl SQL> 
\connect aUser@localhost:3306
Creating a session to 'aUser@localhost:3306'
Please provide the password for 'aUser@localhost:3306': *
MySQL Error 3955 (HY000): Access denied for user 'aUser'@'localhost'. Account is blocked for 1 day(s) (1 day(s) remaining) due to 1 consecutive failed logins.

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

$ mysqlsh root@localhost:3306 --sql


MySQL localhost:3306 ssl SQL> 
CREATE USER aUser2@localhost IDENTIFIED BY 'Dr0wssAp';


ALTER USER aUser2@localhost FAILED_LOGIN_ATTEMPTS 2 PASSWORD_LOCK_TIME UNBOUNDED;

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 :

MySQL localhost:3306 ssl SQL>
CREATE USER aLockedUser@localhost IDENTIFIED BY RANDOM PASSWORD ACCOUNT LOCK;
+-------------+-----------+----------------------+
| user        | host      | generated password   |
+-------------+-----------+----------------------+
| aLockedUser | localhost | @.Yp{;ONp7-G62+EfON1 |
+-------------+-----------+----------------------+

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 :

MySQL localhost:3306 ssl SQL>
SELECT user, host, account_locked FROM mysql.user WHERE user = 'aLockedUser';
+-------------+-----------+----------------+
| user        | host      | account_locked |
+-------------+-----------+----------------+
| aLockedUser | localhost | Y              |
+-------------+-----------+----------------+

Any connection to this account will raised error 3118 :

MySQL localhost:3306 ssl SQL>
\connect aLockedUser@localhost:3306 
Creating a session to 'aLockedUser@localhost:3306'
Please provide the password for 'aLockedUser@localhost:3306': ********************
MySQL Error 3118 (HY000): Access denied for user 'aLockedUser'@'localhost'. Account is locked.

This account can be activate with something like :

MySQL localhost:3306 ssl SQL> 
ALTER USER aLockedUser@localhost ACCOUNT UNLOCK FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME UNBOUNDED;

Again mysql.user table will give you some information :

MySQL localhost:3306 ssl SQL> 
SELECT user, host, account_locked, User_attributes FROM mysql.user WHERE user = 'aLockedUser'\G
*************************** 1. row ***************************
           user: aLockedUser
           host: localhost
 account_locked: N
User_attributes: {"Password_locking": {"failed_login_attempts": 5, "password_lock_time_days": -1}}

Account unlock

Account can be unlocked with an ALTER USER … ACCOUNT UNLOCK statement :

MySQL localhost:3306 ssl SQL>
\connect aUser@localhost:3306
Creating a session to 'aUser@localhost:3306'
Please provide the password for 'aUser@localhost:3306': 
MySQL Error 3955 (HY000): Access denied for user 'aUser'@'localhost'. Account is blocked for unlimited day(s) (unlimited day(s) remaining) due to 2 consecutive failed logins.


ALTER USER aUser@localhost ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.0047 sec)


\connect aUser@localhost:3306
Creating a session to 'aUser@localhost:3306'
Please provide the password for 'aUser@localhost:3306': ********
Closing old connection...
Your MySQL connection id is 63
...
SELECT USER();
+-----------------+
| USER()          |
+-----------------+
| aUser@localhost |
+-----------------+

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.

ALTER USER aUser@localhost FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1;

• 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 Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

Filed under:
MySQL, Security, Tuto by Olivier DASINI