前言
在学习这一章节的时候，我们可以先了解一下SQL语言,SQL语言共分为四大类：
- 数据查询语言DQL（Data QueryLanguage）：
select 等 - 数据操纵语言DML（database manage language）：
insert update delete 等 - 数据定义语言DDL（Data Definition Language）：
create drop alter runcate 等 - 数据控制语言DCL（Data Control Language）：
grant revoke ***mit rollback 等
本文会频繁使用到DCL 数据控制语言
点击此处可以了解关系数据库标准语言SQL的概念和发展历史
一、访问控制
1.1 连接验证（阶段1）
当我们连接mysql的时候,MySQL会以下面2点进行连接验证
- 通过提供的账号和密码来验证身份
- 验证账号是否锁定
服务首先检查凭据，然后检查账户的锁定状态。任一步骤失败都会拒绝访问，没有问题就接收连接。锁定状态记录在user表a***ount_locked列中。
mysql> select user,a***ount_locked from user;
+--------+----------------+
| user | a***ount_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.*** | 来自h1.example.***的fred |
| ‘’ | ‘h1.example.***’ | 来自h1.example.***的任何用户 |
| ‘fred’ | ‘%’ | 任何主机的fred |
| ‘’ | ‘’ | 任何主机的任何用户 |
| ‘fred’ | ‘%.example.***’ | 从example.***的任何主机的fred |
| ‘fred’ | ‘x.example.***%’ | 从x.example.***，从x.example.***等的任何主机的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' [A***OUNT UNLOCK]
A***OUNT 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,a***ount_locked from user;
+--------+-----------+----------------+
| user | host | a***ount_locked |
+--------+-----------+----------------+
| root | localhost | N |
| multis | localhost | N |
+--------+-----------+----------------+
2 rows in set (0.00 sec)
1、我们使用alter修改'multis'@'localhost'用户状态为锁定状态
mysql> alter user 'multis'@'localhost' a***ount lock;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,a***ount_locked from user;
+--------+-----------+----------------+
| user | host | a***ount_locked |
+--------+-----------+----------------+
| root | localhost | N |
| multis | localhost | Y |
+--------+-----------+----------------+
2 rows in set (0.00 sec)
重新打开一个窗口进行mysql登陆，登陆的时候，MySQL报错ERROR 3118 (HY000): A***ess denied for user 'multis'@'localhost'. A***ount is locked.帐号被锁定,用户被拒绝访问。
[root@mysql2 ~]# mysql -umultis -p
Enter password:
ERROR 3118 (HY000): A***ess denied for user 'multis'@'localhost'. A***ount 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' a***ount 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' a***ount 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.***f文件中指定：
[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 A***OUNT 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
a***ount_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 ***mand 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 A***OUNT 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.***f文件中指定：
[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 A***OUNT 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.***f配置文件中指定plugin-load=validate_password.so开启密码复杂度
[mysqld]
plugin-load=validate_password.so
关闭密码复杂度在my.***f配置文件中指定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.***f文件中指定：
[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 ***munity 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.***f文件中指定：
[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 A***OUNT 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 ***mand 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 ***mand 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 ***mand line interface can be insecure.
ERROR 1045 (28000): A***ess 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<U-T | 1 |
+-------+-----------+----------------------+-------------+
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 ***mand line interface can be insecure.
ERROR 1045 (28000): A***ess 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 ***mand line interface can be insecure.
ERROR 1045 (28000): A***ess 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 ***mand line interface can be insecure.
ERROR 1045 (28000): A***ess 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 ***mand 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 ***mand line interface can be insecure.
ERROR 1045 (28000): A***ess 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 ***mand line interface can be insecure.
ERROR 1045 (28000): A***ess 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 ***mand line interface can be insecure.
ERROR 1045 (28000): A***ess 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 ***mand line interface can be insecure.
ERROR 3955 (HY000): A***ess denied for user 'test3'@'localhost'. A***ount 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 ***mand line interface can be insecure.
ERROR 3955 (HY000): A***ess denied for user 'test3'@'localhost'. A***ount is blocked for 1 day(s) (1 day(s) remaining) due to 4 consecutive failed logins.
管理员解锁账户方能正常使用：（或者忘记密码，让管理员解锁账号并且重置新密码）
mysql> alter user 'test3'@'localhost' a***ount 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 ***mand 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 ***mand line interface can be insecure.
ERROR 1045 (28000): A***ess 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 ***mand line interface can be insecure.
ERROR 1045 (28000): A***ess 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 ***mand 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 ***mand line interface can be insecure.
ERROR 1045 (28000): A***ess 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 ***mand line interface can be insecure.
ERROR 1045 (28000): A***ess 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 ***mand line interface can be insecure.
ERROR 1045 (28000): A***ess 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 ***mand line interface can be insecure.
ERROR 3955 (HY000): A***ess denied for user 'test3'@'localhost'. A***ount is blocked for 1 day(s) (1 day(s) remaining) due to 4 consecutive failed logins.
[root@mysql2 ~]#
3.4 密码插件
3.4.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.4.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.4.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.***f文件中指定：
[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.4.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 ***mand 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 ，登录失败，因此可以认定双重身份认证机制是生效的。
四、用户权限管理
MySQL数据库对于对象的操作级别分为：全局、数据库、表、字段等。粒度从粗到细。如果粗的粒度的权限满足了，将不再检验细粒度的级别，也就是说全局权限满足了，就不会校验数据库、表和字段，依次类推。
4.1 权限粒度
| 权限相关的表 | 描述 |
|---|---|
| user | 用户账号，全局权限，连接权限 |
| db | 数据库级别权限 |
| tables_priv | 表级别权限 |
| columns_priv | 列级别权限 |
| procs_priv | 存储过程，函数级别权限 |
| proxies_priv | 代理用户权限 |
4.2 显示用户非权限属性
mysql> show create user 'grant'@'localhost' \G;
*************************** 1. row ***************************
CREATE USER for grant@localhost: CREATE USER 'grant'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT A***OUNT UNLOCK
1 row in set (0.00 sec)
ERROR:
No query specified
4.3 全局级权限
我们创建‘grant’@'localhost'用户进行权限的学习
mysql> create user 'grant'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
使用show grants for查询用户权限，可以看到刚才创建的用户拥有USAGE权限
mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT USAGE ON *.* TO 'grant'@'localhost'
1 row in set (0.00 sec)
ERROR:
No query specified
授予用户replication client 和 replication slave用户级权限后进行用户权限的查询
mysql> grant replication client , replication slave on *.* to 'grant'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'grant'@'localhost'
1 row in set (0.00 sec)
ERROR:
No query specified
查询用户权限，全局级权限对应的mysql.user表
mysql> select * from mysql.user where user='grant' \G;
*************************** 1. row ***************************
Host: localhost
User: grant
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: Y
Repl_client_priv: Y
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-20 22:37:57
password_lifetime: NULL
a***ount_locked: N
1 row in set (0.00 sec)
ERROR:
No query specified
4.4 库级别权限
这里创建一个数据库，模拟测试库级别权限，在这里我们回顾一下字符集的设置创建表的时候没有指定字符集，默认使用数据库，如果数据库也没有设置，就默认使用server的字符集。
mysql> show variables like 'character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /opt/mysql/mysql-5.7.39-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.00 sec)
创建数据库，默认字符集utf8
mysql> create database testdb default character set utf8;
Query OK, 1 row affected (0.00 sec)
授予‘grant’@'localhost'用户testdb 库级的权限
mysql> grant select,insert,update,delete,create,alter,lock tables,show view,trigger on tesdb.* to 'grant'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查询用户权限
mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'grant'@'localhost'
*************************** 2. row ***************************
Grants for grant@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES, SHOW VIEW, TRIGGER ON `tesdb`.* TO 'grant'@'localhost'
2 rows in set (0.00 sec)
ERROR:
No query specified
查询库级别权限，库级别权限对应的表是mysql.db表
mysql> select * from mysql.db where user='grant' \G;
*************************** 1. row ***************************
Host: localhost
Db: tesdb
User: grant
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: Y
Create_tmp_table_priv: N
Lock_tables_priv: Y
Create_view_priv: N
Show_view_priv: Y
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: Y
1 row in set (0.00 sec)
ERROR:
No query specified
4.5 表级权限
我们创建一张表，进行权限的学习
mysql> use testdb;
Database changed
mysql> create table test1 (id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test1 |
+------------------+
1 row in set (0.00 sec)
授予‘grant’@'localhost'用户testdb.test1 表级别的权限
mysql> grant select,insert,update,delete,create,alter on testdb.test1 to 'grant'@'localhost';
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查询用户权限
mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'grant'@'localhost'
*************************** 2. row ***************************
Grants for grant@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES, SHOW VIEW, TRIGGER ON `tesdb`.* TO 'grant'@'localhost'
*************************** 3. row ***************************
Grants for grant@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `testdb`.`test1` TO 'grant'@'localhost'
3 rows in set (0.00 sec)
ERROR:
No query specified
查询表级别权限，表级别对应的mysql.tables_priv表
mysql> select * from mysql.tables_priv where table_name='test1'\G;
*************************** 1. row ***************************
Host: localhost
Db: testdb
User: grant
Table_name: test1
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select,Insert,Update,Delete,Create,Alter
Column_priv:
1 row in set (0.00 sec)
ERROR:
No query specified
4.6 列级权限
我们授予授予‘grant’@'localhost'用户testdb.test1 表id字段查询权限，name字段更新权限
mysql> grant select(id),update(name) on testdb.test1 to 'grant'@'localhost';
Query OK, 0 rows affected (0.04 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查询用户权限
mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'grant'@'localhost'
*************************** 2. row ***************************
Grants for grant@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES, SHOW VIEW, TRIGGER ON `tesdb`.* TO 'grant'@'localhost'
*************************** 3. row ***************************
Grants for grant@localhost: GRANT SELECT, SELECT (id), INSERT, UPDATE, UPDATE (name), DELETE, CREATE, ALTER ON `testdb`.`test1` TO 'grant'@'localhost'
3 rows in set (0.00 sec)
ERROR:
No query specified
查询列级别权限
列级别对应的mysql.tables_priv表或者columns_priv表,但是我们查询mysql.tables_priv表发现列级别权限有更查询和更新权限，但不知道是哪一列
mysql> select * from mysql.tables_priv where table_name = 'test1' \G;
*************************** 1. row ***************************
Host: localhost
Db: testdb
User: grant
Table_name: test1
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select,Insert,Update,Delete,Create,Alter
Column_priv: Select,Update
1 row in set (0.00 sec)
ERROR:
No query specified
查询mysql.columns_priv表可以明确看出哪一列有什么权限
mysql> select * from mysql.columns_priv where table_name = 'test1' \G;
*************************** 1. row ***************************
Host: localhost
Db: testdb
User: grant
Table_name: test1
Column_name: id
Timestamp: 0000-00-00 00:00:00
Column_priv: Select
*************************** 2. row ***************************
Host: localhost
Db: testdb
User: grant
Table_name: test1
Column_name: name
Timestamp: 0000-00-00 00:00:00
Column_priv: Update
2 rows in set (0.00 sec)
ERROR:
No query specified
4.7 权限回收
查询用户权限
mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'grant'@'localhost'
*************************** 2. row ***************************
Grants for grant@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES, SHOW VIEW, TRIGGER ON `tesdb`.* TO 'grant'@'localhost'
*************************** 3. row ***************************
Grants for grant@localhost: GRANT SELECT, SELECT (id), INSERT, UPDATE, UPDATE (name), DELETE, CREATE, ALTER ON `testdb`.`test1` TO 'grant'@'localhost'
3 rows in set (0.00 sec)
ERROR:
No query specified
4.7.1 回收部分权限
使用revoke 进行权限的回收
mysql> revoke select(id), update(name) ON testdb.test1 from 'grant'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
在这里，我们需要知道MySQL权限机制中，在一个数据库上多次赋予权限，权限会自动合并；
mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT USAGE ON *.* TO 'grant'@'localhost'
*************************** 2. row ***************************
Grants for grant@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES, SHOW VIEW, TRIGGER ON `tesdb`.* TO 'grant'@'localhost'
2 rows in set (0.00 sec)
ERROR:
No query specified
4.7.2 回收部分权限（MySQL 8.0 新特性）
MySQL 数据库对于对象的操作级别分为：全局、数据库、表、字段等。粒度从粗到细。如果粗的粒度的权限满足了，将不再检验细粒度的级别，这种验证方式有的时候不方便，例如需要把 100 个数据库中除了某一个数据库外的访问权限赋予某个用户，需要进行 99 次赋权。从 MySQL 8.0.16 开始，MySQL 推出了一种部分权限回收（Partial Revokes）的功能，可以将粗粒度赋予的权限在细粒度上回收。
可以使用下面的命令将这个参数打开
mysql> SET PERSIST partial_revokes = ON;
Query OK, 0 rows affected (0.00 sec)
我们创建一个'test'@'localhost'用户
mysql> create user 'test'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
下面的命令赋予用户 'test'@'localhost'对除了 mysql 之外的所有数据库和下面的表的 select 权限：
mysql> grant select on *.* to 'test'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> revoke select on mysql.* from 'test'@'localhost';
Query OK, 0 rows affected (0.01 sec)
赋权完成后可以使用 show grants 命令进行检查：
mysql> show grants for 'test'@'localhost';
+----------------------------------------------------+
| Grants for test@localhost |
+----------------------------------------------------+
| GRANT SELECT ON *.* TO `test`@`localhost` |
| REVOKE SELECT ON `mysql`.* FROM `test`@`localhost` |
+----------------------------------------------------+
2 rows in set (0.00 sec)
权完成后在 mysql.user 表里面的 User_attributes 会有 Restrictions 的属性：
mysql> select User_attributes from mysql.user where user='test';
+---------------------------------------------------------------------+
| User_attributes |
+---------------------------------------------------------------------+
| {"Restrictions": [{"Database": "mysql", "Privileges": ["SELECT"]}]} |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
回收部分权限回收功能可以再次赋予部分权限，例如：
mysql> grant select on *.* to 'test'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'test'@'localhost';
+----------------------------------------------------+
| Grants for test@localhost |
+----------------------------------------------------+
| GRANT SELECT ON *.* TO `test`@`localhost` |
+----------------------------------------------------+
1 rows in set (0.00 sec)
也可以从粗粒度上回收权限，这样细粒度的回收当然没有必要存在了
mysql> revoke all privileges on *.* from 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test'@'localhost';
+------------------------------------------+
| Grants for test@localhost |
+------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`localhost` |
+------------------------------------------+
1 row in set (0.00 sec)
4.7.3 回收全部权限
mysql> revoke all privileges on *.* from 'grant'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查询用户权限
mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT USAGE ON *.* TO 'grant'@'localhost'
*************************** 2. row ***************************
Grants for grant@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, LOCK TABLES, SHOW VIEW, TRIGGER ON `tesdb`.* TO 'grant'@'localhost'
2 rows in set (0.00 sec)
ERROR:
No query specified
是不是很意外，按照我们的思路，'grant'@'localhost'用户应该没有权限，但是在这里revoke说它干不了这活，加钱也干不了。。。查阅了很多资料：MySQL权限机制中，在一个数据库上多次赋予权限，权限会自动合并；但在多个库上多次赋予权限，每个库上都会认为是单独的一组权限。若要收回，必须单独对相应的库使用revoke命令。看来 revoke也有划水摸鱼的时候。但是在8.0 版本中，好像被优化了。我们只能再次回收库级别权限
mysql> revoke select, insert, update, delete, create, alter, lock tables, show view, trigger on tesdb.* from 'grant'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'grant'@'localhost' \G;
*************************** 1. row ***************************
Grants for grant@localhost: GRANT USAGE ON *.* TO 'grant'@'localhost'
1 row in set (0.00 sec)
ERROR:
No query specified
4.8 MySQL权限（MySQL 8.0 新特性）
权限列表大体分为服务级别和表级别，列级别以及大而广的角色（也是MySQL 8.0 新增）存储程序等权限。我们看到有一个特殊的 SUPER 权限，可以做好多个操作。比如 SET 变量，在从机重新指定相关主机信息以及清理二进制日志等。那这里可以看到，SUPER 有点太过强大，导致了仅仅想实现子权限变得十分困难，比如用户只能 SET 变量，其他的都不想要。那么 MySQL 8.0 之前没法实现，权限的细分不够明确，容易让非法用户钻空子。那么 MySQL 8.0 把权限细分为静态权限和动态权限
4.8.1 mysql 8.0 静态权限汇总
MySQL服务器内置了静态特权，下表描述了MySQL中可用的每个静态特权在这里我把权限划分为Server、Table、Column、Rose&User、Stored routine等
| 权限 | 对应user表中的字段 | 权限描述 | 权限划分 | 全局级权限 | 库级权限 | 表级权限 | 列级权限 | 备注 |
|---|---|---|---|---|---|---|---|---|
| usag | 该权限只能用于数据库登录，不能执行任何操作 | Server | 且该权限不能被回收，即使使用REVOKE也不能删除用户权限 | |||||
| All或者All privileges | 所有的权限名 | Server | × | 影响除 with grant option 之外的所有权限 | ||||
| Super | Super_priv | 管理员级命令的使用，如change master to、kill、thread、mysqladmin、debug、purge master log 和set global等 | Server | √ | × | × | × | mysql 8.0中动态权限就是对 SUPER 权限的细分。 SUPER 权限在未来将会被废弃掉，授予管理员除外的用户存在严重的安全隐患 |
| file | File_priv | 执行select …into outfile，load data infile…操作 | Server | √ | × | × | × | 授予管理员除外的用户存在严重的安全隐患 |
| process | Process_priv | 查看所有用户线程和连接的权限 | Server | √ | × | × | × | 授予管理员除外的用户存在严重的安全隐患 |
| reload | Reload_priv | 必须拥有reload权限，才能flush tables、logs、privileges | Server | √ | × | × | × | |
| replication slave | Repl_slave_priv | 拥有此权限可以查看从服务器，从主服务器读取二进制日志 | Server | √ | × | × | × | |
| replication client | Repl_client_priv | 允许执行show master status, show slave status, show binary logs | Server | √ | × | × | × | |
| shutdown | Shutdown_priv | 关闭mysql的权限 | Server | √ | × | × | × | |
| show database | Show_db_priv | 查看拥有的数据库 | Server | √ | × | × | × | |
| event | Event_priv | 表示拥有创建，修改，执行和删除事件（event）的权限 | Server | √ | √ | × | × | |
| lock tables | Lock_tables_priv | 锁表的权限 | Server | √ | √ | × | × | |
| select | Select_priv | 查看表 | Table | √ | √ | √ | √ | 权限的后面需要加上列名列表 column-list |
| insert | Insert_priv | 插入权限 | Table | √ | √ | √ | √ | 权限的后面需要加上列名列表 column-list。 |
| update | Update_priv | 修改表数据 | Table | √ | √ | √ | √ | 权限的后面需要加上列名列表 column-list。 |
| delete | Delete_priv | 删除行权限 | Table | √ | √ | √ | × | |
| create | Create_priv | 创建表的权限 | Table | √ | √ | √ | × | |
| drop | Drop_priv | 删除库，表，索引，视图 | Table | √ | √ | √ | × | |
| references | References_priv | 用户可以将其他的一个字段作为某一个表的外键约束 | Table | √ | √ | √ | × | |
| index | Index_priv | 必须拥有index权限，才能执行create index或者drop index | Table | √ | √ | √ | × | |
| alter | Alter_priv | 修改表的结构 | Table | √ | √ | √ | × | |
| create view | Create_view_priv | 创建视图 | Table | √ | √ | √ | × | |
| show view | Show_view_priv | 查看视图 | Table | √ | √ | √ | × | |
| trigger | Trigger_priv | 允许创建，删除，执行，显示触发器的权限 | Table | √ | √ | √ | × | |
| create temporay tables | Create_tmp_table_priv | 创建临时表 | Table | √ | √ | × | × | |
| create routine | Create_routine_priv | 创建存储函数或者存储过程的权限 | Stored routine | √ | √ | × | × | |
| alter routine | Alter_routine_priv | 更改或者删除存储函数或者存储过程 | Stored routine | √ | √ | × | × | |
| excute | Execute_priv | 以用户执行存储过程的权限 | Stored routine | √ | √ | × | × | |
| create user | Create_user_priv | 用户可以创建和删除新用户的权限 | Rose&User | √ | × | × | × | |
| grant option | Grant_priv | 拥有grant option，就可以将自己拥有的权限授予给其他用户 | Rose&User | √ | × | × | × | 授予权限 语句后面跟with grant option |
| create role | Create_role_priv | 创建角色 | Rose&User | √ | × | × | × | MySQL 8.0 新特性 |
| drop role | Drop_role_priv | 删除角色 | Rose&User | √ | × | × | × | MySQL 8.0 新特性 |
管理权限也就是全局权限（如 super， process， file等）不能够指定某个数据库，on后面必须跟 *.*
其实truncate权限就是create+drop，这点需要注意
4.8.2 动态权限汇总
与在服务器中内置的静态特权相反，动态特权是在运行时定义的。下表描述了MySQL中可用的每个动态特权。大多数动态特权是在服务器启动时定义的。其他特权由特定的服务器组件或插件定义，如特权描述中所述。在这种情况下，除非启用了定义特权的组件或插件，否则特权不可用。
| 权限 | 权限描述 |
|---|---|
| connection_admin | 允许使用KILL语句或mysqladmin kill命令杀死属于其他帐户的线程 |
| encryption_key_admin | 启用InnoDB加密密钥轮换 |
| firewall_exempt | 免除用户防火墙限制。该权限由MYSQL_FIREWALL插件定义； |
| flush_optimizer_costs | 启用优化程序成本重新加载 |
| flush_status | 启用状态指示灯刷新 |
| flush_tables | 启用表刷新 |
| flush_user_resources | 启用用户资源刷新 |
| group_replication_admin | 启动组复制 |
| group_replication_stream | 启用连接安全管理的通信堆栈 |
| innodb_redo_log_archive | 启用重做日志存档管理 |
| innodb_redo_log_enable | 启用或禁用重做日志记录 |
| passwordless_user_admin | 启用无密码用户帐户管理 |
| persist_ro_variables_admin | 启用持久化只读系统变量 |
| replication_applier | 允许该帐户充当PRIVILEGE_CHECKS_USER复制通道的帐户，并执行mysqlbinlog输出中的BINLOG语句 |
| replication_slave_admin | 启用常规复制控制,使帐户能够连接到主服务器，使用START SLAVE和STOP SLAVE语句启动和停止复制，以及使用CHANGE MASTER TO和CHANGE REPLICATION FILTER语句。 |
| resource_group_admin | 启用资源组管理，包括创建，更改和删除资源组，以及将线程和语句分配给资源组 |
| resource_group_user | 允许将线程和语句分配给资源组 |
| role_admin | 启用要授予或撤消的角色 with admin option |
| sensitive_variables_observer | 可以性能模式系统变量表的权限 |
| service_connection_admin | 启用到仅允许管理连接的网络接口的连接 |
| session_variables_admin | 启用设置受限会话系统变量 |
| set_user_id | 启用设置非自身值 |
| show_routine | 启用对存储的例程定义的访问 |
| system_user | 将帐户指定为系统帐户 |
| system_variables_admin | 启用修改或保留全局系统变量 |
| table_encryption_admin | 启用覆盖默认加密设置 |
| xa_recover_admin | 启用 XA 恢复执行 |
这些权限只能后面慢慢测试了
4.8.2 权限授权和回收技巧
授予权限grant 权限1…权限n on dbname.tablename to user;
回收权限revoke 权限1…权限n on dbname.tablename from user;
也就是grant对应的revoke,to对应的from
五、角色管理（MySQL 8.0 新特性）
角色是在MySQL8.0中引入的新功能。在MySQL中，角色是权限的集合，可以为角色添加或移除权限。用户可以被赋予角色，同时也被授予角色包含的权限。对角色进行操作需要较高的权限。并且像用户账户一样，角色可以拥有授予和撤消的权限。引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定，可以确保数据的安全性，这是至关重要的。
5.1 创建角色并授予权限
我们使用create role创建角色
mysql> create role 'dba_role','write_role','read_role';
Query OK, 0 rows affected (0.02 sec)
授予权限
mysql> grant all on *.* to 'dba_role';
Query OK, 0 rows affected (0.01 sec)
mysql> grant select on *.* to 'read_role';
Query OK, 0 rows affected (0.01 sec)
mysql> grant update,delete,insert on *.* to 'write_role';
Query OK, 0 rows affected (0.01 sec)
将角色授予对应的用户
mysql> create user dba1 identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant 'dba_role' to 'dba1'@'%';
Query OK, 0 rows affected (0.01 sec)
在GRANT授权角色的语法和授权用户的语法不同：有一个ON来区分角色和用户的授权，有ON的为用户授权，而没有ON用来分配角色。由于语法不同，因此不能在同一语句中混合分配用户权限和角色。（允许为用户分配权限和角色，但必须使用单独的GRANT语句，每种语句的语法都要与授权的内容相匹配）
5.2 检查角色权限
要验证分配给用户的权限，使用 SHOW GRANTS，例如：
mysql> SHOW GRANTS FOR 'dba1'@'%';
+------------------------------------+
| Grants for dba1@% |
+------------------------------------+
| GRANT USAGE ON *.* TO `dba1`@`%` |
| GRANT `dba_role`@`%` TO `dba1`@`%` |
+------------------------------------+
2 rows in set (0.00 sec)
但是，它会显示每个授予的角色，而不会将其显示为角色所代表的权限。如果要显示角色权限，添加一个 USING来显示：
mysql> SHOW GRANTS FOR 'dba1'@'%' USING 'dba_role' \G;
*************************** 1. row ***************************
Grants for dba1@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `dba1`@`%`
*************************** 2. row ***************************
Grants for dba1@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `dba1`@`%`
*************************** 3. row ***************************
Grants for dba1@%: GRANT `dba_role`@`%` TO `dba1`@`%`
3 rows in set (0.00 sec)
5.3 撤消角色或角色权限
revoke 可以用于角色修改角色权限。这不仅影响角色本身权限，还影响任何授予该角色的用户权限。
假设让所有用户只读，使用revoke 从该dba_ro le角色中删除修改权限 ：
mysql> revoke inster, update, delete on *.* from 'dba_role';
Query OK, 0 rows affected, 1 warning (0.02 sec)
正如可以授权某个用户的角色一样，可以从帐户中撤销这些角色：
mysql> revoke dba_role from dba1@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'dba1'@'%';
+----------------------------------+
| Grants for dba1@% |
+----------------------------------+
| GRANT USAGE ON *.* TO `dba1`@`%` |
+----------------------------------+
1 row in set (0.00 sec)
5.4 删除角色
要删除角色，请使用drop role：
mysql> drop role dba_role;
Query OK, 0 rows affected (0.01 sec)
删除角色会从授权它的每个帐户中撤消该角色
六、资源限制
资源限制可以从以下4个方面限制
- 帐户每小时可发出的查询数量
- 帐户每小时可以发布的更新次数
- 帐户每小时可以连接到服务器的次数
- 帐户同时连接到服务器的数量
6.1 用户创建指定配额
mysql> create user 'test4'@'localhost' identified by '123456' WITH MAX_QUERIES_PER_HOUR 2 MAX_UPDATES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 5 MAX_USER_CONNECTIONS 2;
Query OK, 0 rows affected (0.03 sec)
6.2 修改配额
mysql> alter user 'test4'@'localhost' with MAX_QUERIES_PER_HOUR 100;
Query OK, 0 rows affected (0.01 sec)
至此今天的学习就到此结束了，愿您成为坚不可摧的自己~~~
You can’t connect the dots looking forward; you can only connect them looking backwards. So you have to trust that the dots will somehow connect in your future.You have to trust in something - your gut, destiny, life, karma, whatever. This approach has never let me down, and it has made all the difference in my life
如果我的内容对你有帮助，请 点赞、评论、收藏，创作不易，大家的支持就是我坚持下去的动力
