MySQL的Connection-Control插件

[[381645]]

本文转载自微信公众号「DBA闲思杂想录」，作者潇湘隐者 。转载本文请联系DBA闲思杂想录公众号。  

在Oracle和SQL Server数据库中，可以设置一些复杂的账号密码策略，例如在失败登录超过N次即可锁定账号，那么在MySQL中能否也有这样的功能呢?答案是MySQL也有类似这样的功能，只不过在MySQL中是在其登录失败超过阈值后，它是延迟响应时间，而不是锁定账号，在MySQL 5.6.35以后提供了Connection-Control插件用来控制客户端在登录操作连续失败一定次数后的响应延迟。该插件可有效的防止客户端暴力登录的风险(攻击)。该插件包含以下2个组件

• CONNECTION_CONTROL：用来控制登录失败的次数及延迟响应时间
• CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS：该表将登录失败的操作记录至INFORMATION_SCHEMA库中

下面我们介绍一下Connection-Control Plugins，下面实验的版本为MySQL 8.0.18。

首先查看系统变量plugin_dir，找到插件(Plugins)所在的路径，如下所示

mysql> select version() from dual; 
+-----------+ 
| version() | 
+-----------+ 
| 8.0.18    | 
+-----------+ 
1 row in set (0.00 sec) 
  
  
mysql> show variables like 'plugin_dir'; 
+---------------+--------------------------+ 
| Variable_name | Value                    | 
+---------------+--------------------------+ 
| plugin_dir    | /usr/lib64/mysql/plugin/ | 
+---------------+--------------------------+ 
1 row in set (0.01 sec) 
  
  
  
[root@KerryDB ~]# cd /usr/lib64/mysql/plugin/ 
[root@KerryDB plugin]# ls -lrt 
total 76388 
-rwxr-xr-x. 1 root root   106696 Sep 20  2019 rewrite_example.so 
-rwxr-xr-x. 1 root root   104432 Sep 20  2019 mysql_no_login.so 
-rwxr-xr-x. 1 root root   111464 Sep 20  2019 mypluglib.so 
-rwxr-xr-x. 1 root root   106648 Sep 20  2019 auth_socket.so 
-rwxr-xr-x. 1 root root   163560 Sep 20  2019 adt_null.so 
-rwxr-xr-x. 1 root root   409032 Sep 20  2019 validate_password.so 
-rwxr-xr-x. 1 root root  9054776 Sep 20  2019 libpluginmecab.so 
-rwxr-xr-x. 1 root root   344696 Sep 20  2019 authentication_ldap_sasl_client.so 
-rwxr-xr-x. 1 root root  1145832 Sep 20  2019 rewriter.so 
-rwxr-xr-x. 1 root root   625944 Sep 20  2019 ha_example.so 
-rwxr-xr-x. 1 root root   388992 Sep 20  2019 semisync_slave.so 
-rwxr-xr-x. 1 root root    32368 Sep 20  2019 component_log_sink_json.so 
-rwxr-xr-x. 1 root root   235200 Sep 20  2019 component_audit_api_message_emit.so 
-rwxr-xr-x. 1 root root   494720 Sep 20  2019 keyring_udf.so 
-rwxr-xr-x. 1 root root   149280 Sep 20  2019 component_log_sink_syseventlog.so 
-rwxr-xr-x. 1 root root  1446024 Sep 20  2019 semisync_master.so 
-rwxr-xr-x. 1 root root  2277480 Sep 20  2019 mysql_clone.so 
-rwxr-xr-x. 1 root root  1231376 Sep 20  2019 libmemcached.so 
-rwxr-xr-x. 1 root root   454096 Sep 20  2019 component_mysqlbackup.so 
-rwxr-xr-x. 1 root root   193336 Sep 20  2019 component_log_filter_dragnet.so 
-rwxr-xr-x. 1 root root  1177352 Sep 20  2019 ha_mock.so 
-rwxr-xr-x. 1 root root    83936 Sep 20  2019 locking_service.so 
-rwxr-xr-x. 1 root root  1269784 Sep 20  2019 connection_control.so 
-rwxr-xr-x. 1 root root  1280936 Sep 20  2019 innodb_engine.so 
-rwxr-xr-x. 1 root root   442304 Sep 20  2019 component_validate_password.so 
-rwxr-xr-x. 1 root root  1206024 Sep 20  2019 version_token.so 
-rwxr-xr-x. 1 root root  2338880 Sep 20  2019 keyring_file.so 
-rwxr-xr-x. 1 root root  2031912 Sep 20  2019 ddl_rewriter.so 
-rwxr-xr-x. 1 root root 49246400 Sep 20  2019 group_replication.so 
drwxr-xr-x. 2 root root     4096 Nov  6  2019 debug 

安装插件

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

检查是否安装成功

mysql> show plugins; 
  
mysql> SELECT PLUGIN_NAME, PLUGIN_LIBRARY, PLUGIN_STATUS, LOAD_OPTION  
    -> FROM INFORMATION_SCHEMA.PLUGINS  
    -> WHERE PLUGIN_LIBRARY = 'CONNECTION_CONTROL.SO'; 
