This section describes the system and status variables that
validate_password
provides to enable its
operation to be configured and monitored.
If the validate_password
component is
enabled, it exposes several system variables that enable
configuration of password checking:
mysql> SHOW VARIABLES LIKE 'validate_password.%';
+-------------------------------------------------+--------+
| Variable_name | Value |
+-------------------------------------------------+--------+
| validate_password.changed_characters_percentage | 0 |
| validate_password.check_user_name | ON |
| 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.changed_characters_percentage
Command-Line Format --validate-password.changed-characters-percentage[=value]
Introduced 8.0.34 System Variable validate_password.changed_characters_percentage
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 0
Minimum Value 0
Maximum Value 100
Indicates the minimum number of characters, as a percentage of all characters, in a password that a user must change before
validate_password
accepts a new password for the user's own account. This applies only when changing an existing password, and has no effect when setting a user account's initial password.This variable is not available unless
validate_password
is installed.By default,
validate_password.changed_characters_percentage
permits all of the characters from the current password to be reused in the new password. The range of valid percentages is 0 to 100. If set to 100 percent, all of the characters from the current password are rejected, regardless of the casing. Characters 'abc
' and 'ABC
' are considered to be the same characters. Ifvalidate_password
rejects the new password, it reports an error indicating the minimum number of characters that must differ.If the
ALTER USER
statement does not provide the existing password in aREPLACE
clause, this variable is not enforced. Whether theREPLACE
clause is required is subject to the password verification policy as it applies to a given account. For an overview of the policy, see Password Verification-Required Policy.validate_password.check_user_name
Command-Line Format --validate-password.check-user-name[={OFF|ON}]
System Variable validate_password.check_user_name
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Boolean Default Value ON
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 unlessvalidate_password
is installed.By default,
validate_password.check_user_name
is enabled. This variable controls user name matching independent of the value ofvalidate_password.policy
.When
validate_password.check_user_name
is enabled, it has these effects:Checking occurs in all contexts for which
validate_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 asVALIDATE_PASSWORD_STRENGTH()
.The user names used for comparison are taken from the values of the
USER()
andCURRENT_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's 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 the
USER()
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
Command-Line Format --validate-password.dictionary-file=file_name
System Variable validate_password.dictionary_file
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type File name The path name of the dictionary file that
validate_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
utf8mb3
. 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.For
VALIDATE_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.-
Command-Line Format --validate-password.length=#
System Variable validate_password.length
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 8
Minimum Value 0
The minimum number of characters that
validate_password
requires passwords to have. This variable is unavailable unlessvalidate_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 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 SET_VAR
Hint AppliesNo 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 isMEDIUM
or stronger. This variable is unavailable unlessvalidate_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 SET_VAR
Hint AppliesNo 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 isMEDIUM
or stronger. This variable is unavailable unlessvalidate_password
is installed.-
Command-Line Format --validate-password.policy=value
System Variable validate_password.policy
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Enumeration Default Value 1
Valid Values 0
1
2
The password policy enforced by
validate_password
. This variable is unavailable unlessvalidate_password
is installed.validate_password.policy
affects howvalidate_password
uses its other policy-setting system variables, except for checking passwords against user names, which is controlled independently byvalidate_password.check_user_name
.The
validate_password.policy
value can be specified using numeric values 0, 1, 2, or the corresponding symbolic valuesLOW
,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.
variables.xxx
Policy Tests Performed 0
orLOW
Length 1
orMEDIUM
Length; numeric, lowercase/uppercase, and special characters 2
orSTRONG
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 SET_VAR
Hint AppliesNo 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 isMEDIUM
or stronger. This variable is unavailable unlessvalidate_password
is installed.
If the validate_password
component 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. This variable is unavailable unless
validate_password
is installed.validate_password.dictionary_file_words_count
The number of words read from the dictionary file. This variable is unavailable unless
validate_password
is installed.
In MySQL 8.0, the
validate_password
plugin was
reimplemented as the validate_password
component. The validate_password
plugin
is deprecated; expect it to be removed in a future version
of MySQL. Consequently, its options are also deprecated, and
you should expect them to be removed as well. MySQL
installations that use the plugin should make the transition
to using the component instead. See
Section 8.4.3.3, “Transitioning to the Password Validation Component”.
To control activation of the
validate_password
plugin, use this option:
-
Command-Line Format --validate-password[=value]
Type Enumeration Default Value ON
Valid Values ON
OFF
FORCE
FORCE_PLUS_PERMANENT
This option controls how the server loads the deprecated
validate_password
plugin at startup. The value should be one of those available for plugin-loading options, as described in Section 7.6.1, “Installing and Uninstalling Plugins”. For example,--validate-password=FORCE_PLUS_PERMANENT
tells the server to load the plugin at startup and prevents it from being removed while the server is running.This option is available only if the
validate_password
plugin has been previously registered withINSTALL PLUGIN
or is loaded with--plugin-load-add
. See Section 8.4.3.1, “Password Validation Component Installation and Uninstallation”.
In MySQL 8.0, the
validate_password
plugin was
reimplemented as the validate_password
component. The validate_password
plugin
is deprecated; expect it to be removed in a future version
of MySQL. Consequently, its system variables are also
deprecated and you should expect them to be removed as well.
Use the corresponding system variables of the
validate_password
component instead; see
Password Validation Component System Variables. MySQL
installations that use the plugin should make the transition
to using the component instead. See
Section 8.4.3.3, “Transitioning to the Password Validation Component”.
validate_password_check_user_name
Command-Line Format --validate-password-check-user-name[={OFF|ON}]
System Variable validate_password_check_user_name
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Boolean Default Value ON
This
validate_password
plugin system variable is deprecated; expect it to be removed in a future version of MySQL. Use the correspondingvalidate_password.check_user_name
system variable of thevalidate_password
component instead.validate_password_dictionary_file
Command-Line Format --validate-password-dictionary-file=file_name
System Variable validate_password_dictionary_file
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type File name This
validate_password
plugin system variable is deprecated; expect it to be removed in a future version of MySQL. Use the correspondingvalidate_password.dictionary_file
system variable of thevalidate_password
component instead.-
Command-Line Format --validate-password-length=#
System Variable validate_password_length
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 8
Minimum Value 0
This
validate_password
plugin system variable is deprecated; expect it to be removed in a future version of MySQL. Use the correspondingvalidate_password.length
system variable of thevalidate_password
component instead. validate_password_mixed_case_count
Command-Line Format --validate-password-mixed-case-count=#
System Variable validate_password_mixed_case_count
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 1
Minimum Value 0
This
validate_password
plugin system variable is deprecated; expect it to be removed in a future version of MySQL. Use the correspondingvalidate_password.mixed_case_count
system variable of thevalidate_password
component instead.validate_password_number_count
Command-Line Format --validate-password-number-count=#
System Variable validate_password_number_count
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 1
Minimum Value 0
This
validate_password
plugin system variable is deprecated; expect it to be removed in a future version of MySQL. Use the correspondingvalidate_password.number_count
system variable of thevalidate_password
component instead.-
Command-Line Format --validate-password-policy=value
System Variable validate_password_policy
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Enumeration Default Value 1
Valid Values 0
1
2
This
validate_password
plugin system variable is deprecated; expect it to be removed in a future version of MySQL. Use the correspondingvalidate_password.policy
system variable of thevalidate_password
component instead. validate_password_special_char_count
Command-Line Format --validate-password-special-char-count=#
System Variable validate_password_special_char_count
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 1
Minimum Value 0
This
validate_password
plugin system variable is deprecated; expect it to be removed in a future version of MySQL. Use the correspondingvalidate_password.special_char_count
system variable of thevalidate_password
component instead.
In MySQL 8.0, the
validate_password
plugin was
reimplemented as the validate_password
component. The validate_password
plugin
is deprecated; expect it to be removed in a future version
of MySQL. Consequently, its status variables are also
deprecated; expect it to be removed. Use the corresponding
status variables of the validate_password
component; see
Password Validation Component Status Variables. MySQL
installations that use the plugin should make the transition
to using the component instead. See
Section 8.4.3.3, “Transitioning to the Password Validation Component”.
validate_password_dictionary_file_last_parsed
This
validate_password
plugin status variable is deprecated; expect it to be removed in a future version of MySQL. Use the correspondingvalidate_password.dictionary_file_last_parsed
status variable of thevalidate_password
component instead.validate_password_dictionary_file_words_count
This
validate_password
plugin status variable is deprecated; expect it to be removed in a future version of MySQL. Use the correspondingvalidate_password.dictionary_file_words_count
status variable of thevalidate_password
component instead.