Privileges in SQL

数据库权限分配</span

1.

GRANT ALL
ON Employee
TO Manager
WITH GRANT OPTION

• 用户Manager可以对Employee表进行任何操作，并且可以允许其他用户做同样的事情（通过使用GRANT语句）。

2.

GRANT SELECT, UPDATE(Salary)
ON Employee
TO Finance

• 用户Finance可以查看整个Employee表，并且可以改变其Salary列的值，但不能改变其他任何值或者将他们的权限传递给其他用户。

移除权限

• 之前授予的权限可以使用以下语句被撤销：

REVOKE <privileges>
ON <object>
FROM <users>

• 如果一个用户从其他用户那里获得了相同的权限，那么他们将仍然保留该权限。（一个用户可能从多个不同的用户那里获得相同的权限。如果其中一个授权用户撤销了这个权限，那么这个用户如果还从其他人那里获得了相同的权限，他仍然会保持这个权限。简而言之，只有当所有授予该权限的用户都撤销了这个权限后，这个用户才会失去它。）
• 所有依赖于被撤销权限的其他权限将会自动被撤销。

First example:

1. Admin grants ALL to Manager WITH GRANT OPTION.
2. Admin grants SELECT to Finance WITH GRANT OPTION.
3. Manager grants ALL to Personnel.
4. Finance grants SELECT to Personnel.

Second example:

1. Manager revokes ALL from Personnel.

   • Personnel still has SELECT privilege from Finance.

2. Admin revokes SELECT from Finance.

   • Personnel loses SELECT privilege.

视图

• 权限在表级别上起作用

  • 访问可以通过列来限制
  • 访问不能通过行来限制！
• 视图与权限一起，允许定制的访问控制
• 一个视图是作为SELECT语句结果派生出的表
• SELECT语句随后可以用在视图上，就像在表上一样
• UPDATE语句有时也可以用在视图上

可以用过账户权限以及视图组合来控制用户可以访问的数据

创建视图

示例

• Employee(ID, Name, Salary, Department)
• 我们希望每个用户都能够只查看他们自己部门员工的姓名和电话号码
• 注意：在Oracle中，您可以使用关键字USER来引用当前用户

CREATE VIEW OwnDept AS
SELECT Name, Phone
FROM Employee
WHERE Department = (SELECT Department
                    FROM Employee
                    WHERE name = USER);

• GRANT SELECT ON OwnDept TO PUBLIC;

视图和权限

• 视图和权限结合起来控制访问

  • 创建一个包含所需信息的视图
  • 授予该视图权限，而不是底层表

• 视图是虚拟表

  • 其内容依赖于底层表
  • 我们可以像查询表一样从视图中查询

更新视图

更新视图的内容，本质上是更新了它所基于的那个或那些数据表。然而，并不是所有的视图都是可更新的。要使一个视图可更新，它的定义查询必须满足一些特定的条件：

• 对底层表的更新会改变视图

• 为了使视图本身可更新，定义查询必须满足以下条件：

  1. SELECT中的每个元素是一个列名：这意味着视图必须直接引用数据表的列，而不是使用函数或表达式。
  2. 不应使用DISTINCT：使用DISTINCT关键字意味着返回的结果集中的行是唯一的，这会导致无法确定更新操作应该影响底层表中的哪些行。
  3. 视图应该定义在单一表上：这表示视图不能通过联结（JOIN）或合并（UNION）多个表来创建，因为这会使得更新操作变得复杂，难以反映到底层的多个表上。
  4. WHERE不应包含嵌套的SELECT语句：含有嵌套SELECT的WHERE子句会使确定更新操作影响的行变得复杂。
  5. 不应使用GROUP BY或HAVING：这些子句用于数据聚合操作，结果集中的行代表多个底层表中的行，因此无法单一映射到底层表的行上，使更新操作无法执行。
  6. 如果视图更新了，原始表的数据也会相应的更新

Here is the content extracted from the image:

Access control with views and privileges

• Employee(ID, Name, Salary, Department)
• we want to let the user John see Department and Name, and be able to update Department (only)

CREATE VIEW JohnsView
AS SELECT Name, Department FROM Employee;

• setting the privileges

GRANT SELECT, UPDATE(Department)
ON JohnsView TO John;

REVOKE ALL ON Employee FROM John;