MySQL 8.0 参考手册  / 第 6 章 安全  / 6.4 安全组件和插件  /  6.4.3 密码验证插件

6.4.3 密码验证插件

validate_password插件通过要求帐户密码和启用潜在密码的强度测试来提高安全性。该插件公开了一组系统变量,使您能够配置密码策略。

validate_password插件实现了这些功能:

  • 对于分配作为明文值提供的密码的 SQL 语句,validate_password根据当前密码策略检查密码,如果密码较弱(语句返回 ER_NOT_VALID_PASSWORD错误)则拒绝该密码。这适用于ALTER USERCREATE USERGRANTSET PASSWORD语句,以及作为函数参数给出的密码 PASSWORD()

  • 对于CREATE USER语句, validate_password要求提供密码,并且它满足密码策略。即使帐户最初被锁定也是如此,否则稍后解锁帐户将导致无需满足策略的密码即可访问该帐户。

  • validate_password实现 VALIDATE_PASSWORD_STRENGTH() 评估潜在密码强度的 SQL 函数。此函数接受一个密码参数并返回一个从 0(弱)到 100(强)的整数。

笔记

对于分配、修改或生成帐户密码的语句(ALTER USERCREATE USERGRANTSET PASSWORD;使用 的语句, 此处描述PASSWORD()validate_password功能仅适用于使用在 MySQL 内部存储凭据的身份验证插件的帐户。对于使用执行身份验证的插件的帐户MySQL 外部的凭证系统,密码管理也必须针对该系统在外部处理。有关内部凭证存储的更多信息,请参阅 第 6.2.11 节,“密码管理”

上述限制不适用于该 VALIDATE_PASSWORD_STRENGTH() 功能的使用,因为它不直接影响帐户。

例子:

  • validate_password检查以下语句中的明文密码。在要求密码长度至少为 8 个字符的默认密码策略下,密码很弱,语句会产生错误:

    mysql> ALTER USER USER() IDENTIFIED BY 'abc';
    ERROR 1819 (HY000): Your password does not satisfy the current
    policy requirements
  • 不检查指定为散列值的密码,因为原始密码值不可用于检查:

    mysql> ALTER USER 'jeffrey'@'localhost'
           IDENTIFIED WITH mysql_native_password
           AS '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E';
    Query OK, 0 rows affected (0.01 sec)
  • 此帐户创建语句失败,即使帐户最初被锁定,因为它不包含满足当前密码策略的密码:

    mysql> CREATE USER 'juanita'@'localhost' ACCOUNT LOCK;
    ERROR 1819 (HY000): Your password does not satisfy the current
    policy requirements
  • 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.4.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.

此外,从 MySQL 5.7.15 开始, validate_password支持拒绝与当前会话的有效用户帐户的用户名部分匹配的密码,正向或反向。为了提供对此功能的控制, validate_password公开了一个 validate_password_check_user_name 默认启用的系统变量。