MySQL5.6.6版本之后增加了密码复杂度验证插件validate_password,相关参数设置的较为严格。使用了该插件会检查设置的密码是否符合当前设置的强度规则,若不满足则拒绝设置。
1、密码验证插件安装
要使MySQL服务器可以使用,插件库文件必须位于MySQL插件目录(plugin_dir系统变量指定的目录)中。默认在MySQL安装目录下$MYSQL_HOME/lib/plugin/,如有必要,设置plugin_dir服务器启动时的值, 告知服务器插件目录位置。
插件库文件基本名称是 validate_password。文件名后缀因平台.so而异(例如,对于Unix和类Unix系统,.dll对于Windows)。
要在服务器启动时加载插件,请使用该 --plugin-load-add选项命名包含它的库文件。使用此插件加载方法,每次服务器启动时都必须提供该选项。例如,将这些行放在服务器my.cnf(my.ini)文件中(.so根据需要调整平台的 后缀):
Linux:
[mysqld]plugin-load-add=validate_password.so
windows:
[mysqld]plugin-load-add=validate_password.dll
修改my.cnf(my.ini)后,重新启动服务器使新设置生效。
或者,在运行时注册插件,请使用此语句(.so根据需要调整后缀):
添加:
Linux:
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Windows:
INSTALL PLUGIN validate_password SONAME 'validate_password.dll';
卸载:
UNINSTALL PLUGIN validate_password;
2、安装完成验证
如果未安装,通过select * from mysql.plugin;查询,列表未空。
root@localhost [mysql]>select * from mysql.plugin;Empty set (0.00 sec)
安装完成后查询:
root@localhost [mysql]>select * from mysql.plugin;+-------------------+----------------------+| name | dl |+-------------------+----------------------+| validate_password | validate_password.so |+-------------------+----------------------+1 row in set (0.00 sec)
INSTALL PLUGIN加载插件,并将其注册到mysql.plugins 系统表中,以便为每个后续的正常服务器启动加载插件。
验证插件安装,还可检查 INFORMATION_SCHEMA.PLUGINS表或使用该SHOW PLUGINS语句
root@localhost [(none)]>SELECT PLUGIN_NAME, PLUGIN_STATUS -> FROM INFORMATION_SCHEMA.PLUGINS -> WHERE PLUGIN_NAME LIKE 'validate%';+-------------------+---------------+| PLUGIN_NAME | PLUGIN_STATUS |+-------------------+---------------+| validate_password | ACTIVE |+-------------------+---------------+root@localhost [(none)]>show plugins;
3、查看MySQL全局参数策略配置
该问题其实与mysql的validate_password_policy的值有关。
查看一下msyql密码相关的几个全局参数:
root@localhost [(none)]>select @@validate_password_policy;+----------------------------+| @@validate_password_policy |+----------------------------+| MEDIUM |+----------------------------+1 row in set (0.00 sec)root@localhost [(none)]>SHOW VARIABLES LIKE 'validate_password%';+--------------------------------------+--------+| Variable_name | Value |+--------------------------------------+--------+| validate_password_check_user_name | OFF || validate_password_dictionary_file | || validate_password_length | 8 || validate_password_mixed_case_count | 1 || validate_password_number_count | 1 || validate_password_policy | MEDIUM || validate_password_special_char_count | 1 |+--------------------------------------+--------+
4、参数解释
validate_password_dictionary_file:插件用于验证密码强度的字典文件路径,策略为STRONG才需要。
validate_password_length:密码最小长度,参数默认为8,它有最小值的限制,最小值为:validate_password_number_count + validate_password_special_char_count + (2 * validate_password_mixed_case_count)
validate_password_mixed_case_count:密码至少要包含的小写字母个数和大写字母个数。
validate_password_number_count:密码至少要包含的数字个数。
validate_password_policy:密码强度检查等级,0/LOW、1/MEDIUM、2/STRONG。有以下取值:
Policy Tests Performed
0 or LOW Length
1 or MEDIUM Length; numeric, lowercase/uppercase, and special characters
2 or STRONG Length; numeric, lowercase/uppercase, and special characters; dictionary file
默认是1,即MEDIUM,所以刚开始设置的密码必须符合长度,且必须含有数字,小写或大写字母,特殊字符。
validate_password_special_char_count:密码至少要包含的特殊字符数。
5、验证密码负责度
root@localhost [(none)]>alter user 'sysdba'@'%' identified by '1234';ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
不符合策略无法修改。
root@localhost [(none)]>alter user 'sysdba'@'%' identified by 'Mysql!123';Query OK, 0 rows affected (0.00 sec)
修改成功。
6、改变密码策略,修改简单密码
root@localhost [(none)]>set global validate_password_policy=0;Query OK, 0 rows affected (0.00 sec)root@localhost [(none)]>set global validate_password_length=4;Query OK, 0 rows affected (0.00 sec)root@localhost [(none)]>set global validate_password_number_count=0;Query OK, 0 rows affected (0.00 sec)root@localhost [(none)]>set global validate_password_special_char_count=0;Query OK, 0 rows affected (0.00 sec)root@localhost [(none)]> set global validate_password_mixed_case_count=0;Query OK, 0 rows affected (0.00 sec)root@localhost [(none)]>SHOW VARIABLES LIKE 'validate_password%';+--------------------------------------+-------+| Variable_name | Value |+--------------------------------------+-------+| validate_password_check_user_name | OFF || validate_password_dictionary_file | || validate_password_length | 4 || validate_password_mixed_case_count | 0 || validate_password_number_count | 0 || validate_password_policy | LOW || validate_password_special_char_count | 0 |+--------------------------------------+-------+7 rows in set (0.00 sec)root@localhost [(none)]>alter user 'sysdba'@'%' identified by '1234';Query OK, 0 rows affected (0.00 sec)
验证登录:
[root@bogon ~]# mysql -usysdba -p1234mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.7.31-log MySQL Community Server (GPL)