Introduction to MySQL Connection Control Plugin

1. Introduction to the connection control (connection_control) plugin

The MySQL server contains a plug-in library that allows you to customize and install various plug-ins. The connection_control plug-in is one of them, which is mainly used to control the delay of the client's response after a certain number of consecutive login operations have failed. This plug-in can effectively prevent the risk of brute force login from the client. The plugin consists of the following two components:

• CONNECTION_CONTROL : Used to control the number of login failures and delayed response time.
• CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS : Log failed login attempts to the information_schema system database.

The base name of the connection control plugin file is connection_control . The file name suffix is ​​different for each platform (.so for Unix and Unix like systems, .dll for Windows ). The following takes Linux system as an example to install the connection_control plug-in. Windows system, you only need to change .so to .dll.

1.1 Dynamically install the connection_control plugin

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)

1.2 Verify plugin status

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 |
+------------------------------------------+---------------+

1.3 After the installation is complete, you can see the relevant system variables

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 installation of the plug-in is very simple, but what specific function does this plug-in have? Let's first explain the relevant system variables:

• connection_control_failed_connections_threshold : The number of consecutive failed attempts allowed for an account. The default value is 3, which means that connection control is enabled after 3 connection failures, and 0 means it is not enabled.
• connection_control_max_connection_delay : The maximum delay (in milliseconds) for connection failures that exceed the threshold, default 2147483647 milliseconds, about 25 days.
• connection_control_min_connection_delay : The minimum delay (in milliseconds) for a connection failure that exceeds the threshold. The default is 1000 milliseconds, or 1 second.
  

At this point, you may understand the role of the connection_control plug-in, which is that when the client fails to connect to the database for a certain number of consecutive times, 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 experimental effect, the failure threshold is set to 10 and the minimum delay time is set to 1 minute. That is, after ten consecutive connection failures, the minimum delay response time is 1 minute. Let's try it by deliberately entering the wrong password:

2.1 Initial State

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)

2.2 Deliberately entering the wrong password

[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)

2.3 View failure records

mysql> SELECT * FROM information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
+----------------+-----------------+
| USERHOST | FAILED_ATTEMPTS |
+----------------+-----------------+
| 'testuser'@'%' | 1 |
+----------------+-----------------+
1 row in set (0.00 sec)

# When the number of consecutive failures exceeds the threshold, there will be a delay when connecting again, that is, it will take a certain period of time to return whether the password is correct.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, if you enter the wrong password, an error message will be returned immediately. When the number of consecutive failures reaches the threshold, the next connection attempt will be delayed. The specific manifestation is that it will be stuck and the error message will not be returned until the delay is over. The tables in the information_schema system library record the usernames and failure times of failed logins. When a delay occurs, the delayed connection can also be found in processlist . If the password is entered correctly, the delay will be cancelled and the counting will be restarted.

Therefore, you should understand why this plug-in can prevent client-side brute force cracking. Assuming that brute force cracking attempts 120 times per minute, after enabling this plug-in, the response will be delayed after a certain number of consecutive failures, and the delay time will increase with the increase in the number of failures. The next cracking can be started immediately before, 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, you should pay attention to whether there are delayed connections, because delayed connections also occupy the number of connections, which may cause connection backlogs and lead to insufficient connections. Therefore, when a delayed connection occurs, you should quickly check where the connection is going and ensure that the password is entered correctly.

To enable this plugin, be sure to configure the appropriate threshold and delay time, and remember to write these parameters to the configuration file. Generally, there may be this requirement in the security assessment, and the connection control plug-in will be useful at this time.

This is the end of this article about the MySQL connection control plug-in. For more relevant MySQL connection control plug-in content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!