6.4.3.2 Password Validation Plugin Options and Variables (original) (raw)

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 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_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

Command-Line Format	--validate-password-check-user-name[={OFF\|ON}]
Introduced	5.7.15
System Variable	validate_password_check_user_name
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	OFF
Whether `validate_password` compares passwords to the user name part of the effective user account for the current session and rejects them if they match. This variable is unavailable unless`validate_password` is installed.
By default,validate_password_check_user_name is disabled. This variable controls user name matching independent of the value ofvalidate_password_policy.
Whenvalidate_password_check_user_name is enabled, it has these effects:

Checking occurs in all contexts for whichvalidate_password is invoked, which includes use of statements such asALTER USER orSET PASSWORD to change the current user's password, and invocation of functions such asPASSWORD() andVALIDATE_PASSWORD_STRENGTH().
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 sufficient privileges to set another user's password can set the password to that user's name, and cannot set that user' password to the name of the user executing the statement. For example,'root'@'localhost' can set the password for 'jeffrey'@'localhost' to 'jeffrey', but cannot set the password to 'root.
Only the user name part of theUSER() andCURRENT_USER() function values is used, not the host name part. If a user name is empty, no comparison occurs.
If a password is the same as the user name or its reverse, a match occurs and the password is rejected.
User-name matching is case-sensitive. The password and user name values are compared as binary strings on a byte-by-byte basis.
If a password matches the user name,VALIDATE_PASSWORD_STRENGTH() returns 0 regardless of how othervalidate_password system variables are set.

validate_password_dictionary_file
The path name of the dictionary file thatvalidate_password uses for checking passwords. This variable is unavailable unlessvalidate_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 thevalidate_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.
ForVALIDATE_PASSWORD_STRENGTH(), the password is checked against all policies, includingSTRONG, so the strength assessment includes the dictionary check regardless of thevalidate_password_policy value.
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.
Thevalidate_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 ofvalidate_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 givenvalidate_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	012
The password policy enforced by`validate_password`. This variable is unavailable unless `validate_password` is installed.
validate_password_policy affects how `validate_password` uses its other policy-setting system variables, except for checking passwords against user names, which is controlled independently byvalidate_password_check_user_name.
Thevalidate_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 thevalidate_password_length system variable. Similarly, the required values for the other tests are given by othervalidate_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 | 2019-10-03 08:33:49 |
| validate_password_dictionary_file_words_count | 1902                |
+-----------------------------------------------+---------------------+

The following list describes the meaning of each status variable.

validate_password_dictionary_file_last_parsed
When the dictionary file was last parsed.
validate_password_dictionary_file_words_count
The number of words read from the dictionary file.