Security : Privilege Capture – Helps to Audit

Privilege capture is a feature to track the privileges that is used for a user,
This is very useful in auditing and show results of privileges has used during the capture period.

In 12c the Separate user duties are as below

SQL> select username from dba_users where username like '%SYS%';

 USERNAME
 ----------------------------------
 SYS  : Super user
 SYSKM : Key magament tasks
 SYSDG : Data Guard Managment
 SYSBACKUP : backup management

There is a new DBMS api called , DBMS_PRIVILEGE_CAPTURE which is used to capture the privileges

The Privilege Analysis feature allows you to:

	Define a privilege capture
	Start a privilege analysis during a period of time

Delete capture analysis

The Capture can be Done on Three level :

	User
	Role

Context

to understand this new package check the below examples :

	sqlplus / as sysdba

	SQL> create user Geek DBA identified  by Geek DBA ;

	SQL> grant create session to Geek DBA ;

	Grant succeeded.

	SQL> grant select any table to Geek DBA ;

	Grant succeeded.

Create new Capture using this package :

	exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( -
		 name           =>  'Geek DBA_capture', -
		description    =>  'Capture_all_thing', -
		type           =>   dbms_privilege_capture.g_database)

Where 

	 Name : Package name
	 Description : What this package fo
	 type : depend on level users ( g_database ) , role (g_role )  , Context : (g_role_and_context)

we need to enable this package to start capture :

	 SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => 'Geek DBA_capture');

PL/SQL procedure successfully completed.

Test:-

	SQL> conn Geek DBA/Geek DBA ;
	Connected.

	SQL> select * from Geek DBA.Geek DBA ;
	no rows selected

	SQL> select * from Geek DBA.Geek DBA2 ;
	   TEST_ID TEST_NAME
	---------- --------------------
	  1 KUMAR

again reconnect as / as sysdba to disable capture then generate results like the following :

	SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name => 'Geek DBA_capture');
	PL/SQL procedure successfully completed.

Generate the Result

	SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name => 'Geek DBA_capture');
	PL/SQL procedure successfully completed.

View the Result

	SQL> select username, object_owner, object_name, obj_priv  from   dba_used_objprivs
	 where  username in ('OSAMA', 'TEST') 

	USERNAME   OBJECT_OWNER OBJECT_NAME         OBJ_PRIV
	---------- ------------ ------------------------------ -------------------------
	TEST    SYS  ORA$BASE                     USE
	TEST    SYS  DBMS_APPLICATION_INFO        EXECUTE
	TEST    SYS  DUAL			  SELECT
	TEST    SYS  DUAL                         SELECT
	TEST    SYSTEM PRODUCT_PRIVS              SELECT

Drop the Privilge Capture :

	SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => 'Geek DBA_capture')