MySQL 账号密码永不过期为什么不起作用？

原创

月饮沙 2023-08-01 15:12:50 博主文章分类：MySQL ©著作权

©著作权归作者所有：来自51CTO博客作者月饮沙的原创作品，请联系作者获取转载授权，否则将追究法律责任

背景

客户反馈MySQL账号已经设置成密码永不过期了，但是在登录后总是提示报错 ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

排查方法

首先检查一下MySQL服务器设置的密码过期时间,可以看到默认密码60天就会过期

mysql> show variables like '%lifetime%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 60    |
+---------------------------+-------+
1 row in set (0.01 sec)

然后检查一下mysql.user表，看看用户说提示报错的账号的配置

mysql> select user,host,password_expired,password_last_changed,password_lifetime from mysql.user where user='user';
+------+------+------------------+-----------------------+-------------------+
| user | host | password_expired | password_last_changed | password_lifetime |
+------+------+------------------+-----------------------+-------------------+
| user | %    | N                | 2023-02-06 15:03:41   |                 0 |
+------+------+------------------+-----------------------+-------------------+
1 row in set (0.00 sec)

password_expired列为N 这个账号没有被手动设置密码过期
password_lifetime列为0 账号正常应该已经设置成密码永不过期了
password_last_changed列为2023-02-06 15:03:41 上次密码修改时间是2023-02-06

疑点

这个问题诡异的地方就是password_lifetime列为0。正常情况下，单个账号的策略会覆盖全局策略。也就是说，如果设置过单个账号密码永不过期，就不会再参考default_password_lifetime参数值计算密码过期时间。这个账号如果按照上次密码修改时间+默认60天过期计算的话，确认是密码过期该修改密码了。

分析

排查了半天，后来灵机一动，想起来一种情况：这个账号是客户自己设置成密码永不过期的，正常我们设置密码永不过期都是使用ALTER USER user PASSWORD EXPIRE NEVER语句，但是客户会不会用了其他方法呢，比如说直接修改mysql.user表？我们知道，MySQL会在启动时将权限表读取到内存中。而直接更新mysql.user表，不会立即生效，需要再执行FLUSH PRIVILEGES重新加载权限表。

后来追问客户，客户果然是在navicat界面中直接点击mysql.user表，把password_lifetime列改为0，就以为设置成功了。

处理方案

执行FLUSH PRIVILEGES刷新权限后，果然账号登录时不再报错。

原因

设置密码永不过期的方法错误，只更新了mysql.user表，没有刷新权限正确方法

ALTER USER user PASSWORD EXPIRE NEVER；
-- 或者
UPDATE mysql.user SET password_lifetime=0 WHERE user='user';
FLUSH PRIVILEGES;

扩展知识

MySQL的权限修改何时生效

MySQL在启动时将权限表加载到内存中，之后使用内存中的权限表进行访问控制

使用账号管理语句，如ALTER USER/GRANT/REVOKE/SET PASSWORD等，会重新加载权限表，立即生效。
使用INSERT,UPDATE,DELETE等更改权限表，不会加载到内存中，不会立即生效。要使这些更改生效，可以：
- 重新加载权限表 FLUSH PRIVILEGES
- 重启数据库

设置密码有效期策略

全局有效期更改

SET GLOBAL default_password_lifetime = 60

单个账号有效期更改

-- 跟随全局策略
ALTER USER user PASSWORD EXPIRE DEFAULT
-- 密码永不过期
ALTER USER user PASSWORD EXPIRE NEVER
-- 密码N天过期
ALTER USER user PASSWORD EXPIRE INTERVAL N DAY