Enhanced password strength in MySQL with validate_password plugin

Preamble

Have you ever wanted to have Oracle profiles in MySQL. Means a feature allowing you to check that password are following few security rules and are strong. Starting with MySQL 5.6.6 this is possible using validate_password plugin. Not all functionalities of Oracle profiles are there but it is a good start for further enhancements…

Testing has been done with MySQL 5.7.14 running on Oracle Linux Server release 7.2. The base of the feature is available in MySQL 5.6.6, Oracle has just added few features over time…

Validate_password plugin installation

By default the validate_password plugin is not activated:

mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like 'validate%';
Empty set (0.00 sec)

It can be installed without restarting your MySQL instance. The below activation is persistent across reboot:

mysql> install plugin validate_password soname 'validate_password.so';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like 'validate%';
+-------------------+---------------+
| PLUGIN_NAME       | PLUGIN_STATUS |
+-------------------+---------------+
| validate_password | ACTIVE        |
+-------------------+---------------+
1 row in set (0.00 sec)

You have now also access to a set of new configuration variables:

mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| 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      |
+--------------------------------------+--------+
6 rows in set (0.00 sec)

So by default we have:

  • validate_password_dictionary_file: no validation in an external dictionary datafile (see below for an example of this cool feature)
  • validate_password_length: a password length of 8 characters minimum
  • validate_password_mixed_case_count: 1 lowercase or uppercase character (because validate_password_policy is set to MEDIUM)
  • validate_password_number_count: 1 digit minimum
  • validate_password_policy: MEDIUM password policy means performed checks are on Length; numeric lowercase/uppercase, and special characters”.
  • validate_password_special_char_count: 1 non alphanumeric characters minimum

Validate_password plugin testing

Then you cannot create or change a password that does not satisfy all the above listed rules:

mysql> create user 'account'@'localhost' identified by 'secure_password';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'account'@'localhost' identified by 'Secure_password';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'account'@'localhost' identified by 'secure_password1';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'account'@'localhost' identified by 'Secure_password1';
Query OK, 0 rows affected (0.00 sec)

As there is a need of one special character I’m wondering why the last test is successful. Anyway all other failures are inline with expected rules.

Now let’s imagine for legacy reason all your company accounts are created with this well known ‘Secure_password1’ value. How to forbid further its usage ? With the dictionary file for example !

I create a file and insert into it the value I’d like to forbid. The inserted value must be lowercase. The maximum file size is 1 MB:

[mysql@server4 ~]$ cat /tmp/dictionary_file
secure_password1

As of MySQL 5.6.26 the dictionary file can be read without restarting the MySQL server:

mysql> set global validate_password_dictionary_file='/tmp/dictionary_file';
Query OK, 0 rows affected (0.00 sec)
mysql> show status like 'validate_password%';
+-----------------------------------------------+---------------------+
| Variable_name                                 | Value               |
+-----------------------------------------------+---------------------+
| validate_password_dictionary_file_last_parsed | 2016-09-07 15:15:13 |
| validate_password_dictionary_file_words_count | 1                   |
+-----------------------------------------------+---------------------+
2 rows in set (0.00 sec)

You also need to change the password policy to STRONG (to check Length; numeric, lowercase/uppercase, and special characters; dictionary file):

mysql> set global validate_password_policy='STRONG';
Query OK, 0 rows affected (0.00 sec)

Now if I try to create a user with this password (even if the password fill the security requirements):

mysql> create user 'account'@'localhost' identified by 'Secure_password1';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Worth to mention another interesting variable default_password_lifetime, as of MySQL 5.7.4, that define the password expiration policy. If you do not want your user to keep same password for ages this is the parameter to use…

There is another interesting procedure called VALIDATE_PASSWORD_STRENGTH, unfortunately unlike Oracle profile it can only be used to get an idea of a password strength but cannot be used to force user to satisfy a minimum value of password strength. Returned values are between 0 and 100:

mysql> select validate_password_strength('Secure_password1');
+------------------------------------------------+
| validate_password_strength('Secure_password1') |
+------------------------------------------------+
|                                             75 |
+------------------------------------------------+
1 row in set (0.00 sec)

References

This entry was posted in MySQL and tagged . Bookmark the permalink.

Leave a Reply

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

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>