MySQL的validate_password插件/组件总结

 

Password Validation插件和组件

 

在MySQL 8.0之前，MySQL使用的是validate_password插件（plugin）检测、验证账号密码强度，保障账号的安全性，而到了MySQL 8.0，引入了服务器组件（Components）这个特性，validate_password插件已用服务器组件重新实现。下面是官方文档的介绍：

 

Note

In MySQL 8.0, the validate_password plugin was reimplemented as the validate_password component. (For general information about server components, see Section 5.5, “MySQL Server Components”.) The following instructions describe how to use the component, not the plugin. For instructions on using the plugin form of validate_password, 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”.

 

 

这篇文章会介绍validate_password插件和validate_password组件，测试环境为MySQL 8.0.18，如果有其它版本的测试，会在文中进行标明、注释。

 

 

Password Validation安装插件

 

 

检查是否安装了插件/组件

 

插件：

 

mysql> SELECT PLUGIN_NAME, PLUGIN_LIBRARY, PLUGIN_STATUS, LOAD_OPTION

    -> FROM INFORMATION_SCHEMA.PLUGINS

    -> WHERE PLUGIN_NAME = 'validate_password';

Empty set (0.01 sec)

 

如果已经安装了MySQL密码强度审计插件:validate_password的话，可以跳过后面的安装步骤。

 

组件：

 

mysql> SELECT * FROM mysql.component;

 

 

安装/启用插件

 

方法1：在参数文件my.cnf中添加参数。

 

[mysqld]

plugin-load-add=validate_password.so

#ON/OFF/FORCE/FORCE_PLUS_PERMANENT: 是否使用该插件(及强制/永久强制使用)

validate-password=FORCE_PLUS_PERMANENT

 

注释：插件库(plugin library)中的validate_password文件名的后缀名根据平台不同有所差异。 对于Unix和Unix-like系统而言，它的文件后缀名是.so，对于Windows系统而言，它的文件后缀名是.dll。

 

注意：参数FORCE_PLUS_PERMANENT是为了防止插件在MySQL运行时的时候被卸载，如下所示，当你卸载插件时就会报错：

 

mysql> SELECT PLUGIN_NAME, PLUGIN_LIBRARY, PLUGIN_STATUS, LOAD_OPTION

    -> FROM INFORMATION_SCHEMA.PLUGINS

    -> WHERE PLUGIN_NAME = 'validate_password';

+-------------------+----------------------+---------------+----------------------+

| PLUGIN_NAME       | PLUGIN_LIBRARY       | PLUGIN_STATUS | LOAD_OPTION          |

+-------------------+----------------------+---------------+----------------------+

| validate_password | validate_password.so | ACTIVE        | FORCE_PLUS_PERMANENT |

+-------------------+----------------------+---------------+----------------------+

1 row in set (0.00 sec)

mysql>  UNINSTALL PLUGIN  validate_password;

ERROR 1702 (HY000): Plugin 'validate_password' is force_plus_permanent and can not be unloaded

mysql>

 

 另外，修改参数后必须重启MySQL服务才能生效。

 

 

方法2：运行时命令安装

 

mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';

Query OK, 0 rows affected, 1 warning (0.11 sec)

 

注意：此方法也会注册到元数据，也就是mysql.plugin表中，所以不用担心MySQL重启后插件会失效。

 

 

注意：MySQL支持在服务器启动和运行时加载插件。还可以在启动时控制加载插件的激活状态，并在运行时卸载它们。在加载插件时，可以从INFORMATION_SCHEMA获得关于插件的信息。

 

 

方法3：命令启动时，使用参数。这个其实和方法并无什么差异。

 

# /usr/sbin/mysqld start --plugin-load='validate_password.so'

 

 

安装组件

 

 

INSTALL COMPONENT 'file://component_validate_password';

 

 

检查确认

 

 

插件：

mysql> SELECT PLUGIN_NAME, PLUGIN_LIBRARY, PLUGIN_STATUS, LOAD_OPTION

    -> FROM INFORMATION_SCHEMA.PLUGINS

    -> WHERE PLUGIN_NAME = 'validate_password';

+-------------------+----------------------+---------------+-------------+

| PLUGIN_NAME       | PLUGIN_LIBRARY       | PLUGIN_STATUS | LOAD_OPTION |

