{"id":2136,"date":"2018-03-01T10:58:14","date_gmt":"2018-03-01T09:58:14","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=2136"},"modified":"2020-05-27T07:31:11","modified_gmt":"2020-05-27T06:31:11","slug":"mysql-security-password-validation-plugin","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2018\/03\/01\/mysql-security-password-validation-plugin\/","title":{"rendered":"MySQL Security \u2013 Password Validation Plugin"},"content":{"rendered":"

When thinking about security within a MySQL installation, you should consider a wide range of possible procedures \/ best practices and how they affect the security of your MySQL server and related applications. MySQL provides many tools \/ features \/ plugins in order to protect your data including some advanced features like Transparent Data Encryption aka TDE<\/a>,\u00a0Audit<\/a>, Data Masking & De-Identification<\/a>, Firewall<\/a>, Password Management<\/a>, User Account Locking<\/a>, The Connection-Control Plugins<\/a>, etc…<\/p>\n

\"MySQL<\/p>\n

In this article, 1st of a MySQL Security<\/strong> series, we will see how to enforce Strong Passwords\u00a0with\u00a0Password Validation Plugin<\/a>\u00a0when using MySQL 5.7.<\/p>\n

Authentication with ID and password is a very simple and\u00a0common (because it’s simple) way to secure the access to a resource, however the password can be the weak point of this system. In order to increase the security level, you can required that your user\u00a0passwords meet certain minimal security requirements<\/strong>, using the MySQL\u00a0Password\u00a0validation plugin!<\/p>\n

Password Validation Plugin<\/h2>\n

The Password\u00a0validation plugin serves to test passwords and improve security. It exposes a set of system variables that enable you to define password policy.<\/p>\n

For\u00a0ALTER USER<\/a>, CREATE USER<\/a>, GRANT<\/a>, and SET PASSWORD<\/a> statements\u00a0the plugin checks the password against the current password policy and rejects it if it is weak.<\/p>\n

Examples are made with MySQL CE 5.7.21 on Linux:<\/p>\n

mysql> SHOW VARIABLES LIKE 'version%';\n+-------------------------+------------------------------+\n| Variable_name           | Value                        |\n+-------------------------+------------------------------+\n| version                 | 5.7.21                       |\n| version_comment         | MySQL Community Server (GPL) |\n| version_compile_machine | x86_64                       |\n| version_compile_os      | Linux                        |\n+-------------------------+------------------------------+<\/pre>\n
Installation<\/h3>\n
Plugins are located in the… plugin directory. To know where is your MySQL plugin directory you can use SHOW VARIABLES :<\/p>\n
mysql> \nSHOW VARIABLES LIKE 'plugin_dir';\n+---------------+--------------------------+\n| Variable_name | Value                    |\n+---------------+--------------------------+\n| plugin_dir    | \/usr\/lib64\/mysql\/plugin\/ |\n+---------------+--------------------------+\n\nsystem ls -l \/usr\/lib64\/mysql\/plugin\/ | grep validate\n-rwxr-xr-x 1 root root   29336 Dec 28 04:07 validate_password.so<\/pre>\n
Use the regular\u00a0INSTALL PLUGIN<\/a> statement:<\/p>\n
-- Install validate_password plugin\nmysql> \nINSTALL PLUGIN validate_password SONAME 'validate_password.so';\n\n-- Check validate_password status\nSELECT PLUGIN_NAME, PLUGIN_STATUS \nFROM INFORMATION_SCHEMA.PLUGINS \nWHERE PLUGIN_NAME LIKE 'validate%';\n+-------------------+---------------+\n| PLUGIN_NAME       | PLUGIN_STATUS |\n+-------------------+---------------+\n| validate_password | ACTIVE        |\n+-------------------+---------------+<\/pre>\n
INSTALL PLUGIN loads the plugin, and also registers it in the mysql.plugins system table to cause the plugin to be loaded for each subsequent normal server startup.<\/p>\n
Alternatively you can modify the MySQL configuration file (e.g. my.cnf or my.ini) and reboot the instance.<\/p>\n
e.g.<\/p>\n
# sample from my.cnf\n[mysqld]\nplugin-load-add=validate_password.so<\/pre>\n
When installed some system and status variables are available:<\/p>\n
mysql> SHOW VARIABLES LIKE 'validate%';\n+--------------------------------------+--------+\n| Variable_name                        | Value  |\n+--------------------------------------+--------+\n| validate_password_check_user_name    | OFF    |\n| validate_password_dictionary_file    |        |\n| validate_password_length             | 8      |\n| validate_password_mixed_case_count   | 1      |\n| validate_password_number_count       | 1      |\n| validate_password_policy             | MEDIUM |\n| validate_password_special_char_count | 1      |\n+--------------------------------------+--------+\n\n\nSHOW STATUS LIKE 'validate%';\n+-----------------------------------------------+---------------------+\n| Variable_name                                 | Value               |\n+-----------------------------------------------+---------------------+\n| validate_password_dictionary_file_last_parsed | 2018-02-06 14:58:19 |\n| validate_password_dictionary_file_words_count | 0                   |\n+-----------------------------------------------+---------------------+<\/pre>\n
They are described here<\/a>.<\/p>\n
Playtime<\/h3>\n
Let’s play a little a bit with the Password Validation Plugin.<\/p>\n
Set Password Validation Plugin to the LOW level<\/h4>\n
When validate_password_policy<\/em><\/strong> is set to LOW<\/strong> (or 0<\/b>) it checks only the length i.e. validate_password_length >= 8 (by default)<\/p>\n
mysql> \nSET GLOBAL validate_password_policy = 0;\n\n\nSHOW VARIABLES LIKE 'validate_password_policy';\n+--------------------------+-------+\n| Variable_name            | Value |\n+--------------------------+-------+\n| validate_password_policy | LOW   |\n+--------------------------+-------+<\/pre>\n
Warning<\/span><\/p>\n
Passwords in the following examples are not secure. Do NOT use trivial passwords!<\/em><\/p>\n
User creation that is not satisfy the policy will failed<\/p>\n
mysql> \n-- NOK because password length < 8 \nCREATE USER u_low1 IDENTIFIED by 'p';\nERROR 1819 (HY000): Your password does not satisfy the current policy requirements\n\n\n-- OK because password length >= 8\nCREATE USER u_low2 IDENTIFIED by 'p2345678';\nQuery OK, 0 rows affected (0.01 sec)\n\nCREATE USER u_low3 IDENTIFIED by 'pppppppp';\nQuery OK, 0 rows affected (0.00 sec)\n\n\n\nmysql> \n-- new users created\nSELECT user FROM mysql.user WHERE user LIKE 'u%';\n+--------+\n| user   |\n+--------+\n| u_low2 |\n| u_low3 |\n+--------+<\/pre>\n
Set Password Validation Plugin to the MEDIUM level<\/h4>\n
When validate_password_policy<\/strong> is set to MEDIUM<\/strong> (or 1<\/strong>) it checks<\/p>\n