SQL Authorization（授权）

Authorization

• A file system identifies certain privileges on the objects (files) it manages.（文件系统会识别它所管理的文件或者对象的权限）

Typically read, write, execute.（读、写、执行）

• A file system identifies certain participants to whom privileges may be granted.（文件系统能够识别被赋予了权限的主体）

Typically the owner, a group, all users.

Privileges

• 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.