dasini.net – Diary of a MySQL expert

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…

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.

---

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.

$ mysqlsh daz@localhost --sql

MySQL SQL> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.19    |
+-----------+

PASSWORD EXPIRE

Force user to change its password at the first connection.

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

-- Mark the password expired so that the user must choose a new one at the first connection to the server
MySQL SQL> 
CREATE USER 'aUser'@'localhost' IDENTIFIED BY RANDOM PASSWORD PASSWORD EXPIRE;
+-------+-----------+----------------------+
| user  | host      | generated password   |
+-------+-----------+----------------------+
| aUser | localhost | (wvx3n7jH)bVNi3tOiQV |
+-------+-----------+----------------------+

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

MySQL SQL> 
SELECT user, host, password_lifetime, password_expired, password_last_changed FROM mysql.user WHERE user = 'aUser' \G
*************************** 1. row ***************************
                 user: aUser
                 host: localhost
    password_lifetime: NULL
     password_expired: Y
password_last_changed: 2020-04-01 12:31:57

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

$ mysql -u aUser -p
Enter password: 
...


mysql> SELECT USER();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.


mysql> ALTER USER user() IDENTIFIED BY 'n3w_pAssw0rd';
Query OK, 0 rows affected (0.01 sec)


mysql> SELECT USER();
+-----------------+
| USER()          |
+-----------------+
| aUser@localhost |
+-----------------+

Column password_expired is now N.

MySQL SQL> 
SELECT user, host, password_lifetime, password_expired, password_last_changed FROM mysql.user WHERE user = 'aUser' \G
*************************** 1. row ***************************
                 user: aUser
                 host: localhost
    password_lifetime: NULL
     password_expired: N
password_last_changed: 2020-04-01 12:41:25

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 :

MySQL SQL> 
-- Require that a new password be chosen every 90 days
CREATE USER 'aNewUser'@'localhost' IDENTIFIED BY 'aN3w_pAssw0rd' PASSWORD EXPIRE INTERVAL 90 DAY;

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

MySQL SQL> 
SELECT user, host, password_lifetime, password_expired, password_last_changed FROM mysql.user WHERE user = 'aNewUser'\G
*************************** 1. row ***************************
                 user: aNewUser
                 host: localhost
    password_lifetime: 90
     password_expired: N
password_last_changed: 2020-04-01 15:40:14

Note that password_lifetime column is 90.

After 90 days any statement will generate error 1820 :

$ mysql -u aNewUser -p
...


mysql {aNewUser}> SELECT USER();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Password could be reset with ALTER USER command :

mysql {aNewUser}> 
ALTER USER user() IDENTIFIED BY '4noth3r_pa5sw0rd';
Query OK, 0 rows affected (0.01 sec)


mysql {aNewUser}> SELECT USER();
+--------------------+
| USER()             |
+--------------------+
| aNewUser@localhost |
+--------------------+

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 :

MySQL SQL> 
SET PERSIST default_password_lifetime = 30;

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

[mysqld]
default_password_lifetime = 30

but it will require a server restart.

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

MySQL SQL> 
CREATE USER olivier@localhost  PASSWORD EXPIRE DEFAULT;


ALTER USER aNewUser@localhost PASSWORD EXPIRE DEFAULT;

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

MySQL SQL> 
SET PERSIST default_password_lifetime = 0;


SHOW VARIABLES LIKE 'default_password_lifetime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 0     |
+---------------------------+-------+

PASSWORD EXPIRE NEVER

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

MySQL SQL> 
-- Disables password expiration for the account so that its password never expires
CREATE USER 'pingDB'@'localhost' IDENTIFIED BY 'A-p45swOrd' PASSWORD EXPIRE NEVER;


SELECT user, host, password_lifetime, password_expired, password_last_changed FROM mysql.user WHERE user = 'pingDB'\G
*************************** 1. row ***************************
                 user: pingDB
                 host: localhost
    password_lifetime: 0
     password_expired: N
password_last_changed: 2020-04-02 12:42:03

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

• Password Expiration Policy
• default_password_lifetime
• Server Handling of Expired Passwords

MySQL Security Serie (1st edition)

• Password Validation Plugin
• Password Management
• User Account Locking
• The Connection-Control Plugins
• Enterprise Audit
• Enterprise Transparent Data Encryption (TDE)
• Enterprise Firewall
• Enterprise Data Masking and De-Identification

Thanks for using MySQL!

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

Filed under:
MySQL, Security, Tuto by Olivier DASINI