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:

If necessary, you can lock it again:

Please check this following documentation for more details:

Learn more about Percona Server for MySQL

Share this post

Comments (3)

  • Fadi El-Eter (itoctopus) Reply

    Hi Pablo,

    Thanks for sharing this. I really doubt though that such a feature will find its way into the practical world, this is because an automatic expiry of a password may lead to crashing a whole application (such as website).

    Additionally, the PCI-DSS description of this requirement is as follows:

    “For a sample of system components, inspect system configuration settings to verify that user password parameters are set to require users to change passwords at least every 90 days.”

    In my opinion, the above is a bit ambiguous – what do they mean by users? Do they mean “database users” or “application users”, or both? My guess is that they only mean application users and this is what most compliant websites out there interpret this term.

    February 11, 2016 at 9:29 am
    • scaarup Reply

      I am pretty sure, that according to PCI, system accounts (application users) does not need to have password expiration set. However DBA’s/Operations users should indeed have their passwords expired every 90 days at a minimum. Therefore I think this makes perfect sense.

      January 9, 2017 at 9:38 am
  • Ketav Chotaliua Reply

    I’m using MySQL version 5.5.50 And none of your command is run in my version. except Create user, Delete user, Grant Access.

    August 31, 2016 at 7:48 am

Leave a Reply