Consider the relational database schema in Figure 3.5. Suppose that all the rela
ID: 3531216 • Letter: C
Question
Consider the relational database schema in 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 accounrts A, B, C, D, E:
b) Account B can retrieve all the attributes of EMPLOYEE and DEPARTMENT except for Salary, Mgr_ssn, and Mgr_start_date.
c) Account C can retrieve or modify WORKS_ON but can only retrieve the Fname, Minit, Lname, and Ssn attributes of EMPLOYEE and the Pname and 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 where appropriate.
Explanation / Answer
a)
GRANT SELECT, UPDATE
ON EMPLOYEE, DEPARTMENT, DEPT_LOCATIONS, PROJECT,
WORKS_ON
TO ACCOUNTA
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 ACCOUNTB;
CREATE VIEW DEPTS AS
SELECT DNAME, DNUMBER
FROM DEPARTMENT;
GRANT SELECT ON DEPTS
TO ACCOUNTB;
--------------------------------------------------------------------------------------------------------------
c))GRANT SELECT, UPDATE
ON WORKS_ON
TO ACCOUNTC;
CREATE VIEW EMP1 AS
SELECT FNAME, MINIT, LNAME, SSN
FROM EMPLOYEE;
GRANT SELECT ON EMP1
TO ACCOUNTC;
CREATE VIEW PROJ1 AS
SELECT PNAME, PNUMBER
FROM PROJECT;
GRANT SELECT ON PROJ1
TO ACCOUNTC;
----------------------------------------------------------------------------------------------------------------------
d)) GRANT SELECT ON EMPLOYEE, DEPENDENT
TO ACCOUNTD;
GRANT UPDATE ON DEPENDENT
TO ACCOUNTD;
---------------------------------------------------------------------------------------------------------------------
e)) CREATE VIEW DNO3_EMPLOYEES AS
SELECT * FROM EMPLOYEE
WHERE DNO = 3;
GRANT SELECT ON DNO3_EMPLOYEES
TO ACCOUNTE;
-----------------------------------------------------------------------------------------------------------------------
please rate it