SQL系列总结（四）：DCL（数据控制语言）

2023-07-06 13:28:15 浏览数 (1)

目录：

TOC

环境说明： 数据库：Mysql 5.5 连接软件：Navicat

前言

SQL总结系列目录：

  • SQL系列总结（一）：DDL（数据定义语言）- Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com)
  • SQL系列总结（二）：DQL（数据查询语言）- Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com)
  • SQL系列总结（三）：DML（数据操纵语言） - Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com)
  • SQL系列总结（四）：DCL（数据控制语言） - Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com)
  • SQL系列总结（五）：TCL（事务控制语言） - Roookie博客 | 记录 · 收纳 · 分享 (wlplove.com)

数据控制语言(Data Control Language，DCL)，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、存储程序、用户自定义函数等数据库对象的控制权。由GRANT和REVOKE两个指令组成。

0x01.权限的授予与收回

用户对某一数据对象的操作权称为权限。

  • 数据库管理员拥有对数据库中所有对象的所有权限。
  • 用户对自己建立的基本表和视图拥有全部的操作权限。

关系数据库系统中数据库模式的存取权限：

对象

操作权限

SCHEMA（模式）

CREATE

VIEW（视图）

CREATE

TABLE（基本表）

CREATE、ALTER

INDEX（索引）

CREATE

关系数据库系统中数据的存取权限：

对象

操作权限

TABLE（基本表）、VIEW（视图）

CREATE、INSERT、SELECT、UPDATE、DELETE、REFERENCES、ALL PRIVILEGES

属性列

SELECT、INSERT、UPDATE、REFERENCES、ALL PRIVILEGES

SQL中使用GTANT和REVOKE语句向用户授予或收回对数据的操作权限。

GRANT

GRANT语句向用户授予权限，一般格式为：

代码语言：sql复制
GRANT <权限> ON <对象类型><对象名> TO <用户> [WITH GRANT OPTION];

其语义为：将对某个操作对象（基本表、视图等）的指定操作权限授予某个用户

  • 执行GRANT语句的可以是数据库管理员，也可以是数据库对象创建者（即owner）或者已经拥有该权限的用户
  • 接受权限的用户可以是一个或多个具体用户，也可以是PUBLIC，即全体用户
  • [WITH GRANT OPTION]子句是可选的，如果指定了这个子句，则表示获得权限的用户还可以把这种权限再授予其他的用户（仅限于获得的这些权限），此时这些权限可以称为依赖权限。反之则该用户不能传播该权限
  • SQL标准允许具有WITH GRANT OPTION的用户把相应权限或其子集传递授予其他用户，但不允许循环授权，即被授权者不能把权限再授回给授权者或其祖先

例1：把查询Student表的权限授给用户U1，并允许U1将此权限授予给其他用户 GRANT SELECT ON TABLE Student TO U1 WITH GRANT OPTION; 例2：把对Student表和Course表的全部操作权限授予用户U2和U3 GRANT ALL PRIVILEGES ON TABLE Student,Course TO U2 U3; 例3：把对表SC的所有权限授予给全体用户 GRANT ALL PRIVILEGES ON TABLE SC TO PUBLIC; 例4：把查询Student表格修改学生学号的权限授给用户U4 GRANT ALTER(Sno) ON TABLE Student TO U4;

查看当前用户拥有的权限：

代码语言：sql复制
SHOW GRANTS;

查看指定用户拥有的权限，前提是有超级用户权限：

代码语言：sql复制
SHOW GRANTS FOR <用户名>;
REVOKE

REVOKE语句收回已经授予给用户的权限，一般格式为：

代码语言：sql复制
REVOKE [GRANT OPTION FOR] <权限> ON <对象类型> <对象名> FROM <用户> [CASCADE | RESTRICT]

[GRANT OPTION FOR]是可选项。如果声明了GRANT OPTION FOR，那么只是撤销对该权限的授权的权力，而不是撤销该权限本身

关于REVOKE中的CASCADE与RESTRICT的区别： 在赋予了用户A某一权限以及对该权限授权的权力之后， 情况一：用户A并未将权限授予给其他用户，用REVOKE语句回收权限时不管加CASCADE还是RESTRICT情况都是一致的。表示的都是回收用户A的权限以及对该权限授权的权力； 情况二：用户A将权限赋予给了用户B和C，此时数据库中存在依赖权限（定义见上条），那么：CASCADE意思为级联操作，加CASCADE参数表示回收用户A的权限以及对该权限授权的权力以及用户B和用户C的依赖权限 RESTRICT意思为限制操作，因为此时存在依赖权限（可以认为是一种限制），加RESTRICT参数之后将会拒绝执行语句，除非限制解除（依赖权限不存在） 总结一下： CASCADE选项表示DBMS撤销指定的权限以及依赖于被撤销权限的所有权限。RESTRICT选项表示DBMS服务器在存在任何依赖权限不要撤销指定的权限。 undefined 以上纯为个人理解，可能会有错误。因为书上与网上对REVOKE中的CASCADE和RESTRICT这部分解释的很笼统，基本都是模棱两可地在解释CASCADE（其中网上大部分结果都是复制粘贴外网上的同一篇文章），而RESTRICT基本都没有解释。对着翻译查了部分英文资料，大概有了一些理解，可能不太准确。如有错误还望不吝赐教，请在文末评论区留言指出。

