查询现有用户密码有效期
mysql> select user,host,password_expired,password_last_changed,password_lifetime from mysql.user;
+------------------+-----------+------------------+-----------------------+-------------------+
| user | host | password_expired | password_last_changed | password_lifetime |
+------------------+-----------+------------------+-----------------------+-------------------+
| test | % | N | 2023-12-26 18:58:59 | NULL |
| mysql.infoschema | localhost | N | 2023-12-26 18:58:49 | NULL |
| mysql.session | localhost | N | 2023-12-26 18:58:49 | NULL |
| mysql.sys | localhost | N | 2023-12-26 18:58:49 | NULL |
| root | localhost | N | 2023-12-26 18:58:59 | 90 |
+------------------+-----------+------------------+-----------------------+-------------------+
5 rows in set (0.00 sec)
设置root密码有效期90天
mysql> alter user root@'localhost' password expire interval 90 day;
Query OK, 0 rows affected (0.01 sec)
设置test密码有效期90天
mysql> alter user test@'%' password expire interval 90 day;
Query OK, 0 rows affected (0.00 sec)
查看test和root的密码有效期 均为90天
mysql> select user,host,password_expired,password_last_changed,password_lifetime from mysql.user;
+------------------+-----------+------------------+-----------------------+-------------------+
| user | host | password_expired | password_last_changed | password_lifetime |
+------------------+-----------+------------------+-----------------------+-------------------+
| test | % | N | 2023-12-26 18:58:59 | 90 |
| mysql.infoschema | localhost | N | 2023-12-26 18:58:49 | NULL |
| mysql.session | localhost | N | 2023-12-26 18:58:49 | NULL |
| mysql.sys | localhost | N | 2023-12-26 18:58:49 | NULL |
| root | localhost | N | 2023-12-26 18:58:59 | 90 |
+------------------+-----------+------------------+-----------------------+-------------------+
5 rows in set (0.00 sec)
本次不涉及.设置密码永不过期
alter user test@'%' password expire never;
查询密码策略
SHOW VARIABLES LIKE "%password%";
mysql> SHOW VARIABLES LIKE "%password%";
+----------------------------------------------+-----------------+
| Variable_name | Value |
+----------------------------------------------+-----------------+
| caching_sha2_password_auto_generate_rsa_keys | ON |
| caching_sha2_password_digest_rounds | 5000 |
| caching_sha2_password_private_key_path | private_key.pem |
| caching_sha2_password_public_key_path | public_key.pem |
| default_password_lifetime | 0 |
| disconnect_on_expired_password | ON |
| generated_random_password_length | 20 |
| mysql_native_password_proxy_users | OFF |
| password_history | 0 |
| password_require_current | OFF |
| password_reuse_interval | 0 |
| report_password | |
| sha256_password_auto_generate_rsa_keys | ON |
| sha256_password_private_key_path | private_key.pem |
| sha256_password_proxy_users | OFF |
| sha256_password_public_key_path | public_key.pem |
| 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 |
+----------------------------------------------+-----------------+
添加密码复杂度插件
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
显示已安装插件
show plugins;
修改my.cnf配置,#服务器在启动时加载插件,并防止在服务器运行时删除插件。并配置密码复杂度,修改后重启生效
[mysqld]
plugin-load-add=validate_password.so
validate-password=FORCE_PLUS_PERMANENT
validate_password_policy = MEDIUM
validate_password_length = 10
validate_password_number_count = 1
validate_password_special_char_count = 1
validate_password_mixed_case_count = 1
备注:临时修改复杂度策略,重启后失效
SET GLOBAL validate_password_policy = MEDIUM; -- 密码策略级别:LOW, MEDIUM, STRONG
SET GLOBAL validate_password_length = 10; -- 密码最小长度
SET GLOBAL validate_password_number_count = 1; -- 密码中至少包含的数字个数
SET GLOBAL validate_password_special_char_count = 1; -- 密码中至少包含的特殊字符个数
SET GLOBAL validate_password_mixed_case_count = 1; -- 密码中至少包含的大小写字母个数
SET GLOBAL validate_password_check_user_name = OFF; -- 是否禁止使用用户名作为密码的一部分(建议设置为OFF)