Password Validation Plugin in MySQL5.6

Table of Contents

1. What is Password Validation Plugin
2. Why we need Password Validation Plugin
3. Pre–checks for Implementation
4. Installation and UN-installation steps
4.1 Run-time password plugin installation
4.2 Permanent password plugin installation
5. Password validation plugin options and variables
6. Test scenarios

1.What is Password Validation Plugin

The Password Validation Plugin is an add on which is supporting from MySQL5.6.6 version. Its make sure that the end user provided password meet certain minimal security criteria.

The password validate plugin check user password as they are set through SET PASSWORD and GRANT statements, it will allow if its meet the password criteria else it will reject.

2. Why we need Password Validation Plugin
As of now you can set MySQL password as your wish, even you can set empty password or single charter password also. Have you think about if some one/hacker crack your password what happen your valuable data ?
To avoid such situation strongly recommend to implement the password validate plugin which emphasis to you set more secure password.

3. Pre–checks for Implementation
Before install password validation plugin make sure below points:
3.1. Password validation plugin file “validate_password.so” under MySQL liblery plugin directory.
Login into MySQL and check plugin directory path:
mysql> select @@plugin_dir;
+——————————————+
|         @@plugin_dir                      |
+——————————————+
|        /var/lib/mysql/plugin/             |
+——————————————+
Check it now on OS level
-bash-4.1$ ls -l /var/lib/mysql/plugin/validate_password.so
Expected Output:
-rwxr-xr-x 1 mysql mysql 136K Aug 27 2016 /var/lib/mysql/plugin/validate_password.so

3.2. Login into respective MySQL instance and check whether already the password validate plugin installed or not.
If password validation plugin is not installed:

Expected Output:
mysql> show plugins;
+—————————-+—————+—————————–+——————+————————-+
|               Name         |   Status   |          Type                  |       Library  |     License            |
+—————————-+————–+——————————+——————+————————-+
| binlog                      |  ACTIVE | STORAGE ENGINE  | NULL           | PROPRIETARY  |
| sha256_password | ACTIVE  | AUTHENTICATION | NULL           | PROPRIETARY  |

| audit_log                | ACTIVE  | AUDIT                         | audit_log.so | PROPRIETARY |
+—————————+—————+——————————-+——————–+———————–+

If password validation plugin is installed:
Expected Output:
mysql> show plugins;
+—————————-+—————+—————————+—————————–+————————-+
|               Name         |   Status   |               Type          |       Library               |     License            |
+—————————-+————–+—————————-+——————————+————————-+
| binlog                      | ACTIVE |STORAGE ENGINE |NULL                           | PROPRIETARY  |
| sha256_password | ACTIVE |AUTHENTICATION|NULL                           | PROPRIETARY  |

| audit_log                 | ACTIVE |AUDIT                       |audit_log.so                 |PROPRIETARY |
|validate_password| ACTIVE| validate password   |validate_password.so|PROPRIETARY |
+—————————+—————+—————————-+———————————+———————–+

other way to cross verify on information_schema using below query.
If password validation plugin is not installed:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE ‘validate%’;
Empty set (0.03 sec)

If password validation plugin is installed:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE ‘validate%’;
+——————————-+—————————-+
| PLUGIN_NAME         | PLUGIN_STATUS   |
+——————————-+—————————-+
| validate_password  | ACTIVE                   |
+——————————-+—————————-+

4. Installation and UN-installation steps

If the password validate plugin not installed then proceed with below steps.

4.1 Login to respective MySQL instance and install the password plugin during the run time.
mysql> INSTALL PLUGIN validate_password SONAME ‘validate_password.so’;
Query OK, 0 rows affected (0.13 sec)

Once installation get completed you can see below expected output.
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE ‘validate%’;
+——————————+—————————+
| PLUGIN_NAME       | PLUGIN_STATUS |
+——————————+—————————+
| validate_password | ACTIVE                   |
+—————————–+—————————-+

mysql> show plugins;
+—————————-+—————+—————————+—————————–+————————-+
|               Name         |   Status   |               Type          |       Library               |     License            |
+—————————-+————–+—————————-+——————————+————————-+
| binlog                      | ACTIVE |STORAGE ENGINE |NULL                           | PROPRIETARY  |
| sha256_password | ACTIVE |AUTHENTICATION|NULL                           | PROPRIETARY  |

| audit_log                 | ACTIVE |AUDIT                       |audit_log.so                 |PROPRIETARY |
|validate_password| ACTIVE| validate password|validate_password.so|PROPRIETARY|
+—————————+—————+—————————-+———————————+———————–+

[same way we can uninstall the password validate plugin during run time]
And you can see password validation variables, if its not installed these variables not exist.

