Introduction on validate_password plugin

Since version 5.6.6 MySQL provides a new security plugins named Password Validation Plugin. The password-validation plugin aims to test passwords strength and improve security. The goal of this blog is to provide you a short overview of the functionalities provided through this plugin and illustrate these functionalities with concrete examples.

As explained into the documentation The validate_password plugin implements two capabilities:

1. The plugin checks the password against the current password policy and rejects the password if it is weak
2. The VALIDATE_PASSWORD_STRENGTH() SQL function assesses the strength of potential passwords. The function takes a password argument and returns an integer from 0 (weak) to 100 (strong).

validate_password plugin implements three level of password checking that are described below:

  • LOW – policy tests password length only.
  • MEDIUM (Default) – policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase character, 1 uppercase character, and 1 special (nonalphanumeric) character
  • STRONG – policy adds the condition that password substrings of length 4 or longer must not match words in the dictionary file, if one has been specified.

validate_password plugin provides several checks that can be seen using the show variables command:

SHOW VARIABLES LIKE 'validate_password.%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| 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      |
+--------------------------------------+--------+
7 rows in set (0,01 sec)

Tests with validate_password.policy=LOW

First let’s set the validate_password.policy to LOW to check which tests are done by the plugin. It should only check password length.

SET GLOBAL validate_password.policy=LOW;

+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 8     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 1     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+
create user 'steulet'@'localhost' identified by '1234567';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

create user 'steulet'@'localhost' identified by '12345678';
Query OK, 0 rows affected (0,01 sec)

 

Tests with validate_password.policy=MEDIUM

MEDIUM policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase character, 1 uppercase character, and 1 special (nonalphanumeric) character

SET GLOBAL validate_password.policy=MEDIUM;
Query OK, 0 rows affected (0,00 sec)

SHOW VARIABLES LIKE 'validate_password.%';
+--------------------------------------+-------------------------------------+
| Variable_name | Value |
+--------------------------------------+-------------------------------------+
| validate_password.check_user_name | ON |
| 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 |
+--------------------------------------+-------------------------------------+
7 rows in set (0.00 sec)
create user 'hueber'@'localhost' identified by '12345678';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

create user 'hueber'@'localhost' identified by '1234567L';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

create user 'hueber'@'localhost' identified by '123456zL';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

create user 'hueber'@'localhost' identified by '12345!zL';
Query OK, 0 rows affected (0.01 sec)

 

Tests with validate_password.policy=STRONG

In order to check the validate_password.policy=STRONG I uploaded a password file used for brute force attack. You can download this file from: https://github.com/danielmiessler/SecLists/blob/master/Passwords/Most-Popular-Letter-Passes.txt

SET GLOBAL validate_password.dictionary_file='/u01/mysqldata/mysqld2/PasswordList';
Query OK, 0 rows affected (0,00 sec)
SET GLOBAL validate_password.policy=strong;
Query OK, 0 rows affected (0,00 sec)
SHOW VARIABLES LIKE 'validate_password.%';
+--------------------------------------+-------------------------------------+
| Variable_name | Value |
+--------------------------------------+-------------------------------------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | /u01/mysqldata/mysqld2/PasswordList |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | STRONG |
| validate_password.special_char_count | 1 |
+--------------------------------------+-------------------------------------+

7 rows in set (0.00 sec)

create user 'neuhaus'@'localhost' identified by 'Manager1;';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

create user 'neuhaus'@'localhost' identified by 'Password1;';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

If I decrease the validate_password.policy to medium, the plugin doesn’t check the dictionary file anymore:

SET GLOBAL validate_password.policy=medium;
Query OK, 0 rows affected (0,00 sec)

create user 'neuhaus'@'localhost' identified by 'Password1;';
Query OK, 0 rows affected (0,00 sec)

Function VALIDATE_PASSWORD_STRENGTH()

As explained above the validate_password_strength test a password and returns an integer from 0 (weak) to 100 (strong) representing the password strength.

select VALIDATE_PASSWORD_STRENGTH('abcd');
+------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('abcd') |
+------------------------------------+
| 25 |
+------------------------------------+
1 row in set (0.00 sec)
select VALIDATE_PASSWORD_STRENGTH('password');
+----------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('password') |
+----------------------------------------+
| 50 |
+----------------------------------------+
1 row in set (0.00 sec)
select VALIDATE_PASSWORD_STRENGTH('Manager1!');
+-----------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('Manager1!') |
+-----------------------------------------+
| 75 |
+-----------------------------------------+
1 row in set (0.00 sec)
select VALIDATE_PASSWORD_STRENGTH('aZbq!1)m8N');
+------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('aZbq!1)m8N') |
+------------------------------------------+
| 100 |
+------------------------------------------+
1 row in set (0.00 sec)