数据库系统 第7节 数据控制语言

数据控制语言（Data Control Language, DCL）是一组SQL命令，主要用于管理数据库中的数据访问权限和隐私。DCL允许数据库管理员或具有足够权限的用户向其他用户授予或收回对数据库对象（如表、视图、存储过程等）的访问权限。

下面分别介绍 GRANT 和 REVOKE 命令的基本用法：

GRANT

GRANT 语句用于赋予用户对特定数据库对象的访问权限。基本语法如下：

GRANT privilege [, privilege] ... ON object TO username [, username] ... [WITH GRANT OPTION];

• privilege: 指定要授予的权限类型，例如 SELECT, INSERT, UPDATE, DELETE, ALTER, REFERENCES 等。
• object: 指定权限所应用于的对象，可以是表名、视图名或者整个数据库。
• username: 接受权限的用户名称。
• WITH GRANT OPTION: 如果指定此选项，则被授予权限的用户也可以将这些权限再授予其他用户。

示例

假设有一个名为 employees 的表，并且想要授予用户 johndoe 对该表的 SELECT 和 INSERT 权限：

GRANT SELECT, INSERT ON employees TO johndoe;

如果还想让 johndoe 能够进一步将这些权限授予其他用户，则可以使用 WITH GRANT OPTION：

GRANT SELECT, INSERT ON employees TO johndoe WITH GRANT OPTION;

REVOKE

REVOKE 语句用于从用户处收回之前授予的权限。基本语法如下：

REVOKE privilege [, privilege] ... ON object FROM username [, username] ... [RESTRICT | CASCADE];

• privilege: 要撤销的权限类型。
• object: 权限所应用于的对象。
• username: 被撤销权限的用户名称。
• RESTRICT: 默认选项，如果用户还依赖于这些权限，则撤销操作会被拒绝。
• CASCADE: 如果指定此选项，则会撤销所有直接或间接依赖于这些权限的权限。

示例

如果要撤销 johndoe 在 employees 表上的 SELECT 权限：

REVOKE SELECT ON employees FROM johndoe;

如果还要撤销 johndoe 可能已经授予其他用户的 SELECT 权限，可以使用 CASCADE 选项：

REVOKE SELECT ON employees FROM johndoe CASCADE;

通过这些命令，数据库管理员可以有效地控制谁能够访问哪些数据以及他们可以执行什么样的操作。

GRANT 语句示例

1. 授予用户对特定表的查询权限：

   GRANT SELECT ON employees TO 'new_user';
   

   这里，new_user被授予对employees表的查询权限。

2. 授予用户对数据库的所有表进行修改的权限：

   GRANT UPDATE ON database_name.* TO 'power_user';
   

   power_user被授予对database_name数据库中所有表的更新权限。

3. 授予用户对特定存储过程的执行权限：

   GRANT EXECUTE ON PROCEDURE add_employee TO 'manager';
   

   manager被授予执行add_employee存储过程的权限。

4. 授予用户对整个数据库的完全控制权限：

   GRANT ALL PRIVILEGES ON database_name TO 'admin';
   

   admin被授予对database_name数据库的所有权限。

5. 授予用户对特定视图的创建权限：

   GRANT CREATE VIEW ON database_name TO 'developer';
   

   developer被授予在database_name数据库中创建视图的权限。

REVOKE 语句示例

1. 撤销用户对特定表的查询权限：

   REVOKE SELECT ON employees FROM 'new_user';
   

   new_user之前被授予的对employees表的查询权限被撤销。

2. 撤销用户对数据库所有表的修改权限：

   REVOKE UPDATE ON database_name.* FROM 'power_user';
   

   power_user之前被授予的对database_name数据库所有表的更新权限被撤销。

3. 撤销用户对存储过程的执行权限：

   REVOKE EXECUTE ON PROCEDURE add_employee FROM 'manager';
   

   manager之前被授予的执行add_employee存储过程的权限被撤销。

4. 撤销用户对整个数据库的完全控制权限：

   REVOKE ALL PRIVILEGES ON database_name FROM 'admin';
   

   admin之前被授予的对database_name数据库的所有权限被撤销。

5. 撤销用户对特定视图的创建权限：

   REVOKE CREATE VIEW ON database_name FROM 'developer';
   

   developer之前被授予的在database_name数据库中创建视图的权限被撤销。

