Technology Sharing | What happened to MySQL user password expiration

Author: Geng Jin

A member of the Aikesheng DBA team, responsible for the operation and maintenance of the company's DMP products and the handling of customer MySQL issues. Have a strong interest in database technology. Have you seen the MySQL error at four o'clock in the morning?

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

Overview:

Let's first re-recognize the password expiration field in the mysql.user table,

mysql> use mysql
Database changed
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
。。。。
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.01 sec)

mysql> 

password_expired: Starting from MySQL version 5.6.6, the password_expired function has been added, which allows setting the user's expiration time.

password_last_changed: The time when the password was last changed.

password_lifetime: The lifetime of the user's password, the default value is NULL, unless the user password expiration mechanism is manually modified, otherwise it is NULL.

Another parameter is explained:

default_password_lifetime: Starting from MySQL 5.7.4 version, this global variable can set a global automatic password expiration policy.

test:

1. password_expired: Manually set expired

1. Set the password to never expire

mysql> grant all on *.* to test@'localhost' identified by '123';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> select User,password_last_changed,password_lifetime,password_expired from mysql.user;
+---------------+-----------------------+-------------------+------------------+
| User          | password_last_changed | password_lifetime | password_expired |
+---------------+-----------------------+-------------------+------------------+
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| mysql.session | 2021-03-31 14:11:06   |              NULL | N                |
| mysql.sys     | 2021-03-31 14:11:06   |              NULL | N                |
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| universe_op   | 2021-03-31 14:11:10   |              NULL | N                |
| kobe          | 2021-04-01 16:45:20   |              NULL | N                |              |
| test          | 2021-04-16 17:30:18   |              NULL | N                |
+---------------+-----------------------+-------------------+------------------+
7 rows in set (0.00 sec)

mysql> ALTER USER 'test'@'localhost' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select User,password_last_changed,password_lifetime,password_expired from mysql.user;
+---------------+-----------------------+-------------------+------------------+
| User          | password_last_changed | password_lifetime | password_expired |
+---------------+-----------------------+-------------------+------------------+
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| mysql.session | 2021-03-31 14:11:06   |              NULL | N                |
| mysql.sys     | 2021-03-31 14:11:06   |              NULL | N                |
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| universe_op   | 2021-03-31 14:11:10   |              NULL | N                |
| kobe          | 2021-04-01 16:45:20   |              NULL | N                |                |
| test          | 2021-04-16 17:30:18   |                 0 | N                |
+---------------+-----------------------+-------------------+------------------+
7 rows in set (0.00 sec)

mysql>

注：如果该参数设置为0，即表示密码永不过期。

2. Manually set the user password to 30 days (it will automatically override the global policy of password expiration)

mysql> ALTER USER 'test'@'localhost' PASSWORD EXPIRE INTERVAL 30 DAY;
Query OK, 0 rows affected (0.01 sec)

mysql> select User,password_last_changed,password_lifetime,password_expired from mysql.user;
+---------------+-----------------------+-------------------+------------------+
| User          | password_last_changed | password_lifetime | password_expired |
+---------------+-----------------------+-------------------+------------------+
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| mysql.session | 2021-03-31 14:11:06   |              NULL | N                |
| mysql.sys     | 2021-03-31 14:11:06   |              NULL | N                |
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| universe_op   | 2021-03-31 14:11:10   |              NULL | N                |
| kobe          | 2021-04-01 16:45:20   |              NULL | N                |                |
| test          | 2021-04-16 17:30:18   |                30 | N                |
+---------------+-----------------------+-------------------+------------------+
7 rows in set (0.00 sec)

mysql> 

3. Set the password to expire immediately

mysql> ALTER USER 'hhh'@'%' PASSWORD EXPIRE;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> select User,password_last_changed,password_lifetime,password_expired from mysql.user;
+---------------+-----------------------+-------------------+------------------+
| User          | password_last_changed | password_lifetime | password_expired |
+---------------+-----------------------+-------------------+------------------+
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| mysql.session | 2021-03-31 14:11:06   |              NULL | N                |
| mysql.sys     | 2021-03-31 14:11:06   |              NULL | N                |
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| universe_op   | 2021-03-31 14:11:10   |              NULL | N                |
| kobe          | 2021-04-01 16:45:20   |              NULL | N                |                |
| test          | 2021-04-16 17:30:18   |                30 | Y                |
| gengjin       | 2021-04-16 17:42:33   |              NULL | N                |
| hhh           | 2021-04-16 18:00:32   |              NULL | Y                |
| kkk           | 2021-04-16 18:26:06   |              NULL | N                |
+---------------+-----------------------+-------------------+------------------+
10 rows in set (0.00 sec)

mysql> exit
Bye
[root@manage01 ~]# /opt/mysql/base/5.7.25/bin/mysql -uhhh -p -S /opt/mysql/data/3306/mysqld.sock
Logging to file '/mysqldata/mysql_3306/log/test.log'
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39469
Server version: 5.7.25-log

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> 

Two, default_password_lifetime: automatic expiration mechanism

1. Set the global password expiration time:

#配置文件
[mysqld]
default_password_lifetime=90
or
#命令行全局修改
mysql> SET GLOBAL default_password_lifetime = 90;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> show variables like "default_password_lifetime";
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 90    |
+---------------------------+-------+
1 row in set (0.00 sec)

mysql> 

2. Create a user:

mysql> grant all on *.* to hhh@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> select User,password_last_changed,password_lifetime,password_expired from mysql.user;
+---------------+-----------------------+-------------------+------------------+
| User          | password_last_changed | password_lifetime | password_expired |
+---------------+-----------------------+-------------------+------------------+
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| mysql.session | 2021-03-31 14:11:06   |              NULL | N                |
| mysql.sys     | 2021-03-31 14:11:06   |              NULL | N                |
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| universe_op   | 2021-03-31 14:11:10   |              NULL | N                |
| kobe          | 2021-04-01 16:45:20   |              NULL | N                |                |
| test          | 2021-04-16 17:30:18   |                30 | Y                |
| gengjin       | 2021-04-16 17:42:33   |              NULL | N                |
| hhh           | 2021-04-16 18:00:32   |              NULL | N                |
+---------------+-----------------------+-------------------+------------------+
9 rows in set (0.00 sec)

mysql> 

Note: Many people see why the password_lifetime has not changed. Shouldn't it become 90? Is it a mysql bug, but it is not.

By the way, post the address of this "bug":

https://bugs.mysql.com/bug.php?id=89349

The way it works is as follows:

There is a global system variable default_password_lifetime, which specifies the policy for all accounts that use the default password lifetime. This will store a NULL in the system tables. The NULL value is used as a sign to indicate that the account involved does not have a special lifetime for each user's password. Set each user's special password lifetime by ALTER USER password EXPIRE NEVER (set the column to 0) or ALTER USER password EXPIRE INTERVAL N DAY (set the column to N).

Therefore, all password lifetimes of all users who have not set a specific password lifetime will follow the value of the global variable.