mysql> SHOW GLOBAL 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          |
+————————————————————+—————-+

Above variables values are default, you can change variable’s values by dynamically except “validate_password_dictionary_file” variable.
By default, this variable has an empty value and dictionary checks are not performed. To enable dictionary checks, you must set this variable to a nonempty value.

4.2 Permanent password plugin installation.
It will help to prevent removing the password plugin at run time and not require to load the plugin every time after start-up server.

Make it below entry in [mysqld] session on my.cnf file

[mysqld]
plugin-load = validate_password.so
validate-password = FORCE_PLUS_PERMANENT
validate_password_policy = 1
validate_password_dictionary_file = /var/lib/mysql/plugin/password_dictionary.txt

Once make it above entries restart the MySQL for change get reflect.
Login into MySQL and verify.
Expected output:
mysql> SHOW VARIABLES LIKE ‘validate_password%’;
+———————————————————+————————————————————————–+
| Variable_name                                         |                               Value                                                    | +——————————————————–+————————————————————————–+
| validate_password_dictionary_file         |/var/lib/mysqlplugin/password_dictionary.txt       |
| 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                                                                                      |
+————————————–+——————————————————————————————-+

5. Password validation plugin options and variables
Below variables are controlling activation of password validation plugin.
Plugin_dir
Plugin_load
Validate_password
Validate_password_dictionary_file
Validate_password_length
Validate_password_mixed_case_count
Validate_password_number_count
Validate_password_policy
Validate_password_special_char_count

Plugin_dir:
The path name of the plugin directory, if we not mentioned then I will check in default path : BASEDIR/lib/plugin.

Plugin_load:
This option tells the server to load the named plugins at start-up.

Validate_password:
This option controls how the server loads the validate_password plugin at start-up, If its enabled then only we could see its variables.
Options:
ON – If the plugin fails to initialize the server runs with the plugin disabled.
OFF – Its tell the server to disable the plugin.
FORCE – If plugin initialization fails, the server does not start.
FORCE_PLUS_PERMANENT – Like FORCE, but in addition prevents the plugin
from being unloaded at run-time.

Validate_password_dictionary_file:
The path name of the dictionary file used by validate_password plugin for checking passwords. Its contents should be lowercase, one word per line.
Contents are treated as having a character set of utf8. The maximum permitted file size is 1MB.

Validate_password_length:
Server will not set the value less than the value of below expression

validate_password_number_count
+ validate_password_special_char_count
+ (2 * validate_password_mixed_case_count)

If you changed above variables value according to re-set the ‘Validate_password_length’ values and it writes a message to the error log.
Validate_password_mixed_case_count:
It check minimum number of lowercase and uppercase characters in that passwords.
Validate_password_number_count:
The minimum number of numeric (digit) characters that passwords checked by the validate_password plugin must have if the password policy is MEDIUM or stronger.

Validate_password_policy:
It value can be specified using numeric values 0, 1, 2, or the corresponding symbolic values LOW, MEDIUM, STRONG

Policy                            Tests Performed
0 or LOW                      Length
1 or MEDIUM              Length; numeric, lowercase/uppercase, and special characters
2 or STRONG               Length; numeric, lowercase/uppercase, special characters and
                                        dictionary file

Validate_password_special_char_count :
The minimum number of non-alphanumeric characters that passwords checked by the validate_password plugin must have if the password policy is MEDIUM or stronger.

Test scenarios:
After Installation verify whether password validate plugin working as expected or not.
Test #1.
Create a test user and try to set password with not satisfying condition.
mysql> GRANT SELECT ON *.* TO ‘testpass’@’172.16.%.%’ IDENTIFIED BY ‘Cal5spl8’;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Now try create a user with satisfying password condition.
mysql> GRANT SELECT ON *.* TO ‘testpass’@’172.16.%.%’ IDENTIFIED BY ‘Cal5$pl8’;
Query OK, 0 rows affected (0.01 sec)

Test #2:
Create a user and try to set dictionary password.
Dictionary file:
$ cat /var//lib/mysql/plugin/password_dictionary_assic.txt
P@ssw0rd
wa@!2go0

Try to set dictionary password.
mysql> GRANT SELECT ON *.* TO ‘testpass’@’172.16.%.%’ IDENTIFIED BY ‘P@ssw0rd’;ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Try to set non dictionary password.
mysql> GRANT SELECT ON *.* TO ‘testpass’@’172.16.%.%’ IDENTIFIED BY ‘P@ssw1rd’;Query OK, 0 rows affected (0.01 sec)

 

References : https://dev.mysql.com/doc/refman/5.6/en/validate-password-plugin.html

================================== END ===================================

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s