一、访问控制
1.1 连接验证（阶段1）
当我们连接MySQL的时候,MySQL会以下面2点进行连接验证
通过提供的账号和密码来验证身份
验证账号是否锁定
服务首先检查凭据，然后检查账户的锁定状态。任一步骤失败都会拒绝访问，没有问题就接收连接。锁定状态记录在user表account_locked列中。
mysql> select user,account_locked from user;
+--------+----------------+
| user | account_locked |
+--------+----------------+
| root | N |
| multis | N |
| multis | N |
| root | N |
+--------+----------------+
4 rows in set (0.00 sec)
1.2 允许的连接
下表展示了User和Host值在各种的组合下， user表适用于传入的连接
User Host 允许的连接
‘fred’ h1.example.net 来自h1.example.net的fred
‘’ ‘h1.example.net’ 来自h1.example.net的任何用户
‘fred’ ‘%’ 任何主机的fred
‘’ ‘’ 任何主机的任何用户
‘fred’ ‘%.example.net’ 从example.net的任何主机的fred
‘fred’ ‘x.example.net%’ 从x.example.net，从x.example.com等的任何主机的fred
‘fred’ ‘198.51.100.177’ 从ip198.51.100.177主机的fred
‘fred’ ‘198.51.100.%’ 从ip198.51.100C类网中任何主机的fred
‘fred’ '198.51.100.0/255.255.255.0 与前面相同
1.3 连接的优先级
当有多个匹配项时，需要确认使用哪个匹配项，按照以下的方式解决问题
当服务器将user表读入内存，它会对user表进行排序
客户端尝试连接时，服务器将按排序顺序浏览各行
服务器使用与客户端主机名和用户名匹配的第一行
排序顺序
优先Host进行排序，其次User
有具体文字比%更具有高优先级（127.0.0.1比localhost高）
示例一
要查看其工作原理，假设user 表如下所示：
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| % | root | ...
| % | jeffrey | ...
| localhost | root | ...
| localhost | | ...
+-----------+----------+-
读入到内存时，自动排序后的结果如下所示：
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| localhost | root | ...
| localhost | | ...
| % | jeffrey | ...
| % | root | ...
+-----------+----------+-
挡localhost 上的root用户连接mysql时候，这个时候会匹配| localhost | root |这个条目对应的权限。 其他条目虽然也满足， 但不会匹配它们对应的权限。
示例二
我们创建'test'@'%'和''@'192.168.79.140’两个用户，进行连接的优先级的测试
[root@mysql2 ~]# mysql -root-p123456
## 忽略部分登陆代码 ##
mysql> create user ''@'192.168.79.140' identified by '123456';
mysql> create user 'test'@'%' identified by '123456';
mysql> select host,user from user;
+----------------+------+
| host | user |
+----------------+------+
| % | test |
| 192.168.79.140 | |
| localhost | root |
+----------------+------+
3 rows in set (0.00 sec)
mysql> exit;
使用我们刚才创建的'test'@'%'用户登录mysql
[root@mysql2 ~]# mysql -utest -p123456 -h192.168.79.140
按照mysql连接的优先级，用户在登陆mysql时，mysql读入到内存自动排序后的结果应如下所示：
+----------------+------+
| host | user |
+----------------+------+
| 192.168.79.140 | |
| localhost | root |
| % | test |
+----------------+------+
当前登录的'test'@'%'用户匹配连接是| 192.168.79.140 | |，我们可以通过CURRENT_USER()函数查验证当前用户是否匹配| 192.168.79.140 | |
mysql> select current_user();
+-----------------+
| current_user() |
+-----------------+
| @192.168.79.140 |
+-----------------+
1 row in set (0.00 sec)
验证结果如我们推测一致，正式坏境中建议不要创建有不带用户名只有主机的用户。
1.4 请求验证（阶段二）
MySQL建立连接后，服务器进入访问控制的第二阶段。该连接发出的每个请求，会检查是否具有足够的权限，这些权限对应着如下这些表：
user 用户（全局）
db 数据库
tables_priv 表
columns_priv 列
procs_priv 存储过程
proxies_priv代理用户权限
二、用户管理
在这里我们需要知道的是mysql的用户名和主机名在一起才是一个有效的用户
2.1 新增用户
MySQL的语法和Oracle是相似的，如下
create user 'user'@'localhost' identified by 'passwd' [ACCOUNT UNLOCK]
ACCOUNT UNLOCK 用户默认是没有锁定的，如果用户锁定，我们登录的用户不会有第二阶段的请求验证。
我们创建一个本地可以登陆的multis用户，该用户默认是未锁定的状态。
mysql> create user 'multis'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
2.2 用户修改
MySQL是可以通过数据操纵语言DML和数据定义语言DDL来进行修改的
2.2.1 使用DDL语言修改用户
我们查询当前数据库所有的用户名、主机和用户的状态
mysql> select user,host,account_locked from user;
+--------+-----------+----------------+
| user | host | account_locked |
+--------+-----------+----------------+
| root | localhost | N |
| multis | localhost | N |
+--------+-----------+----------------+
2 rows in set (0.00 sec)
1、我们使用alter修改'multis'@'localhost'用户状态为锁定状态
mysql> alter user 'multis'@'localhost' account lock;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,account_locked from user;
+--------+-----------+----------------+
| user | host | account_locked |
+--------+-----------+----------------+
| root | localhost | N |
| multis | localhost | Y |
+--------+-----------+----------------+
2 rows in set (0.00 sec)
重新打开一个窗口进行mysql登陆，登陆的时候，MySQL报错ERROR 3118 (HY000): Access denied for user 'multis'@'localhost'. Account is locked.帐号被锁定,用户被拒绝访问。
[root@mysql2 ~]# mysql -umultis -p
Enter password:
ERROR 3118 (HY000): Access denied for user 'multis'@'localhost'. Account is locked.
2、我们使用alter修改'test'@'localhost'用户的密码
mysql> alter user 'test'@'localhost' identified by '654321';
Query OK, 0 rows affected (0.01 sec)
我们登陆'test'@'localhost'用户验证
[root@mysql2 ~]# mysql -utest -p654321
## 省略部分登陆提示 ##
mysql>
'test'@'localhost'用户使用修改后密码登陆成功
2.2.2 DML语句管理mysql用户
我们也可以直接更新mysql.user表的信息，进行用户的管理，但是需要flush privileges，将内存和磁盘中的数据保持一致。
1、修改'multis' @'localhost'用户用户名为multis1
mysql> update mysql.user set user = 'multis1' where user = 'multis' and host='localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查询mysql.user表,进行校验
mysql> select user,host from mysql.user;
+---------+-----------+
| user | host |
+---------+-----------+
| multis1 | localhost |
| root | localhost |
+---------+-----------+
2 rows in set (0.00 sec)
2、修改'multis1' @'localhost'用户host为127.0.0.1
mysql> update mysql.user set host = '127.0.0.1' where user = 'multis1' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
我们不刷新flush privileges，查询'multis1' @'localhost'用户信息
mysql> select user,host from mysql.user;
+---------+-----------+
| user | host |
+---------+-----------+
| multis1 | localhost |
| root | localhost |
+---------+-----------+
2 rows in set (0.00 sec)
发现虽然更改了host,但是还以前的host,我们flush privileges后，继续查询'multis1' @'localhost'用户信息
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+---------+-----------+
| user | host |
+---------+-----------+
| multis1 | 127.0.0.1 |
| root | localhost |
+---------+-----------+
2 rows in set (0.00 sec)
2.3 删除用户
方法一：drop user
mysql> create user 'test'@'localhost' identified by '123456' account unlock;
Query OK, 0 rows affected (0.00 sec)
mysql> drop user 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec
方案二：delete
mysql> create user 'test'@'localhost' identified by '123456' account unlock;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from mysql.user where user = 'test' and host='localhost';
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
某个用户已经被我们删除了，但是已经通过该用户连接进来的是不受影响的。那么如何清理掉这些用户,断开连接呢？
查询已经删除用户id，生成SQL
mysql> select concat('kill ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/kill.sql';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
报错原因： 没有指定secure_file_priv参数
解决方法： secure_file_priv是MySQL中的一个系统变量，用于限制LOAD DATA INFILE和SELECT … INTO OUTFILE语句的文件读写权限。它指定了允许从哪个目录中读取或写入文件。开启secure_file_priv参数，须在配置文件中修改，并重启数据库。
查询secure_file_priv参数，当前参数值为null
mysql> select @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.00 sec)
我们可以在my.cnf文件中指定：
[mysqld]
secure_file_priv=/tmp/
也可以通过命令来实时修改，可以随时生效而且永久生效（MySQL 8.0 推荐使用）：
mysql> set persist secure_file_priv=/tmp/;
Query OK, 0 rows affected (0.00 sec)
重新查询secure_file_priv参数，当前参数值为/tmp/
mysql> select @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| /tmp/ |
+--------------------+
1 row in set (0.00 sec)
继续查询清理的用户id，生成SQL
mysql> select concat('kill ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/kill.sql';
Query OK, 1 row affected (0.00 sec)
mysql> source /tmp/kill.sql
OK，已删除的用户就已断开
2.4 查看用户
2.4.1 show create user 显示用户非权限属性
mysql> create user test@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> show create user 'test'@'localhost' \G;
*************************** 1. row ***************************
CREATE USER for test@localhost: CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
1 row in set (0.00 sec)
ERROR:
No query specified
2.4.2 select * from mysql.user 查看已经创建的用户
mysql> select * from mysql.user where user='test' and host='localhost' \G;
*************************** 1. row ***************************
Host: localhost
User: test
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
password_expired: N
password_last_changed: 2023-04-18 22:00:36
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
ERROR:
No query specified
2.4.3 select current_user() 查看当前登录用户
mysql> select current_user;
+----------------+
| current_user |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
三、密码管理
3.1 修改密码（最终版）
3.1.1 alter修改密码（推荐使用）
mysql> alter user 'test'@'localhost' identified by '654321';
Query OK, 0 rows affected (0.01 sec)
3.1.2 mysqladmin修改密码
[root@mysql2 ~]# mysqladmin -utest -p654321 password
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
New password:
Confirm new password:
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
3.1.3 set password修改密码
1 、使用默认加密
mysql> set password for 'test'@'%' ='abcdefg';
Query OK, 0 rows affected (0.01 sec)
2、使用PASSWORD()函数加密（mysql使用该方式进行密码加密）
mysql> set password for 'test'@'%' =password('123456');
Query OK, 0 rows affected (0.01 sec)
3.1.4 update(不推荐使用)
mysql> update mysql.user set authentication_string=password('Aroot123#') where user='dba';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
3.1.5 修改当前登录用户密码
mysql> alter user current_user() identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> alter user user() identified by '123456';
Query OK, 0 rows affected (0.00 sec)
3.2 密码策略
在设置密码策略时 identified by 'password' 可以忽略
3.2.1 密码过期策略
MySQL版本5.7.4版本起，可以通过全局变default_password_lifetime来设置全局的密码过期策略。主要有以下四种策略
1、密码立即过期策略 PASSWORD EXPIRE
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
重置密码后才能正常登陆
我们修改'test'@'localhost'用户密码立即过期，查询改用户的非权限属性
mysql> alter user 'test'@'localhost' identified by '123456' password expire;
Query OK, 0 rows affected (0.00 sec)
mysql> show create user 'test'@localhost \G;
*************************** 1. row ***************************
CREATE USER for test@localhost: CREATE USER 'test'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE ACCOUNT UNLOCK
1 row in set (0.00 sec)
ERROR:
No query specified
我们可以发现密码策略从PASSWORD EXPIRE DEFAULT变为PASSWORD EXPIRE，我们重新登陆mysql，发现使用sql语句时无法使用，提示在执行该语句之前，必须使用ALTER USER语句重置密码
mysql> select current_user();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
使用root用户修改'test'@'localhost'用户密码
mysql> alter user 'test'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
密码修改完成后，可以正常登陆mysql
[root@mysql2 ~]# mysql -utest -p123456
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
OK，测试完成
2、密码从不过期策略 PASSWOD EXPIRE NEVER
mysql> alter user 'test'@'localhost' identified by '123456' password expire;
Query OK, 0 rows affected (0.00 sec)
当前 'test'@'localhost' 用户永不过期。
3、指定过期时间策略 PASSWORD EXPIRE INTERVAL 90 DAY
mysql> alter user 'test'@'localhost' password expire interval 90 day;
Query OK, 0 rows affected (0.00 sec)
当前 'test'@'localhost' 用户90天以后过期，过期后需要我们修改 'test'@'localhost' 密码。
4、默认过期时间策略 PASSWORD EXPIRE DEFAULT
创建用户时，mysql默认的是该策略PASSWORD EXPIRE DEFAULT ，由default_password_lifetime参数决定密码的有效期。
可以使用 select @@default_password_lifetime或者show variables like '%default_password_lifetime%'进行查询
mysql> select @@default_password_lifetime;
+-----------------------------+
| @@default_password_lifetime |
+-----------------------------+
| 0 |
+-----------------------------+
1 row in set (0.00 sec)
我们修改 'test'@'localhost'为默认时间过期
mysql> alter user 'test'@'localhost' password expire default;
Query OK, 0 rows affected (0.01 sec)
如果需要修改全局参数，我们可以在my.cnf文件中指定：
[mysqld]
default_password_lifetime=180
default_password_lifetime=180 密码有效期为6个月，最大值65535
default_password_lifetime=0 默认是0，密码永不过期
也可以通过命令来实时修改，可以随时生效而且永久生效（mysql 8.0版本支持，推荐使用）：
mysql> set default_password_lifetime=180;
Query OK, 0 rows affected (0.00 sec)
我们现在要求每90天更改一次密码，我们可以create user和alter user...password expire interval 90 day
mysql> create user 'test1'@'localhost' identified by '123456' password expire interval 90 day;
Query OK, 0 rows affected (0.00 sec)
我们需要注意的是，当我们指定了用户的是密码立即失效、永久生效和指定过期间隔策略都会覆盖该语句命名的用户的全局策略。当指定用户默认过期时间，用户将会遵循全局密码策略。
当密码过期，我们可以通过修改密码，处理密码过期；下面进行测试
我们创建一个用户，默认策略是全局参数
mysql> create user 'test3'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> show create user 'test3'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for test3@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'test3'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
修改当前用户立即失效
mysql> alter user 'test3'@'localhost' password expire;
Query OK, 0 rows affected (0.00 sec)
打开新的窗口登陆mysql,查询数据
[root@mysql2 ~]# mysql -utest3 -p123456
## 忽略登陆部分 ##
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>
系统提示修改密码
mysql> alter user 'test3'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
重新登陆mysql
[root@mysql2 ~]# mysql -utest3 -p123456
## 忽略登陆部分 ##
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
经过上面测试，我们得知修改密码，可以处理密码过期
3.2.2 密码复杂度策略
MySQL5.6.6版本之后增加了密码强度验证插件validate_password，相关参数设置的较为严格。使用了该插件会检查设置的密码是否符合当前设置的强度规则，若不满足则拒绝设置。默认没有安装这个插件，我们可以通过 select * from mysql.plugin 或者使用show variables like '%validate_password%'查看列表为空
mysql> select * from mysql.plugin;
Empty set (0.00 sec)
mysql> show variables like '%validate_password%';
Empty set (0.00 sec)
我们可以使用命令install plugin validate_password soname 'validate_password.so'进行安装
mysql> install plugin validate_password soname 'validate_password.so';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'vali%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | OFF |
| 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.01 sec)
而关闭密码复杂度使用改命令uninstall plugin validate_password
mysql> uninstall plugin validate_password;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'vali%';
Empty set (0.00 sec)
也可以在my.cnf配置文件中指定plugin-load=validate_password.so开启密码复杂度
[mysqld]
plugin-load=validate_password.so
关闭密码复杂度在my.cnf配置文件中指定validate-password=off重启服务即可。
[mysqld]
plugin-load=validate_password.so
validate-password=off
建议使用install plugin validate_password soname 'validate_password.so'和uninstall plugin validate_password进行密码复杂度的开启和关闭
我们通过查询，知道密码复杂度有
validate_password_check_user_name 用户名检测，检测是否重名,默认关闭
validate_password_dictionary_file 字典文件，字典文件中存在的密码不得使用
validate_password_length 密码的最小长度，此处为8
validate_password_mixed_case_count 至少要包含小写或大写字母的个数，此处为1
validate_password_number_count 至少要包含的数字的个数，此处为1
validate_password_policy 密码安全策略 0~2,默认MEDIUM（2）
LOW OR 1: 只限制长度
MEDIUM OR 2： 限制长度、数字、字母、特殊字符
STRONG OR 3： 限制长度、数字、字母、特殊字符、字典
validate_password_special_char_count 至少要包含的字符的个数，此处为1
我们使用set global 进行全局变量设置密码复杂度策略调为STRONG(3)，从8开始，可通过SET PERSIST命令将全局变量的修改持久化到配置文件中。我们开始测试
mysql> set global validate_password_policy=2;
Query OK, 0 rows affected (0.00 sec)
首先我们创建密码字典文件
[root@mysql2 ~]# echo 'admin'> /tmp/dic.file
[root@mysql2 ~]# cat /tmp/dic.file
admin
使用set global指定字典文件
mysql> set global validate_password_dictionary_file ='/tmp/dic.file';
Query OK, 0 rows affected (0.00 sec)
我们查询密码复杂度策略
mysql> SHOW VARIABLES LIKE 'vali%';
+--------------------------------------+---------------+
| Variable_name | Value |
+--------------------------------------+---------------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | /tmp/dic.file |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | STRONG |
| validate_password_special_char_count | 1 |
+--------------------------------------+---------------+
我们进行密码的最小长度，大小写字母，符号，字典等方面进行测试，如下
--密码长度6位
mysql> alter user 'test3'@'localhost' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
--密码长度8位
mysql> alter user 'test3'@'localhost' identified by '12345678';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
--密码长度10位，包含大小写字母各1位
mysql> alter user 'test3'@'localhost' identified by 'A12345678a';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
--密码长度11位，包含大小写字母符号各1位，成功
mysql> alter user 'test3'@'localhost' identified by 'A12345678a#';
Query OK, 0 rows affected (0.00 sec)
--密码字母包含在字典里面，失败
mysql> alter user 'test3'@'localhost' identified by 'Admin123456#';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
OK，测试成功，我们关闭密码复杂度策略
mysql> uninstall plugin validate_password ;
Query OK, 0 rows affected (0.00 sec)
3.2.3 密码必要验证策略（MySQL 8.0 新特性）
在MySQL 8.0 之前的版本，普通用户可以直接更改自己密码，不需要旧密码验证，也不需要知会管理员，比如用户test 需要更改密码，在MySQL 5.7 下直接敲alter user 命令即可,假设test在服务器上登陆数据库过后，忘记退出，test2用户刚好到服务器上修改密码。为了防止这类不安全事件的生，MySQL 8.0 发布了一系列密码验证策略。
密码必要验证策略password_require_current 也就是修改密码前必须验证密码
我们可以在my.cnf文件中指定：
[mysqld]
password_require_current =on
也可以通过命令来实时修改，可以随时生效而且永久生效（推荐使用）：
mysql> set persist password_require_current=on;
Query OK, 0 rows affected (0.00 sec)
我们通过show variables like 'password%'命令来查询参数是否生效：
mysql> show variables like 'password%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| password_require_current | ON |
+--------------------------+-------+
1 rows in set (0.01 sec)
可以在通过create user 和 alter user...password require current optional或者 password require current default 来覆盖全局参数的值，但是注意的是password require current default参数取决于require current optional参数的值
mysql> create user 'test'@'localhost' identified by '123456' password expire never password require current;
Query OK, 0 rows affected (0.01 sec)
登陆刚才创建的'test'@'localhost'用户登录，并修尝试的是该改密码
[root@mysql2 ~]# mysql -utest -p123456 -S /data/mysql/my3307/mysql.sock
## 省略部分登陆代码 ##
Server version: 8.0.32 MySQL Community Server - GPL
mysql> alter user user() identified by '654321';
ERROR 3892 (HY000): Current password needs to be specified in the REPLACE clause in order to change it.
当前密码需要在REPLACE子句中指定，以便更改它。接下来，alter user 跟上子句replace 来让'test'@'localhost'用户输入旧密码，成功更改新密码
mysql> alter user user() identified by '123456' replace '123456';
Query OK, 0 rows affected (0.01 sec)
我们使用alter user...password require current optional来关闭'test1'@'localhost'用户的密码必要验证策略
mysql>alter 'test1'@'localhost' password require current optional;
Query OK, 0 rows affected (0.01 sec)
接下来 'test1'@'localhost'用户又恢复为MySQL旧版本的安全行为
mysql> alter user user() identified by '123456';
Query OK, 0 rows affected (0.01 sec)
OK，测试完成
3.2.4 密码的重复使用策略（MySQL 8.0 新特性）
MySQL 8.0允许对重用以前的密码进行限制。 可以根据密码更改次数，已用时间或两者来确定重用限制。 可以在全局建立重用策略，并且可以将个人帐户设置为遵循全局策略或使用指定的某个帐户行为来覆盖全局策略。
帐户的密码历史记录包含着过去分配的密码。 MySQL可以限制从此历史记录中选择新密码，我们可以查询mysql.password_history，记录的密码更改的历史
如果根据密码更改次数限制帐户，则无法从指定数量的最新密码中选择新密码。 例如，如果密码更改的最小数量设置为3，则新密码不能与最近最新的3个密码相同。
如果根据已用时间限制帐户，则无法从历史记录中比指定天数更新的密码中选择新密码。 例如，如果密码重用间隔设置为60，则新密码不能和过去60天内的密码相同。
如果根据已用时间限制帐户和密码更改次数限制，则两个参数都需要满足才可以更改密码
注：空密码不会算在密码历史记录中。想要设置密码重用策略，可以通过设置password_history 和password_reuse_interval系统变量来实现： 默认都是0，即不启用重用密码策略。
最近使用的密码保留次数 password_history
最近使用的密码保留天数 password_reuse_interval
我们可以在my.cnf文件中指定：
[mysqld]
password_history=3
password_reuse_interval=30
也可以通过命令来实时修改，可以随时生效而且永久生效（推荐使用）：
mysql> set persist password_history=3;
Query OK, 0 rows affected (0.00 sec)
mysql> set persist password_reuse_interval=30;
Query OK, 0 rows affected (0.00 sec)
我们通过show variables like 'password%'命令来查询参数是否生效：
mysql> show variables like 'password%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| password_history | 3 |
| password_require_current | ON |
| password_reuse_interval | 30 |
+--------------------------+-------+
3 rows in set (0.01 sec)
可以在通过create user 和 alter user...password history 3 password reuse interval 30 来覆盖全局参数的值
mysql> create user 'test4'@'localhost' identified by '123456' password history 3 password reuse interval 30 day;
Query OK, 0 rows affected (0.00 sec)
我们可以使用命令show create user命令查询'test4'@'localhost'用户的非权限属性，可以发现PASSWORD HISTORY 3 PASSWORD REUSE INTERVAL 30 DAY，用户策略覆盖了全局策略
mysql> show create user 'test4'@'localhost' \G;
*************************** 1. row ***************************
CREATE USER for test4@localhost: CREATE USER `test4`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$IPDZ.elS9pULZ|sjDgdLpKcyNUdLk2LZ7VfI9T6y56gc79nD2zOTB9xF5' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY 3 PASSWORD REUSE INTERVAL 30 DAY PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.00 sec)
还原到全局配置：
mysql> alter user 'test4'@'localhost' password history default password reuse interval default;
Query OK, 0 rows affected (0.00 sec)
password history default 和password reuse interval default是由password_history 和password_reuse_interval的全局参数决定。
3.2.5 双密码策略（MySQL 8.0 新特性）
MySQL 8.0.14版本中，用户支持双密码（主密码和副密码），双密码策略就是在日常运维中，需要定期更改指定用户密码，同时又需要旧密码暂时保留一定时长的一种策略。其作用是延迟应用与数据库之间的用户新旧密码对接时间。
双密码机制其实就是主密码与备密码，当备密码不再使用时，告知管理员丢弃备密码，此时用户的主密码即是唯一密码，用法如下：
管理员先创建一个新用户 'test1'@'localhost' ，密码是 test1old ，完了更改他的密码为 test1new，并且retain current password。此时 rootnew 即为主密码，而 rootold 即为备密码
mysql> create user 'test1'@'localhost' identified by 'test1old';
Query OK, 0 rows affected (0.01 sec)
mysql> alter user 'test1'@'localhost' identified by 'test1new' retain current password;
Query OK, 0 rows affected (0.01 sec)
接下来用户'test1'@'localhost' 分别使用备密码与主密码连接 MySQL 并且执行一条简单的 SQL 语句：
[root@mysql2 ~]# mysql -utest1 -ptest1new -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| hello world |
+-------------+
| hello world |
+-------------+
[root@mysql2 ~]# mysql -utest1 -ptest1old -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| hello world |
+-------------+
| hello world |
+-------------+
可以发现在两个密码都能正常使用，相关业务更改完成后，即可告知管理员丢弃备密码discard old password：
mysql> alter user 'test1'@'localhost' discard old password;
Query OK, 0 rows affected (0.01 sec)
双密码策略有以下需要注意的事项：
1、如果用户本身已经有双密码策略，再次更改新密码时没有带 retain current password 子句，那之前的主密码被替换成新改的密码，但是备密码不会被替换。还有一点需要注意的细节，如果不带 retain current password 子句，并且更改新密码为空串，那么主备密码则会统一更改为空串。下面直接8个空格
mysql> alter user 'test1'@'localhost' identified by ' ';
Query OK, 0 rows affected (0.02 sec)
[root@mysql2 ~]# mysql -utest1 -ptest1old -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test1'@'localhost' (using password: YES)
2、使用双密码策略时，不能更改用户的认证插件。
mysql> alter user 'test1'@'localhost' identified with sha256_password by 'testtest' retain current password;
ERROR 3894 (HY000): Current password can not be retained for user 'test1'@'localhost' because authentication plugin is being changed.
3、新密码为空，不允许使用备用密码
mysql> alter user 'test1'@'localhost' identified by '' retain current password;
ERROR 3895 (HY000): Current password can not be retained for user 'test1'@'localhost' because new password is empty.
3.2.5 随机密码策略（MySQL 8.0 新特性）
以往旧版本有生成随机密码的需求，在 MySQL 端无法直接设定，MySQL 8.0 直接可以在create user
mysql> create user 'test2'@'localhost' identified by random password;
+-------+-----------+----------------------+-------------+
| user | host | generated password | auth_factor |
+-------+-----------+----------------------+-------------+
| test2 | localhost | 7HcEGYi6:K.*sURH-n.C | 1 |
+-------+-----------+----------------------+-------------+
1 row in set (0.00 sec)
也可以用 set password 子句来设置随机密码
mysql> set password for 'test2'@'localhost' to random;
+-------+-----------+----------------------+-------------+
| user | host | generated password | auth_factor |
+-------+-----------+----------------------+-------------+
| test2 | localhost | tJKznYxoL1u+J3YS
+-------+-----------+----------------------+-------------+
1 row in set (0.00 sec)
也可以用 alter user来设置随机密码
mysql> alter user 'test2'@'localhost' identified by random password;
+-------+-----------+----------------------+-------------+
| user | host | generated password | auth_factor |
+-------+-----------+----------------------+-------------+
| test2 | localhost | vZ-q{OqQqDnaT]gbhCy+ | 1 |
+-------+-----------+----------------------+-------------+
1 row in set (0.00 sec)
3.2.6 失败登录追踪和临时密码锁定（MySQL 8.0 新特性）
失败登录追踪和临时密码锁定，后面我们简称为：FLTTAL 。
和之前几个密码策略不同，FLTTAL 没有全局参数匹配，只能在创建用户或者是更改用户属性时被匹配。 有两个选项
failed_login_attempts 密码错误次数和 password_lock_time 锁定天数
需要注意的是：
1、failed_login_attempts 和 password_lock_time 必须同时不为 0 ，FLTTAL 才能生效。
mysql> create user 'test3'@'localhost' identified by 'test' failed_login_attempts 2 password_lock_time 0;
Query OK, 0 rows affected (0.01 sec)
[root@mysql2 ~]# mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]# mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]# mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]# mysql -utest3 -ptest -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| hello world |
+-------------+
| hello world |
+-------------+
2、创建新用户不指定 failed_login_attempts 和 password_lock_time ，则默认关闭 FLTTAL 。
3、已使用FLTTAL的用户，管理员对其 alter user 后可改变原有密码验证策略。
mysql> alter user 'test3'@'localhost' identified by 'test' failed_login_attempts 4 password_lock_time 1;
Query OK, 0 rows affected (0.00 sec)
[root@mysql2 ~]# mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]# mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]# mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]# mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3955 (HY000): Access denied for user 'test3'@'localhost'. Account is blocked for 1 day(s) (1 day(s) remaining) due to 4 consecutive failed logins.
4、一旦账户被锁定，即使输入正确密码也无法登录。
[root@mysql2 ~]# mysql -utest3 -ptest -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3955 (HY000): Access denied for user 'test3'@'localhost'. Account is blocked for 1 day(s) (1 day(s) remaining) due to 4 consecutive failed logins.
管理员解锁账户方能正常使用：（或者忘记密码，让管理员解锁账号并且重置新密码）
mysql> alter user 'test3'@'localhost' account unlock;
Query OK, 0 rows affected (0.01 sec)
[root@mysql2 ~]# mysql -utest3 -ptest -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| hello world |
+-------------+
| hello world |
+-------------+
还有最重要的一点：由于 FLTTAL 对密码验证正确与否的连续性，任意一次成功登录，FLTTAL 计数器重置
[root@mysql2 ~]# mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]# mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]# mysql -utest3 -ptest -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| hello world |
+-------------+
| hello world |
+-------------+
[root@mysql2 ~]# mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]# mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]# mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test3'@'localhost' (using password: YES)
[root@mysql2 ~]# mysql -utest3 -ptest11111 -e "select 'hello world'" -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3955 (HY000): Access denied for user 'test3'@'localhost'. Account is blocked for 1 day(s) (1 day(s) remaining) due to 4 consecutive failed logins.
[root@mysql2 ~]#
3.3 密码插件
3.3.1 mysql 5.7 版本默认使用的密码插件
mysql_native_password
mysql> show variables like '%authentication%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.78 sec)
3.3.2 mysql 8.0 版本默认使用的密码插件
caching_sha2_password
mysql> select @@default_authentication_plugin ;
+---------------------------------+
| @@default_authentication_plugin |
+---------------------------------+
| caching_sha2_password |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)
3.3.3 mysql 8 要兼容之前版本
方法一：alter 修改用户指定密码插件
mysql> alter user 'root'@'localhost' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
方法二：我们可以在my.cnf文件中指定：
[mysqld]
default_authentication_plugin=mysql_native_password
也可以通过命令来实时修改，可以随时生效而且永久生效（MySQL 8.0 推荐使用）：
mysql> set persist default_authentication_plugin=mysql_native_password;
Query OK, 0 rows affected (0.00 sec)
3.3.4 MySQL 8.0 多因素身份认证（了解）
MySQL 8.0.27 增加了多因素身份认证功能，可以为一个用户指定多重的身份校验。为此还引入了新的系统变量 authentication_policy ，用于管理多因素身份认证功能。在 MySQL 8.0.27 之前，create user 的时候可以指定一种认证插件，在未明确指定的情况下会取系统变量 default_authentication_plugin的值。default_authentication_plugin 的有效值有3个，分别是 mysql_native_password ，sha256_password ，caching_sha2_password 。
在 MySQL 8.0.27 中由 authentication_policy 来管理用户的身份认证，同时查看下 authentication_policy 和 default_authentication_plugin 的值
mysql> show variables like '%authentication_%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| authentication_policy | *,, |
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
2 rows in set (0.06 sec)';
看到 authentication_policy 的默认值是*,,
第1个元素值是*，表示可以是任意插件，默认值取 default_authentication_plugin 的值。如果该元素值不是星号，则必须设置为 mysql_native_password ，sha256_password ，caching_sha2_password 中的一个。
第2，3个元素值为空，这两个位置不能设置成内部存储的插件。如果元素值为空，代表插件是可选的。
我们创建一个用户，不指定插件名称时，自动使用默认插件caching_sha2_password
mysql> create user 'test'@'localhost' identified by'123456';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host,plugin from mysql.user where user='test';
+------+-----------+-----------------------+
| user | host | plugin |
+------+-----------+-----------------------+
| test | localhost | caching_sha2_password |
+------+-----------+-----------------------+
1 row in set (0.00 sec)
指定插件名称时，会使用到对应的插件
mysql> create user 'test4'@'localhost' identified with mysql_native_password by'123456';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host,plugin from mysql.user where user='test4';
+-------+-----------+-----------------------+
| user | host | plugin |
+-------+-----------+-----------------------+
| test4 | localhost | mysql_native_password |
+-------+-----------+-----------------------+
1 row in set (0.00 sec)
尝试变更一下 authentication_policy 第一个元素的值，设置为 sha256_password
mysql> set persist authentication_policy='sha256_password,,';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'authentication_policy';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| authentication_policy | *,, |
+-----------------------+-------+
1 row in set (0.01 sec)
再次创建一个用户，不指定插件的名称
mysql> create user 'test5'@'localhost' identified by'123456';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host,plugin from mysql.user where user='test5';
+-------+-----------+-----------------+
| user | host | plugin |
+-------+-----------+-----------------+
| test5 | localhost | sha256_password |
+-------+-----------+-----------------+
1 row in set (0.00 sec)
可以看到默认使用的插件是 sha256_password ，说明当 authentication_policy 第一个元素指定插件名称时，default_authentication_plugin 被弃用了
我们现在进行多重身份验证的用户，首先我们恢复 authentication_policy 至默认值
mysql> set persist authentication_policy='*,,';
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'authentication_policy';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| authentication_policy | *,, |
+-----------------------+-------+
1 row in set (0.01 sec)
创建一个双重认证的用户，如下创建失败了，因为我们同时用2种内部存储插件。
mysql> create user 'test7'@'localhost' identified by '123456' and identified with mysql_native_password by '123456';
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 'test7'@'localhost' identified by '123456' 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='test7';
+-------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| user | host | plugin | User_attributes |
+-------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| test7 | 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)
创建成功，之后用户’test6’@'localhost’必须提供正确的密码，且同时本地主机的登录用户为 root 时，才会验证通过。
[root@mysql2 bin]#mysql -utest7 --password1=123456 --password2 -S /data/mysql/my3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Enter password:
## 省略部分代码 ##
mysql>
Ok，测试成功。也可以修改同时登陆用户，如下我们先输入一个错误的密码，在输入一个正确的密码
mysql> alter user 'test7'@'localhost' modify 2 factor identified with auth_socket as 'test7';
再次以主机 root 用户身份，提供正确的密码 123 ，登录失败，因此可以认定双重身份认证机制是生效的。