+------------------------------------------+-----------------------+---------------+-------------+ 
| PLUGIN_NAME                              | PLUGIN_LIBRARY        | PLUGIN_STATUS | LOAD_OPTION | 
+------------------------------------------+-----------------------+---------------+-------------+ 
| CONNECTION_CONTROL                       | connection_control.so | ACTIVE        | FORCE       | 
| CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | connection_control.so | ACTIVE        | FORCE       | 
+------------------------------------------+-----------------------+---------------+-------------+ 
2 rows in set (0.00 sec) 
  
mysql>  

设置系统变量

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

• connection_control_failed_connections_threshold #登陆失败次数限制,默认值为3
• connection_control_max_connection_delay #限制重试时间最大值，单位为毫秒( milliseconds)，默认值2147483647
• connection_control_min_connection_delay #限制重试时间最小值，单位为毫秒( milliseconds)，默认值为1000毫秒，也就是1秒

注意事项：

1：connection_control_min_connection_delay的值必须小于connection_control_max_connection_delay，connection_control_max_connection_delay不能小于connection_control_min_connection_delay的值。

mysql> set global connection_control_min_connection_delay=60000; 
 
Query OK, 0 rows affected (0.00 sec) 

注意，命令方式设置全局系统变量在服务器重启后丢失，所以最好的方式在参数文件my.cnf设置全局系统变量

-- 配置文件增加以下配置

[mysqld]  
  
plugin-load-add = connection_control.so          #不是必须 
  
connection-control = FORCE                       #不是必须 
  
connection-control-failed-login-attempts = FORCE #不是必须 
  
connection_control_min_connection_delay = 60000  
  
connection_control_max_connection_delay = 1800000  
  
connection_control_failed_connections_threshold = 3 

三次连续输错密码后，就会在第四次输入密码后挂起

[root@lnx02 ~]# mysql -h 10.20.57.24 -u test -p 
Enter password:  
ERROR 1045 (28000): Access denied for user 'test'@'192.168.27.180' (using password: YES) 
[root@lnx02 ~]# mysql -h 10.20.57.24 -u test -p 
Enter password:  
ERROR 1045 (28000): Access denied for user 'test'@'192.168.27.180' (using password: YES) 
[root@lnx02 ~]# mysql -h 10.20.57.24 -u test -p 
Enter password:  
ERROR 1045 (28000): Access denied for user 'test'@'192.168.27.180' (using password: YES) 
[root@lnx02 ~]# mysql -h 10.20.57.24 -u test -p 
Enter password:  

注意，MySQL服务重启过后，INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS中的数据全部前空。

• The CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS plugin must be activated for this table to be available, and the CONNECTION_CONTROL plugin must be activated or the table contents will always be empty. See Section 6.4.2, “The Connection-Control Plugins”.

必须激活CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS插件才能使用该表CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS，并且要么激活CONNECTION_CONTROL插件，要么该表的内容始终为空。请参见第6.4.2节“连接控制插件”。

• The table contains rows only for clients that have had one or more consecutive failed connection attempts without a subsequent successful attempt. When a client connects successfully, its failed-connection count is reset to zero and the server removes any row corresponding to the client.

该表仅包含已进行一次或多次连续失败连接尝试而没有随后成功尝试的客户端的行。当客户端成功连接时，其失败连接计数将重置为零，并且服务器将删除与该客户端对应的任何行。

• Assigning a value to the connection_control_failed_connections_threshold system variable at runtime resets all accumulated failed-connection counters to zero, which causes the table to become empty.

在运行时为connection_control_failed_connections_threshold系统变量分配一个值会将所有累积的失败连接计数器重置为零，这将导致表变空。

解除账号延迟响应限制

方法1：重启MySQL实例

方法2：调整系统变量connection_control_failed_connections_threshold的值。

mysql> SELECT * FROM  
    -> INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS; 
+-------------------+-----------------+ 
| USERHOST          | FAILED_ATTEMPTS | 
+-------------------+-----------------+ 
| 'test'@'192.168%' |               5 | 
+-------------------+-----------------+ 
1 row in set (0.00 sec) 
  
  
mysql>  
mysql> set global connection_control_failed_connections_threshold=2; 
Query OK, 0 rows affected (0.00 sec) 
  
mysql> SELECT * FROM  
    -> INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS; 
Empty set (0.00 sec) 

3：卸载插件plugin

mysql> UNINSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS; 
 
mysql> UNINSTALL PLUGIN CONNECTION_CONTROL; 

注意事项：

关于Connection-Control的实现原理，淘宝数据库内核月报有篇文章分析过代码，介绍过实现原理，Connection-Control插件虽然可以防止恶意暴力破解MySQL账户，但是可能会浪费MySQL的资源;

• 比如如果短时间内有大量的恶意攻击，该插件虽然可以防止破解MySQL账户，但是会消耗主机资源(每一个连接创建一个线程);
• 如果这里使用了线程池，虽然可以避免消耗主机资源，但是等线程池中的线程被消耗光，再有新连接来就会拒绝服务。

参考资料：

https://dev.mysql.com/doc/refman/5.7/en/connection-control-installation.html

https://dev.mysql.com/doc/refman/5.7/en/connection-control-failed-login-attempts-table.html

https://dev.mysql.com/doc/refman/5.7/en/connection-control-variables.html