- 11 Oct, 2023
- 0 Comments
- 2 Mins Read
How To Use DBMS_PRIVILEGE_CAPTURE To Capture Privs In Oracle 12c
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.
ENABLE_CAPTURE.
DISABLE_CAPTURE( after waiting for necessary time).
GENERATE_RESULT.
DROP_CAPTURE
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##test.t1; COUNT(*) ---------- 4 SQL> select * from c##test.t1; 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 .