MySQL的Connection-Control介绍
在Oracle和SQL Server数据库中，可以设置一些复杂的账号密码策略，例如在失败登录超过N次即可锁定账号，那么在MySQL中能否也有这样的功能呢？答案是MySQL也有类似这样的功能，只不过在MySQL中是在其登录失败超过阀值后，延迟响应时间，而不是锁定账号，在MySQL 5.7.17 以后提供了Connection-Control插件用来控制客户端在登录操作连续失败一定次数后的响应延迟。该插件可有效的防止客户端暴力登录的风险(攻击)。该插件包含以下2个组件
· CONNECTION_CONTROL：用来控制登录失败的次数及延迟响应时间
· CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS：该表将登录失败的操作记录至IS库中
下面我们介绍一下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) [ ~]# cd /usr/lib64/mysql/plugin/ [ 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
三次连续输错密码后，就会在第四次输入密码后挂起
[ ~]# 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) [ ~]# 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) [ ~]# 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) [ ~]# 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;
参考资料：
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