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 mysqlponent;
安装/启⽤插件
⽅法1：在参数⽂件myf中添加参数。
[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 mysqlponent;
+--------------+--------------------+------------------------------------+
| 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    |
mysql操作官方文档| 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_length8密码的最⼩长度，也就是说密码长度必须⼤于或等于8
validate_password_mixed_case_count1如果密码策略是中等或更强的，validate_password要求密
码具有的⼩写和⼤写字符的最⼩数量。对于给定的这个值密
码必须有那么多⼩写字符和那么多⼤写字符。
validate_password_number_count1密码必须包含的数字个数
validate_password_policy MEDIUM密码强度检验等级，可以使⽤数值0、1、2或相应的符号值
LOW、MEDIUM、STRONG来指定。
0/LOW：只检查长度。
1/MEDIUM：检查长度、数字、⼤⼩写、特殊字符。
2/STRONG：检查长度、数字、⼤⼩写、特殊字符、字典⽂
件。
validate_password_special_char_count1密码必须包含的特殊字符个数
注意：组件和插件的默认值可能有所不同。例如，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。关于密码复杂度对应的密码复杂度策略。如下表格所⽰：
注意：如果想要使⽤字典过滤弱⼝令的话，系统变量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 tru
e, 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)
参考资料：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。