一、检车插件
# 安装前检查 为空则说明未安装此插件 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