Improving MySQL Password Security with Validation Plugin

By
MySQL Password Security Plugin

MySQL Password Security PluginIn 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:

 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:

[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):

[mysqld]
validate-password=FORCE_PLUS_PERMANENT

Checking Installation

You can check if the plugin is installed by either checking plugins:

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:

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

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:

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:

mysql> set password = '123456Ab@';
Query OK, 0 rows affected (0.00 sec)

If setting:

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)

$ cat /tmp/dictionary.txt
man1

The following passwords that include case insensitive string “man1” are not accepted:

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:

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:

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:
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:

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:

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:

– password expiration

– add delay to successive login retries

– limit user attempts and lock account (connection control plugin + lock account)

– secure password transmission

– password reuse policy


by Carlos Tutte via Percona Database Performance Blog

Comments

Post a Comment