mysql安全向加固validate_password和connection_control

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

mysql> select version() from dual;
5.7.27-log
mysql> show variables like 'plugin_dir';
plugin_dir  /usr/lib64/mysql/plugin/
[root@node3 ~]# ll /usr/lib64/mysql/plugin/
总用量 59436
-rwxr-xr-x 1 root root    96336 6月  10 2019 adt_null.so
-rwxr-xr-x 1 root root   349760 6月  10 2019 authentication_ldap_sasl_client.so
-rwxr-xr-x 1 root root    36336 6月  10 2019 auth_socket.so
-rwxr-xr-x 1 root root   933000 6月  10 2019 connection_control.so
drwxr-xr-x 2 root root     4096 2月   7 2021 debug
-rwxr-xr-x 1 root root 21655880 6月  10 2019 group_replication.so
-rwxr-xr-x 1 root root   473544 6月  10 2019 ha_example.so
-rwxr-xr-x 1 root root   961176 6月  10 2019 innodb_engine.so
-rwxr-xr-x 1 root root   957000 6月  10 2019 keyring_file.so
-rwxr-xr-x 1 root root   452832 6月  10 2019 keyring_udf.so
-rwxr-xr-x 1 root root  1177392 6月  10 2019 libmemcached.so
-rwxr-xr-x 1 root root  8966704 6月  10 2019 libpluginmecab.so
-rwxr-xr-x 1 root root    21424 6月  10 2019 locking_service.so
-rwxr-xr-x 1 root root    46712 6月  10 2019 mypluglib.so
-rwxr-xr-x 1 root root    33872 6月  10 2019 mysql_no_login.so
-rwxr-xr-x 1 root root 22233728 6月  10 2019 mysqlx.so
-rwxr-xr-x 1 root root    42280 6月  10 2019 rewrite_example.so
-rwxr-xr-x 1 root root   590904 6月  10 2019 rewriter.so
-rwxr-xr-x 1 root root   926624 6月  10 2019 semisync_master.so
-rwxr-xr-x 1 root root   152696 6月  10 2019 semisync_slave.so
-rwxr-xr-x 1 root root   202296 6月  10 2019 validate_password.so
-rwxr-xr-x 1 root root   499104 6月  10 2019 version_token.so

--------------------------------------------------------
mysql> select version() from dual;
8.0.23
mysql> show variables like 'plugin_dir';
plugin_dir  /usr/lib64/mysql/plugin/
[root@mysql8 ~]# ll /usr/lib64/mysql/plugin/
总用量 96728
-rwxr-xr-x. 1 root root   178600 12月 11 2020 adt_null.so
-rwxr-xr-x. 1 root root   661280 12月 11 2020 authentication_ldap_sasl_client.so
-rwxr-xr-x. 1 root root   117040 12月 11 2020 auth_socket.so
-rwxr-xr-x. 1 root root   273584 12月 11 2020 component_audit_api_message_emit.so
-rwxr-xr-x. 1 root root   199456 12月 11 2020 component_log_filter_dragnet.so
-rwxr-xr-x. 1 root root   469224 12月 11 2020 component_log_sink_json.so
-rwxr-xr-x. 1 root root   155160 12月 11 2020 component_log_sink_syseventlog.so
-rwxr-xr-x. 1 root root   539808 12月 11 2020 component_mysqlbackup.so
-rwxr-xr-x. 1 root root    39896 12月 11 2020 component_query_attributes.so
-rwxr-xr-x. 1 root root   928936 12月 11 2020 component_reference_cache.so
-rwxr-xr-x. 1 root root   511328 12月 11 2020 component_validate_password.so
-rwxr-xr-x. 1 root root  1351456 12月 11 2020 connection_control.so
-rwxr-xr-x. 1 root root  3200552 12月 11 2020 ddl_rewriter.so
drwxr-xr-x. 2 root root     4096 3月   8 2021 debug
-rwxr-xr-x. 1 root root 64278200 12月 11 2020 group_replication.so
-rwxr-xr-x. 1 root root   637960 12月 11 2020 ha_example.so
-rwxr-xr-x. 1 root root  1365312 12月 11 2020 ha_mock.so
-rwxr-xr-x. 1 root root  1312904 12月 11 2020 innodb_engine.so
-rwxr-xr-x. 1 root root  2699400 12月 11 2020 keyring_file.so
-rwxr-xr-x. 1 root root   552008 12月 11 2020 keyring_udf.so
-rwxr-xr-x. 1 root root  1286656 12月 11 2020 libmemcached.so
-rwxr-xr-x. 1 root root  9070376 12月 11 2020 libpluginmecab.so
-rwxr-xr-x. 1 root root    92840 12月 11 2020 locking_service.so
-rwxr-xr-x. 1 root root   117880 12月 11 2020 mypluglib.so
-rwxr-xr-x. 1 root root  2807760 12月 11 2020 mysql_clone.so
-rwxr-xr-x. 1 root root   114736 12月 11 2020 mysql_no_login.so
-rwxr-xr-x. 1 root root   117152 12月 11 2020 rewrite_example.so
-rwxr-xr-x. 1 root root  1582552 12月 11 2020 rewriter.so
-rwxr-xr-x. 1 root root  1711920 12月 11 2020 semisync_master.so
-rwxr-xr-x. 1 root root   795144 12月 11 2020 semisync_slave.so
-rwxr-xr-x. 1 root root   441344 12月 11 2020 validate_password.so
-rwxr-xr-x. 1 root root  1382792 12月 11 2020 version_token.so

