在Oracle和SQL Server資料庫中，可以設置一些複雜的賬號密碼策略，例如在失敗登錄超過N次即可鎖定賬號，那麼在MySQL中能否也有這樣的功能呢？答案是MySQL也有類似這樣的功能，只不過在MySQL中是在其登錄失敗超過閥值後，延遲響應時間，而不是鎖定賬號，在MySQL 5.7.17 以後提... ...
在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)
[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設置全局系統變數
-- 配置文件增加以下配置
