MySQL密码安全

一、检车插件

# 安装前检查 为空则说明未安装此插件
mysql> show variables like 'validate%';
Empty set (0.00 sec)

 二、安装 validate_password 插件

# 通过 INSTALL PLUGIN 命令可安装此插件
# 每个平台的文件名后缀都不同 对于 Unix 和类 Unix 系统，为.so，对于 Windows 为.dll
mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected, 1 warning (0.28 sec)

# 查看 validate_password 相关参数
mysql> show variables like 'validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | ON     |
| 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.00 sec)

 三、设置密码详情

# 设置密码长度至少10位
mysql> set global validate_password_length = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'validate%';                                                                                   
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | ON     |
| validate_password_dictionary_file    |        |
| validate_password_length             | 10     |
| 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.00 sec)

# 若想永久生效，建议将以下参数写入配置文件
[mysqld]
plugin-load = validate_password.so
validate_password_length = 10
validate_password_policy = 1
validate-password = FORCE_PLUS_PERMANENT

 四、测试

# 新建用户设置密码
mysql> create user 'testuser'@'%' identified by '123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'testuser'@'%' identified by 'ab123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'testuser'@'%' identified by 'Ab@123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'testuser'@'%' identified by 'Bsdf@5467672';
Query OK, 0 rows affected (0.01 sec)

# 更改密码
mysql> alter user 'testuser'@'%' identified by 'dfgf3435';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> alter user 'testuser'@'%' identified by 'dBsdf@5467672';
Query OK, 0 rows affected (0.01 sec)

 五、设置密码有效时间（单个用户）

# 通过 mysql.user 系统表查看数据库账号状态
mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user;
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
| user             | host      | password_expired | password_lifetime | password_last_changed | account_locked |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
| expuser          | %         | N                |              NULL | 2021-01-05 14:30:30   | N              |
| root             | %         | N                |              NULL | 2020-10-30 14:45:43   | N              |
| testuser         | %         | N                |              NULL | 2021-01-04 17:22:37   | N              |
| mysql.infoschema | localhost | N                |              NULL | 2020-10-30 14:37:09   | Y              |
| mysql.session    | localhost | N                |              NULL | 2020-10-30 14:37:09   | Y              |
| mysql.sys        | localhost | N                |              NULL | 2020-10-30 14:37:09   | Y              |
| root             | localhost | N                |              NULL | 2020-10-30 14:38:55   | N              |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
7 rows in set (0.01 sec)

# 使 expuser 账号密码立即过期
mysql> ALTER USER 'expuser'@'%' PASSWORD EXPIRE;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user;
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
| user             | host      | password_expired | password_lifetime | password_last_changed | account_locked |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
| expuser          | %         | Y                |              NULL | 2021-01-05 14:30:30   | N              |
| root             | %         | N                |              NULL | 2020-10-30 14:45:43   | N              |
| testuser         | %         | N                |              NULL | 2021-01-04 17:22:37   | N              |
| mysql.infoschema | localhost | N                |              NULL | 2020-10-30 14:37:09   | Y              |
| mysql.session    | localhost | N                |              NULL | 2020-10-30 14:37:09   | Y              |
| mysql.sys        | localhost | N                |              NULL | 2020-10-30 14:37:09   | Y              |
| root             | localhost | N                |              NULL | 2020-10-30 14:38:55   | N              |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
7 rows in set (0.00 sec)

# 修改账号密码永不过期
mysql> ALTER USER 'expuser'@'%' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.01 sec)

# 单独设置该账号密码90天过期
mysql> ALTER USER 'expuser'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user;
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
| user             | host      | password_expired | password_lifetime | password_last_changed | account_locked |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
| expuser          | %         | N                |                90 | 2021-01-05 14:41:28   | N              |
| root             | %         | N                |              NULL | 2020-10-30 14:45:43   | N              |
| testuser         | %         | N                |              NULL | 2021-01-04 17:22:37   | N              |
| mysql.infoschema | localhost | N                |              NULL | 2020-10-30 14:37:09   | Y              |
| mysql.session    | localhost | N                |              NULL | 2020-10-30 14:37:09   | Y              |
| mysql.sys        | localhost | N                |              NULL | 2020-10-30 14:37:09   | Y              |
| root             | localhost | N                |              NULL | 2020-10-30 14:38:55   | N              |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
7 rows in set (0.00 sec)

# 让此账号使用默认的密码过期全局策略
mysql> ALTER USER 'expuser'@'%' PASSWORD EXPIRE DEFAULT;
Query OK, 0 rows affected (0.01 sec)

 六、设置全局过期策略

# 设置全局过期策略 先手动更改再加入配置文件
mysql> SET GLOBAL default_password_lifetime = 90;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 90    |
+---------------------------+-------+
1 row in set (0.00 sec)

# 写入配置文件使得重启生效
[mysqld]
default_password_lifetime = 90