MySQL Password Expiration Features to Help You Comply With PCI-DSS

PCI Compliance (section 8.2.4) requires users to change their passwords every 90 days. Until MySQL 5.6.6 there wasn’t a built-in way to comply with this requirement.

Since MySQL version 5.6.6 there’s a password_expired feature which allows one to set a user’s password as expired.

This has been added to the mysql.user table and its default value is "N." You can change it to "Y" using the ALTER USER statement.

Here’s a quick example on how to set an expiration date for a MySQL user account:

mysql> ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE;

Once this is set to "Y" the username will still be able to log in to the MySQL server, but it will not be able to run any queries before setting the new password. Instead, you will get an ERROR 1820 message:

mysql> SHOW DATABASES;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

Keep in mind that this does not affect any current connections the account has open.

After setting a new password, all operations performed using the account will be allowed (according to the account privileges):

mysql> SET PASSWORD=PASSWORD('mechipoderranen');
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| data               |
| logs               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql>

This allows administrators to perform password expiration by scheduling the ALTER USER via cron.

Since MySQL 5.7.4, this has been improved and there’s a new feature to set a policy for password expiration that provides more control through a global variable, default_password_lifetime, which allows setting a global automatic password expiration policy.

Example usage:

Setting a default value on our configuration file. This will set all account passwords to expire every 90 days, and will start counting from the day this variable was set effective on your MySQL server:

[mysqld]
default_password_lifetime=90

Setting a global policy for the passwords to never expire. Note this is the default value (so it is not strictly necessary to declare in the configuration file):

[mysqld]
default_password_lifetime=0

This variable can also be changed at runtime if the user has SUPER privileges granted:

mysql> SET GLOBAL default_password_lifetime = 90;
Query OK, 0 rows affected (0.00 sec)

You can also set specific values for each user account using ALTER USER. This will override the global password expiration policy. Please note that ALTER USER only understands INTERVAL expressed in DAY:

ALTER USER ‘testuser’@‘localhost' PASSWORD EXPIRE INTERVAL 30 DAY;

Disable password expiration:

ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE NEVER;

Set to default value, which is the current value of default_password_lifetime:

ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE DEFAULT;

Since MySQL 5.7.6, you can use the ALTER USER to change the user’s password:

mysql> ALTER USER USER() IDENTIFIED BY '637h1m27h36r33K';
Query OK, 0 rows affected (0.00 sec)

For more information on this variable, please refer to the documentation page: https://dev.mysql.com/doc/refman/5.7/en/password-expiration-policy.html

Bonus post:

Another new feature in MySQL 5.7.8 related to user management is locking/unlocking user accounts when CREATE USER, or at a later time running the ALTER USER statement.

In this example, we will first create a username with the ACCOUNT LOCK:

mysql> CREATE USER 'furrywall'@'localhost' IDENTIFIED BY '71m32ch4n6317' ACCOUNT LOCK;
Query OK, 0 rows affected (0.00 sec)

As you can see below, the newly created user gets an ERROR 3118 message while trying to login:

$ mysql -ufurrywall -p
Enter password:
ERROR 3118 (HY000): Access denied for user 'furrywall'@'localhost'. Account is locked.

We can unlock the account using the ALTER USER ... ACCOUNT UNLOCK; statement:

mysql>ALTER USER 'furrywall'@'localhost' ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.00 sec)

Now, the user account is unlocked and accessible:

$ mysql -ufurrywall -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 17
Server version: 5.7.8-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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>

If necessary, you can lock it again:

mysql> ALTER USER 'furrywall'@'localhost' ACCOUNT LOCK;
Query OK, 0 rows affected (0.00 sec)

Please check this following documentation for more details: https://dev.mysql.com/doc/refman/5.7/en/account-locking.html