3.2 db表
具体数据库操作的权限
mysql> desc db; +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | Host | char(255) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | +-----------------------+---------------+------+-----+---------+-------+ 22 rows in set (0.00 sec)
主键 Host & Db & User 权限_priv
3.3 tables_priv表和columns_priv表
tables_priv表
mysql> desc tables_priv; +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+ | Host | char(255) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Table_name | char(64) | NO | PRI | | | | Grantor | varchar(288) | NO | MUL | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | | Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | | | Column_priv | set('Select','Insert','Update','References') | NO | | | | +-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+ 8 rows in set (0.01 sec)
主键 Host & Db & User & Table_name
columns_priv表
mysql> desc columns_priv; +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+ | Host | char(255) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Table_name | char(64) | NO | PRI | | | | Column_name | char(64) | NO | PRI | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | | Column_priv | set('Select','Insert','Update','References') | NO | | | | +-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+ 7 rows in set (0.01 sec)
主键 5个联合，粒度越小
3.4 procs_priv表
mysql> desc procs_priv; +--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+ | Host | char(255) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Routine_name | char(64) | NO | PRI | | | | Routine_type | enum('FUNCTION','PROCEDURE') | NO | PRI | NULL | | | Grantor | varchar(288) | NO | MUL | | | | Proc_priv | set('Execute','Alter Routine','Grant') | NO | | | | | Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | +--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+ 8 rows in set (0.01 sec)
4. 访问控制（了解）
正常情况下，并不希望每个用户都可以执行所有的数据库操作r当MySQL允许一个用户执行各种操作时，它将首 先核实该用户向MySQL服务器发送的连接请求，然后确认用户的操作请求是否被允许。这个过程称为MySQL中的 访问控制过程。MySQL的访问控制分为两个阶段:连接核实阶段和请求核实阶段。
4.1 连接核实阶段
当用户试图连接MySQL服务器时，服务器基于用户的身份以及用户是否能提供正确的密码验证身份来确定接受或 者拒绝连接。即客户端用户会在连接请求中提供用户名、主机地址、用户密码，MySQL服务器接收到用户请求 后，会使用user表中的host、 user和
authentication_string这3个字段匹配客户端提供信息。
服务器只有在user表记录的Host和User字段匹配客户端主机名和用户名，并且提供正确的密码时才接受连接。如果连接核实没有通过，服务器就完全拒绝访问否则，服务器接受连接，然后进入阶段2等待用户请求。
4.2 请求核实阶段
一旦建立了连接，服务器就进入了访问控制的阶段2，也就是请求核实阶段。对此连接上进来的每个请求，服务 器检查该请求要执行什么操作、是否有足够的权限来执行它，这正是需要授权表中的权限列发挥作用的地方。这 些权限可以来自user、db、table_priv和column_priv表。
确认权限时，MySQL首先检查user表，如果指定的权限没有在user表中被授予，那么MySQL就会继续检查db表，
db表是下一安全层级，其中的权限限定于数据库层级，在该层级的SELECT权限允许用户查看指定数据库的所有表
中的数据;如果在该层级没有找到限定的权限，则MySQL继续检查tables_priv表以及columns_priv表，如果
所有权限表都检查完毕，但还是没有找到允许的权限操作，MySQL将返回错误信息，用户请求的操作不能执行，
操作失败。请求核实的过程如图所示。
提示:
MySQL通过向下层级的顺序(从user表到columns_priv表)检查权限表，但并不是所有的权限都要执行该过程。例如，一个用户登录到MySQL服务器之后只执行对MySQL的管理操作，此时只涉及管理权限，因此MySQL 只检查user表。另外，如果请求的权限操作不被允许，MySQL也不会继续检查下一层级的表。
5. 角色管理
5.1角色的理解
角色是在MySQL8.0中引入的新功能。在MySQL中，角色是权限的集合，可以为角色添加或移除权限。用户可以被赋予角色，同时也被授予角色包含的权限。对角色进行操作需要较高的权限。并且像用户账户一样，角色可以 拥有授予和撤消的权限。
引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定，可以确保数据的安全性，这是至关重要的。
5.2 创建角色
在实际应用中，为了安全性，需要给用户授予权限。当用户数量较多时，为了避免单独给每一个用户授予多个权限，可以先将权限集合放入角色中，再赋予用户相应的角色。
创建角色使用CREATE ROLE语句，语法如下
CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]..
角色名称的命名规则和用户名类似。如果host_name省略，默认为%，role_name不可省略，不可为空。
练习:我们现在需要创建一个经理的角色，就可以用下面的代码:
CREATE ROLE 'manager'@'localhost';
这里创建了一个角色，角色名称是“manager”，角色可以登录的主机是"localhost”，意思是只能从数据库服务器运 行的这台计算机登录这个账号。你也可以不写主机名，直接创建角色“manager”:
CREATE ROLE 'manager';
如果不写主机名，MySQL默认是通配符“%”，意思是这个账号可以从任何一台主机上登录数据库。 同样道理，如果我们要创建库管的角色，就可以用下面的代码:
CREATE ROLE 'stocker';
通过如下的指令，一次性创建3个角色:
CREATE ROLE 'app_develeper','app_read','app_write';
测试
mysql> create role 'manager'@'%'; Query OK, 0 rows affected (0.03 sec) mysql> create role 'boss'@'%'; Query OK, 0 rows affected (0.01 sec)
5.3 给角色赋予权限
创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权。给角色授权的语法结构是:
GRANT privileges ON table_name To 'role_name'[@'host_name'];
上述语句中privileges代表权限的名称，多个权限以逗号隔开。可使用SHOW语句查询权限名称，权限列表，
SHOW PRIVILEGES\G;
测试
mysql> grant select,update on dbtest1.* to 'manager'; Query OK, 0 rows affected (0.00 sec) mysql> grant all privileges on *.* to 'boss' @'%'; Query OK, 0 rows affected (0.00 sec)
5.4 查看角色的权限
赋予角色权限后，我们可以通过SHOW GRANT语句，来查看权限是否创建成功了：
测试
mysql> show grants for 'manager'@'%'; +------------------------------------------------------+ | Grants for manager@% | +------------------------------------------------------+ | GRANT USAGE ON *.* TO `manager`@`%` | | GRANT SELECT, UPDATE ON `dbtest1`.* TO `manager`@`%` | +------------------------------------------------------+ 2 rows in set (0.01 sec) mysql> show grants for 'boss'; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for boss@% ····
5.5 回收角色的权限
角色授权后，可以对角色的权限进行维护，对权限进行添加或撤销。添加权限使用GRANT语句，与角色授权相同。撤销角色或角色权限使用REVOKE语句。
修改了角色的权限，会影响拥有该角色的账户的权限。 撤销角色权限的SQL语法如下:
REVOKE privileges ON tablename FROM 'rolename';
测试
mysql> revoke update on dbtest1.* from 'manager'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'manager'@'%'; +----------------------------------------------+ | Grants for manager@% | +----------------------------------------------+ | GRANT USAGE ON *.* TO `manager`@`%` | | GRANT SELECT ON `dbtest1`.* TO `manager`@`%` | +----------------------------------------------+ 2 rows in set (0.00 sec) mysql>
5.6删除角色
当我们需要对业务重新整合的时候，可能就需要对之前创建的角色进行清理，删除一些不会再使用的角色。删除 角色的操作很简单，你只要掌握语法结构就行了。
DROP ROLE role[,role2]..
注意，如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限。
练习:执行如下SQL删除角色school_read。
DROP ROLE 'school_read';
测试
mysql> create role 'admin'; Query OK, 0 rows affected (0.00 sec) mysql> drop role 'admin'; Query OK, 0 rows affected (0.01 sec) mysql> show grants for 'admin'; ERROR 1141 (42000): There is no such grant defined for user 'admin' on host '%' mysql>
5.7给用户赋予角色
角色创建并授权后，要赋给用户并处于激活状态才能发挥作用。给用户添加角色可使用GRANT语句，语法形式如下：
先查看有哪些用户。
Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select host,user from user; +-----------+------------------+ | host | user | +-----------+------------------+ | % | boss | | % | li4 | | % | manager | | % | root | | % | zhang3 | | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | zhang3 | +-----------+------------------+ 9 rows in set (0.00 sec) mysql>
创建一个用户，用于测试
mysql> create user 'wang5'@'%' identified by 'abc123'; Query OK, 0 rows affected (0.00 sec)
登录
[root@centos7-mysql-1 ~]# mysql -uwang5 -pabc123 mysql> show grants; +-----------------------------------+ | Grants for wang5@% | +-----------------------------------+ | GRANT USAGE ON *.* TO `wang5`@`%` | +-----------------------------------+ 1 row in set (0.00 sec) mysql>
测试：给wang5赋予角色
mysql> grant 'manager'@'%' to 'wang5'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'wang5'; +------------------------------------+ | Grants for wang5@% | +------------------------------------+ | GRANT USAGE ON *.* TO `wang5`@`%` | | GRANT `manager`@`%` TO `wang5`@`%` | +------------------------------------+ 2 rows in set (0.00 sec) mysql>
wang5中查看权限
mysql> show grants; +------------------------------------+ | Grants for wang5@% | +------------------------------------+ | GRANT USAGE ON *.* TO `wang5`@`%` | | GRANT `manager`@`%` TO `wang5`@`%` | +------------------------------------+ 2 rows in set (0.00 sec) mysql>
即使退出重登或flush 权限
查询当前角色，如果角色未激活，结果显示NONE。
SELECT CURRENT_ROLE();
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec)
所以需要激活权限
注意：角色默认是不激活的，所有必须要手动激活
5.8 激活角色
激活角色有两种方式：
方式1：使用set default role命令激活角色
举例：
SET DEFAULT ROLE TO 'kangshifu'@'localhost';
测试
mysql> SET DEFAULT role 'manager'@'%' TO 'wang5'@'%'; Query OK, 0 rows affected (0.00 sec)
退出重新登录
mysql> SELECT CURRENT_ROLE(); +----------------+ | CURRENT_ROLE() | +----------------+ | `manager`@`%` | +----------------+ 1 row in set (0.00 sec)
注意：用户需要退出重新登录，才能看到赋予的角色。
方式2：使用activate_all_roles_on_login设置为ON
- 默认情况：
mysql> show variables like 'activate_all_roles_on_login'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | activate_all_roles_on_login | OFF | +-----------------------------+-------+ 1 row in set (0.01 sec) mysql>
- 设置：
SET GLOBAL activate_all_roles_on_login=ON;
这条SQL语句的意思是，对所有角色永久激活。运行这条语句后，用户才真正拥有赋予角色的所有权限。
查看当前会话已激活的角色：
SELECT CURRENT_ROLE();
5.9 撤销用户的角色
撤销用户的角色的SQL语法如下：
REVOKE role FROM user;
测试
wang5自己不能回收权限
mysql> revoke 'manager'@'%' from 'wang5'@'%'; ERROR 1227 (42000): Access denied; you need (at least one of) the WITH ADMIN, ROLE_ADMIN, SUPER privilege(s) for this operation mysql>
root回收权限
mysql> revoke 'manager'@'%' from 'wang5'@'%'; Query OK, 0 rows affected (0.00 sec) mysql>
需要退出重登
5.10 设置强制的角色（mandatory role）
强制角色是给每个创建账户的默认角色，不需要手动设置。强制角色无法被REVOKE或者DROP。
方式1:服务启动前设置
[mysqld] mandatory_roles='role1.role2@localhost,r3@%.atguigu.com'
方式2:运行时设置
SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com';#系统重启后仍然有效 SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com';#系统重启后失效
5.11小结
MySQL主要管理角色的语句如下：
