MySQL的Connection-Control插件
作者：瀟湘隱者
在Oracle和SQL Server數據庫中，可以設置一些復雜的賬號密碼策略，例如在失敗登錄超過N次即可鎖定賬號，那么在MySQL中能否也有這樣的功能呢?
本文轉載自微信公眾號「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
責任編輯：武曉燕
來源：
DBA閑思雜想錄
