An owner of a relational database (ADMIN) created the following relations: STUDE

Question

An owner of a relational database (ADMIN) created the following relations:

STUDENT = (ID, NAME, TOTAL_CREDIT, DEP_NUMBER)

INSTRUCTOR = (ID, NAME, SALARY, DEP_NUMBER)

COURSES = (COURS_ID, TITLE, DEP_NUMBER, CREDITS)

DEPARTMENT = (DEP_NUMBER, BUILDING, BUDGET)

The ADMIN user wants to pass privileges to other accounts X, Y and Z. Write the correct SQL statements to grant the following privileges.

1-Account X can retrieve or modify any relation except COURSES and can pass any of these privileges to other accounts.

2- Account Y can retrieve all the attributes of STUDENT and COURSES except TOTAL_CREDIT, DEP_NUMBER for both relations. [2 marks]

3- Account Z can retrieve any attribute of INSTRUCTOR but only for those whom working on the IT (DEP_NUMBER=2). [1 mark]

Explanation / Answer

1) Grant works on single object at a time, so in order to give permission on 3 tables we need to write 3 grant statements as written below. At the end of each permission WITH GRANT OPTION is given, which will authority the user to grant same permission to some other user.

GRANT SELECT, UPDATE
ON STUDENT
TO X
WITH GRANT OPTION;

GRANT SELECT, UPDATE
ON INSTRUCTOR
TO X
WITH GRANT OPTION;

GRANT SELECT, UPDATE
ON INSTRUCTOR
TO X
WITH GRANT OPTION;

2) For this grant is given to select statement only on two attributes of the table. Two grant statement is written for two objects / table.

GRANT SELECT (TOTAL_CREDIT, DEP_NUMBER)
ON STUDENT
TO Y;

GRANT SELECT (CREDITS, DEP_NUMBER)
ON COURSES
TO Y;  

3) To give this kind of permission first we need to create a view based on the given condition i.e select on Instructor working in DEP_NUMBER 2. Once the view is created I have given the select permission on this view to user Z.

CREATE VIEW INSTRUCTOR_IT AS
SELECT *
FROM INSTRUCTOR
WHERE DEP_NUMBER = 2;

GRANT SELECT
ON INSTRUCTOR
TO Z;

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.