Database Reference
In-Depth Information
After all granting of privileges
DB
System
Employee,
ALL, GRANT
All, ALL,
GRANT
Employee,
ALL, GRANT
Miller
Rogers
Employee,
SELECT, GRANT
Chen
Employee,
SELECT, GRANT
Employee,
ALL, GRANT
Rodriguez
Goldstein
Williams
After revoking of privileges from Miller by Rogers with cascade option
DB
System
All, ALL,
GRANT
Miller
Rogers
Employee,
SELECT, GRANT
Chen
Employee,
SELECT, GRANT
Rodriguez
Goldstein
Williams
Figure 16-5
Authorization graph.
table will be dropped.
The EMPLOYEE table itself,
however,
will not be
dropped.
Now suppose Miller has the SELECT privilege on the DeptNo column of the
DEPARTMENT table, not the REFERENCES privilege. In this case, Miller will
not be allowed to create the EMPLOYEE table with a foreign key column refer-
ring to DeptNo in the DEPARTMENT table.
Why not grant Miller the SELECT privilege and allow him to create the
EMPLOYEE table with a foreign key column referring to the DeptNo column in
the DEPARTMENT table? If this is done, assume that Miller creates the table with
a foreign key constraint as follows:
EMPLOYEE (EmployeeNo,
FirstName,
LastName,
Address,
Phone,
Employee
Position, Salary, EmployeeCode, DeptNo)
Foreign Key: DeptNo REFERENCES DEPARTMENT ON DELETE NO ACTION
With the NO ACTION option in the foreign key specification, Nash is prevented
from deleting rows from the DEPARTMENT table even though he is the owner.
For this reason, whenever such a restrictive privilege needs to be authorized, the
more stringent privilege REFERENCES is applied. The SELECT privilege is there-
fore intended as permission just to read the values.
Use of Views
Earlier we had discussions on user views. A user view is like a personalized model
of the database tailored for individual groups of users. If a user group, say, in the
marketing department, needs to access only some columns of the DEPARTMENT
Search WWH ::
Custom Search