dasini.net – Diary of a MySQL expert

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, User Account Locking, etc…

An ordinary threat databases could face is an attempt to discover the password by systematically trying every possible combination (letters, numbers, symbols). This is known as a brute force attack.
In this fourth episode of the MySQL Security series, we will see how the MySQL DBA can leverage the Connection-Control Plugins to slow down brute force attacks.

The Connection-Control Plugins

The MySQL Server includes a plugin library that enables administrators to introduce an increasing delay in server response to clients after a certain number of consecutive failed connection attempts. This capability provides a deterrent that slows down brute force attacks that attempt to access MySQL user accounts.

Installation

In MySQL 5.7, the Connection-Control plugin is not installed by default :

mysql> 
SELECT version();
+-----------+
| version() |
+-----------+
| 5.7.21    |
+-----------+

SELECT PLUGIN_NAME, PLUGIN_STATUS 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME LIKE 'connection%';
Empty set (0.00 sec)


SHOW VARIABLES LIKE 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+

The plugin library contains two plugins :

• CONNECTION_CONTROL checks incoming connections and adds a delay to server responses as necessary.
• CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS implements an INFORMATION_SCHEMA table that exposes more detailed monitoring information for failed connection attempts.

As usual, you can easily register the plugins at runtime with INSTALL PLUGIN statement :

mysql> 
INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
Query OK, 0 rows affected (0.02 sec)


INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
Query OK, 0 rows affected (0.00 sec)


SELECT PLUGIN_NAME, PLUGIN_STATUS 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME LIKE 'connection%';
+------------------------------------------+---------------+
| PLUGIN_NAME                              | PLUGIN_STATUS |
+------------------------------------------+---------------+
| CONNECTION_CONTROL                       | ACTIVE        |
| CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE        |
+------------------------------------------+---------------+

Alternatively you can modify the configuration file (my.cnf / my.ini) and then restart the server

[mysqld]
plugin-load-add=connection_control.so

If the plugins have been previously registered with INSTALL PLUGIN or are loaded with plugin-load-add, you can use the connection-control and connection-control-failed-login-attempts options at server startup to control plugin activation.

e.g. to load the plugins at startup and prevent them from being removed at runtime, use these options :

[mysqld]
plugin-load-add=connection_control.so
connection-control=FORCE_PLUS_PERMANENT
connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT

Configuration

To enable you to configure its operation, the CONNECTION_CONTROL plugin exposes 3 system variables :

• connection_control_failed_connections_threshold : The number of consecutive failed connection attempts permitted to clients before the server adds a delay for subsequent connection attempts.
• connection_control_min_connection_delay : The amount of delay to add for each consecutive connection failure above the threshold.
• connection_control_max_connection_delay : The maximum delay to add.

Note : To entirely disable checking for failed connection attempts, set connection_control_failed_connections_threshold to zero.

Default values are :

mysql> 
SHOW VARIABLES LIKE 'connection_control%';
+-------------------------------------------------+------------+
| Variable_name                                   | Value      |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 3          |
| connection_control_max_connection_delay         | 2147483647 |
| connection_control_min_connection_delay         | 1000       |
+-------------------------------------------------+------------+

You can modify these variables at runtime with SET GLOBAL :

mysql> 
SET GLOBAL connection_control_failed_connections_threshold = 2;
Query OK, 0 rows affected (0.00 sec)

 
SET GLOBAL connection_control_min_connection_delay = 1000;
Query OK, 0 rows affected (0.00 sec)

 
SHOW VARIABLES LIKE 'connection_control%';
+-------------------------------------------------+------------+
| Variable_name                                   | Value      |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 2          |
| connection_control_max_connection_delay         | 2147483647 |
| connection_control_min_connection_delay         | 1000       |
+-------------------------------------------------+------------+

Indeed they can be made persistent with the configuration file :

[mysqld]
plugin-load-add=connection_control.so
connection-control=FORCE_PLUS_PERMANENT
connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT
connection_control_failed_connections_threshold=2
connection_control_min_connection_delay=1000

Let’s test the Connection-Control plugin behavior with a threshold = 2 and a delay = 1 second :

$ time mysql -uroot -pWrongPwd
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

real	0m0.070s


$ time mysql -uroot -pWrongPwd
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

real	0m0.067s


$ time mysql -uroot -pWrongPwd
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

real	0m1.069s


$ time mysql -uroot -pWrongPwd
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

real	0m2.061s


$ time mysql -uroot -pWrongPwd
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

real	0m3.072s


$ time mysql -uroot -pWrongPwd
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

real	0m4.065s

Please focus on the command execution time i.e. real NmN.NNNs

Starting at the 3rd attempts the delay between each connection increase (approximately +1s (= 1000 ms) between each new failed connection attempts).

Monitoring

To monitor failed connections, use these information sources:

• The Connection_control_delay_generated status variable indicates the number of times the server added a delay to its response to a failed connection attempt. This does not count attempts that occur before reaching the threshold defined by the connection_control_failed_connections_threshold system variable.
• The INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS table provides information about the current number of consecutive failed connection attempts per client user/host combination. This counts all failed attempts, regardless of whether they were delayed.

mysql> 
SHOW STATUS LIKE 'connection_control%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 4     |
+------------------------------------+-------+


SELECT * FROM INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
+--------------------+-----------------+
| USERHOST           | FAILED_ATTEMPTS |
+--------------------+-----------------+
| 'root'@'localhost' |               6 |
+--------------------+-----------------+

You can easily monitor different user accounts :

$ time mysql -uWrongUser -pWrongPwd
ERROR 1045 (28000): Access denied for user 'WrongUser'@'localhost' (using password: YES)

real	0m0.065s


$ time mysql -uWrongUser -pWrongPwd
ERROR 1045 (28000): Access denied for user 'WrongUser'@'localhost' (using password: YES)

real	0m0.088s


$ time mysql -uWrongUser -pWrongPwd
ERROR 1045 (28000): Access denied for user 'WrongUser'@'localhost' (using password: YES)

real	0m1.063s


$ time mysql -uWrongUser -pWrongPwd
ERROR 1045 (28000): Access denied for user 'WrongUser'@'localhost' (using password: YES)

real	0m2.076s

Connection-Control failure monitoring :

mysql> 
SHOW STATUS LIKE 'connection_control%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 6     |
+------------------------------------+-------+


SELECT * FROM INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS ;
+-------------------------+-----------------+
| USERHOST                | FAILED_ATTEMPTS |
+-------------------------+-----------------+
| 'WrongUser'@'localhost' |               4 |
| 'root'@'localhost'      |               6 |
+-------------------------+-----------------+

Uninstalling Plugins

To remove the plugins, use the UNINSTALL PLUGIN statement :

• UNINSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
• UNINSTALL PLUGIN CONNECTION_CONTROL;

mysql> 
UNINSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
Query OK, 0 rows affected (0.01 sec)


UNINSTALL PLUGIN CONNECTION_CONTROL;
Query OK, 0 rows affected (0.01 sec)

Note : Update the configuration file (my.cnf / my.ini) if necessary

In order to go further

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 Connection-Control Plugins
• MySQL 5.7 Connection-Control Plugin Installation
• MySQL 5.7 Connection-Control System and Status Variables

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