2021年 · MySQL

MySQL 配置密码复杂度

一、mysql 5.7.35 版本信息
[root@localhost mysql]# mysql -V
mysql Ver 14.14 Distrib 5.7.35, for Linux (x86_64) using EditLine wrapper

二、配置或安装插件名”validate_password”
方法一:在/etc/my.cnf加上配置,并重启mysql,重启会导致业务中断
[mysqld]
plugin-load=validate_password.so
validate_password_policy=1
validate-password=FORCE_PLUS_PERMANENT

方法二:管理员登录数据库执行如下命令,此操作无需重启服务,可在线处理业务问题
MySQL [(none)]> INSTALL PLUGIN validate_password SONAME ‘validate_password.so’;
Query OK, 0 rows affected (0.00 sec)

注意:
1、validate_password_policy(命令设置:set global validate_password_policy=1;)
代表的密码策略,可配置的值有以下:默认是MEDIUM
— 0 or LOW 仅需需符合密码长度(由参数validate_password_length指定)
— 1 or MEDIUM 满足LOW策略,同时还需满足至少有1个数字,小写字母,大写字母和特殊字符
— 2 or STRONG 满足MEDIUM策略,同时密码不能存在字典文件(dictionary file)中

2、validate_password_dictionary_file
# 用于配置密码的字典文件,当validate_password_policy设置为STRONG时可以配置密码字典文件,字典文件中存在的密码不得使用。

3、validate_password_length(命令设置:set global validate_password_length=8;)
# 用来设置密码的最小长度,默认值是8最小是0

4、validate_password_mixed_case_count
# 当validate_password_policy设置为MEDIUM或者STRONG时,密码中至少同时拥有的小写和大写字母的数量,默认是1最小是0;默认是至少拥有一个小写和一个大写字母。

5、validate_password_number_count
# 当validate_password_policy设置为MEDIUM或者STRONG时,密码中至少拥有的数字的个数,默认1最小是0

6、validate_password_special_char_count
# 当validate_password_policy设置为MEDIUM或者STRONG时,密码中至少拥有的特殊字符的个数,默认1最小是0

三、登陆数据库查看

MySQL [(none)]> show variables like ‘validate%’;
+————————————–+——-+
| 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 | LOW |
| validate_password_special_char_count | 1 |
+————————————–+——-+
7 rows in set (0.01 sec)

MySQL [(none)]> grant all on oa_bak.* to ‘testuser’@’192.168.%’ identified by ‘123’;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
MySQL [(none)]> grant all on oa_bak.* to ‘testuser’@’192.168.%’ identified by ‘123456789’;
Query OK, 0 rows affected, 1 warning (0.01 sec)
=================以下为修改数据库密码长度为8位(默认)同时还需满足至少有1个数字,小写字母,大写字母和特殊字符=========
MySQL [(none)]> show variables like ‘validate%’;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 2
Current database: *** NONE ***

+————————————–+——–+
| 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 |
+————————————–+——–+
7 rows in set (0.01 sec)
#测试密码长度大于8位,但全部为数字,授权用户报错
MySQL [(none)]> grant all on oa_bak.* to ‘testuser’@’192.168.%’ identified by ‘123456789’;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
#测试密码长度大于8位,密码包括大小写字母、数字、特殊符号,授权用户成功
MySQL [(none)]> grant all on oa_bak.* to ‘testuser’@’192.168.%’ identified by ‘123us_UEj232’;
Query OK, 0 rows affected, 1 warning (0.00 sec)
#测试密码长度大于8位,密码包括大小写字母、数字、无特殊符号,授权用户报错
MySQL [(none)]> grant all on oa_bak.* to ‘testuser’@’192.168.%’ identified by ‘123usUEj232’;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements