MySQL的密码策略设置 | 生产级配置指南，安全合规一步到位！你必须要知道！

最新推荐文章于 2026-08-23 09:35:19 发布

原创最新推荐文章于 2026-08-23 09:35:19 发布 · 184 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

收录于

MySQL必知必会

【客户需求】

Oracle数据库可以设置密码策略为180天后自动过期，MySQL如何设置呢？

以下的方法，经过验证，生产环境直接可以用！

【设置方法】

1. 创建新用户时设置密码过期策略

-- 查看全局密码过期设置
SHOW VARIABLES LIKE 'default_password_lifetime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 0     |
+---------------------------+-------+

分两种情况

1) 创建用户,但是不设置过期策略
CREATE USER 'wewin'@'%' IDENTIFIED BY 'wewin';
CREATE USER 'wewin'@'localhost' IDENTIFIED BY 'wewin';

2) 创建用户,且设置过期策略
CREATE USER 'wewin180'@'%' IDENTIFIED BY 'wewin' PASSWORD EXPIRE INTERVAL 180 DAY;

如果用户已经存在，且修改过期策略
-- 设置wewin用户密码180天后过期
ALTER USER 'wewin180'@'%' PASSWORD EXPIRE INTERVAL 180 DAY;

2. 查看详细的密码过期设置

SELECT 
    user, 
    host,
    account_locked,
    password_expired,
    password_last_changed,
    password_lifetime,
    DATE_ADD(password_last_changed, INTERVAL password_lifetime DAY) as password_expiry_date
FROM mysql.user 
order by user, host;
+------------------+-----------+----------------+------------------+-----------------------+-------------------+----------------------+
| user             | host      | account_locked | password_expired | password_last_changed | password_lifetime | password_expiry_date |
+------------------+-----------+----------------+------------------+-----------------------+-------------------+----------------------+
| ABC              | %         | N              | N                | 2025-07-01 11:11:30   |              NULL | NULL                 |
| DEF              | %         | N              | N                | 2025-07-01 12:20:13   |              NULL | NULL                 |
| mysql.infoschema | localhost | Y              | N                | 2023-05-04 13:27:59   |              NULL | NULL                 |
| mysql.session    | localhost | Y              | N                | 2023-05-04 13:27:59   |              NULL | NULL                 |
| repuser          | %         | N              | N                | 2025-05-29 14:25:42   |              NULL | NULL                 |
| root             | %         | N              | N                | 2023-05-04 13:47:17   |              NULL | NULL                 |
| root             | localhost | N              | N                | 2025-01-10 23:39:42   |              NULL | NULL                 |
| testuser         | localhost | N              | N                | 2025-01-11 15:42:18   |              NULL | NULL                 |
| wewin            | %         | N              | N                | 2025-07-01 16:44:56   |              NULL | NULL                 |
| wewin            | localhost | N              | N                | 2026-07-01 03:46:15   |              NULL | NULL                 |
| wewin180         | %         | N              | N                | 2025-07-01 16:52:00   |               180 | 2025-12-28 16:52:00  |
+------------------+-----------+----------------+------------------+-----------------------+-------------------+----------------------+
11 rows in set (0.00 sec)

3. 测试过期后的情况

将测试环境的系统时间修改到180天

date -s 'Wed Jul 01 03:40:39 CST 2026'

1）重新登录
错误号：[1862]
错误消息：Your password has expired. To log in you must change it using a client that supports expired passwords.

2）已经连接的会话在执行操作的时候提示密码已过期
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

ALTER USER 'wewin180'@'%' IDENTIFIED BY 'wewin180';

其生效机制是：

计时起点：从用户最近一次密码更改的时间（记录在mysql.user表的password_last_changed字段）开始计时。
自动评估：每次客户端连接时，服务器会自动评估密码的已使用时间是否超过了password_lifetime（本例为180天）。
过期标记：如果超过，服务器会在本次会话中将该密码视为“已过期”。

修改密码后继续查看

