Password expiration policy in MySQL Server 5.7

I’ve previously noted my wish to have a comprehensive password policy in MySQL Server.  MySQL Server 5.7.4 takes a significant step towards this goal by adding native support for enforcing password lifetime policy.  This complements the validate_password plugin introduced in MySQL Server 5.6, which helps ensure adequate password complexity, and builds on the password expiration mechanism also introduced in MySQL Server 5.6.  This new feature has a new documentation page, and is covered in the MySQL Server 5.7.4 change logs, which state:

MySQL now enables database administrators to establish a policy for automatic password expiration: Any user who connects to the server using an account for which the password is past its permitted lifetime must change the password.

Good stuff – let’s look at it in some detail.

default_password_lifetime

This new system variable defines how long the passwords will be considered valid since they were last changed.  The default is 360 days (12 moons?), so passwords will expire roughly every year.  Note that the some packages (e.g., Windows .ZIP packages) ship with pre-built system tables, and these include the date the password for default accounts was generated.  A consequence of this is that, if you download a .ZIP package of MySQL Server 5.7.4 a year from now, the default accounts will require password changes on first use.  The clock starts ticking when the accounts are created, not when the server is first installed or started.  For users upgrading from earlier versions, the clock starts ticking when you run mysql_upgrade (as this creates and populates the required tracking columns in the mysql.user system table).

If you don’t want automatic password expiration, set default_password_lifetime to 0.

Per-account settings

Regardless of the default password expiration definition, users can specify account-specific behavior.  This is done using ALTER USER syntax (it cannot be specified as part of CREATE USER syntax):

mysql> SELECT
->   user,
->   host,
->   password_last_changed,
->   password_lifetime,
->   password_expired
-> FROM mysql.user
-> WHERE user = 'test_exp'\G
*************************** 1. row ***************************
user: test_exp
host: localhost
password_last_changed: 2014-03-28 09:13:30
password_lifetime: NULL
password_expired: N
1 row in set (0.00 sec)

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

mysql> SELECT
->   user,
->   host,
->   password_last_changed,
->   password_lifetime,
->   password_expired
-> FROM mysql.user
-> WHERE user = 'test_exp'\G
*************************** 1. row ***************************
user: test_exp
host: localhost
password_last_changed: 2014-03-28 09:13:30
password_lifetime: 0
password_expired: N
1 row in set (0.00 sec)

The password_lifetime value was updated from NULL to 0, indicating that the password will never expire (NULL means, “use the server default”). We can also specify a different expiration threshold for specific accounts:

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

mysql> SELECT
->   user,
->   host,
->   password_last_changed,
->   password_lifetime,
->   password_expired
-> FROM mysql.user
-> WHERE user = 'test_exp'\G
*************************** 1. row ***************************
user: test_exp
host: localhost
password_last_changed: 2014-03-28 09:13:30
password_lifetime: 30
password_expired: N
1 row in set (0.00 sec)

Finally, we can set the account policy back to the server default:

mysql> ALTER USER test_exp@localhost PASSWORD EXPIRE DEFAULT;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT
->   user,
->   host,
->   password_last_changed,
->   password_lifetime,
->   password_expired
-> FROM mysql.user
-> WHERE user = 'test_exp'\G
*************************** 1. row ***************************
user: test_exp
host: localhost
password_last_changed: 2014-03-28 09:13:30
password_lifetime: NULL
password_expired: N
1 row in set (0.01 sec)

Effects of time-expired passwords

It’s important to realize that accounts which have exceeded the password expiration threshold are not fully deactivated – users can still connect using these credentials, but the MySQL Server will not accept commands until SET PASSWORD is issued (another privileged user can also change the password for an impacted account, thus resetting the password_last_changed date and re-enabling access for the affected user):

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

mysql> UPDATE mysql.user
-> SET password_last_changed = NOW() - INTERVAL 1 DAY
-> WHERE user = 'test_exp';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

D:\mysql-5.7.4-m14-winx64>bin\mysql -utest_exp -P3304
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.4-m14

