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 Expiration 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 may require that users can not reuse a previous password.

You can do that by setting how often and / or how long an old password can be reuses. In this article, from my new MySQL Security series, we will see how to establish a policy for password reuse with MySQL 8.0 Password Reuse Policy.

---

TL;DR

MySQL provides password-reuse capability, which allows database administrators to determine the number of unique passwords a user must use before they can use an old password again.

---

The main goal of Password Reuse Policy is to enable restrictions to be placed on reuse of previous passwords.
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 reuse policy.

Let’s dig into it using MySQL 8.0.

$ mysqlsh daz@localhost --sql

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

PASSWORD HISTORY

Prohibit reusing any of the last 10 (then 24) passwords :

MySQL SQL> 
CREATE USER 'aUser'@'localhost' PASSWORD HISTORY 10;


SELECT user, host, password_reuse_history, password_last_changed FROM mysql.user WHERE user = 'aUser'\G
*************************** 1. row ***************************
                  user: aUser
                  host: localhost
password_reuse_history: 10
 password_last_changed: 2020-04-03 09:45:45


ALTER USER 'aUser'@'localhost' PASSWORD HISTORY 24;


SELECT user, host, password_reuse_history, password_last_changed FROM mysql.user WHERE user = 'aUser'\G
*************************** 1. row ***************************
                  user: aUser
                  host: localhost
password_reuse_history: 24
 password_last_changed: 2020-04-03 09:45:45

PASSWORD REUSE INTERVAL n DAY

Require a minimum of 180 (then 365) days elapsed before permitting reuse :

MySQL SQL> 
CREATE USER 'bUser'@'localhost' PASSWORD REUSE INTERVAL 180 DAY;


SELECT user, host, password_reuse_time, password_last_changed FROM mysql.user WHERE user = 'bUser'\G
*************************** 1. row ***************************
                 user: bUser
                 host: localhost
  password_reuse_time: 180
password_last_changed: 2020-04-03 10:03:20


ALTER USER 'bUser'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;


SELECT user, host, password_reuse_time, password_last_changed FROM mysql.user WHERE user = 'bUser'\G
*************************** 1. row ***************************
                 user: bUser
                 host: localhost
  password_reuse_time: 365
password_last_changed: 2020-04-03 10:03:20

Combine types of reuse restrictions

It is also possible to combine both types of reuse restrictions.
Simply use PASSWORD HISTORY and PASSWORD REUSE INTERVAL n DAY together :

MySQL SQL> 
CREATE USER 'cUser'@'localhost' 
  PASSWORD HISTORY 5 
  PASSWORD REUSE INTERVAL 180 DAY;


SELECT user, host, password_reuse_history, password_reuse_time, password_last_changed FROM mysql.user WHERE user = 'cUser'\G
*************************** 1. row ***************************
                  user: cUser
                  host: localhost
password_reuse_history: 5
   password_reuse_time: 180
 password_last_changed: 2020-04-03 10:11:31


ALTER USER 'cUser'@'localhost' 
  PASSWORD HISTORY 10 
  PASSWORD REUSE INTERVAL 365 DAY;


SELECT user, host, password_reuse_history, password_reuse_time, password_last_changed FROM mysql.user WHERE user = 'cUser'\G
*************************** 1. row ***************************
                  user: cUser
                  host: localhost
password_reuse_history: 10
   password_reuse_time: 365
 password_last_changed: 2020-04-03 10:11:31

Global Policy

Reuse policy can be established globally, as specified by the password_history and password_reuse_interval system variables.

The default password_history value is 0, which disables automatic password expiration.
Same for password_reuse_interval.

password_history and password_reuse_interval variables can be set in the MySQL configuration file (usually my.cnf or my.ini) but it can also be set and persisted at runtime using SET PERSIST :

SET PERSIST password_history = 10;

SET PERSIST password_reuse_interval = 365;


SHOW VARIABLES WHERE Variable_name IN ('password_history','password_reuse_interval');
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| password_history        | 10    |
| password_reuse_interval | 365   |
+-------------------------+-------+

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

[mysqld]
password_history = 10
password_reuse_interval = 365

However it requires a server restart.

To defer the global policy for an account for both types of reuse restrictions you must use the DEFAULT clause :

MySQL SQL> 
CREATE USER olivier@localhost
  PASSWORD HISTORY DEFAULT
  PASSWORD REUSE INTERVAL DEFAULT;


ALTER USER ethan@localhost
  PASSWORD HISTORY DEFAULT
  PASSWORD REUSE INTERVAL DEFAULT;

To establish a global policy such that none of these restriction exist, set password_history and password_reuse_interval to 0 :

MySQL SQL> 
SET PERSIST password_history = 0;

SET PERSIST password_reuse_interval = 0;


SHOW VARIABLES WHERE Variable_name IN ('password_history','password_reuse_interval');
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| password_history        | 0     |
| password_reuse_interval | 0     |
+-------------------------+-------+

Please note that the empty password does not count in the password history and is subject to reuse at any time.

To Go Further

Reference Manual

• Password Reuse Policy
• password_history
• password_reuse_interval

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