6.4.3 The Password Validation Component

The validate_password component serves to test passwords and improve security. This component exposes system variables that enable you to define password policy, and status variables for component monitoring.

Note

In MySQL 8.0.4, the validate_password plugin was reimplemented as the validate_password component. The following instructions describe how to use the component, not the plugin. For instructions on using the plugin, see The Password Validation Plugin in MySQL 5.7 Reference Manual.

The plugin form of validate_password is still available but is deprecated and will be removed in a future version of MySQL. MySQL installations that use the plugin should make the transition to using the component instead. See Section 6.4.3.3, “Transitioning to the Password Validation Component”.

The validate_password component implements these capabilities:

In SQL statements that assign a password supplied as a cleartext value, the component 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 ALTER USER, CREATE USER, GRANT, and SET PASSWORD statements.
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, 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> 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 for checking:

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

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.4.3.2, “Password Validation 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.

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.

In addition, validate_password 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 provide control over this capability, validate_password exposes a validate_password.check_user_name system variable, which is enabled by default.

Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-validate-password.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut