Introduction to MySQL connection control plugin

Foreword:

When there are too many failed connections to the database, will MySQL restrict logins? How should the database server respond to brute force cracking? This article introduces the connection control plug-in in MySQL, let's learn the role of this plug-in together.

1. Introduction of connection control (connection_control) plug-in

The MySQL server includes a plug-in library, which can be customized to install various plug-ins. The connection_control plug-in is also one of them, which is mainly used to control the delay of the client's response after a certain number of consecutive failed login operations. The plug-in can effectively prevent the risk of violent login on the client side. The plug-in contains the following two components:

• CONNECTION_CONTROL : used to control the number of login failures and delay response time.
• CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS : Log the failed login operation to the information_schema system library.

The basic name of the connection control plug-in file is connection_control. The file name suffix is different for each platform (.so for Unix and Unix-like systems, and .dll for Windows). Let's take the Linux system as an example to install the connection_control plug-in. For the Windows system, you only need to change .so to .dll.

# 动态安装 connection_control 插件
mysql> INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
Query OK, 0 rows affected (0.04 sec)

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

# 验证插件状态
mysql> 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        |
+------------------------------------------+---------------+

# 安装完成后 可以看到相关系统变量
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       |
+-------------------------------------------------+------------+

It can be seen that the plug-in installation is still very simple, but what is the specific role of this plug-in? Let us first explain the relevant system variables:

• connection_control_failed_connections_threshold : The number of consecutive failed attempts allowed by the account. The default is 3, which means that the connection control is enabled when the connection fails 3 times, and 0 means not to enable it.
• connection_control_max_connection_delay : The maximum delay (in milliseconds) for connection failures that exceed the threshold. The default is 2147483647 milliseconds, which is about 25 days.
• connection_control_min_connection_delay : The minimum delay (in milliseconds) for connection failures that exceed the threshold. The default is 1000 milliseconds, which is 1 second.

At this point, you may understand the role of the connection_control plug-in, that is, when the client fails to connect to the database for a certain number of consecutive failures, the server will delay the response for a period of time. The more consecutive failed attempts, the longer the response delay time.

2. Connection control experiment

Let's do a specific experiment. For the sake of experimentation, here we set the failure threshold to 10 and the minimum delay time to 1 minute. That is, after ten consecutive connection failures, the minimum delay response time is 1 minute. We will deliberately enter the error below. Try the password:

# 初始状态
mysql> show variables like 'connection_control%';
+-------------------------------------------------+------------+
| Variable_name                                   | Value      |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 10         |
| connection_control_max_connection_delay         | 2147483647 |
| connection_control_min_connection_delay         | 60000      |
+-------------------------------------------------+------------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
Empty set (0.00 sec)

# 故意输错密码
[root@localhost ~]# mysql -utestuser -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'testuser'@'localhost' (using password: YES)

# 查看失败记录
mysql> SELECT * FROM information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
+----------------+-----------------+
| USERHOST       | FAILED_ATTEMPTS |
+----------------+-----------------+
| 'testuser'@'%' |               1 |
+----------------+-----------------+
1 row in set (0.00 sec)

# 当连续失败次数超过阈值后 再次进行连接会产生延迟 即延迟一定时间后才会返回密码是否正确
mysql> SELECT * FROM information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
+----------------+-----------------+
| USERHOST       | FAILED_ATTEMPTS |
+----------------+-----------------+
| 'testuser'@'%' |              10 |
+----------------+-----------------+
mysql> show processlist;
+---------+----------+--------------------+--------------------+---------+-------+--------------------------------------+------------------+
| Id      | User     | Host               | db                 | Command | Time  | State                                | Info             |
+---------+----------+--------------------+--------------------+---------+-------+--------------------------------------+------------------+
| 1817003 | root     | localhost          | NULL               | Query   |     0 | starting                             | show processlist |
| 1817091 | testuser | localhost          | NULL               | Connect |    16 | Waiting in connection_control plugin | NULL             |
+---------+----------+--------------------+--------------------+---------+-------+--------------------------------------+------------------+

Under normal circumstances, entering the wrong password will return an error immediately. When the number of consecutive failures reaches the threshold, if the connection attempt is made again, the response will be delayed. The specific manifestation is that it has been stuck until the delay is over. The error is returned. The table in the information_schema system library will record the user name and the number of failed logins. When the delay occurs, the delayed connection can also be found from the processlist. If the password is entered correctly, the delay will be cancelled and the count will be restarted.

Ever since, you should understand why this plug-in can prevent client brute-force cracking. Assuming that brute-force cracking attempts 120 times per minute, now that the plug-in is enabled, the response will be delayed after a certain number of consecutive failures, and the delay will be delayed as the number of failures increases. The time will also increase. Originally, the next crack can be started immediately, but now the next attempt can only be initiated after the delay time, so the risk of brute force cracking can be greatly reduced.

However, after enabling the connection control plug-in, pay attention to whether there is a delayed connection, because the delayed connection also occupies the number of connections, which may cause a backlog of connections and cause the number of connections to be insufficient. So when there is a delayed connection, you should find out where the connection is as soon as possible to ensure that the password is entered correctly.

To enable this plug-in, pay attention to configure the appropriate threshold and delay time, and remember to write these parameters into the configuration file. Generally, there may be such a requirement in the evaluation of the waiting guarantee, at this time the connection control plug-in will be used.