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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 第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
相关文章
|
14天前
|
存储 SQL 关系型数据库
Mysql高可用架构方案
本文阐述了Mysql高可用架构方案,介绍了 主从模式,MHA模式,MMM模式,MGR模式 方案的实现方式,没有哪个方案是完美的,开发人员在选择何种方案应用到项目中也没有标准答案,合适的才是最好的。
75 3
Mysql高可用架构方案
|
1月前
|
监控 关系型数据库 MySQL
深入了解MySQL主从复制:构建高效稳定的数据同步架构
深入了解MySQL主从复制:构建高效稳定的数据同步架构
120 1
|
2月前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择:MySQL、PostgreSQL 还是 NoSQL?
在微服务架构中,数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中,我们将深入探讨传统的关系型数据库(如 MySQL 和 PostgreSQL)与现代 NoSQL 数据库的优劣势,并分析在微服务架构下的最佳实践。
|
9天前
|
SQL 存储 缓存
【赵渝强老师】MySQL的体系架构
本文介绍了MySQL的体系架构,包括Server层的7个主要组件(Connectors、Connection Pool、Management Service & Utilities、SQL Interface、Parser、Optimizer、Query Caches & Buffers)及其作用,以及存储引擎层的支持情况,重点介绍了InnoDB存储引擎。文中还提供了相关图片和视频讲解。
【赵渝强老师】MySQL的体系架构
|
3月前
|
SQL 关系型数据库 MySQL
(二十五)MySQL主从实践篇:超详细版读写分离、双主热备架构搭建教学
在上篇《主从原理篇》中,基本上把主从复制原理、主从架构模式、数据同步方式、复制技术优化.....等各类细枝末节讲清楚了,本章则准备真正对聊到的几种主从模式落地实践,但实践的内容通常比较枯燥乏味,因为就是调整各种配置、设置各种参数等步骤。
548 2
|
7天前
|
缓存 负载均衡 JavaScript
探索微服务架构下的API网关模式
【10月更文挑战第37天】在微服务架构的海洋中,API网关犹如一座灯塔,指引着服务的航向。它不仅是客户端请求的集散地,更是后端微服务的守门人。本文将深入探讨API网关的设计哲学、核心功能以及它在微服务生态中扮演的角色,同时通过实际代码示例,揭示如何实现一个高效、可靠的API网关。
|
5天前
|
Cloud Native 安全 数据安全/隐私保护
云原生架构下的微服务治理与挑战####
随着云计算技术的飞速发展,云原生架构以其高效、灵活、可扩展的特性成为现代企业IT架构的首选。本文聚焦于云原生环境下的微服务治理问题,探讨其在促进业务敏捷性的同时所面临的挑战及应对策略。通过分析微服务拆分、服务间通信、故障隔离与恢复等关键环节,本文旨在为读者提供一个关于如何在云原生环境中有效实施微服务治理的全面视角,助力企业在数字化转型的道路上稳健前行。 ####
|
6天前
|
Dubbo Java 应用服务中间件
服务架构的演进:从单体到微服务的探索之旅
随着企业业务的不断拓展和复杂度的提升,对软件系统架构的要求也日益严苛。传统的架构模式在应对现代业务场景时逐渐暴露出诸多局限性,于是服务架构开启了持续演变之路。从单体架构的简易便捷,到分布式架构的模块化解耦,再到微服务架构的精细化管理,企业对技术的选择变得至关重要,尤其是 Spring Cloud 和 Dubbo 等微服务技术的对比和应用,直接影响着项目的成败。 本篇文章会从服务架构的演进开始分析,探索从单体项目到微服务项目的演变过程。然后也会对目前常见的微服务技术进行对比,找到目前市面上所常用的技术给大家进行讲解。
17 1
服务架构的演进:从单体到微服务的探索之旅
|
5天前
|
Cloud Native 安全 API
云原生架构下的微服务治理策略与实践####
—透过云原生的棱镜,探索微服务架构下的挑战与应对之道 本文旨在探讨云原生环境下,微服务架构所面临的关键挑战及有效的治理策略。随着云计算技术的深入发展,越来越多的企业选择采用云原生架构来构建和部署其应用程序,以期获得更高的灵活性、可扩展性和效率。然而,微服务架构的复杂性也带来了服务发现、负载均衡、故障恢复等一系列治理难题。本文将深入分析这些问题,并提出一套基于云原生技术栈的微服务治理框架,包括服务网格的应用、API网关的集成、以及动态配置管理等关键方面,旨在为企业实现高效、稳定的微服务架构提供参考路径。 ####
24 5
|
7天前
|
监控 API 微服务
后端技术演进:从单体架构到微服务的转变
随着互联网应用的快速增长和用户需求的不断演化,传统单体架构已难以满足现代软件开发的需求。本文深入探讨了后端技术在面对复杂系统挑战时的演进路径,重点分析了从单体架构向微服务架构转变的过程、原因及优势。通过对比分析,揭示了微服务架构如何提高系统的可扩展性、灵活性和维护效率,同时指出了实施微服务时面临的挑战和最佳实践。
25 7