Skip to content

Password Validation in MySQL

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)

 

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Grégory Steulet

Grégory Steulet

Grégory Steulet is Partner Manager and CFO at dbi services. He previously served as CFO and COO of the Sequotech Group. Earlier in his career, he spent more than ten years in database and infrastructure management, with experience in Oracle technologies, high availability, Avaloq banking applications and open-source technologies such as MySQL and Unix/Linux. Although his current role is no longer focused on database administration, he continues to share his experience through occasional blog posts. Before joining dbi services, Grégory was a Senior Consultant at Trivadis and an IT Administrator at Box Telecom. He holds an Executive MBA, a Bachelor's degree in Business Administration and Computer Science, and Certificates of Advanced Studies in Mergers and Acquisitions and Data Protection. His industry experience includes telecommunications, financial services, logistics and pharmaceuticals.View Author posts

42 posts since February 2011

Most read in Database

The Database posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *