How To Use DBMS_PRIVILEGE_CAPTURE To Capture Privs In Oracle 12c

Introduction:- Oracle 12c introduced the DBMS_PRIVILEGE_CAPTURE package, which helps us in doing privilege analyze and find a report on used and unused privileges.In order to use the DBMS_PRIVILEGE_CAPTURE package, you must be granted the CAPTURE_ADMIN role. One of the most important Security Principals while maintaining your Enterprise Database Landscape is implementing the “least privilege” principle. The least privilege principle is applied from IT Security level on any enterprise system and system administrators should regularly review and fix excessive privileges.

One of the very nice “security” features of Oracle 12c release, is the ability to track and capture privileges of running applications using the package (DBMS_PRIVILEGE_CAPTURE).

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 a 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 complete

Let’s 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: