【MySQL】——用户和权限管理（二）

最新推荐文章于 2024-05-19 03:18:57 发布

Y小夜

最新推荐文章于 2024-05-19 03:18:57 发布

阅读量1.1k

点赞数 23

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/shsjssnn/article/details/136681948

版权

MySQL 专栏收录该内容

25 篇文章 2 订阅

订阅专栏

💻博主现有专栏：

C51单片机（STC89C516），c语言，c++，离散数学，算法设计与分析，数据结构，Python，Java基础，MySQL，linux，基于HTML5的网页设计及应用，Rust（官方文档重点总结），jQuery，前端vue.js，Javaweb开发，Python机器学习等
🥏主页链接：

Y小夜-CSDN博客

🎯权限管理

🎃MySQL的各种权限

MySQL的权限类型分为全局级、数据库级、表级、列级和例程（存储过程、函数）级。用户权限都存储在mysql系统数据库的权限表中，如表所示。

（1）全局级（用户级）权限：和MySQL所有的数据库相关。

（2）数据库级权限：和一个具体的数据库中的所有表相关。

（3）表级权限：和一个具体表中的所有数据相关。

（4）列级权限：和表中的一个具体列相关。

（5）例程级权限。这些权限可以被授予为全局级和数据库级，也可以被授予为例程级。

🎃授予权限和查看权限

在MySQL中使用GRANT语句只能对已存在的用户授权，如果授权的用户不存在，则会出错。

基本语法格式为：

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...

ON [object_type] priv_level

TO user_or_role [, user_or_role] ...

[WITH GRANT OPTION]

参数说明：

priv_type：表示用户的权限，如select，update。

column_list ：列名。

object_type：（如果存在）应指定为表、函数或过程。

priv_level ：表示用户的权限范围。

user_or_role：用户或角色，用户由用户名和主机名组成。

WITH GRANT OPTION：表示该用户可以将自己拥有的权限授权给其他用户。

1. 查看新用户的权限

        用户可以通过SHOW GRANTS语句查看拥有哪些权限，当然如果有对mysql数据库的访问权限也可以直接查询权限表。

        基本语法格式为：

SHOW GRANTS [FOR user_or_role];

说明：user_or_role表示用户或角色。

SHOW GRANTS;语句表示查看当前用户的权限，也可以表示成：

SHOW GRANTS FOR CURRENT_USER;

或者 SHOW GRANTS FOR CURRENT_USER();

2.授予表级权限和列级权限

（1）授予表级权限

        授予表级权限时，priv_type的值具体可以查看相应的权限表。

        基本格式为：

GRANT权限列表 ON 数据库名.表名 TO 用户 [WITH GRANT OPTION];

3. 授予数据库权限

授予数据库权限时，priv_type的取值可以查看mysql.db表。

        基本格式为：

GRANT权限列表 ON 数据库名.* TO 用户 [WITH GRANT OPTION];

说明：授予数据库权限时ON关键字后面跟“*”和“数据库.*”。“*”表示当前数据库中的所有表；“数据库.*”表示某个数据库中的所有表。

4. 授予全局级权限

授予全局权限时priv_type的取值可以查看mysql.user表。

基本格式为： GRANT权限列表 ON *.* TO 用户 [WITH GRANT OPTION];

说明： ON子句中使用“*.*”，表示所有数据库的所有表。

🎃收回权限

        收回权限就是取消某个用户的某些权限。

        要使用REVOKE，用户必须拥有mysql数据库的全局CREATE USER权限或UPDATE权限。

基本语法格式：

REVOKE priv_type[(column_list)] [, priv_type [(column_list)]] ...

ON [object_type] priv_level

FROM user_or_role [, user_or_role] ...

或者： REVOKE ALL [PRIVILEGES], GRANT OPTION FROM user_or_role [, user_or_role]         说明：参数的含义与grant命令的参数含义相同。第一种格式用来回收某些特定的权限，第二种格式回收所有该用户的权限。

🎯角色管理

MySQL角色是权限的命名集合。像用户一样，角色可以拥有授予和撤销的权限。

