preface:
Will MySQL restrict login when there are too many failures to connect to the database? How should the database server deal with violent cracking? This article introduces the connection control plug-in in MySQL. Let's learn the role of this plug-in.
1. Introduction to connection_control plug-in
MySQL server contains a plug-in library, which can customize and install various plug-ins. connection_ The control plug-in is also one of them. It is mainly used to control the response delay of the client after a certain number of consecutive login failures. The plug-in can effectively prevent the risk of violent login of the client. The plug-in contains the following two components:
- CONNECTION_CONTROL: used to control the number of login failures and delayed response time.
- CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS: log the operation of login failure to information_schema is stored in the system library.
The basic name of the connection control plug-in file is connection_control . File name suffixes vary from platform to platform (. so for Unix and Unix like systems,. dll for Windows). Next, take Linux system as an example to install connection_control plug-in, windows system only needs to change. so to. dll.
# Dynamic installation connection_control plug-in
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)
# Verify plug-in 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 |
+------------------------------------------+---------------+
# After installation, 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 plug-in installation is still very simple, but what is the function of this plug-in? Let's first explain the relevant system variables:
- connection_control_failed_connections_threshold: the number of consecutive failed attempts allowed for the account. The default value is 3, which means that connection control is enabled when the connection fails 3 times, and 0 means that it is not enabled.
- connection_control_max_connection_delay: the maximum delay (in milliseconds) of connection failure exceeding the threshold. The default is 2147483647 milliseconds, about 25 days.
- connection_control_min_connection_delay: the minimum delay (in milliseconds) of connection failure exceeding the threshold. The default is 1000 milliseconds, i.e. 1 second.
At this point, you may understand connection_ The function of the control plug-in is that when the client fails to connect to the database for a certain number of times, the server will delay the response for a period of time. The more consecutive failed attempts, the longer the response delay.
2. Connection control experiment
Let's do the next experiment. For the experimental effect, set the threshold of failure times 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. Let's deliberately enter the wrong password to try:
# 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) # Deliberately enter 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) # 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, connecting again will cause a delay, that is, whether the password is correct will be returned after a certain delay 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, the wrong password is returned immediately. When the number of consecutive failures reaches the threshold, the connection attempt again will delay the response. The specific performance is that it is stuck all the time, and the error will not be returned until the end of the delay. information_ The table in the schema system library will record the user name and failure times of login failure. When the delay occurs, you can also find the delayed connection from the processlist. If the password is entered correctly, the delay will be cancelled and the count will be counted again.
Therefore, you should understand why this plug-in can prevent the client from brute force cracking. Suppose that the brute force cracking attempts 120 times per minute. Now, after the plug-in is enabled, the response will be delayed after a certain number of consecutive failures, and the delay time will increase with the increase of the number of failures. It was originally possible to start the next cracking immediately, Now the next attempt can only be launched 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 are delayed connections, because the delayed connections also occupy the number of connections, which may cause connection backlog and insufficient connections. Therefore, when there is a delayed connection, you should check where the connection is as soon as possible to ensure that the password is entered correctly.
To enable this plug-in, pay attention to configuring the appropriate threshold and delay time, and remember to write these parameters to the configuration file. This requirement may be required for general warranty evaluation, and the connection control plug-in will be used at this time.