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)