MySQL Security – Password Validation Plugin

March 1, 2018

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 TDEAudit, Firewall, Password Management, User Account Locking, The Connection-Control Plugins, etc…

MySQL Security

In this article, 1st of a MySQL 5.7 Security series, we will see how to enforce Strong Passwords with Password Validation Plugin when using MySQL 5.7.

Authentication with ID and password is a very simple and common (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 passwords meet certain minimal security requirements, using the MySQL Password validation plugin!

 

Password Validation Plugin

 

The Password validation plugin serves to test passwords and improve security. It exposes a set of system variables that enable you to define password policy.

For ALTER USER, CREATE USER, GRANT, and SET PASSWORD statements the plugin checks the password against the current password policy and rejects it if it is weak.

 

Examples are made with MySQL CE 5.7.21 on Linux:

 

Installation

Plugins are located in the… plugin directory. To know where is your MySQL plugin directory you can use SHOW VARIABLES :

 

Use the regular INSTALL PLUGIN statement:

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.

Alternatively you can modify the MySQL configuration file (e.g. my.cnf or my.ini) and reboot the instance.

e.g.

 

When installed some system and status variables are available:

They are described here.

 

Playtime

Let’s play a little a bit with the Password Validation Plugin.

 

Set Password Validation Plugin to the LOW level

When validate_password_policy is set to LOW (or 0) it checks only the length i.e. validate_password_length >= 8 (by default)

 

Warning

Passwords in the following examples are not secure. Do NOT use trivial passwords!

 

User creation that is not satisfy the policy will failed

 

Set Password Validation Plugin to the MEDIUM level

When validate_password_policy is set to MEDIUM (or 1) it checks

  • the length i.e. validate_password_length >= 8 (by default)
  • numeric
  • lowercase/uppercase
  • special characters

 

 

 

Set Password Validation Plugin to the STRONG level

When validate_password_policy is set to STRONG (or 2) it checks

  • the length i.e. validate_password_length >= 8 (by default)
  • numeric
  • lowercase/uppercase
  • special characters
  • dictionary file

 

 

The main difference with the medium policy is the possibility to use a dictionary file to for checking password against. Set validate_password_dictionary_file variable. By default, this variable has an empty value and dictionary checks are not performed.

 

 

In order to go further

MySQL 5.7 Security Series

  1. Password Validation Plugin
  2. Password Management
  3. User Account Locking
  4. The Connection-Control Plugins
  5. Enterprise Audit
  6. Enterprise Transparent Data Encryption (TDE)
  7. Enterprise Firewall

 

Reference Manual

 

MySQL Security

 

Thanks for using MySQL!

Follow me on twitter

5 Responses to “MySQL Security – Password Validation Plugin”

  1. […] data including some advanced features like Transparent Data Encryption aka TDE,  Audit, Firewall, Password Validation Plugin, […]

  2. […] advanced features like Transparent Data Encryption aka TDE,  Audit, Firewall, Password Management, Password Validation Plugin, […]

  3. […] MySQL Security – Password Validation Plugin […]

  4. […] advanced features like Transparent Data Encryption aka TDE,  Audit, Firewall, Password Management, Password Validation Plugin, User Account Locking, […]

  5. […] some advanced features like Transparent Data Encryption aka TDE,  Firewall, Password Management, Password Validation Plugin, […]

Leave a Reply