ORACLE DBA - PRIVILEGE MANAGEMENT

Privilege
A privilege is a permission to execute an action or to access another user
objects. Or
A privilege is a permission to perform transaction against the database.
There are two types of privileges.
 System level privileges
 Object level privileges
System Privileges
Enables users to perform particular action in the database is called system
privileges.
 The grant command adds a privilege to a user or a group of users.
 The revoke command deleted the privilege.
SQL> conn system/manager
Syntax: create user <user name> identified by <password>;
SQL> create user test identified by sekhar ;
SQL> grant create session, create table, create view to test;
SQL> revoke create table, create view from test;
SQL> grant create table, create view, create synonym to sekhar  with admin option;
SQL> conn sekhar /sekhar

SQL> grant create table,create view,create synonym to suresh with admin option;
SQL> revoke create table,create view from sekhar ;
System Privileges Ex:
Index: create any index, alter any index, and drop any index.
Tables: create table, alter any table, drop any table, select any table, update any table,
and delete any table.
Session: create session, alter session and restricted session.
Tablespace: create tablespace, alter tablespace, drop tablespace and unlimited
tablespace.
Object Privileges
Enable users to access and manipulate specific objects is called object
privileges. Or
Privileges granted on user created privileges are called object privileges.
SQL> conn scott/tiger
Connected
SQL> grant select, insert, update, delete on emp to chandra;
SQL> grant insert, select on dept to chandra;
SQL> grant insert, select on dept to kirshna with grant option;
Note: So chandra can grant the insert, select privileges to other user.
In oracle there are around 150 system privileges
Ex: create session
Q: Must give the at the same time both privileges with security?
A: Can give by using role option.
Note: Default roles are connect, resource.
What is session?
A user connected to the database is called session. Create session is the
minimum privilege of any database user needs. Next create table, create view, create
synonym these are the system privileges.
Q: Can give the at the same time system and object privileges?
A: We can’t give the system privileges and object privileges. Because, system is
metadata and objects are data. So, can’t give both at a time. We can give separate.
The privileges of ‘SYSOPER’
 Startup.
 Shutdown.
 Set database in mount stage.
 We take the trace of control files.
 Recovery the database.
 We can set the database in archive log mode.
The privileges of ‘SYSDBA’
 All sysoper privileges with admin option.
 Create database.
 Alter database begin/end backup.
 Restricted session
 recover db
DATA DICTIONARY VIEWS
SESSION_PRIVS
DBA_SYS_PRIVS
USER_SYS_PRIVS
DBA_ROLE_PRIVS
USER_ROLE_PRIVS