MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide

6.4.3.2 Password Validation Plugin Options and Variables

This section describes the options, system variables, and status variables that validate_password provides to enable its operation to be configured and monitored.

Password Validation Plugin Options

To control activation of the validate_password plugin, use this option:

Password Validation Plugin System Variables

If the validate_password plugin is enabled, it exposes several system variables that enable configuration of password checking: 

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

To change how passwords are checked, you can set these system variables at server startup or at runtime. The following list describes the meaning of each variable.

  • validate_password_dictionary_file

    Command-Line Format --validate-password-dictionary-file=file_name
    System Variable validate_password_dictionary_file
    Scope Global
    Dynamic (≥ 5.6.26) Yes
    Dynamic (≤ 5.6.25) No
    Type File name

    The path name of the dictionary file that validate_password uses for checking passwords. This variable is unavailable unless validate_password is installed.

    By default, this variable has an empty value and dictionary checks are not performed. For dictionary checks to occur, the variable value must be nonempty. If the file is named as a relative path, it is interpreted relative to the server data directory. File 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.

    For the dictionary file to be used during password checking, the password policy must be set to 2 (STRONG); see the description of the validate_password_policy system variable. Assuming that is true, each substring of the password of length 4 up to 100 is compared to the words in the dictionary file. Any match causes the password to be rejected. Comparisons are not case-sensitive.

    For VALIDATE_PASSWORD_STRENGTH(), the password is checked against all policies, including STRONG, so the strength assessment includes the dictionary check regardless of the validate_password_policy value.

    Before MySQL 5.6.26, changes to the dictionary file while the server is running require a restart for the server to recognize the changes. As of MySQL 5.6.26, validate_password_dictionary_file can be set at runtime and assigning a value causes the named file to be read without a server restart.

  • validate_password_length

    Command-Line Format --validate-password-length=#
    System Variable validate_password_length
    Scope Global
    Dynamic Yes
    Type Integer
    Default Value 8
    Minimum Value 0

    The minimum number of characters that validate_password requires passwords to have. This variable is unavailable unless validate_password is installed.

    The validate_password_length minimum value is a function of several other related system variables. The value cannot be set less than the value of this expression: 

    validate_password_number_count
+ validate_password_special_char_count
+ (2 * validate_password_mixed_case_count)

    If validate_password adjusts the value of validate_password_length due to the preceding constraint, it writes a message to the error log.

  • validate_password_mixed_case_count

    Command-Line Format --validate-password-mixed-case-count=#
    System Variable validate_password_mixed_case_count
    Scope Global
    Dynamic Yes
    Type Integer
    Default Value 1
    Minimum Value 0

    The minimum number of lowercase and uppercase characters that validate_password requires passwords to have if the password policy is MEDIUM or stronger. This variable is unavailable unless validate_password is installed.

    For a given validate_password_mixed_case_count value, the password must have that many lowercase characters, and that many uppercase characters.

  • validate_password_number_count

    Command-Line Format --validate-password-number-count=#
    System Variable validate_password_number_count
    Scope Global
    Dynamic Yes
    Type Integer
    Default Value 1
    Minimum Value 0

    The minimum number of numeric (digit) characters that validate_password requires passwords to have if the password policy is MEDIUM or stronger. This variable is unavailable unless validate_password is installed.

  • validate_password_policy

    Command-Line Format --validate-password-policy=value
    System Variable validate_password_policy
    Scope Global
    Dynamic Yes
    Type Enumeration
    Default Value 1
    Valid Values

    0

    1

    2

    The password policy enforced by validate_password. This variable is unavailable unless validate_password is installed.

    The validate_password_policy value can be specified using numeric values 0, 1, 2, or the corresponding symbolic values LOW, MEDIUM, STRONG. The following table describes the tests performed for each policy. For the length test, the required length is the value of the validate_password_length system variable. Similarly, the required values for the other tests are given by other validate_password_xxx variables.

    Policy Tests Performed
    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_special_char_count

    Command-Line Format --validate-password-special-char-count=#
    System Variable validate_password_special_char_count
    Scope Global
    Dynamic Yes
    Type Integer
    Default Value 1
    Minimum Value 0

    The minimum number of nonalphanumeric characters that validate_password requires passwords to have if the password policy is MEDIUM or stronger. This variable is unavailable unless validate_password is installed.

Password Validation Plugin Status Variables

If the validate_password plugin is enabled, it exposes status variables that provide operational information: 

mysql> SHOW STATUS LIKE 'validate_password%';
+-----------------------------------------------+---------------------+
| Variable_name                                 | Value               |
+-----------------------------------------------+---------------------+
| validate_password_dictionary_file_last_parsed | 2015-06-29 11:08:51 |
| validate_password_dictionary_file_words_count | 1902                |
+-----------------------------------------------+---------------------+

The following list describes the meaning of each status variable.

MySQL Documentation Library