SELECT 
    user, 
    host,
    account_locked,
    password_expired,
    password_last_changed,
    password_lifetime,
    DATE_ADD(password_last_changed, INTERVAL password_lifetime DAY) as password_expiry_date
FROM mysql.user 
order by user, host;
+------------------+-----------+----------------+------------------+-----------------------+-------------------+----------------------+
| user             | host      | account_locked | password_expired | password_last_changed | password_lifetime | password_expiry_date |
+------------------+-----------+----------------+------------------+-----------------------+-------------------+----------------------+
| ABC              | %         | N              | N                | 2025-07-01 11:11:30   |              NULL | NULL                 |
| DEF              | %         | N              | N                | 2025-07-01 12:20:13   |              NULL | NULL                 |
| mysql.infoschema | localhost | Y              | N                | 2023-05-04 13:27:59   |              NULL | NULL                 |
| mysql.session    | localhost | Y              | N                | 2023-05-04 13:27:59   |              NULL | NULL                 |
| repuser          | %         | N              | N                | 2025-05-29 14:25:42   |              NULL | NULL                 |
| root             | %         | N              | N                | 2023-05-04 13:47:17   |              NULL | NULL                 |
| root             | localhost | N              | N                | 2025-01-10 23:39:42   |              NULL | NULL                 |
| testuser         | localhost | N              | N                | 2025-01-11 15:42:18   |              NULL | NULL                 |
| wewin            | %         | N              | N                | 2025-07-01 16:44:56   |              NULL | NULL                 |
| wewin            | localhost | N              | N                | 2026-07-01 03:46:15   |              NULL | NULL                 |
| wewin180         | %         | N              | N                | 2026-07-01 03:53:22   |               180 | 2026-12-28 03:53:22  |
+------------------+-----------+----------------+------------------+-----------------------+-------------------+----------------------+
11 rows in set (0.00 sec)

3. 其他密码策略

3.1 设置全面的密码策略

-- 1. 设置密码过期时间
ALTER USER 'wewin'@'%' PASSWORD EXPIRE INTERVAL 180 DAY;

-- 2. 设置密码历史（防止重复使用最近6个密码）
ALTER USER 'wewin'@'%' PASSWORD HISTORY 6;
例如：
ALTER USER 'wewin'@'%' IDENTIFIED BY 'wewin180' REPLACE 'wewin181';
ERROR 3638 (HY000): Cannot use these credentials for 'wewin@%' because they contradict the password history policy

-- 3. 设置密码重用间隔（90天内不能重用）
ALTER USER 'wewin'@'%' PASSWORD REUSE INTERVAL 90 DAY;

-- 4. 要求密码验证（修改密码时需要提供旧密码）
ALTER USER 'wewin'@'%' PASSWORD REQUIRE CURRENT;
说明：
如果用root登录可以直接改，但是用wewin登录后，修改会提示
mysql> ALTER USER 'wewin'@'%' IDENTIFIED BY 'wewin181';
ERROR 3892 (HY000): Current password needs to be specified in the REPLACE clause in order to change it.
ALTER USER 'wewin'@'%' IDENTIFIED BY 'OKGO123' REPLACE 'wewin180';
Query OK, 0 rows affected (0.10 sec)

-- 5. 设置失败登录锁定策略（5次失败锁定3天）
ALTER USER 'wewin'@'%' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 3;
一次性设置所有策略
ALTER USER 'wewin'@'%'
PASSWORD EXPIRE INTERVAL 180 DAY
PASSWORD HISTORY 6
PASSWORD REUSE INTERVAL 90 DAY
PASSWORD REQUIRE CURRENT
FAILED_LOGIN_ATTEMPTS 5 
PASSWORD_LOCK_TIME 3;

3.2 使用全局默认策略

-- 首先设置全局默认密码过期时间（可选）
SET GLOBAL default_password_lifetime = 180;

-- 然后设置用户遵循全局策略
ALTER USER 'wewin'@'%' PASSWORD EXPIRE DEFAULT;

-- 与所有密码相关参数
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  |
+----------------------------------------------+-----------------+
16 rows in set (0.00 sec)

标签

#mysql #数据库