MySQL password expiration features to help you comply with PCI-DSS

MySQL passwordPCI Compliance (section 8.2.4) requires users to change password 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 to set a user’s password as expired.
This has been added to the mysql.user table and its default value it’s “N.” You can change it to “Y” using the ALTER USER statement.

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

Once this is set to “Y” the username will still be able to login to the MySQL server, but it will not be able to run any queries before setting the new password. You will instead get an ERROR 1820 message:

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):

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 to set 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:

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):

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

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:

Disable password expiration:

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

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

For more information on this variable, please refer to the documentation page:

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:

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

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

Now the user account is unlocked and accessible: