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:

Question

(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 1

(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;