Copyright (c) 2000, 2014, 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> SELECT 1;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> SET PASSWORD = PASSWORD('p4ssW0rd!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

While you may think that the password_expired flag would flip to “N” once the password expiration threshold is reached, it’s important to note that this is not the case:

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
->   user,
->   host,
->   password_last_changed,
->   password_lifetime,
->   IF(password_last_changed < NOW() - INTERVAL 1 DAY, 'Y', 'N') timed_exp
-> FROM mysql.user
-> WHERE user = 'test_exp'\G
*************************** 1. row ***************************
user: test_exp
host: localhost
password_last_changed: 2014-03-27 09:32:12
password_lifetime: 1
timed_exp: Y
1 row in set (0.00 sec)

Monitoring

The fact that password expiration now depends on a combination of password_expiration, password_last_changed and password_lifetime column values, plus the global default_password_lifetime variable, makes it a bit more cumbersome to monitor for expired – or soon-to-be expired – passwords. Below are two examples of how to do this via SQL, which you might find useful to monitor accounts:

mysql> # See who is expired:
mysql> SELECT
->  user,
->  host,
->  password_lifetime,
->  password_last_changed,
->  password_expired AS expired_flag,
->  IF(password_lifetime = 0, NULL,
->   IF(@@global.default_password_lifetime <> 0,
->    password_last_changed + INTERVAL
->     IF(password_lifetime IS NULL,
->       @@global.default_password_lifetime,
->       password_lifetime
->     ) DAY,
->    IF(password_lifetime IS NULL,
->     NULL,
->     password_last_changed + INTERVAL password_lifetime DAY
->     )
->    )
->   ) expiration_date,
->  IF(password_expired = 'Y', 'Y',
->   IF(password_lifetime = 0, 'N',
->    IF(@@global.default_password_lifetime <> 0,
->     IF(password_last_changed < NOW() - INTERVAL
->      IF(password_lifetime IS NULL,
->        @@global.default_password_lifetime,
->        password_lifetime
->      ) DAY,
->      'Y',
->      'N'),
->     IF(password_lifetime IS NULL,
->      'N',
->       IF(password_last_changed < NOW() - INTERVAL password_lifetime DAY,
->        'Y',
->        'N'
->       )
->      )
->     )
->    )
->   ) expired
-> FROM mysql.user\G
*************************** 1. row ***************************
user: root
host: localhost
password_lifetime: NULL
password_last_changed: 2014-03-23 10:16:05
expired_flag: N
expiration_date: 2015-03-18 10:16:05
expired: N
*************************** 2. row ***************************
user: test_exp
host: localhost
password_lifetime: 1
password_last_changed: 2014-03-23 10:16:05
expired_flag: N
expiration_date: 2014-03-24 10:16:05
expired: Y
*************************** 3. row ***************************
user: test_exp2
host: localhost
password_lifetime: 0
password_last_changed: 2014-03-23 10:16:05
expired_flag: N
expiration_date: NULL
expired: N
*************************** 4. row ***************************
user: test_exp3
host: localhost
password_lifetime: 6
password_last_changed: 2014-03-23 10:16:05
expired_flag: N
expiration_date: 2014-03-29 10:16:05
expired: N
4 rows in set (0.00 sec)

mysql> # See which accounts will expire in the coming week:
mysql> SELECT
->  user,
->  host,
->  password_lifetime,
->  password_last_changed,
->  IF(password_lifetime = 0, NULL,
->   IF(@@global.default_password_lifetime <> 0,
->    password_last_changed + INTERVAL
->     IF(password_lifetime IS NULL,
->       @@global.default_password_lifetime,
->       password_lifetime
->     ) DAY,
->    IF(password_lifetime IS NULL,
->     NULL,
->     password_last_changed + INTERVAL password_lifetime DAY
->     )
->    )
->   ) expiration_date
-> FROM mysql.user
-> WHERE
->  password_lifetime <> 0
->  AND password_last_changed IS NOT NULL
->  AND ((@@global.default_password_lifetime <> 0
->    AND password_lifetime IS NULL)
->   OR (password_lifetime IS NOT NULL
->    AND password_last_changed < NOW()
->       + INTERVAL 1 WEEK
->       - INTERVAL password_lifetime DAY
->    AND password_last_changed >= NOW()
->       - INTERVAL password_lifetime DAY)
->   OR (password_lifetime IS NULL
->    AND password_last_changed < NOW()
->       + INTERVAL 1 WEEK
->       - INTERVAL @@global.default_password_lifetime DAY
->    AND password_last_changed >= NOW()
->       - INTERVAL @@global.default_password_lifetime DAY)
->  )\G
*************************** 1. row ***************************
user: test_exp3
host: localhost
password_lifetime: 6
password_last_changed: 2014-03-23 10:16:05
expiration_date: 2014-03-29 10:16:05
1 row in set (0.00 sec)

It’s not the prettiest SQL in the world, but it gets the job done.

Conclusion

The addition of password lifetime policies is a welcome addition for users looking to have MySQL support more robust password management policies.

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.