DBA Hub

📋Steps in this guide1/6

how to use DBMS_PRIVILEGE_CAPTURE to capture privs in oracle 12c - DBACLASS DBACLASS

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_

oracle clusteringintermediate
by OracleDba
19 views
1

Overview

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
2

Section 2

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
3

Section 3

Example: ——- Create an user and give some privileges: Create capture process

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
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.
4

Section 4

Enable the created capture(test_capture) Lets do some operations in the database. Now you can disable the capture

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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.

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

PL/SQL procedure successfully completed.

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
5

Section 5

Generate the capture result: Now check whats privileges were used in the database and by which user: 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SQL> CONN / AS SYSDBA

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

PL/SQL procedure successfully completed.

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

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
6

Section 6

Find existing capture policies: To drop the capture procedure: In similar way we can use g_role,G_CONTEXT,G_ROLE_AND_CONTEXT .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!