DBMS_PRIVILEGE_CAPTURE:
---------

Oracle 12c introduced the DBMS_PRIVILEGE_CAPTURE package, which helps us in doing privilege analyze and find report on used and unused privileges.

In order to use the DBMS_PRIVILEGE_CAPTURE package you must be granted the CAPTURE_ADMIN role.

steps involve:
------

CREATE_CAPTURE
ENABLE_CAPTURE
DISABLE_CAPTURE( after waiting for necessary time)
GENERATE_RESULT
DROP_CAPTURE

Though there are 4 options to create_capture . we will discuss on one or two option(mostly used)

G_DATABASE : Analyzes all privilege usage on the database, except the SYS user.
G_ROLE : Analyzes all privilege usage by the roles specified in the ROLES parameter

Example:
---

Create an user and give some privileges:

SQL> create user c##test identified  by test ; 

User created.


SQL> Grant create session to C##TEST;

Grant succeeded.

SQL> grant select any table to C##TEST;

Grant succeeded.

Create capture process

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

PL/SQL procedure successfully completed.

Enable the created capture(test_capture)

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

PL/SQL procedure successfully completed.

Lets do some operations in the database.

SQL> conn c##test/test
Connected.



SQL>  select count(*) from c##raj.ram;

  COUNT(*)
----------
         4


SQL> select * from c##raj.ram;

         N
----------
         2
         2
         2
         2

Now you can disable the capture

SQL> CONN / AS SYSDBA

SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name => 'test_capture');

PL/SQL procedure successfully completed.

Generate the capture result:

SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name => 'test_capture');

PL/SQL procedure successfully completed.


NOTE: If you try to generate the result before disabling the capture you will get below error.


SQL> exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name => 'test_capture');
BEGIN SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name => 'test_capture'); END;

*
ERROR at line 1:
ORA-47932: Privilege capture test_capture is still enabled.
ORA-06512: at "SYS.DBMS_PRIVILEGE_CAPTURE", line 58
ORA-06512: at line 1

Now check whats privileges were used in the database and by which user:

SQL> ;
  1   SELECT USERNAME, SYS_PRIV
  2  FROM DBA_USED_SYSPRIVS
  3* WHERE USERNAME='C##TEST';


USERNAME     SYS_PRIV
------------ ----------------------------------------
C##TEST      SELECT ANY TABLE
C##TEST      CREATE SESSION

The result shows that c##test users used select any table and create session privileges. This way we can estimate which privileges the user is using and after analysis and checking with the app team, we can revoke the unused privileges.

Find existing capture policies:

SQL> COLUMN name FORMAT A15
COLUMN roles FORMAT A20
COLUMN context FORMAT A30
SET LINESIZE 100

SELECT name,
       type,
       enabled,
       roles,
       context
FROM   dba_priv_captures
ORDER BY name;SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7

NAME            TYPE             E ROLES                CONTEXT
--------------- ---------------- - -------------------- ------------------------------
test_capture    DATABASE         N

To drop the capture procedure:

SQL> exec dbms_privilege_capture.drop_capture('test_capture');

In similar way we can use g_role,G_CONTEXT,G_ROLE_AND_CONTEXT .

ORACLE 12CORACLE SECURITY


Related Topics

Purge AUD$ table using DBMS_AUDIT_MGMT
How to move AUD$ table to another tablespace using DBMS_AUDIT_MGMT
Unified audit trail in Oracle 12c
Lock account automatically with INACTIVE_ACCOUNT_TIME
ENABLE_DDL_LOGGING in oracle 12c
PDB Lockdown Profiles in Oracle 12.2
orapwd tool for password file in oracle
Open wallet automatically after starting the database
how to send mail using utl_mail in oracle 11g
how to use DBMS_PRIVILEGE_CAPTURE to capture privs in oracle 12c

You May Also Like

How to enable active dataguard in physical standby database
How to enable flash recovery area in oracle database
ORA-16855: transport lag has exceeded specified threshold
How to change the case (Lower to Upper and Vice Versa) in R
TNS-01106: Listener using listener name has already been started
Upgrade grid infrastructure to 19C oracle
oraversion utility in oracle 18c - New feature
How to use oratop tool for oracle database monitoring
How to install oracle client in silent mode using response file
How to flashback a RAC database

From This Website

How to move spfile from file system to ASM in RAC
FLASHBACK parameter in DATAPUMP(EXPDP)
Apply patch on oracle 12.2 database ( Release update)
Find pending distributed pending transactions in oracle
Perform Flashback in pluggable database(PDB) in oracle 12.2
Useful DGMGRL commands in oracle dataguard
ENABLE_DDL_LOGGING in oracle 12c
How to move controlfile to a new location in oracle
Exception in thread -main" java.lang.OutOfMemoryError: GC overhead limit exceeded with bsu.sh
How to multiplex control file in standalone database