SQL Authorization（授权）-优快云博客

SQL identifies a more detailed set of privileges on objects (relations) than the typical file system.（SQL对于表的权限设定相较于传统的文件系统更为细节）
Nine privileges in all, some of which can be restricted to one column of on relation.（又九种权限，可以限制关系中的某一列）
Some important privileges on a relation:

SELECT = right to query the relation.

INSERT = right to insert tuples. May apply to only one attribute.

DELETE = right to delete tuples.

UPDATE = right to update tuples. May apply to only one attribute.

Example: Privileges

For the statement below:

INSERT INTO Beers(name)
SELECT beer FROM Sells
WHERE NOT EXISTS
(SELECT * FROM Beers
WHERE name = beer);

We require privileges SELECT on Sells and Beers, and INSERT on Beers or Beers.name.

Database Objects

The objects on which privileges exist include stored tables and views.（储存表和视图也可以进行权限控制）
Other privileges are the right to create objects of a type, e.g., triggers.（其他权限包括创建特定类型对象）
Views form an important tool for access control.（视图是用来控制访问的重要工具）

Example: Views as Access Control

We might not want to give the SELECT privilege on Emps(name, addr, salary).（我们不希望授予这张表查询的权限）
But it is safer to give SELECT on: CREATE VIEW SafeEmps AS SELECT name, addr FROM Emps;（通过创建一个视图，然后赋予这个视图查询的权限）
Queries on SafeEmps do not require SELECT on Emps, just on SafeEmps

所以视图可以作为访问控制的手段

Authorization ID’s

A user is referred to by authorization ID, typically their login name.（通过权限ID来识别用户）
There is an authorization ID PUBLIC.（PUBLIC控制符）
Granting a privilege to PUBLIC makes it available to any authorization ID

Granting Privileges

You have all possible privileges on the objects, such as relations, that you create.（谁创造谁拥有）
You may grant privileges to other users (authorization ID’s), including PUBLIC.
You may also grant privileges WITH GRANT OPTION, which lets the grantee also grant this privilege.

创建者拥有所有权限、能够赋予其他用户权限、能够是其他用户传递权限

The GRANT Statement

To grant privileges, say:

GRANT <list of privileges>
ON <relation or other object>
TO <list of authorization ID’s>;

If you want the recipient(s) to be able to pass the privilege(s) to others add:

WITH GRANT OPTION

Example: GRANT

Suppose you are the owner of Sells.

You may say:

GRANT SELECT, UPDATE(price)
ON Sells
TO sally;

Now Sally has the right to issue any query on Sells and can update the price component only.

Example: Grant Option

Suppose we also grant:

GRANT UPDATE ON Sells TO sally
WITH GRANT OPTION;

Now, Sally not only can update any attribute of Sells, but can grant to others the privilege UPDATE ON Sells.
Also, she can grant more specific privileges like UPDATE(price)ON Sells.

Revoking Privileges

REVOKE <list of privileges>
ON <relation or other object>
FROM <list of authorization ID’s>;

Your grant of these privileges can no longer be used by these users to justify their use of the privilege.
But they may still have the privilege because they obtained it independently from elsewhere.

REVOKE Options

We must append to the REVOKE statement either:

CASCADE. Now, any grants made by a revokee are also not in force, no matter how far the privilege was passed.（级联撤销：当执行撤销权限时，检查是否还有权限传递存在，如果存在就全部删除）
RESTRICT. If the privilege has been passed to others, the REVOKE fails as a warning that something else must be done to “chase the privilege down.（限制撤销：当执行撤销操作时，检查是否存在权限传递的情况，如果那么操作不执行）

Grant Diagrams（权限图）

Nodes = user/privilege/grant option?/is owner?（结点包括四个属性）

UPDATE ON R, UPDATE(a) on R, and UPDATE(b) ON R live in different nodes.（列级权限隔离，这里的三个将会被看做三个结点）

SELECT ON R and SELECT ON R WITH GRANT OPTION live in different nodes.（授予选项隔离，同理这里的两个也会别看做两个结点）

Edge X ->Y means that node X was used to grant Y

权限结点有四个属性（权限持有者/用户、权限表、传递权限、是否为所有者）

Notation for Nodes

Use AP for the node representing 、authorization ID A having privilege P.
P * = privilege P with grant option.
P ** = the source of the privilege P.

I.e., A is the owner of the object on which P is a privilege.（A代表权限所有者，P代表权限）

Note ** implies grant option.（节点中的双星号代表授予权限）

Manipulating Edges

When A grants P to B, We draw an edge from AP * or AP ** to BP or to BP * if the grant is with grant option.（当将两者之间存在权限传递时，使用边将两者连接起来）
If A grants a subprivilege Q of P [say UPDATE(a) on R when P is UPDATE ON R] then the edge goes to BQ or BQ * , instead.（注意列级权限隔离，也就是说当A将P权限的一个子权限赋予了B，那么边要指向BQ/BQ*而不是BP/BP*）
Fundamental rule: User C has privilege Q as long as there is a path from XP** to CQ, CQ * , or CQ ** , and P is a superprivilege of Q.（只要一条边从一个权限指向另一个权限，就能说明P是Q的父权限）

Remember that P could be Q, and X could be C

If A revokes P from B with the CASCADE option, delete the edge from AP to BP.
But if A uses RESTRICT instead, and there is an edge from BP to anywhere, then reject the revocation and make no change to the graph.