CHAPTER 23: DATABASE SECURITY AND AUTHORIZATION

Answers to Selected Exercises

23.22 Consider the relational database schema of Figure 5.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.

Write SQL statements to grant these privileges. Use views were appropriate.

Answer:

(a)

GRANT SELECT, UPDATE

ON EMPLOYEE, DEPARTMENT, DEPT_LOCATIONS, PROJECT, WORKS_ON

TO USER_A

WITH GRANT OPTION;

(b)

CREATE VIEW EMPS AS

SELECT FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX,

SUPERSSN, DNO

FROM EMPLOYEE;

GRANT SELECT ON EMPS

TO USER_B;

CREATE VIEW DEPTS AS

SELECT DNAME, DNUMBER

FROM DEPARTMENT;

GRANT SELECT ON DEPTS

TO USER_B;

(c)

GRANT SELECT, UPDATE ON WORKS_ON TO USER_C;

CREATE VIEW EMP1 AS

SELECT FNAME, MINIT, LNAME, SSN

FROM EMPLOYEE;

GRANT SELECT ON EMP1

TO USER_C;

CREATE VIEW PROJ1 AS

SELECT PNAME, PNUMBER

FROM PROJECT;

GRANT SELECT ON PROJ1

TO USER_C:

(d)

GRANT SELECT ON EMPLOYEE, DEPENDENT TO USER_D;

GRANT UPDATE ON DEPENDENT TO USER_D;

(e)

CREATE VIEW DNO3_EMPLOYEES AS

SELECT * FROM EMPLOYEE

WHERE DNO=3;

GRANT SELECT ON DNO3_EMPLOYEES TO USER_E;

23.23 Suppose that privilege (a) of exercise 23.1 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

granted to USER_A is level 1, so that USER_A can then grant it with level 0 vertical

limit (i.e. without the GRANT OPTION) to at most five users, who then cannot further

grant the privilege.

23.24 Consider the relation shown in Figure 23.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

Smith U null U null U U

If a classification U user tried to update the salary of Smith to $50,000, a third

polyinstantiation of Smith's tuple would result as follows:

NAME SALARY JobPerformance TC

Smith U 40000 C Fair S S

Smith U 40000 C Excellent C C

Smith U 50000 U null U U

Brown C 80000 S Good C S