Documentation Home
Security in MySQL
Related Documentation Download this Excerpt
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb
EPUB - 309.1Kb
HTML Download (TGZ) - 268.9Kb
HTML Download (Zip) - 278.5Kb


Security in MySQL  /  ...  /  Password Validation Plugin Options and Variables

7.3.2 Password Validation Plugin Options and Variables

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

If the validate_password plugin is enabled, it exposes several system variables representing the parameters that control password checking:

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| 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_check_user_name

    Introduced5.7.15
    Command-Line Format--validate_password_check_user_name
    System VariableNamevalidate_password_check_user_name
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Whether passwords are compared to the user name part of the effective user account for the current session and rejected if they match. By default, validate_password_check_user_name is disabled. This variable controls user name matching independent of the value of validate_password_policy.

    When validate_password_check_user_name is enabled, it has these effects:

    • Checking occurs in all contexts for which the validate_password plugin is invoked, which includes statements such as ALTER USER and SET PASSWORD, and invocation of functions such as PASSWORD() and VALIDATE_PASSWORD_STRENGTH().

    • If a password is the same as the user name or its reverse, a match occurs and the password is rejected.

    • If a password matches the user name, VALIDATE_PASSWORD_STRENGTH() returns 0 regardless of how other validate_password system variables are set.

    • The user names used for comparison are taken from the values of the USER() and CURRENT_USER() functions for the current session. (An implication is that a user who has the SUPER privilege can execute a statement to set another user's password to that user name, and cannot set that user's password to the name of the user executing the statement.)

    • Only the user name part of the USER() and CURRENT_USER() function values is used, not the host name part. If a user name is empty, no comparison is done.

    • User name matching is case sensitive. The password and user name values are compared as binary strings on a byte-by-byte basis.

  • validate_password_dictionary_file

    System Variable (<= 5.7.7)Namevalidate_password_dictionary_file
    Variable ScopeGlobal
    Dynamic VariableNo
    System Variable (>= 5.7.8)Namevalidate_password_dictionary_file
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypefile name

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

    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. If the file is named as a relative path, it is interpreted relative to the server data directory. 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.

    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.7.8, changes to the dictionary file while the server is running require a restart for the server to recognize the changes. As of MySQL 5.7.8, validate_password_dictionary_file can be set at runtime and assigning a value causes the named file to be read without a restart.

  • validate_password_length

    System VariableNamevalidate_password_length
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default8
    Min Value0

    The minimum number of characters that passwords checked by the validate_password plugin must have. This variable is unavailable unless that plugin is installed.

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

    validate_password_number_count
    + validate_password_special_char_count
    + (2 * validate_password_mixed_case_count)
    

    If the validate_password plugin 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

    System VariableNamevalidate_password_mixed_case_count
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default1
    Min Value0

    The minimum number of lowercase and uppercase characters that passwords checked by the validate_password plugin must have if the password policy is MEDIUM or stronger. For a given value, the password must have that many lowercase characters, and that many uppercase characters. This variable is unavailable unless that plugin is installed.

  • validate_password_number_count

    System VariableNamevalidate_password_number_count
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default1
    Min Value0

    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. This variable is unavailable unless that plugin is installed.

  • validate_password_policy

    System VariableNamevalidate_password_policy
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    Default1
    Valid Values0
    1
    2

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

    validate_password_policy affects how the plugin uses its other policy-setting system variables, except for checking passwords against user names, which is controlled independently by validate_password_check_user_name.

    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.

    PolicyTests Performed
    0 or LOWLength
    1 or MEDIUMLength; numeric, lowercase/uppercase, and special characters
    2 or STRONGLength; numeric, lowercase/uppercase, and special characters; dictionary file
  • validate_password_special_char_count

    System VariableNamevalidate_password_special_char_count
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default1
    Min Value0

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

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.


User Comments
Sign Up Login You must be logged in to post a comment.