Chapter 30: Database Security
1
CHAPTER 30: DATABASE SECURITY
Answers to Selected Exercises
30.30 – 30.31: No solutions provided.
30.32 – Consider the relational database schema of Figure 3.5. Suppose that all the relations
were created by (and hence are owned by) user X, who wants to grant the following
privileges to user accounts A, B, C, D, and E:
(a) Account A can retrieve or modify any relation except DEPENDENT and can grant any
of these privileges to other users.
(b) Account B can retrieve all the attributes of EMPLOYEE and DEPARTMENT except
for SALARY, MGRSSN, and MGRSTARTDATE.
(c) Account C can retrieve or modify WORKS_ON but can only retrieve the FNAME,
MINIT, LNAME, SSN attributes of EMPLOYEE and the PNAME, PNUMBER attributes of
PROJECT.
(d) Account D can retrieve any attribute of EMPLOYEE or DEPENDENT and can modify
DEPENDENT.
(e) Account E can retrieve any attribute of EMPLOYEE but only for EMPLOYEE tuples
that have DNO = 3.
(f) Write SQL statements to grant these privileges. Use views were appropriate.
Answer:
(a)
GRANT SELECT, UPDATE
(b)
CREATE VIEW EMPS AS
SELECT FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX,
SUPERSSN, DNO
(c)
GRANT SELECT, UPDATE ON WORKS_ON TO USER_C;
CREATE VIEW EMP1 AS
SELECT FNAME, MINIT, LNAME, SSN
Chapter 30: Database Security
2
(d)
(e)
CREATE VIEW DNO3_EMPLOYEES AS
30.33 – Suppose that privilege (a) of exercise 24.32 is to be given with GRANT OPTION
but only so that account A can grant it to at most five accounts, and each of these
accounts can propagate the privilege to other accounts but without the GRANT
OPTION privilege. What would the horizontal and vertical propagation limits be
in this case?
Answer:
The horizontal propagation limit granted to USER_A is 5. The vertical propagation limit
30.26 – Consider the relation shown in Figure 24.2 (d). How would it appear to a user with
classification U? Suppose a classification U user tries to update the salary of “Smith” to
$50,000; what would be the result of this action?
Answer:
EMPLOYEE would appear to users within classification U as follows:
NAME SALARY JobPerformance TC