MySQL Security – Password Expiration Policy

April 21, 2020

When thinking about security within a MySQL installation, you can consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications.

MySQL provides many tools / features / plugins or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE)Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Reuse Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control Plugins, Password Validation Component, etc…

MySQL Security

Basic password policy practices teaches us :

  • Each user must have a password
  • A user’s password should be changed periodically

However, often this is not enough. Actually, some regulations required that the password is renewed in a timely and appropriate manner (e.g. every 90 days).

In this article, we will see how to establish a policy for password expiration with MySQL 8.0 Password Expiration Policy.


TL;DR

MySQL provides password-expiration capability, which enables database administrators to require that users reset their password.

Establish a policy for password expiration with MySQL

The main goal of Password Expiration Policy is to require passwords to be changed periodically.
It can be established globally, and individual accounts can be set to either defer to the global policy or override the global policy with specific per-account behavior.

There are different clauses a DBA can use with CREATE USER or ALTER USER to establish a per account password expiration policy.

Let’s take a closer look using MySQL 8.0.

PASSWORD EXPIRE

Force user to change its password at the first connection.

Create a user with a random password and mark that password expired :

We can see if the password is expired with mysql.user table :

Note that password_expired column is Y.

In clear, this new MySQL user will be able to connect to the server but he must reset its password before being able to executing statements

Column password_expired is now N.

PASSWORD EXPIRE INTERVAL n DAY

Force user to change its password every N days.

Create a user with password that will expire in 90 days :

We can see the password options in the mysql.user table :

Note that password_lifetime column is 90.

After 90 days any statement will generate error 1820 :

Password could be reset with ALTER USER command :

PASSWORD EXPIRE DEFAULT

This clause sets the account so that the global password expiration policy applies, as specified by the default_password_lifetime system variable.

The default default_password_lifetime value is 0, which disables automatic password expiration.
If the value of default_password_lifetime is a positive integer N, it indicates the permitted password lifetime; passwords must be changed every N days.

default_password_lifetime can be set in the MySQL configuration file but it can also be set and persisted at runtime using SET PERSIST :

The same behavior can be achieved using the configuration file (usually my.cnf or my.ini) :

but it will require a server restart.

To defer the global expiration policy for an account you should use Password Expire Default clause :

Lastly, to establish a global policy such that passwords never expire, set default_password_lifetime to 0 :

PASSWORD EXPIRE NEVER

It’s also possible to disable password expiration for an account.

Note that password_lifetime column is 0.

This expiration option overrides the global policy for all accounts named by the statement.

To Go Further

Reference Manual

MySQL Security Serie (1st edition)

Thanks for using MySQL!

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

3 Responses to “MySQL Security – Password Expiration Policy”

  1. […] Establish a policy for password expiration with MySQL […]

  2. […] (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Dual […]

  3. […] (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration Policy, Password Reuse Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, […]