安装插件

第一种方式：

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

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

mysql> install plugin validate_password soname 'validate_password.so';
Query OK, 0 rows affected, 1 warning (0.01 sec)

运行时注册插件,无需重启mysql。

第二种方式：
my.cnf配置文件添加，之后需要重启mysql

[mysqld]
plugin-load-add=validate_password.so
plugin-load-add = connection_control.so

检查插件是否安装成功

#使用 show plugins;查看安装组件中有无validate_password参数
mysql> show plugins;
+------------------------------------------+----------+--------------------+-----------------------+---------+
| Name                                     | Status   | Type               | Library               | License |
+------------------------------------------+----------+--------------------+-----------------------+---------+
| binlog                                   | ACTIVE   | STORAGE ENGINE     | NULL                  | GPL     |
| mysql_native_password                    | ACTIVE   | AUTHENTICATION     | NULL                  | GPL     |
| sha256_password                          | ACTIVE   | AUTHENTICATION     | NULL                  | GPL     |
| caching_sha2_password                    | ACTIVE   | AUTHENTICATION     | NULL                  | GPL     |
| sha2_cache_cleaner                       | ACTIVE   | AUDIT              | NULL                  | GPL     |
| daemon_keyring_proxy_plugin              | ACTIVE   | DAEMON             | NULL                  | GPL     |
| CSV                                      | ACTIVE   | STORAGE ENGINE     | NULL                  | GPL     |
| MEMORY                                   | ACTIVE   | STORAGE ENGINE     | NULL                  | GPL     |
| InnoDB                                   | ACTIVE   | STORAGE ENGINE     | NULL                  | GPL     |
| INNODB_TRX                               | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_CMP                               | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_CMP_RESET                         | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_CMPMEM                            | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_CMPMEM_RESET                      | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_CMP_PER_INDEX                     | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_CMP_PER_INDEX_RESET               | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_BUFFER_PAGE                       | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_BUFFER_PAGE_LRU                   | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_BUFFER_POOL_STATS                 | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_TEMP_TABLE_INFO                   | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_METRICS                           | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_FT_DEFAULT_STOPWORD               | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_FT_DELETED                        | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_FT_BEING_DELETED                  | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_FT_CONFIG                         | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_FT_INDEX_CACHE                    | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_FT_INDEX_TABLE                    | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_TABLES                            | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_TABLESTATS                        | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_INDEXES                           | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_TABLESPACES                       | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_COLUMNS                           | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_VIRTUAL                           | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_CACHED_INDEXES                    | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| INNODB_SESSION_TEMP_TABLESPACES          | ACTIVE   | INFORMATION SCHEMA | NULL                  | GPL     |
| MyISAM                                   | ACTIVE   | STORAGE ENGINE     | NULL                  | GPL     |
| MRG_MYISAM                               | ACTIVE   | STORAGE ENGINE     | NULL                  | GPL     |
| PERFORMANCE_SCHEMA                       | ACTIVE   | STORAGE ENGINE     | NULL                  | GPL     |
| TempTable                                | ACTIVE   | STORAGE ENGINE     | NULL                  | GPL     |
| ARCHIVE                                  | ACTIVE   | STORAGE ENGINE     | NULL                  | GPL     |
| BLACKHOLE                                | ACTIVE   | STORAGE ENGINE     | NULL                  | GPL     |
| FEDERATED                                | DISABLED | STORAGE ENGINE     | NULL                  | GPL     |
| ngram                                    | ACTIVE   | FTPARSER           | NULL                  | GPL     |
| mysqlx_cache_cleaner                     | ACTIVE   | AUDIT              | NULL                  | GPL     |
| mysqlx                                   | ACTIVE   | DAEMON             | NULL                  | GPL     |
| CONNECTION_CONTROL                       | ACTIVE   | AUDIT              | connection_control.so | GPL     |
| CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE   | INFORMATION SCHEMA | connection_control.so | GPL     |
| validate_password                        | ACTIVE   | VALIDATE PASSWORD  | validate_password.so  | GPL     |
+------------------------------------------+----------+--------------------+-----------------------+---------+
48 rows in set (0.00 sec)

