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
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?
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
=> Authorization is one very important use of views.
GRANT <privileges> ON R TO <users> [ WITH GRANT OPTION ]
<privileges>: operations as earlier, separated by commas
<users>: list of user/group names, or
GRANT DELETE, UPDATE(A) ON R TO PUBLIC;A user granted privileges "
WITH GRANT OPTION" may grant equal or lesser privileges to other users.
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.
REVOKEcommand not allowed if it would cause any privileges to be revoked by cascade rules.
CREATE TABLE-> U becomes owner
Question: When is this setup useful?