Database Security -Exercise and Case study Solution

Created May 23, 2024 by Girija Narasimhan

Unit 3 - Authorization Exercise Solution

1.Create Role Manager.

Ans:

create role manager;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

2. Assign create table privilege to role Manager.

Ans:

grant create table to manager;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

3. create user induja which has unlimited tablespace and assign 100m quota. Then assign manager role to user induja.

grant create session to induja identified by induja123;

grant manager to induja;

grant unlimited tablespace to induja;

alter user induja quota 100m on users;

alter user induja quota unlimited on users;

while creating user assign default tablespace and quota

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

4. Assign manager role to user induja.

Ans: grant manager to induja;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

5. Connect the user session Induja and create table STAFF and insert values.

Ans:

Conn : induja/induja123

CREATE TABLE STAFF (STAFF_ID NUMBER(4) PRIMARY KEY, STAFF_NAME VARCHAR2(15), SALARY

NUMBER(8));

INSERT INTO STAFF VALUES(11,'NABRAS', 40000);

INSERT INTO STAFF VALUES(12,'SENTHIL',60000);

INSERT INTO STAFF VALUES(13,'AMBU',55000);

commit;

---------------------------------------------------------------------------------------------------

6. Display Staff table

Ans: Select * from staff;

----------------------------------------------------------------------------------------------------------------------------------------------------------------

7. Modify the salary of Staff_id 13 as 65000

Ans: update staff set salary=65000 where staff_id=13;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

8. The user sinduja assign create table privilege to user arvind; [Hint: conn sinduja and give privilege]

Ans:

grant create table to sinduja with admin option;

conn sinudja/sinduja123

grant create table to arvind;

-----------------------------------------------------------------------------------------------------------------------------

9. Login Arvind, create course table and insert values [hint: assign tablespace unlimited]

Ans:

CONN arvind/arvind123

create table course(course_id number(5) primary key, course_name varchar2(10));

insert into course values(1,'DB');

insert into course values(2,'SE');

insert into course values(3,'NT');

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

10. Delete course_id 3

Ans: delete from course where course_id=3;

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

11. Insert course_id as 13 and course_name is "NT"

Ans: insert into course values(13,'NT');

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

12. Modify the course name as "BUSS" for course_id 13

Ans: UPDATE COURSE SET COURSE_NAME='BUSS' WHERE COURSE_ID=13;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

13. Create user Meenu, assign select privilege of staff_id, salary column of staff table; [Hint: create view staffview]

Ans:

grant create seSsion to meenu identified by me;

CREATE VIEW STAFFVIEW AS SELECT STAFF_ID,SALARY FROM INDUJA.STAFF

grant select ON STAFFVIEW to meenu;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

14. Display Object privileges assign to user Meenu.

Ans: select * from DBA_TAB_PRIVS where grantee='MEENU';

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

15. Assign Update column privilege of staff_name, salary of staff table to new role admin_staff.

Ans:

CREATE ROLE ADMIN_STAFF;

GRANT UPDATE(STAFF_NAME,SALARY) ON INDUJA.STAFF TO ADMIN_STAFF;

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

16. Remove update privilege of salary column from role admin_staff

Ans:

REVOKE UPDATE ON INDUJA.STAFF FROM ADMIN_STAFF;

GRANT UPDATE(SALARY) ON INDUJA.STAFF TO ADMIN_STAFF;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

17. Create role "HR_HEAD", assign "HR_HEAD" to manager. Assign manager role to Meenu.

Ans:

CREAE ROLE HR_HEAD;

GRANT HR_HEAD TO MANAGER;

GRANT manager TO MEENU;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

18. Display Role assign to user Meenu.

Ans: select * from DBA_role_privs where GRANTEE='MEENU';

-------------------------------------------------------------------------------------------------------------

19. Display Role assign to role Manager

Ans: select * from ROLE_role_privs where ROLE='MANAGER';

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

20. Assign all the privilege of course table to role admin_staff

Ans: GRANT ALL ON ARVIND.COURSE TO ADMIN_STAFF;

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Unit 3 - Authorization Exercise Solution

Standards