第03章 用户和权限管理【1.MySQL架构篇】【MySQL高级】3

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 第03章 用户和权限管理【1.MySQL架构篇】【MySQL高级】3

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主要管理角色的语句如下:



相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3天前
|
存储 SQL 关系型数据库
Mysql高可用架构方案
本文阐述了Mysql高可用架构方案,介绍了 主从模式,MHA模式,MMM模式,MGR模式 方案的实现方式,没有哪个方案是完美的,开发人员在选择何种方案应用到项目中也没有标准答案,合适的才是最好的。
35 3
Mysql高可用架构方案
|
1月前
|
监控 关系型数据库 MySQL
深入了解MySQL主从复制:构建高效稳定的数据同步架构
深入了解MySQL主从复制:构建高效稳定的数据同步架构
107 1
|
2月前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择:MySQL、PostgreSQL 还是 NoSQL?
在微服务架构中,数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中,我们将深入探讨传统的关系型数据库(如 MySQL 和 PostgreSQL)与现代 NoSQL 数据库的优劣势,并分析在微服务架构下的最佳实践。
|
3月前
|
SQL 关系型数据库 MySQL
(二十五)MySQL主从实践篇:超详细版读写分离、双主热备架构搭建教学
在上篇《主从原理篇》中,基本上把主从复制原理、主从架构模式、数据同步方式、复制技术优化.....等各类细枝末节讲清楚了,本章则准备真正对聊到的几种主从模式落地实践,但实践的内容通常比较枯燥乏味,因为就是调整各种配置、设置各种参数等步骤。
496 2
|
SQL 缓存 NoSQL
MySQL架构与SQL的执行流程_2
MySQL架构与SQL的执行流程_2
146 0
MySQL架构与SQL的执行流程_2
|
SQL 缓存 网络协议
MySQL架构与SQL的执行流程_1
MySQL架构与SQL的执行流程_1
143 0
MySQL架构与SQL的执行流程_1
|
23天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
56 3
Mysql(4)—数据库索引
|
9天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
45 2
|
12天前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
52 4
|
17天前
|
存储 关系型数据库 MySQL
如何在MySQL中创建数据库?
【10月更文挑战第16天】如何在MySQL中创建数据库?