最后三行就是新安装的插件
validate_password安装完默认值即为MEDIUM，通过show variables like 'validate_password%'; 查看已经开启密码策略。

# 查看 validate_password 的相关MySQL系统变量
mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | ON     |       检查密码与用户名是否相同或相反，on检查，off不检查。
| validate_password_dictionary_file    |        |       检查密码与密码字典,字典文件的路径
| validate_password_length             | 8      |       检查密码的最小长度，即密码长度必须大于或等于8
| validate_password_mixed_case_count   | 1      |       如果密码策略是中等或者更强的，validate_password要求密码具有的小写和大写字符的最小数量。对于给定的这个值密码必须有那么多小写字符和那么多大写字符。
| validate_password_number_count       | 1      |       密码必须包含的数字个数
| validate_password_policy             | MEDIUM |       密码强度等级：可以使用数字0、1、2或者相应的符号值LOW、MEDIUM、STRONG来指定。
0/LOW：只检查长度
1/MEDIUM：检查长度、数字、大小写、特殊字符
2/STRONG：检查长度、数字、大小写、特殊字符、字典文件。
| validate_password_special_char_count | 1      |      密码必须包含的特殊字符个数。
+--------------------------------------+--------+
7 rows in set (0.00 sec)

修改配置方式：

mysql> set global validate_password_mixed_case_count=2
注意
动态修改，不一定会直接影响到validate_password_length的长度，如果validate_password_length已经是最小值时才会被动态修改掉，否则不会。
注意
validate_password_number_count，validate_password_special_char_count，validate_password_mixed_case_count三个的改动只会直接修改掉已经处在最小值的valide_password_length的值。
mysql> set global validate_password_policy=2

修改my.cnf配置文件之后重启mysql

[mysqld]
validate_password = on
plugin-load=validate_password.so
validate_password_policy=2
#服务器在启动时加载插件，并防止在服务器运行时删除插件。
validate-password=FORCE_PLUS_PERMANENT

检查Connection-Control的相关MySQL系统变量

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        | ON          |
| CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | connection_control.so | ACTIVE        | ON          |
+------------------------------------------+-----------------------+---------------+-------------+
2 rows in set (0.00 sec)

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       |
+-------------------------------------------------+------------+
3 rows in set (0.01 sec)

connection_control_failed_connections_threshold
登陆失败次数限制,默认值为3
connection_control_max_connection_delay
限制重试时间最大值，单位为毫秒( milliseconds)，默认值2147483647
connection_control_min_connection_delay
限制重试时间最小值，单位为毫秒( milliseconds)，默认值为1000毫秒，也就是1秒
注意事项：
connection_control_min_connection_delay的值必须小于connection_control_max_connection_delay，connection_control_max_connection_delay不能小于connection_control_min_connection_delay的值。

设置Connection-Control的相关系统变量

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

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

修改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服务重启过后，INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS中的数据全部前空。

必须激活CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS插件才能使用该表CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS，并且要么激活CONNECTION_CONTROL插件，要么该表的内容始终为空。

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

在运行时为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> 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)

卸载插件plugin

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

卸载 validate_password 插件

### 卸载 validate_password  插件
mysql> UNINSTALL PLUGIN validate_password;

扩展

如果想关闭内置插件，比如MRG_MYISAM插件，配置如下：

[mysqld]
MRG_MYISAM=OFF
validate_password = OFF