dasini.net – Diary of a MySQL expert

When thinking about security within a MySQL installation, you should 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 in order to protect your data including some advanced features like Transparent Data Encryption aka TDE, Audit, Data Masking & De-Identification, Firewall, Password Validation Plugin, User Account Locking, The Connection-Control Plugins, etc…

Some regulations required that the password is renewed in a timely and appropriate manner (e.g. every 90 days). In this article, 2nd of the MySQL  Security series, we will see how to establish a policy for password expiration with MySQL 5.7 Password Management.

Password Management

Basic password policy practices teaches us :

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

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

Let’s have a closer look!

$ mysql -u root -p

mysql {root}> 
SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.7.21-log |
+------------+

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

Let’s play with some of them.

PASSWORD EXPIRE

Force user to change its password at the first connection.

-- Mark the password expired so that the user must choose a new one at the first connection to the server
mysql {root}> 
CREATE USER 'aUser'@'localhost' IDENTIFIED BY 'AutoGeneratedPwd' PASSWORD EXPIRE;

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: 2018-02-08 14:22:24

Note that password_expired column is Y.

This new MySQL user will be able to connect to the server but he must reset its password.

$ mysql -u aUser -p
...

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

ALTER USER 'aUser'@'localhost' IDENTIFIED BY 'new_passw0rd';
Query OK, 0 rows affected (0.00 sec)


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

Column password_expired is now N.

mysql {root}> 
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: 2018-02-08 14:23:50

PASSWORD EXPIRE INTERVAL N DAY

Force user to change its password every N days.

mysql {root}> 
-- Require that a new password be chosen every 90 days:
CREATE USER 'aNewUser'@'localhost' IDENTIFIED BY 'n3w_password'  PASSWORD EXPIRE INTERVAL 90 DAY;

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: 2018-02-08 14:24:53

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 {root}>
ALTER USER 'aNewUser'@'localhost' IDENTIFIED BY '4noth3r_password' PASSWORD EXPIRE INTERVAL 90 DAY;

PASSWORD EXPIRE NEVER

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

mysql {root}>
-- Disables password expiration for the account so that its password never expires.
CREATE USER 'pingDB'@'localhost' IDENTIFIED BY 'new_p4ssword' 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: 2018-02-08 14:29:43

Note that password_lifetime column is 0.

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.

In MySQL 5.7 it applies to accounts that use MySQL built-in authentication methods (accounts that use an authentication plugin of mysql_native_password or sha256_password).

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 changed at runtime with SET GLOBAL command. However it must be set in the MySQL configuration file for persistence.

e.g.

$ cat my.cnf 

[mysqld]
# global policy that passwords have a lifetime of approximately 3 months
default_password_lifetime=90
...

e.g. Create a user account with the default global password policy (90 days)

mysql>
-- default_password_lifetime set to 90 days
SET GLOBAL default_password_lifetime = 90;

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

-- Sets the account so that the global expiration policy applies, as specified by the default_password_lifetime system variable.
CREATE USER 'dba'@'localhost' IDENTIFIED BY 'new_pas5word' PASSWORD EXPIRE DEFAULT;

In order to go further

MySQL Security Series

1. Password Validation Plugin
2. Password Management
3. User Account Locking
4. The Connection-Control Plugins
5. Enterprise Audit
6. Enterprise Transparent Data Encryption (TDE)
7. Enterprise Firewall
8. Enterprise Data Masking and De-Identification

Reference Manual

• MySQL 5.7 Password Management
• MySQL 5.7 Password Expiration and Sandbox Mode

MySQL Security

• Security in MySQL
• MySQL 5.7 Secure Deployment Guide
• MySQL 5.7 Reference Manual – Security

 

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 by Olivier DASINI