+-------------------+----------------------+---------------+-------------+

| validate_password | validate_password.so | ACTIVE        | ON          |

+-------------------+----------------------+---------------+-------------+

1 row in set (0.00 sec)

mysql>

mysql> select * from mysql.plugin;

+-------------------+----------------------+

| name              | dl                   |

+-------------------+----------------------+

| CLONE             | mysql_clone.so       |

| validate_password | validate_password.so |

+-------------------+----------------------+

 

 

组件：

 

 

mysql> SELECT * FROM mysql.component;

+--------------+--------------------+------------------------------------+

| component_id | component_group_id | component_urn                      |

+--------------+--------------------+------------------------------------+

|            1 |                  1 | file://component_validate_password |

+--------------+--------------------+------------------------------------+

1 row in set (0.00 sec)

 

 

如果只安装了插件，查看插件相关的系统变量，如下所示：

 

mysql> show variables like 'validate_password%';

+--------------------------------------+--------+

| Variable_name                        | Value  |

+--------------------------------------+--------+

| validate_password_check_user_name    | ON     |

| validate_password_dictionary_file    |        |

| validate_password_length             | 8      |

| validate_password_mixed_case_count   | 1      |

| validate_password_number_count       | 1      |

| validate_password_policy             | MEDIUM |

| validate_password_special_char_count | 1      |

+--------------------------------------+--------+

7 rows in set (0.00 sec)

mysql>

 

MySQL 8.0.18上可以同时安装validate_password插件和validate_password组件，此时查看系统变量时，就会出现这种情形（相信有些不知情同学的看到这种情况的时候，会有点懵逼），其实对于MySQL 8，我们只需安装validate_password组件即可。

 

 

关于插件和组件对应的系统变量说明：

 

选项	默认值	参数描述
validate_password_check_user_name	ON	设置为ON的时候表示能将密码设置成当前用户名。
validate_password_dictionary_file		用于检查密码的字典文件的路径名，默认为空
validate_password_length	8	密码的最小长度，也就是说密码长度必须大于或等于8
validate_password_mixed_case_count	1	如果密码策略是中等或更强的，validate_password要求密码具有的小写和大写字符的最小数量。对于给定的这个值密码必须有那么多小写字符和那么多大写字符。
validate_password_number_count	1	密码必须包含的数字个数
validate_password_policy	MEDIUM	密码强度检验等级，可以使用数值0、1、2或相应的符号值LOW、MEDIUM、STRONG来指定。 0/LOW：只检查长度。 1/MEDIUM：检查长度、数字、大小写、特殊字符。 2/STRONG：检查长度、数字、大小写、特殊字符、字典文件。
validate_password_special_char_count	1	密码必须包含的特殊字符个数

 

注意：组件和插件的默认值可能有所不同。例如，MySQL 5.7. validate_password_check_user_name的默认值为OFF。更多详细具体信息，建议参考官方文档。

 

下面我们来看看validate_password插件和validate_password组件会怎么影响账号创建、密码修改等行为：

 

The validate_password component 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 ALTER USER, CREATE USER, and SET PASSWORD statements.

 

·         For CREATE USER statements, validate_password requires that a password be given, and that it satisfies the password policy. This is true even if an account is locked initially because otherwise unlocking the account later would cause it to become accessible without a password that satisfies the 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).

 

 

验证测试：

 

mysql> create user test@'192.168.%' identified by '12345678';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> show variables like 'validate_password%';

+--------------------------------------+--------+

| Variable_name                        | Value  |

+--------------------------------------+--------+

| validate_password.check_user_name    | ON     |

| validate_password.dictionary_file    |        |

| validate_password.length             | 8      |

| validate_password.mixed_case_count   | 1      |

| validate_password.number_count       | 1      |

| validate_password.policy             | MEDIUM |

| validate_password.special_char_count | 1      |

+--------------------------------------+--------+

7 rows in set (0.00 sec)

mysql>  set global validate_password.policy=LOW;  #注意，如果是插件的话,SQL为set global validate_password_policy=LOW

Query OK, 0 rows affected (0.00 sec)

mysql> create user test@'192.168.%' identified by '12345678';

Query OK, 0 rows affected (0.01 sec)

 

 

 

修改密码验证安全强度（插件）

 

SET GLOBAL validate_password_policy=LOW;

