MySQL Secure Deployment Guide  /  Installing the MySQL Password Validation Plugin

Chapter 6 Installing the MySQL Password Validation Plugin

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

The plugin implements two capabilities:

  • In statements that assign a password supplied as a cleartext value, the plugin checks the password against the current password policy and rejects it if it is weak. This affects the ALTER USER, CREATE USER, GRANT, and SET PASSWORD statements. Passwords given as arguments to the PASSWORD() function are checked as well.

  • The VALIDATE_PASSWORD_STRENGTH() SQL function assesses the strength of potential passwords. The function takes a password argument and returns an integer from 0 (weak) to 100 (strong).

The validate_password plugin provides three levels of password checking: LOW, MEDIUM, and STRONG. The default is MEDIUM; controlled by the validate_password_policy configuration option. The policies implement increasingly strict password tests.

  • The LOW policy tests password length only. Passwords must be at least 8 characters long.

  • The MEDIUM policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase character, 1 uppercase character, and 1 special (nonalphanumeric) character.

  • The STRONG policy adds the condition that password substrings of length 4 or longer must not match words in the dictionary file, if one has been specified.

In addition, the validate_password plugin can reject passwords that match the user name part of the effective user account for the current session, either forward or in reverse. To enable this capability, you must enable the validate_password_check_user_name system variable.

To install and configure the password validation plugin:

  1. Add these options under the [mysqld] option group in the MySQL configuration file (/etc/my.cnf):

    plugin-load-add=validate_password.so
    validate-password=FORCE_PLUS_PERMANENT
    validate_password_policy=MEDIUM
    validate_password_check_user_name=1
    • plugin-load-add=validate_password.so

      Loads the validate_password.so plugin library each time the server is started.

    • validate-password=FORCE_PLUS_PERMANENT

      Prevents the server from running without the password-validation plugin, and server startup fails if the plugin does not initialize successfully.

    • validate_password_policy=MEDIUM

      Specifies that passwords must be at least 8 characters long, contain at least 1 numeric character, 1 lowercase character, 1 uppercase character, and 1 special (nonalphanumeric) character. MEDIUM is the default setting.

    • validate_password_check_user_name=1

      Rejects passwords that match the user name part of the effective user account for the current session, either forward or in reverse.

  2. To verify plugin installation, restart the server and examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement:

    $> systemctl restart mysqld
    $> cd /usr/local/mysql 
    $> bin/mysqladmin -u root -p version
    Enter password: (enter root password here)
    mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
           FROM INFORMATION_SCHEMA.PLUGINS
           WHERE PLUGIN_NAME LIKE 'validate%';
    +-------------------+---------------+
    | PLUGIN_NAME       | PLUGIN_STATUS |
    +-------------------+---------------+
    | validate_password | ACTIVE        |
    +-------------------+---------------+
  3. To verify that the password validation plugin works, attempt to create a user with a non-compliant password:

    mysql> CREATE USER 'bob.smith'@'localhost' IDENTIFIED BY 'abc';
    ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

For more information about the password validation plugin, see The Password Validation Plugin.