CS145 Lecture Notes -- Authorization





Solution: For a relation R and user U, U may be authorized for: Example schema:
  Student(ID, name, address, GPA, SAT)
  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?






Example: Allow user to delete Student info for Berkeley applicants only

Question: How?





=> Authorization is one very important use of views.


Obtaining privileges

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

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 ]

Miscellaneous

We've been assuming a session with a known user U. 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)