MySQL 5.7 user table: password_last_changed & password_lifetime

MySQL 5.7.4 has added two fields to the mysql.user table — password_last_changed, a timestamp and password_lifetime, a small but unsigned integer. Several blogs ago I started to cobble together a password expiration tracking script before these two columns were added. But I could see three ways of tracking expired passwords but none of them were palatable. Todd Farmer was working on a similar idea.

So when you run mysql_upgrade after upgrading to 5.7.4, you will find these two new columns. The password_last_changed will be set to the time you ran the upgrade and password_lifetime will be set to null.

You can set global password lifetime policy in the options file.
[mysqld]
default_password_lifetime=180

So 180 is about six months and zero would set a never expire policy.

ALTER USER 'dave'@localhost' PASSWORD EXPIRE INTERVAL 90 DAYS;
ALTER USER 'john'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'jane'@'localhost' PASSWORD EXPIRE DEFAULT;