6.3 The Password Validation Plugin

6.3.1 Password Validation Plugin Installation

6.3.2 Password Validation Plugin Options and Variables

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 an ER_NOT_VALID_PASSWORD error). This applies to the CREATE USER, GRANT, and SET PASSWORD statements, and passwords given as arguments to the PASSWORD() and OLD_PASSWORD() functions.

• For CREATE USER statements, validate_password requires that a password be given, and that it satisfies the password policy.

• validate_password implements a VALIDATE_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).

Note

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_xxx; these are the parameters that control password policy. See Section 6.3.2, “Password Validation Plugin Options and Variables”.

If validate_password is not installed, the validate_password_xxx system variables are not available, passwords in statements are not checked, and the 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, modify validate_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, modify validate_password_number_count, validate_password_mixed_case_count, and validate_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, modify validate_password_dictionary_file.