共计 7104 个字符，预计需要花费 18 分钟才能阅读完成。
作者：金长龙
爱可生测试工程师，负责 DMP 产品的测试工作
本文起源：原创投稿
* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。
MySQL 8.0.27 减少了多因素身份认证 (MFA) 性能，能够为一个用户指定多重的身份校验。为此还引入了新的零碎变量 authentication_policy，用于治理多因素身份认证性能。
咱们晓得在 MySQL 8.0.27 之前，create user 的时候能够指定一种认证插件，在未明确指定的状况下会取零碎变量 default_authentication_plugin 的值。default_authentication_plugin 的有效值有 3 个，别离是 mysql_native_password，sha256_password，caching_sha2_password，这个 3 个认证插件是内置的、不须要注册步骤的插件。
一、零碎变量 authentication_policy
在 MySQL 8.0.27 中由 authentication_policy 来治理用户的身份认证，先启个 mysql
root@ubuntu:~# docker run --name mysql-1 -e MYSQL_ROOT_PASSWORD=123 -d --ip 172.17.0.2 mysql:8.0.27同时查看下 authentication_policy 和 default_authentication_plugin 的值
root@ubuntu:~# docker run -it --rm mysql:8.0.27 mysql -h172.17.0.2 -uroot -p123
......
mysql> show global variables like 'authentication_policy';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| authentication_policy | *,, |
+-----------------------+-------+
1 row in set (0.02 sec)
mysql> show global variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)咱们看到 authentication_policy 的默认值是 *,,
第 1 个元素值是星号（），示意能够是任意插件，默认值取 default_authentication_plugin 的值。如果该元素值不是星号（），则必须设置为 mysql_native_password，sha256_password，caching_sha2_password 中的一个。
第 2，3 个元素值为空，这两个地位不能设置成外部存储的插件。如果元素值为空，代表插件是可选的。
建个用户看一下，不指定插件名称时，主动应用默认插件 caching_sha2_password
mysql> create user 'wei1'@'localhost' identified by '123';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host,plugin from mysql.user where user='wei1';
+------+-----------+-----------------------+
| user | host | plugin |
+------+-----------+-----------------------+
| wei1 | localhost | caching_sha2_password |
+------+-----------+-----------------------+
1 row in set (0.00 sec)指定插件名称时，会应用到对应的插件
mysql> create user 'wei2'@'localhost' identified with mysql_native_password by '123';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host,plugin from mysql.user where user='wei2';
+------+-----------+-----------------------+
| user | host | plugin |
+------+-----------+-----------------------+
| wei2 | localhost | mysql_native_password |
+------+-----------+-----------------------+
1 row in set (0.01 sec)尝试变更一下 authentication_policy 第一个元素的值，设置为 sha256_password
mysql> set global authentication_policy='sha256_password,,';
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'authentication_policy';
+-----------------------+-------------------+
| Variable_name | Value |
+-----------------------+-------------------+
| authentication_policy | sha256_password,, |
+-----------------------+-------------------+
1 row in set (0.00 sec)再次创立一个用户，不指定插件的名称
mysql> create user 'wei3'@'localhost' identified by '123';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host,plugin from mysql.user where user='wei3';
+------+-----------+-----------------+
| user | host | plugin |
+------+-----------+-----------------+
| wei3 | localhost | sha256_password |
+------+-----------+-----------------+
1 row in set (0.00 sec)能够看到默认应用的插件是 sha256_password，阐明当 authentication_policy 第一个元素指定插件名称时，default_authentication_plugin 被弃用了。
二、多重身份验证的用户
首先咱们复原 authentication_policy 至默认值
mysql> set global authentication_policy='*,,';
Query OK, 0 rows affected (0.01 sec)
mysql> show global variables like 'authentication_policy';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| authentication_policy | *,, |
+-----------------------+-------+
1 row in set (0.01 sec)创立一个双重认证的用户。如下创立失败了，因为不能够同时用 2 种外部存储插件。
mysql> create user 'wei3'@'localhost' identified by '123' and identified with mysql_native_password by '123';
ERROR 4052 (HY000): Invalid plugin "mysql_native_password" specified as 2 factor during "CREATE USER".那咱们来装一个可插拔插件 Socket Peer-Credential
mysql> INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%socket%';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| auth_socket | ACTIVE |
+-------------+---------------+
1 row in set (0.00 sec)再创立一个双重认证的用户
mysql> create user 'wei4'@'localhost' identified by '123' and identified with auth_socket as 'root';
Query OK, 0 rows affected (0.05 sec)
mysql> select user,host,plugin,User_attributes from mysql.user where user='wei4';
+------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| user | host | plugin | User_attributes |
+------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| wei4 | localhost | caching_sha2_password | {"multi_factor_authentication": [{"plugin": "auth_socket", "passwordless": 0, "authentication_string": "root", "requires_registration": 0}]} |
+------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)创立胜利，之后用户 ’wei4’@’localhost’ 必须提供正确的明码，且同时本地主机的登录用户为 root 时，才会验证通过。
来试一下，以主机 root 用户身份，提供正确的明码 123，登录胜利。
root@ubuntu:~# docker exec -it mysql-1 bash
root@1d118873f98e:/# mysql -uwei4 --password1=123 --password2
mysql: [Warning] Using a password on the command line interface can be insecure.
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>批改一下，将 ’wei4’@’localhost’ 要求的主机登录用户批改为 wei4
mysql> alter user 'wei4'@'localhost' modify 2 factor identified with auth_socket as 'wei4';
Query OK, 0 rows affected (0.16 sec)
mysql> select user,host,plugin,User_attributes from mysql.user where user='wei4';
+------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| user | host | plugin | User_attributes |
+------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| wei4 | localhost | caching_sha2_password | {"multi_factor_authentication": [{"plugin": "auth_socket", "passwordless": 0, "authentication_string": "wei4", "requires_registration": 0}]} |
+------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)再次以主机 root 用户身份，提供正确的明码 123，登录失败
root@ubuntu:~# docker exec -it mysql-1 bash
root@1d118873f98e:/# mysql -uwei4 --password1=123 --password2
mysql: [Warning] Using a password on the command line interface can be insecure.
Enter password:
ERROR 1698 (28000): Access denied for user 'wei4'@'localhost'
root@1d118873f98e:/#因而能够认定双重身份认证机制是失效的。MySQL 8.0.27 最多能够对一个用户设置三重的身份认证，这里不再做展现阐明。
简略总结下，已有的明码口令身份验证很适宜网站或者应用程序的拜访，然而在特定的状况下 如网络在线金融交易方面可能还是不够平安。多因素身份认证 (MFA) 性能的引入，能够在肯定水平上晋升数据库系统的安全性。
参考资料：
https://dev.mysql.com/doc/ref…
正文完
