MySQL Bugs: #89349: password_lifetime set to Null for user after changing default_password

Bug #89349	password_lifetime set to Null for user after changing default_password_lifetime
Submitted:	22 Jan 2018 18:34	Modified:	23 Jan 2018 8:41
Reporter:	Vishal Kasle	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	5.7.18	OS:	Linux
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
When default_password_lifetime is set dynamically or in the configuration file and the server is restarted the value oi password_lifetime for every user in mysql.user is Null which should be the value set as default_password_lifetime according to https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_default_passwo.... The password_lifetime value is only changed when an ALTER USER statement is used to change PASSWORD EXPIRE INTERVAL

How to repeat:
Set default_password_lifetime in my.cnf 

root@pikachu:~# grep default_password_lifetime /etc/mysql/my.cnf
default_password_lifetime=90

Reboot the mysql instance and check user information in mysql.user

root@pikachu:~# service mysql restart
 * Stopping MySQL Community Server 5.7.18
 * MySQL Community Server 5.7.18 is already stopped
 * Re-starting MySQL Community Server 5.7.18
...
 * MySQL Community Server 5.7.18 is started

+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| default_password_lifetime             | 90    |

mysql>  select User,password_last_changed,password_lifetime from mysql.user; 
+------------------+-----------------------+-------------------+
| User             | password_last_changed | password_lifetime |
+------------------+-----------------------+-------------------+
| root             | 2018-01-22 22:01:35   |              NULL |
| root             | 2017-07-05 16:26:17   |              NULL |
| root             | 2017-07-05 16:26:17   |              NULL |
| root             | 2017-07-05 16:26:17   |              NULL |
|                  | 2017-07-05 16:26:17   |              NULL |
|                  | 2017-07-05 16:26:17   |              NULL |
| debian-sys-maint | 2017-07-05 16:26:17   |              NULL |
| test             | 2017-07-05 16:26:17   |              NULL |
| test             | 2017-07-05 16:26:17   |              NULL |
| pmm              | 2017-07-05 16:26:17   |              NULL |
| pmm              | 2017-07-05 16:26:17   |              NULL |
| repl             | 2017-07-05 16:26:17   |              NULL |
| mysql.sys        | 2017-07-05 16:26:18   |              NULL |
| testuser         | 2017-07-05 16:26:24   |              NULL |
+------------------+-----------------------+-------------------+

Create new user and check information again

mysql> GRANT SELECT ON *.* TO 'test2'@'localhost' IDENTIFIED BY PASSWORD '*****';

mysql> select User,password_last_changed,password_lifetime from mysql.user;
+------------------+-----------------------+-------------------+
| User             | password_last_changed | password_lifetime |
+------------------+-----------------------+-------------------+
| root             | 2018-01-22 22:01:35   |              NULL |
| root             | 2017-07-05 16:26:17   |              NULL |
| root             | 2017-07-05 16:26:17   |              NULL |
| root             | 2017-07-05 16:26:17   |              NULL |
|                  | 2017-07-05 16:26:17   |              NULL |
|                  | 2017-07-05 16:26:17   |              NULL |
| debian-sys-maint | 2017-07-05 16:26:17   |              NULL |
| test             | 2017-07-05 16:26:17   |              NULL |
| test             | 2017-07-05 16:26:17   |              NULL |
| pmm              | 2017-07-05 16:26:17   |              NULL |
| pmm              | 2017-07-05 16:26:17   |              NULL |
| repl             | 2017-07-05 16:26:17   |              NULL |
| mysql.sys        | 2017-07-05 16:26:18   |              NULL |
| testuser         | 2017-07-05 16:26:24   |              NULL |
| test2            | 2018-01-22 22:04:44   |              NULL |
+------------------+-----------------------+-------------------+

ALTER USER to change PASSWORD EXPIRE INTERVAL and check again

mysql> ALTER USER 'test2'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql>  select User,password_last_changed,password_lifetime from mysql.user;
+------------------+-----------------------+-------------------+
| User             | password_last_changed | password_lifetime |
+------------------+-----------------------+-------------------+
| root             | 2018-01-22 22:01:35   |              NULL |
| root             | 2017-07-05 16:26:17   |              NULL |
| root             | 2017-07-05 16:26:17   |              NULL |
| root             | 2017-07-05 16:26:17   |              NULL |
|                  | 2017-07-05 16:26:17   |              NULL |
|                  | 2017-07-05 16:26:17   |              NULL |
| debian-sys-maint | 2017-07-05 16:26:17   |              NULL |
| test             | 2017-07-05 16:26:17   |              NULL |
| test             | 2017-07-05 16:26:17   |              NULL |
| pmm              | 2017-07-05 16:26:17   |              NULL |
| pmm              | 2017-07-05 16:26:17   |              NULL |
| repl             | 2017-07-05 16:26:17   |              NULL |
| mysql.sys        | 2017-07-05 16:26:18   |              NULL |
| testuser         | 2017-07-05 16:26:24   |              NULL |
| test2            | 2018-01-22 22:04:44   |                90 |
+------------------+-----------------------+-------------------+
15 rows in set (0.00 sec)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

The way it works is as follows:
1. There is the global system variable default_password_lifetime that specifies the policy for all accounts that are set to use the default password lifetime. This is done by ALTER USER PASSWORD EXPIRE DEFAULT. In the system table (not that it matters, but still) this stores a NULL. The NULL value is used as a flag that the account in question does not have a special per user password lifetime. The special per-user password lifetime is set via ALTER USER PASSWORD EXPIRE NEVER (which sets the column to 0) or ALTER USER PASSWORD EXPIRE INTERVAL N DAY (which sets the column to N). 
As a consequence all password lifetimes for all users that do not have a specific password lifetime set will follow the value of the global variable.
And if you store a specific password lifefile for a user account it will "detach" iteself from the global variable's value and will be pegged to whatever you've set for it. Until you reset it back to the default of course.

If you feel like you need this explained better in the docs please re-open the bug as a documentation one.