DBA Hub

📋Steps in this guide1/4

How To Use DBMS_PRIVILEGE_CAPTURE To Capture Privs In Oracle 12c

What is DBMS_PRIVILEGE_CAPTURE ?

oracle configurationintermediate
by OracleDba
20 views
1

Overview

What is 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 are as follows : - CREATE_CAPTURE. CREATE_CAPTURE. - ENABLE_CAPTURE. ENABLE_CAPTURE. - DISABLE_CAPTURE( after waiting for necessary time). DISABLE_CAPTURE( after waiting for necessary time). - GENERATE_RESULT. GENERATE_RESULT. - DROP_CAPTURE DROP_CAPTURE CREATE_CAPTURE.
2

Section 2

ENABLE_CAPTURE. DISABLE_CAPTURE( after waiting for necessary time). GENERATE_RESULT. DROP_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
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.

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.
3

Section 3

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
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##test.t1;

COUNT(*)
----------
4
SQL> select * from c##test.t1;

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

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

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
4

Section 4

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

Code/Command (click line numbers to comment):

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!