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;

About these ads

1 Comment

Filed under MySQL Security, Security

One response to “MySQL 5.7 user table: password_last_changed & password_lifetime

  1. Pingback: Expirar senha de usuário e forçar troca de senha MySQL 5.7.4 | Mathias Brem

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s