Documentation Home
Security in MySQL
Related Documentation Download this Excerpt
PDF (US Ltr) - 1.1Mb
PDF (A4) - 1.1Mb
EPUB - 298.3Kb
HTML Download (TGZ) - 259.5Kb
HTML Download (Zip) - 269.1Kb


Security in MySQL  /  Security Plugins  /  The Password Validation Plugin

7.2 The Password Validation Plugin

The validate_password plugin serves to test passwords and improve security. The plugin exposes a set of system variables that enable you to define password policy.

This plugin implements two capabilities:

  • In statements that assign a password supplied as a cleartext value, the plugin checks the password against the current password policy and rejects it if it is weak (the statement returns an ER_NOT_VALID_PASSWORD error). This affects the ALTER USER, CREATE USER, GRANT, and SET PASSWORD statements. Passwords given as arguments to the PASSWORD() and OLD_PASSWORD() functions are checked as well.

  • The VALIDATE_PASSWORD_STRENGTH() SQL function assesses the strength of potential passwords. The function 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> ALTER USER USER() IDENTIFIED BY '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:

mysql> ALTER USER 'jeffrey'@'localhost'
    -> IDENTIFIED WITH mysql_native_password
    -> AS '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E';
Query OK, 0 rows affected (0.01 sec)

System variables having names of the form validate_password_xxx represent the parameters that control password policy. To configure password checking, modify these variables; see Section 7.2.2, “Password Validation Plugin Options and Variables”.

If the validate_password plugin 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.

Assuming that the validate_password plugin 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.

  • MEDIUM policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase and uppercase character, and 1 special (nonalphanumeric) character.

  • 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.

In addition, as of MySQL 5.7.15, the validate_password plugin supports the capability of rejecting passwords that match the user name part of the effective user account for the current session, either forward or in reverse. To enable control over this capability, the plugin exposes a validate_password_check_user_name system variable. By default, this variable is disabled.


User Comments
Sign Up Login You must be logged in to post a comment.