The validate_password
plugin serves to improve
security by requiring account passwords and enabling strength
testing of potential passwords. This plugin exposes a set of
system variables that enable you to configure password policy.
The validate_password
plugin implements these
capabilities:
For SQL statements that assign a password supplied as a cleartext value,
validate_password
checks the password against the current password policy and rejects the password if it is weak (the statement returns anER_NOT_VALID_PASSWORD
error). This applies to theCREATE USER
,GRANT
, andSET PASSWORD
statements, and passwords given as arguments to thePASSWORD()
andOLD_PASSWORD()
functions.For
CREATE USER
statements,validate_password
requires that a password be given, and that it satisfies the password policy.validate_password
implements aVALIDATE_PASSWORD_STRENGTH()
SQL function that assesses the strength of potential passwords. This function takes a password argument and returns an integer from 0 (weak) to 100 (strong).
For statements that assign, modify, or generate account
passwords (CREATE USER
,
GRANT
, and
SET PASSWORD
; statements that use
PASSWORD()
and
OLD_PASSWORD()
), the
validate_password
capabilities described here
apply only to accounts that use an authentication plugin that
stores credentials internally in the
mysql.user
system table
(mysql_native_password
or
sha256_password
). For accounts that use
plugins that perform authentication against an external
credential system, password management must be handled
externally against that system as well.
The preceding restriction does not apply to use of the
VALIDATE_PASSWORD_STRENGTH()
function because it does not affect accounts directly.
Examples:
validate_password
checks the cleartext password in the following statement. 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 hashed values are not checked because the original password value is not available for checking:
mysql> SET PASSWORD = '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E'; Query OK, 0 rows affected (0.01 sec)
To check a password, use the
VALIDATE_PASSWORD_STRENGTH()
function:mysql> SELECT VALIDATE_PASSWORD_STRENGTH('weak'); +------------------------------------+ | VALIDATE_PASSWORD_STRENGTH('weak') | +------------------------------------+ | 25 | +------------------------------------+ mysql> SELECT VALIDATE_PASSWORD_STRENGTH('lessweak$_@123'); +----------------------------------------------+ | VALIDATE_PASSWORD_STRENGTH('lessweak$_@123') | +----------------------------------------------+ | 50 | +----------------------------------------------+ mysql> SELECT VALIDATE_PASSWORD_STRENGTH('N0Tweak$_@123!'); +----------------------------------------------+ | VALIDATE_PASSWORD_STRENGTH('N0Tweak$_@123!') | +----------------------------------------------+ | 100 | +----------------------------------------------+
To configure password checking, modify the system variables having
names of the form
validate_password_
;
these are the parameters that control password policy. See
Section 6.3.2, “Password Validation Plugin Options and Variables”.
xxx
If validate_password
is not installed, the
validate_password_
system variables are not available, passwords in statements are
not checked, and the
xxx
VALIDATE_PASSWORD_STRENGTH()
function always returns 0. For example, without the plugin
installed, accounts can be assigned passwords shorter than 8
characters, or no password at all.
Assuming that validate_password
is installed,
it implements three levels of password checking:
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, which
can be modified by changing the appropriate system variables.
LOW
policy tests password length only. Passwords must be at least 8 characters long. To change this length, modifyvalidate_password_length
.MEDIUM
policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase character, 1 uppercase character, and 1 special (nonalphanumeric) character. To change these values, modifyvalidate_password_number_count
,validate_password_mixed_case_count
, andvalidate_password_special_char_count
.STRONG
policy adds the condition that password substrings of length 4 or longer must not match words in the dictionary file, if one has been specified. To specify the dictionary file, modifyvalidate_password_dictionary_file
.