14 Lecture DBA Assignment 2

14 Lecture DBA  Assignment 2

DownLoad Link at the Last:

Youtube Video Link: 

 Question.1: 

A college in town admitted 50 and 70 students for 11th and 12th class respectively. There are 7 teachers, 2 admin staff and one super admin. College runs its mighty database which need to be managed by you as DBA. Solve the above problem by creating users, roles and privileges according to your own understanding. Also assign privileges properly. USE SQL QUERIES ONLY. Ans

CREATE USER t1

IDENTIFIED BY t1

DEFAULT TABLESPACE data

DEFAULT TEMPORARY TABLESPACE temp

QUOTA 5M ON data

QUOTA 10M ON temp

PASSWORD EXPIRE;

CREATE USER t2

IDENTIFIED BY t2

DEFAULT TABLESPACE data

DEFAULT TEMPORARY TABLESPACE temp

QUOTA 5M ON data

QUOTA 10M ON temp

PASSWORD EXPIRE;

CREATE USER t3

IDENTIFIED BY t3

DEFAULT TABLESPACE data

DEFAULT TEMPORARY TABLESPACE temp

QUOTA 5M ON data

QUOTA 10M ON temp

PASSWORD EXPIRE;

CREATE USER t4

IDENTIFIED BY t4

DEFAULT TABLESPACE data

DEFAULT TEMPORARY TABLESPACE temp

QUOTA 5M ON data

QUOTA 10M ON temp

PASSWORD EXPIRE;

CREATE USER t5

IDENTIFIED BY t5

DEFAULT TABLESPACE data

DEFAULT TEMPORARY TABLESPACE temp

QUOTA 5M ON data

QUOTA 10M ON temp

PASSWORD EXPIRE;

CREATE USER t6

IDENTIFIED BY t6

DEFAULT TABLESPACE data

DEFAULT TEMPORARY TABLESPACE temp

QUOTA 5M ON data

QUOTA 10M ON temp

PASSWORD EXPIRE;

CREATE USER t7

IDENTIFIED BY t7

DEFAULT TABLESPACE data

DEFAULT TEMPORARY TABLESPACE temp

QUOTA 5M ON data

QUOTA 10M ON temp

PASSWORD EXPIRE;

CREATE USER a1

IDENTIFIED BY a1

DEFAULT TABLESPACE data

DEFAULT TEMPORARY TABLESPACE temp

QUOTA 5M ON data

QUOTA 10M ON temp

PASSWORD EXPIRE;

CREATE USER a2

IDENTIFIED BY a2

DEFAULT TABLESPACE data

DEFAULT TEMPORARY TABLESPACE temp

QUOTA 5M ON data

QUOTA 10M ON temp

PASSWORD EXPIRE;

CREATE USER sa

IDENTIFIED BY sa

DEFAULT TABLESPACE data

DEFAULT TEMPORARY TABLESPACE temp

QUOTA 5M ON data

QUOTA 10M ON temp

PASSWORD EXPIRE; 

CREATE ROLE  t_role

IDENTIFIED BY t_role;

GRANT CREATE SESSION, index, table, tablespace  TO  t_role;

GRANT EXECUTE, alter, insert, select ON tb1 TO t_role;

GRANT t_role TO  t1, t2,t3,t4,t5,t6,t7;

CREATE ROLE  a_role

IDENTIFIED BY a_role;

GRANT CREATE SESSION, index, table, tablespace  TO  a_role WITH ADMIN OPTION;

GRANT EXECUTE,alter,insert,select ON tb1 TO t_role with grant option;

GRANT a_role TO  a1, a2;

GRANT CREATE SESSION, index, table, tablespace  TO  sa WITH ADMIN OPTION;

GRANT EXECUTE, alter, insert, select, delete, index, references, update ON tb1 TO sa with grant option;

Question.2: Write all privileges for TABLE and TABLESPACE.

Ans

Main privileges of table and tablespace are:-

• Any keyword signifies that user has privileges in all schema

• Grant used for adding privileges to the users or groups

• Revoke used for removing any privilege from the schema

Question.3: 

How you can obtain privileges information.

Ans

Information of privileges can be obtained by:-

• Dba_sys_privs

• Dba_tab_privs

• Session_privs

• Dba_col_privs

Question.4: 

Write any 4 predefined roles with one line detail of each.

Ans

Question.5: 

How you can remove roles from user. Show by SQL Query example.

Ans

REVOKE CREATE TABLE FROM testing.

Download Link: 

Click Here To Download Lecture 14