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 Management, User Account Locking, The Connection-Control Plugins, etc…

In this article, 1st of a MySQL Security series, we will see how to enforce Strong Passwords with Password Validation Plugin when using MySQL 5.7.

Authentication with ID and password is a very simple and common (because it’s simple) way to secure the access to a resource, however the password can be the weak point of this system. In order to increase the security level, you can required that your user passwords meet certain minimal security requirements, using the MySQL Password validation plugin!

Password Validation Plugin

The Password validation plugin serves to test passwords and improve security. It exposes a set of system variables that enable you to define password policy.

For ALTER USER, CREATE USER, GRANT, and SET PASSWORD statements the plugin checks the password against the current password policy and rejects it if it is weak.

Examples are made with MySQL CE 5.7.21 on Linux:

mysql> SHOW VARIABLES LIKE 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.7.21                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+

Installation

Plugins are located in the… plugin directory. To know where is your MySQL plugin directory you can use SHOW VARIABLES :

mysql> 
SHOW VARIABLES LIKE 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+

system ls -l /usr/lib64/mysql/plugin/ | grep validate
-rwxr-xr-x 1 root root   29336 Dec 28 04:07 validate_password.so

Use the regular INSTALL PLUGIN statement:

-- Install validate_password plugin
mysql> 
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- Check validate_password status
SELECT PLUGIN_NAME, PLUGIN_STATUS 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME LIKE 'validate%';
+-------------------+---------------+
| PLUGIN_NAME       | PLUGIN_STATUS |
+-------------------+---------------+
| validate_password | ACTIVE        |
+-------------------+---------------+

INSTALL PLUGIN loads the plugin, and also registers it in the mysql.plugins system table to cause the plugin to be loaded for each subsequent normal server startup.

Alternatively you can modify the MySQL configuration file (e.g. my.cnf or my.ini) and reboot the instance.

e.g.

# sample from my.cnf
[mysqld]
plugin-load-add=validate_password.so

When installed some system and status variables are available:

mysql> SHOW VARIABLES LIKE 'validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+


SHOW STATUS LIKE 'validate%';
+-----------------------------------------------+---------------------+
| Variable_name                                 | Value               |
+-----------------------------------------------+---------------------+
| validate_password_dictionary_file_last_parsed | 2018-02-06 14:58:19 |
| validate_password_dictionary_file_words_count | 0                   |
+-----------------------------------------------+---------------------+

They are described here.

Playtime

Let’s play a little a bit with the Password Validation Plugin.

Set Password Validation Plugin to the LOW level

When validate_password_policy is set to LOW (or 0) it checks only the length i.e. validate_password_length >= 8 (by default)

mysql> 
SET GLOBAL validate_password_policy = 0;


SHOW VARIABLES LIKE 'validate_password_policy';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| validate_password_policy | LOW   |
+--------------------------+-------+

Warning

Passwords in the following examples are not secure. Do NOT use trivial passwords!

User creation that is not satisfy the policy will failed

mysql> 
-- NOK because password length < 8 
CREATE USER u_low1 IDENTIFIED by 'p';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements


-- OK because password length >= 8
CREATE USER u_low2 IDENTIFIED by 'p2345678';
Query OK, 0 rows affected (0.01 sec)

CREATE USER u_low3 IDENTIFIED by 'pppppppp';
Query OK, 0 rows affected (0.00 sec)



mysql> 
-- new users created
SELECT user FROM mysql.user WHERE user LIKE 'u%';
+--------+
| user   |
+--------+
| u_low2 |
| u_low3 |
+--------+

Set Password Validation Plugin to the MEDIUM level

When validate_password_policy is set to MEDIUM (or 1) it checks

• the length i.e. validate_password_length >= 8 (by default)
• numeric
• lowercase/uppercase
• special characters

mysql> 
SET GLOBAL validate_password_policy = 1;


SHOW VARIABLES LIKE 'validate_password_policy';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| validate_password_policy | MEDIUM |
+--------------------------+--------+

mysql>
-- NOK because password length < 8, no special character, nor numeric, nor uppercase
CREATE USER u_medium1 IDENTIFIED by 'p';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirement

-- NOK because no special character, nor uppercase
CREATE USER u_medium2 IDENTIFIED by 'p2345678';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

-- NOK because no uppercase
CREATE USER u_medium3 IDENTIFIED by 'p_345678';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

-- NOK because no uppercase
CREATE USER u_medium4 IDENTIFIED by 'p_p45678';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements


-- OK because password length >= 8, numeric, lowercase/uppercase, special character
CREATE USER u_medium5 IDENTIFIED by 'p_P45678';
Query OK, 0 rows affected (0.00 sec)



mysql>
-- new users created
SELECT user FROM mysql.user WHERE user LIKE 'u%';
+-----------+
| user      |
+-----------+
| u_low2    |
| u_low3    |
| u_medium5 |
+-----------+

Set Password Validation Plugin to the STRONG level

When validate_password_policy is set to STRONG (or 2) it checks

• the length i.e. validate_password_length >= 8 (by default)
• numeric
• lowercase/uppercase
• special characters
• dictionary file

mysql> 
SET GLOBAL validate_password_policy = 2;


SHOW VARIABLES LIKE 'validate_password_policy';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| validate_password_policy | STRONG |
+--------------------------+--------+

The main difference with the medium policy is the possibility to use a dictionary file to for checking password against. Set validate_password_dictionary_file variable. By default, this variable has an empty value and dictionary checks are not performed.

-- No dictionary file by default
mysql> 
SHOW VARIABLES LIKE 'validate_password_dictionary_file';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| validate_password_dictionary_file |       |
+-----------------------------------+-------+

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 Validation Plugin
• MySQL 5.7 Password Validation Plugin Installation
• MySQL 5.7 Password Validation Plugin Options and Variables

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