dasini.net – Diary of a MySQL expert

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

---

When thinking about security within a MySQL installation, you should 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 in order to protect your data including some advanced features like Transparent Data Encryption aka TDE, Audit, Data Masking & De-Identification, Firewall, Password Management, Password Validation Plugin, The Connection-Control Plugins, etc…

For security reasons some context require you to setup a user account locking policy. Thus an unauthorized user is not able (anymore) to login to the MySQL server. In this 3rd article of the MySQL Security series, we will see how to [un]lock a user account.

User Account Locking

MySQL supports locking and unlocking user accounts using the ACCOUNT LOCK and ACCOUNT UNLOCK clauses for the CREATE USER and ALTER USER statements:

• When used with CREATE USER, these clauses specify the initial locking state for a new account. In the absence of either clause, the account is created in an unlocked state.
• When used with ALTER USER, these clauses specify the new locking state for an existing account. In the absence of either clause, the account locking state remains unchanged.

So let’s create a user batchman { not this one 🙂 } with all privileges on sakila database:

mysql> 
select VERSION();
+-----------+
| version() |
+-----------+
| 5.7.21    |
+-----------+

CREATE USER batchman@localhost IDENTIFIED BY 'p4s5W0%d';

GRANT ALL ON sakila.* TO batchman@localhost;

Account locking state is recorded in the account_locked column of the mysql.user table:

mysql> 
SELECT user, host, account_locked FROM mysql.user WHERE user = 'batchman'\G
*************************** 1. row ***************************
          user: batchman
          host: localhost
account_locked: N

The output from SHOW CREATE USER indicates whether an account is locked or unlocked:

mysql> 
SHOW CREATE USER batchman@localhost\G
*************************** 1. row ***************************
CREATE USER for batchman@localhost: CREATE USER 'batchman'@'localhost' IDENTIFIED WITH 'mysql_native_password' 
AS '*15E0AF3C6647A20428477A460202CD7C89D78DDF' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK

Indeed accounts are unlock by default.

The new user account is allowed to connect to the server and can handle sakila’s objects:

$ mysql -u batchman -p

mysql_batchman> 
SELECT USER(); SHOW SCHEMAS;
+--------------------+
| USER()             |
+--------------------+
| batchman@localhost |
+--------------------+


+--------------------+
| Database           |
+--------------------+
| information_schema |
| sakila             |
+--------------------+

Lock account

What if your security policy requires you to lock this account?

ACCOUNT LOCK clause is the answer :

mysql> 
ALTER USER batchman@localhost ACCOUNT LOCK;


SELECT user, host, account_locked FROM mysql.user WHERE user = 'batchman';
+----------+-----------+----------------+
| user     | host      | account_locked |
+----------+-----------+----------------+
| batchman | localhost | Y              |
+----------+-----------+----------------+


mysql> 
SHOW CREATE USER batchman@localhost\G
*************************** 1. row ***************************
CREATE USER for batchman@localhost: CREATE USER 'batchman'@'localhost' 
IDENTIFIED WITH 'mysql_native_password' AS '*15E0AF3C6647A20428477A460202CD7C89D78DDF' 
REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK

batchman@localhost is now locked!

Any connection attempt with this user will failed :

-- User try to connect
$ mysql -u batchman -p 

ERROR 3118 (HY000): Access denied for user 'batchman'@'localhost'. Account is locked.

Note.

If the user is still connected you’ll need to kill the connection.

MySQL returns an ER_ACCOUNT_HAS_BEEN_LOCKED error.

The server increments the Locked_connects status variable that indicates the number of attempts to connect to a locked account and writes a message to the error log :

-- Back to the administrator session
mysql> 
SHOW GLOBAL STATUS LIKE 'Locked_connects';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Locked_connects | 1     |
+-----------------+-------+

-- In the error log
mysql> system tail -n1 /var/log/mysqld.log
2018-02-01T15:30:35.649393Z 65 [Note] Access denied for user 'batchman'@'localhost'. Account is locked.

In order to go further

• MySQL Security – Failed-Login Tracking and Temporary Account Locking

MySQL Security Series

1. Password Validation Plugin
2. Password Management
3. User Account Locking
4. The Connection-Control Plugins
5. Enterprise Audit
6. Enterprise Transparent Data Encryption (TDE)
7. Enterprise Firewall
8. Enterprise Data Masking and De-Identification

Reference Manual

• MySQL 5.7 User Account Locking
• CREATE USER Syntax

MySQL Security

• Security in MySQL
• MySQL 5.7 Secure Deployment Guide
• MySQL 5.7 Reference Manual – Security

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 by Olivier DASINI