可以向用户授予角色，该角色将与每个角色相关联的权限授予该用户。这样就可以将权限集分配给用户，并为授予用户权限提供了一种方便的替代方法，既可以概念化所需的权限分配，也可以实现它们。

🎃创建角色

创建角色使用CREATE ROLE命令，基本语法格式为：

CREATE ROLE [IF NOT EXISTS] role [, role ] ...

角色名的语法和语义同用户名：角色名称由用户名和主机名两部分组成。存储在授权表中时，它们具有与用户名相同的属性，这些属性在授权表账号列属性中有描述。

角色名与用户名不同之处：

角色名称的用户名不能为空。

省略角色名的主机名默认为“%”。但与用户名中的“%”不同，角色名中“%”的主机部分没有通配符属性。
角色名的主机名中的网络掩码没有意义。

注意：

（1）CREATE ROLE一次可以创建一个或多个角色，这些角色被命名为权限集合。若要使用CREATE ROLE语句，必须具有CREATE ROLE 或CREATE USER权限。启用read_only系统变量时，CREATE ROLE还需要CONNECTION_ADMIN 或 SUPER权限。

（2）角色在创建时被锁定，没有密码，并且被分配默认的身份验证插件。

🎃授予及查看角色权限

1. 授予角色权限

使用GRANT将角色授予用户，即将权限的集合授予用户，语法格式为：

GRANT role [, role] ... TO user_or_role [, user_or_role] ... [WITH ADMIN OPTION]

注意：使用该语句，用户必须具有ROLE_ADMIN 或 SUPER权限，或是被授予了包含with ADMIN OPTION子句的GRANT语句的角色。若要授予具有SYSTEM_USER权限的角色，必须具有SYSTEM_USER权限。

2. 查看验证角色权限

要验证角色分配给用户的权限，使用 SHOW GRANTS查看权限。

基本语法格式为： SHOW GRANTS [FOR user_or_role] [USING role] ;

🎃激活角色

        在用户会话中，授予用户的角色可以是活动的或非活动的。如果授予的角色在会话中处于活动状态，则应用其权限；否则，不应用。要确定当前会话中哪些角色处于活动状态，可以使用CURRENT_ROLE()函数。

        默认情况下，将角色授予用户或在mandatory_roles系统变量值中命名用户不会自动导致该角色在用户会话中变为活动角色。例如，由于到目前为止，在前面的示例中rw_user1尚未激活任何角色，如果以rw_user1身份连接到服务器，可以使用CURRENT_ROLE()函数。服务器调用CURRENT_ROLE()函数，则结果为NONE（没有活动角色）。

        用户rw_user1登录后，输入语句：select current_role();则结果为NONE。

        若要指定每次用户连接到服务器并进行身份验证时应激活哪些角色，使用SET DEFAULT ROLE命令

基本语法格式为： SET DEFAULT ROLE ALL TO 角色[,角色]…

🎃收回角色或角色权限

正如角色可以授予用户一样，也可以从用户中收回角色。语法格式为：
REVOKE role FROM user;
还可以对角色应用REVOKE来修改授予它的权限。这不仅影响角色本身，还影响授予该角色的任何用户。

🎃删除角色

删除角色要使用DROP ROLE命令。基本语法格式为：

DROP ROLE [IF EXISTS] role [, role ] ...

如：删除角色app_read和app_write。

SQL语句及执行结果为：

mysql> drop role app_read,app_write;

🎃角色和用户交换

正如前面提到的SHOW GRANTS（显示用户或角色的权限）一样，用户和角色可以互换使用。可以将用户视为角色，并将该用户授予其他用户或角色。其效果就是将用户的权限和角色授予其他用户或角色。

用户和角色的可互换性具有实际应用，例如在以下情况中：假设一个遗留应用程序开发项目在MySQL中的角色出现之前就开始了，因此，与项目关联的所有用户都被直接授予权限（而不是通过被授予角色而授予权限）。其中一个用户是最初被授予以下权限的开发人员用户： CREATE USER 'old_app_dev'@'localhost' IDENTIFIED BY 'old_app_devpass'; GRANT ALL ON old_app.* TO 'old_app_dev'@'localhost';