SET GLOBAL validate_password_policy=MEDIUM;

SET GLOBAL validate_password_policy=STRONG;

 

SET GLOBAL validate_password_policy=0;    // For LOW

SET GLOBAL validate_password_policy=1;    // For MEDIUM

SET GLOBAL validate_password_policy=2;    // For HIGH

 

 

修改密码验证安全强度（组件）

 

SET GLOBAL validate_password.policy=LOW;

SET GLOBAL validate_password.policy=MEDIUM;

SET GLOBAL validate_password.policy=STRONG;

 

SET GLOBAL validate_password.policy = 0;   // For LOW

SET GLOBAL validate_password.policy = 1;   // For MEDIUM

SET GLOBAL validate_password.policy = 2;   // For HIGH

 

 

另外，如果你创建密码是遇到“Your password does not satisfy the current policy requirements”，可以通过函数组件去检测密码是否满足条件： 0-100,当评估在100时就是说明使用上了最基本的规则：大写+小写+特殊字符+数字组成的8位以上密码

 

mysql>  SELECT VALIDATE_PASSWORD_STRENGTH('medium');

+--------------------------------------+

| VALIDATE_PASSWORD_STRENGTH('medium') |

+--------------------------------------+

|                                   25 |

+--------------------------------------+

1 row in set (0.00 sec)

mysql> SELECT VALIDATE_PASSWORD_STRENGTH('K354*45jKd5');

+-------------------------------------------+

| VALIDATE_PASSWORD_STRENGTH('K354*45jKd5') |

+-------------------------------------------+

|                                       100 |

+-------------------------------------------+

1 row in set (0.00 sec)

 

注意：如果没有安装validate_password组件或插件的话，那么这个函数永远都返回0。 关于密码复杂度对应的密码复杂度策略。如下表格所示：

 

 

Password Test	Return Value
Length < 4	0
Length ≥ 4 and < validate_password.length	25
Satisfies policy 1 (LOW)	50
Satisfies policy 2 (MEDIUM)	75
Satisfies policy 3 (STRONG)	100

 

 

注意：如果想要使用字典过滤弱口令的话，系统变量validate_password.policy必须为STRONG。官方文档介绍如下，一般最多设置密码策略强度为STRONG，很少启用字典过滤口令。

 

 

The path name of the dictionary file that validate_password uses for checking passwords. This variable is unavailable unless validate_password is installed.

By default, this variable has an empty value and dictionary checks are not performed. For dictionary checks to occur, the variable value must be nonempty. If the file is named as a relative path, it is interpreted relative to the server data directory. File contents should be lowercase, one word per line. Contents are treated as having a character set of utf8. The maximum permitted file size is 1MB.

For the dictionary file to be used during password checking, the password policy must be set to 2 (STRONG); see the description of the validate_password.policy system variable. Assuming that is true, each substring of the password of length 4 up to 100 is compared to the words in the dictionary file. Any match causes the password to be rejected. Comparisons are not case sensitive.

 

 

组件的 Status Variables

 

 

如果是插件的话，是没有相对应的状态变量，只有组件有对应的系统变量。

 

mysql> SHOW STATUS LIKE 'validate_password%';

mysql> show status like 'validate_pass%';

+-----------------------------------------------+---------------------+

| Variable_name                                 | Value               |

+-----------------------------------------------+---------------------+

| validate_password_dictionary_file_last_parsed | 2020-08-13 11:33:47 |

| validate_password_dictionary_file_words_count | 0                   |

+-----------------------------------------------+---------------------+

2 rows in set (0.00 sec)

mysql>

 

 

卸载插件

 

mysql> UNINSTALL PLUGIN  validate_password;

Query OK, 0 rows affected, 1 warning (0.01 sec)

 

卸载组件

 

mysql> UNINSTALL COMPONENT 'file://component_validate_password';

Query OK, 0 rows affected (0.02 sec)

 

 

参考资料：

 

https://dev.mysql.com/doc/refman/8.0/en/plugin-loading.html

https://dev.mysql.com/doc/refman/8.0/en/validate-password.html

https://dev.mysql.com/doc/refman/8.0/en/validate-password.html

https://dev.mysql.com/doc/refman/8.0/en/uninstall-plugin.html

https://dev.mysql.com/doc/refman/8.0/en/validate-password-options-variables.html

https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_validate-password-strength