CS145 Lecture Notes (12) -- Authorization

Make sure users only see the data they're supposed to
Guard the database against updates by malicious/careless/accident-prone users

Solution:

Users have privileges
Users can only operate on data for which they're authorized

For a relation R and user U, U may be authorized for:

SELECT ON R
SELECT(A1,A2,...,An) ON R
INSERT ON R
INSERT(A1,A2,...,An) ON R
UPDATE ON R
UPDATE(A1,A2,...,An) ON R
DELETE ON R

Example schema:

  Student(ID, name, address, GPA, sizeHS)
  Campus(location, enrollment, rank)
  Apply(ID, location, date, major, decision)

Example:

  UPDATE Apply
  SET decision = 'Y'
  WHERE ID IN (SELECT ID FROM Student WHERE GPA > 3.9)

Question: What privileges are needed for this statement?

Example:

  DELETE FROM Student
  WHERE ID NOT IN (SELECT ID FROM Apply)

Question: What privileges are needed for this statement?

Operation-level privileges on single relations may not provide sufficient control.

Example: Allow user U to select Student info for Berkeley applicants only

Question: How can we do this?

Example: Allow user U to delete Berkeley application records only

Question: How?

=> Authorization is one very important use of views.

Non-updatable views could be limitation
But if goal is update authorization, can usually create an appropriate updatable view
Challenge: Find realistic update authorization requirement that cannot be enforced using views

Obtaining privileges

Creator of relation is owner
Owner has all privileges and may grant privileges

SQL:

  GRANT <privileges> ON R TO <users> [ WITH GRANT OPTION ]

<privileges>: operations as earlier, separated by commas
<users>: list of user/group names, or PUBLIC

Example:

  GRANT DELETE, UPDATE(A) ON R TO PUBLIC;

A user granted privileges "WITH GRANT OPTION" may grant equal or lesser privileges to other users.

Revoking privileges

SQL:

  REVOKE <privileges> ON R FROM <users> [ CASCADE | RESTRICT ]

CASCADE: Also revoke privileges granted from the privileges now being revoked (transitively), except for privileges granted from some other source as well.
RESTRICT: REVOKE command not allowed if it would cause any privileges to be revoked by cascade rules.

Miscellaneous

We've been assuming a session with a known user U.

CREATE TABLE -> U becomes owner
SQL command -> check U's privileges

Can also create modules associated with a user U. U must have privileges to execute statements in the module. The module can be invoked by a user V who does not have the proper privileges.

Question: When is this setup useful?

Not Covered (required reading in textbook)

REFERENCES, USAGE, TRIGGER, EXECUTE, and UNDER privileges
Grant diagrams

Optional (read documentation if you're interested

DDL authorization (not even in textbook)