Table of contents
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
dominique says:
dear yannic,k, is it possible to install the plugin on mysql 5.5.. i’ve tried but it seems error..
Yannick Jaquier says:
Dear Dominique,
To be honest I have not tried. 5.5 is obsolete, moving to 5.6 you will get a performance boost and another one when moving to 5.7 (not yet tried 8.0 that is GA).
So the only good advise I can provide is to upgrade !
Yannick.