In systems nowadays, improving security is a must! One of the weakest links in the security system is the user password from where an attacker can enter. In order to improve password strength and security, MySQL provides a plugin called “Validation plugin” which can be configured to enforce a set of rules for passwords.
Installation
The plugin can be enabled by executing the following at runtime:
1 | INSTALL PLUGIN validate_password SONAME 'validate_password.so'; |
Or by adding the plugin in the configuration file, but this requires bouncing MySQL for it to take effect:
1 2 | [mysqld] plugin-load-add=validate_password.so |
It’s also suggested to add the following variable in my.cnf so that the plugin cannot be removed at runtime (also requires a MySQL bounce to take effect):
1 2 | [mysqld] validate-password=FORCE_PLUS_PERMANENT |
Checking Installation
You can check if the plugin is installed by either checking plugins:
1 2 3 4 5 6 7 8 | mysql> SHOW PLUGINS \G … *************************** 53. row *************************** Name: validate_password Status: ACTIVE Type: VALIDATE PASSWORD Library: validate_password.so License: GPL |
Or by checking if the following variables are enabled in MySQL:
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> show global variables like 'validate%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password_check_user_name | OFF | | 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) |
Usage
A short summary of the variables that affect the password is:
Validate_password_policy: Determines password strength policy which can be LOW, MEDIUM, or STRONG. Depending on which policy is set, some variables might be ignored.
| Policy | Variables checked |
| 0 or LOW | Length |
| 1 or MEDIUM | Length; numeric, lowercase/uppercase, and special characters |
| 2 or STRONG | Length; numeric, lowercase/uppercase, and special characters; dictionary file |
Validate_password_length: Minimum number of characters
Validate_password_mixed_case_count: Minimum number of upper and lower case characters
Validate_password_number_count: Minimum number of numeric characters
Validate_password_special_char_count: Minimum number of non-alpha-numeric characters
Validate_password_check_user_name: If enabled, prevents a user from setting a password equal to its username or the reverse of it
Validate_password_dictionary_file: Path for dictionary file with a maximum size of 1 MB containing one word per line. Any password with a substring that matches any of the words from the dictionary cannot be used, the check is case insensitive, and the minimum length of the dictionary is 4 letters per word. After modifying the dictionary file, “SET GLOBAL Validate_password_dictionary_file = ‘path_to_dictionary’;” must be executed again for the dictionary to be reloaded.
Note: After increasing passwords policies, current passwords are not automatically expired and might potentially not fit in the new policy. A password expiration method should be used to force users to set a new password after a fixed number of days or when password policies have changed.
Examples
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> show global variables like 'validate%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password_check_user_name | OFF | | 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) |
Trying to set the following passwords result in an error:
1 2 3 4 | mysql> set password = '12345678'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> set password = '123456Ab'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements |
Password is only accepted if having at least 8 characters, including 1 special character, 1 numeric, 1 lower and 1 uppercase like the following:
1 2 | mysql> set password = '123456Ab@'; Query OK, 0 rows affected (0.00 sec) |
If setting:
1 2 | mysql> set global validate_password_policy = 'STRONG'; mysql> set global validate_password_dictionary_file = '/tmp/dictionary.txt'; |
And having the following dictionary file (remember a minimum of 4 letter words)
1 2 | $ cat /tmp/dictionary.txt man1 |
The following passwords that include case insensitive string “man1” are not accepted:
1 2 3 4 5 6 | mysql> SET PASSWORD = ‘MAN12345#A'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> SET PASSWORD = 'man12345#A'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> SET PASSWORD = ‘2345man1#A’; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements |
But the following password is accepted since it complies with all the requirements:
1 2 | mysql> set password = '2345man2#A'; Query OK, 0 rows affected (0.00 sec) |
Use cases
Each company will have different password requirements depending on their needs and policies, but for some businesses and companies there are already standard in place, such as:
- PCI/DSS (Payment Card Industry Data Security Standards) which has various policies regarding security, but regarding passwords the requirements are:
- Require a minimum length of at least seven characters.
- Contain both numeric and alphabetic characters.
Which can be implemented by setting the following:
1 2 3 4 5 | SET GLOBAL validate_password_length = 7; SET GLOBAL validate_password_mixed_case_count = 1; SET GLOBAL validate_password_number_count = 1; SET GLOBAL validate_password_special_char_count = 0; SET GLOBAL validate_password_policy = ‘MEDIUM’; |
Note: The above settings might seem weak as per nowadays standards, but the above settings are minimum to comply with PCI DSS and stronger policies can be used. Also, PCI/DSS has other requirements (which cannot be tackled down by validation plugin) such as password expiration policies, lockdown after 6 failed attempts, and more, and does not rely solely on password strength.
- NIST (National Institute of Technology) password minimum requirements only consists of a minimum length of 8 (or 6 for activation passwords) which can be implemented:
1 2 | SET GLOBAL validate_password_length = 8; SET GLOBAL validate_password_policy = ‘LOW’; |
NIST encourages to use more complex passwords, including special characters and dictionaries containing previously breached passwords, dictionary words, repetitive strings and common patterns such as “aaaa” or “1234” which can be fulfilled with the following config:
1 2 3 4 5 6 | SET GLOBAL validate_password_length = 8; SET GLOBAL validate_password_mixed_case_count = 1; SET GLOBAL validate_password_number_count = 1; SET GLOBAL validate_password_special_char_count = 1; SET GLOBAL validate_password_dictionary_file = 'dictionary.txt'; SET GLOBAL validate_password_policy = STRONG’; |
Having a dictionary file with prohibited words. Since the maximum dictionary size is 1 MB, the number of words and patterns that can exist in the dictionary might be limited to the required implementation of policy, although other measures such as password locking after many failures should also be implemented to comply with NIST.
OWASP (Open Web Application Security Project) has the following minimal requirements:
- At least 1 uppercase character (A-Z)
- At least 1 lowercase character (a-z)
- At least 1 digit (0-9)
- At least 1 special character including punctuation marks & spaces
- Be at least 10 characters long.
- Do not have more than 2 identical characters in a row (‘aaa’, ‘bbb’, etc..)
Compliance with the following configuration:
1 2 3 4 5 6 | SET GLOBAL validate_password_length = 10; SET GLOBAL validate_password_mixed_case_count = 1; SET GLOBAL validate_password_number_count = 1; SET GLOBAL validate_password_special_char_count = 1; SET GLOBAL validate_password_dictionary_file = 'dictionary.txt'; SET GLOBAL validate_password_policy = STRONG’; |
And having populated the dictionary with all possible combinations of consecutive characters.
Conclusion
The validation plugin can greatly help in enforcing passwords policies for increased system security and can be set up in a fast and easy way, but there should also be other security mechanisms to complement validation plugin such as:
– add delay to successive login retries
– limit user attempts and lock account (connection control plugin + lock account)
Do I need to install anything before activating this plugin in my system?
Hi trangtriqc! No, you do not need to activate anything prior activating the plugin. Plugin files must be in mysql’s plugin folder (which are by default)