用户可以自主地决定将数据的存取权限授予何人，以及是否也将“授权”的权限授予别人，因此称这样的存取控制是自主存取控制。

例1：把用户U4修改学生学号的权限收回 REVOKE ALTER ON TABLE Student FROM U4; 例2：收回所有用户对表SC的查询权限 REVOKE SELECT ON TABLE SC FROM PUBLIC; 例3：把用户U5对SC表的INSERT权限收回，其中U5的INSERT权限还赋予给了U6、U7 REVOKE INSERT ON TABLE SC FROM U5 CASCADE;这句指定加上了CASCADE参数，在收回U5的INSERT权限的同时还收回了U5赋予给U6、U7的INSERT权限。

0x02.数据库角色

数据库角色是被命名的一组与数据库操作相关的权限，角色是权限的集合。

使用角色来管理数据库权限可以简化授权的过程：在SQL中首先用CREATE ROLE语句创建角色，然后用GRANT语句给角色授权，用REVOKE语句收回授予角色的权限。

角色的创建 ——CREATE ROLE
代码语言：sql复制
CREATE ROLE <角色名>;

刚创建的角色为空，没有任何权限的。

给角色授权 ——GRANT

语句与给用户授权类似，将用户名部分换成角色名即可：

代码语言：sql复制
GRANT <权限> ON <对象类型> <对象名> TO <角色>;
将角色授予给其他角色或用户 ——GRANT
代码语言：sql复制
GRANT <角色> TO <角色>/<用户> [WITH ADMIN OPTION];

该语句把角色授予某个用户或者某个角色，这样一个角色的所有权限就是授予它的全部角色所包含的权限的总和。

[WITH ADMIN OPTION]子句是可选的，如果声明了WITH ADMIN OPTION子句，则获得了权限的角色或者用户还可以把权限再授予给其他的角色

收回角色权限 ——REVOKE

同样的，与收回用户的权限语句类似：

代码语言：sql复制
REVOKE <权限> ON <对象类型> <对象名> FROM <角色>;

REVOKE操作的执行者是角色的创建者或者拥有这些角色的ADMIN OPTIION。

0x03.用户管理

创建用户：
代码语言：sql复制
CREATE USER <用户名> IDENTIFIED BY <密码>;

注意：密码部分记得加引号，否则会报错

删除用户：
代码语言：sql复制
-- 普通删除：
DROP USER <用户名>;

-- 级联删除，把该用户的相关关系也删除掉：
DROP USER <用户名> CASCADE;

-- DELETE语句也可以删除用户
DELETE USER FROM MYSQL.USER WHERE USER='用户名';
FLUSH PRIVILEGES;
重命名用户名：
代码语言：sql复制
RENAME USER <旧用户名> TO <新用户名>;
修改用户密码：
代码语言：sql复制
-- 密码部分记得加引号
SET PASSWORD=PASSWORD('新密码');
-- 或者
UPDATE USER SET PASSWORD=PASSWORD('新密码') WHERE USER='用户名';

-- mysql8.0以上版本也可以用此命令：
ALTER USER <用户名> IDENTIFIED BY <密码>;

或者可以直接在控制台上用mysqladmin命令修改密码：

代码语言：shell复制
mysqladmin -u <用户名> -p password

注意：MySQL中用户数据和权限修改后，若希望在不重启MySQL服务的情况下直接生效，那么就需要执行这个命令：

代码语言：sql复制
FLUSH PRIVILEGES;

-- flush privileges 命令本质上是将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里。

否则就需要重启Mysql服务：

代码语言：shell复制
# Linux系统下：
systemctl restart mysql
# 或者
service mysql restart

# Win10系统，管理员权限运行cmd窗口
net stop mysql
net start mysql

主要参考资料：

《数据库系统概论（第5版）》 王珊 萨师煊 编著

mysql用户操作和权限管理 - gg火花 - 博客园 (cnblogs.com)

sql 数据库 mysql

0 人点赞