MySQL 5.6 Release Notes
The validate_password plugin (available as of
MySQL 5.6.6) can be used to test passwords and improve security.
This plugin implements two capabilities:
In statements that assign a password supplied as a cleartext value, the value is checked against the current password policy and rejected if it is weak (the statement returns an
ER_NOT_VALID_PASSWORDerror). This affects theCREATE USER,GRANT, andSET PASSWORDstatements. Passwords given as arguments to thePASSWORD()andOLD_PASSWORD()functions are checked as well.The strength of potential passwords can be assessed using the
VALIDATE_PASSWORD_STRENGTH()SQL function, which takes a password argument and returns an integer from 0 (weak) to 100 (strong).
For example, the cleartext password in the following statement is checked. Under the default password policy, which requires passwords to be at least 8 characters long, the password is weak and the statement produces an error:
mysql> SET PASSWORD = PASSWORD('abc');
ERROR 1819 (HY000): Your password does not satisfy the current policy
requirements
Passwords specified as already hashed values are not checked because the original password value is not available:
mysql> SET PASSWORD = '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E';
Query OK, 0 rows affected (0.01 sec)
The parameters that control password checking are available as
the values of the system variables having names of the form
validate_password_.
These variables can be modified to configure password checking;
see Section 6.1.2.6.2, “Password Validation Plugin Options and Variables”.
xxx
The three levels of password checking are
LOW, MEDIUM, and
STRONG. The default is
MEDIUM; to change this, modify the value of
validate_password_policy. The
policies implement increasingly strict password tests. The
following descriptions refer to default parameter values; these
can be modified by changing the appropriate system variables.
LOWpolicy tests password length only. Passwords must be at least 8 characters long.MEDIUMpolicy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase and uppercase character, and 1 special (nonalphanumeric) character.STRONGpolicy adds the condition that password substrings of length 4 or longer must not match words in the dictionary file, if one has been specified.
If the validate_password plugin is not
installed, the
validate_password_
system variables are not available, passwords in statements are
not checked, and
xxxVALIDATE_PASSWORD_STRENGTH()
always returns 0. For example, accounts can be assigned
passwords shorter than 8 characters.
The password-validation plugin is named
validate_password. To be usable by the
server, the plugin library object file must be located in the
MySQL plugin directory (the directory named by the
plugin_dir system variable).
If necessary, set the value of
plugin_dir at server startup
to tell the server the location of the plugin directory.
To load the plugin at server startup, use the
--plugin-load option to name
the object file that contains the plugin. With this
plugin-loading method, the option must be given each time the
server starts. For example, put these lines in your
my.cnf file:
[mysqld] plugin-load=validate_password.so
If object files have a suffix different from
.so on your system, substitute the
correct suffix (for example, .dll on
Windows).
Alternatively, to register the plugin at runtime, use this statement (changing the extension as necessary):
mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
INSTALL PLUGIN loads the
plugin, and also registers it in the
mysql.plugins table to cause the plugin to
be loaded for each subsequent normal server startup.
If the plugin has been previously registered with
INSTALL PLUGIN or is loaded
with --plugin-load, you can use
the --validate-password option at server
startup to control plugin activation. For example, to load the
plugin at startup and prevent it from being removed at
runtime, use these options:
[mysqld] plugin-load=validate_password.so validate-password=FORCE_PLUS_PERMANENT
If it is desired to prevent the server from running without
the password-validation plugin, use
--validate-password with a
value of FORCE or
FORCE_PLUS_PERMANENT to force server
startup to fail if the plugin does not initialize
successfully.
For general information about installing or uninstalling
plugins, see Section 5.1.8.1, “Installing and Uninstalling Plugins”. To
verify plugin installation, examine the
INFORMATION_SCHEMA.PLUGINS table
or use the SHOW PLUGINS
statement. See Section 5.1.8.2, “Obtaining Server Plugin Information”.
To control the activation of the
validate_password plugin, use this option:
-
Introduced 5.6.6 Command-Line Format --validate-password[=value]Permitted Values Type enumerationDefault ONValid Values ONOFFFORCEFORCE_PLUS_PERMANENTThis option controls how the server loads the
validate_passwordplugin at startup. The value should be one of those available for plugin-loading options, as described in Section 5.1.8.1, “Installing and Uninstalling Plugins”. For example,--validate-password=FORCE_PLUS_PERMANENTtells 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_passwordplugin has been previously registered withINSTALL PLUGINor is loaded with--plugin-load. See Section 6.1.2.6.1, “Password Validation Plugin Installation”.
If the validate_password plugin is
installed, it exposes several system variables that indicate
the parameters that control 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 any of these variables at server startup, and most of them at runtime. The following list describes the meaning of each variable.
validate_password_dictionary_fileIntroduced 5.6.6 System Variable (<= 5.6.25) Name validate_password_dictionary_fileVariable Scope Global Dynamic Variable No System Variable Name validate_password_dictionary_fileVariable Scope Global Dynamic Variable No System Variable (>= 5.6.26) Name validate_password_dictionary_fileVariable Scope Global Dynamic Variable Yes Permitted Values Type file nameThe path name of the dictionary file used by the
validate_passwordplugin 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 thevalidate_password_policysystem 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_policyvalue.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_filecan be set at runtime and assigning a value causes the named file to be read without a restart.-
Introduced 5.6.6 System Variable Name validate_password_lengthVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 8Min Value 0The minimum number of characters that passwords checked by the
validate_passwordplugin must have. This variable is unavailable unless that plugin is installed.The
validate_password_lengthminimum value is a function of several other related system variables. As of MySQL 5.6.10, 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_passwordplugin adjusts the value ofvalidate_password_lengthdue to the preceding constraint, it writes a message to the error log. validate_password_mixed_case_countIntroduced 5.6.6 System Variable Name validate_password_mixed_case_countVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 1Min Value 0The minimum number of lowercase and uppercase characters that passwords checked by the
validate_passwordplugin must have if the password policy isMEDIUMor stronger. This variable is unavailable unless that plugin is installed.validate_password_number_countIntroduced 5.6.6 System Variable Name validate_password_number_countVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 1Min Value 0The minimum number of numeric (digit) characters that passwords checked by the
validate_passwordplugin must have if the password policy isMEDIUMor stronger. This variable is unavailable unless that plugin is installed.-
Introduced 5.6.6 System Variable Name validate_password_policyVariable Scope Global Dynamic Variable Yes Permitted Values Type enumerationDefault 1Valid Values 012The password policy enforced by the
validate_passwordplugin. This variable is unavailable unless that plugin is installed.The
validate_password_policyvalue 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_lengthsystem variable. Similarly, the required values for the other tests are given by othervalidate_password_variables.xxxPolicy Tests Performed 0orLOWLength 1orMEDIUMLength; numeric, lowercase/uppercase, and special characters 2orSTRONGLength; numeric, lowercase/uppercase, and special characters; dictionary file NoteBefore MySQL 5.6.10,
validate_password_policywas namedvalidate_password_policy_number. validate_password_special_char_countIntroduced 5.6.6 System Variable Name validate_password_special_char_countVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 1Min Value 0The minimum number of nonalphanumeric characters that passwords checked by the
validate_passwordplugin must have if the password policy isMEDIUMor stronger. This variable is unavailable unless that plugin is installed.
If the validate_password plugin is
installed, it exposes several 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 variable.
validate_password_dictionary_file_last_parsedWhen the dictionary file was last parsed.
This variable was added in MySQL 5.6.26.
validate_password_dictionary_file_words_countThe number of words read from the dictionary file.
This variable was added in MySQL 5.6.26.