DATABASE SECURITY - Database Design and Development: An Essential Guide for IT Professionals - page 496

SUBJECT or USER DATABASE OBJECT PRIVILEGE CONSTRAINT

Rogers Department record Modify None

Miller Department record Create DeptNo NOT EQUAL 101

Chen Employee record Select None

Goldstein Employee record Create None

Jenkins Department record Modify Address ONLY

Gonzales Employee record Drop EmployeePosition = 'Staff'

Figure 16-4 Authorization matrix.

Here is an example of a cycle of privileges passed along from Rogers, who is the
owner of table EMPLOYEE:

By Rogers GRANT ALL PRIVILEGES ON EMPLOYEE TO Miller

WITH GRANT OPTION

By Miller GRANT ALL PRIVILEGES ON EMPLOYEE TO Chen

WITH GRANT OPTION

By Chen GRANT ALL PRIVILEGES ON EMPLOYEE TO Williams

WITH GRANT OPTION

By Rogers GRANT SELECT ON EMPLOYEE TO Goldstein WITH

GRANT OPTION

By Goldstein GRANT SELECT ON EMPLOYEE TO Rodriguez WITH

GRANT OPTION

By Rogers REVOKE ALL PRIVILEGES ON EMPLOYEE FROM Miller

CASCADE

Figure 16-5 illustrates this cycle of privileges with an authorization graph. Note
how the privileges are passed along and how the revoking of privileges with cascade
option works.

REFERENCES Option The REFERENCES privilege is not the same as the
SELECT privilege. Let us take an example. Suppose Nash is the owner of the
DEPARTMENT table as indicated below:

DEPARTMENT ( DeptNo , DeptName, DeptLocation)

Nash can authorize Miller to create another table EMPLOYEE with a foreign
key in that table to refer to the DeptNo column in the DEPARTMENT table. Nash
can do this by granting Miller the REFERENCES privilege with respect to the
DeptNo column. Note the EMPLOYEE table shown below:

EMPLOYEE ( EmployeeNo , FirstName, LastName, Address, Phone, Employee

Position, Salary, EmployeeCode, DeptNo)
Foreign Key: DeptNo REFERENCES DEPARTMENT

If Miller loses the REFERENCES privilege with respect to the DeptNo
column in the DEPARTMENT table, the foreign key constraint in the EMPLOYEE