CS145 Lecture Notes -- Authorization
- Make sure users only see the data they're supposed to
- Guard the database against updates by malicious 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, 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
- 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
- DDL authorization (not even in textbook)