请注意，具体的语法可能会根据数据库管理系统的不同而有所变化。上述示例假设了一种通用的SQL语法。在实际应用中，应根据所使用的数据库系统（如MySQL, PostgreSQL, Oracle等）的文档来编写相应的DCL语句。

接下来我将提供一些更具体的例子来说明如何使用 GRANT 和 REVOKE 命令。

GRANT 示例

假设我们有一个数据库 companydb，其中包含一个名为 employees 的表，还有一个名为 departments 的表。我们将为不同的用户授予不同的权限。

1. 授予用户 manager 对 employees 表的所有权限：

   GRANT ALL PRIVILEGES ON companydb.employees TO manager;
   

2. 授予用户 clerk 对 employees 表的 SELECT 和 UPDATE 权限：

   GRANT SELECT, UPDATE ON companydb.employees TO clerk;
   

3. 授予用户 clerk 对 departments 表的 SELECT 权限，并允许他/她进一步授权给其他人：

   GRANT SELECT ON companydb.departments TO clerk WITH GRANT OPTION;
   

REVOKE 示例

现在，假设我们需要撤销之前授予的一些权限。

1. 撤销用户 clerk 对 departments 表的 SELECT 权限：

   REVOKE SELECT ON companydb.departments FROM clerk;
   

2. 撤销用户 clerk 对 employees 表的 UPDATE 权限：

   REVOKE UPDATE ON companydb.employees FROM clerk;
   

3. 撤销用户 clerk 对 departments 表的所有权限，并且级联撤销任何可能已由 clerk 授予其他用户的权限：

   REVOKE ALL PRIVILEGES ON companydb.departments FROM clerk CASCADE;
   

4. 撤销用户 manager 对 employees 表的所有权限，但保留依赖项：

   REVOKE ALL PRIVILEGES ON companydb.employees FROM manager RESTRICT;
   

这些例子展示了如何使用 GRANT 和 REVOKE 来管理和调整不同用户在数据库中的权限。需要注意的是，这些命令通常只能由具有数据库管理员权限的用户执行。此外，在实际应用中，您需要根据具体情况选择合适的权限类型和选项，以确保数据库的安全性和正确性。

这次我们来看一个稍微复杂一点的例子，涉及到多个用户和不同的权限组合。

假设场景

我们有一个名为 university 的数据库，其中包含两个表：students 和 courses。我们的目标是设置适当的权限，以便不同的用户可以执行不同的操作。

1. 创建数据库和表：
   首先，我们需要创建数据库和表。这里只是一个示例，实际的创建语句会更详细。不过，为了简化，我们可以假设这些已经存在了。

2. 用户：

   • professor: 应该能够查看学生信息和修改课程信息。
   • student: 应该能够查看自己的课程信息。
   • admin: 应该能够完全控制所有的表。

GRANT 示例

1. 授予 professor 用户对 students 表的 SELECT 权限和对 courses 表的 SELECT 和 UPDATE 权限：

   GRANT SELECT ON university.students TO professor;
   GRANT SELECT, UPDATE ON university.courses TO professor;
   

2. 授予 student 用户对 courses 表的 SELECT 权限：

   GRANT SELECT ON university.courses TO student;
   

3. 授予 admin 用户对所有表的全部权限：

   GRANT ALL PRIVILEGES ON university.* TO admin;
   

REVOKE 示例

假设我们需要撤销某些权限。

1. 撤销 professor 用户对 students 表的 SELECT 权限：

   REVOKE SELECT ON university.students FROM professor;
   

2. 撤销 student 用户对 courses 表的 SELECT 权限，并级联撤销任何可能已经授予其他用户的权限：

   REVOKE SELECT ON university.courses FROM student CASCADE;
   

3. 撤销 admin 用户的部分权限：
   如果我们只想撤销 admin 用户对 courses 表的 UPDATE 权限，但保留其他权限，可以这样操作：

   REVOKE UPDATE ON university.courses FROM admin;
   

这些例子展示了如何针对不同的用户分配和撤销权限，以满足业务需求并确保数据安全。请注意，在实际环境中，您可能需要更细致地管理权限，以适应复杂的组织结构和数据访问策略。