mysql-密码过期

mysql-密码过期

问题现象

1. error-log提示：ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
2. 使用出错的数据库业务账号登录数据库执行任一命令时提示上面语句

解决办法

临时解决

1. 修改密码

   mysql> set password = password('newpasswd'); #使用过期用户登录直接修改
   mysql> set password for 'test'@'%' = password('newpasswd'); #使用具有修改用户权限的用户登录来修改
   

永久解决

1. 将用户的密码有效期修改为永不过期

   mysql> alter user 'test'@'%' password expire never;
   

2. 修改密码有效期默认策略

   mysql> set global default_password_lifetime=0;
   

逻辑原理

用户指定的密码策略 > 默认策略

1. 创建用户时指定密码有效期

   mysql> alter user 'test'@'%' identified by '321321' password expire interval 1 day;
   

2. 创建用户时为默认密码策略

   mysql> create user 'test'@'%' identified by '321321';
   mysql> show create user 'test'@'%'\G
   *************************** 1. row ***************************
   CREATE USER for test@%: CREATE USER 'test'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*4160291B4C8CC2573CC94951203FFBC858754907' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
   1 row in set (0.00 sec)
   

   其中'PASSWORD EXPIRE'是默认的'DEFAULT'

3. 查看默认策略

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

   '0'代表永不过期

   mysql_5.7.4 - 5.7.10版本默认是360天，5.7.11以后默认永不过期

填坑指南

假如新进公司，数据库属于运维范围，这时候可以想想是否有数据库用户密码过期的坑

1. 查看用户密码状态

   mysql> SELECT user,host,password_expired,password_last_changed,password_lifetime from mysql.user;
   +---------------+-----------+------------------+-----------------------+-------------------+
   | user          | host      | password_expired | password_last_changed | password_lifetime |
   +---------------+-----------+------------------+-----------------------+-------------------+
   | root          | localhost | N                | 2020-07-16 12:19:39   |              NULL |
   | mysql.session | localhost | N                | 2020-07-15 11:11:16   |              NULL |
   | mysql.sys     | localhost | N                | 2020-07-15 11:11:16   |              NULL |
   | test          | %         | N                | 2020-07-16 12:49:23   |              NULL |
   +---------------+-----------+------------------+-----------------------+-------------------+
   4 rows in set (0.00 sec)
   

2. 查看用户的密码策略是否为默认策略

   mysql> SHOW CREATER USER 'test'@'%';
   +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | CREATE USER for test@%                                                                                                                                            |
   +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | CREATE USER 'test'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*4160291B4C8CC2573CC94951203FFBC858754907' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |
   +-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.00 sec)
   

3. 查看数据库密码默认策略

   mysql> SHOW VARIABLES LIKE 'default_password_lifetime';
   +---------------------------+-------+
   | Variable_name             | Value |
   +---------------------------+-------+
   | default_password_lifetime | 0     |
   +---------------------------+-------+
   1 row in set (0.00 sec)
   

4. 查看当前时间

   mysql> SELECT now();
   +---------------------+
   | now()               |
   +---------------------+
   | 2020-07-16 13:17:01 |
   +---------------------+
   1 row in set (0.00 sec)
   

5. 根据第3步的全局密码策略对第1步中'password_last_changed'和第4步的当前时间做比对，来判断是否过期；原因是第1步中的'password_expired'并不准确