It’s very nice new feature in Oracle Database 12C helping quickly analyze  what roles and privileges are used inside database and by who.

Quick summary about this extension and its features:

• new PL/SQL package DBMS_PRIVILEGE_CAPTURE to run analysis
  • for both system privileges and object privileges
  • on application module level or user session level
  • in dedicated time
• create reports that describe used privileges and objects
• no need to use database vault
• can show used and unused privileges for system and objects so it’s easy to decide what can be revoked, changed(grant less powerful roles, privileges) to make more secured database

General concept is very simple

1. Define new type “privilege analysis policy” using

   DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE

   1. role analysis type or
   2. context analysis type or
   3. role and context analysis type or
   4. database analysis type
2. Run your application(s)
3. Enable capture – collect used privileges both system and objects using

   DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE

   1. on module level or
   2. session level
4. Disable capture – stop collecting data using

   DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE

5. Generate results of capture into dedicated Oracle tables using

   DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT

6. Optionally – change your security model based on results
7. Optionally – drop privilege analysis policy and results

   DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE

Important notes:

• only one policy type analysis can be started at a time
• it’s not possible to analyze privileges of the SYS user

Role analysis

For this policy you need to provide list of roles. If a used privilege is from one of the provided roles, then Oracle Database analyzes the privilege use.

Context analysis

For this policy you need to specify boolean expression with SYS_CONTEXT function. The used privileges are analyzed if the condition evaluates to TRUE.

Role and context analysis

For this policy you need to specify both a list of roles to analyze and boolean expression with SYS_CONTEXT function. When a used privilege is from one of the analyzed roles and the given context condition is satisfied, then the privilege is analyzed.

Database analysis

If you do not specify conditions in your privilege analysis policy, then the all privilege use in the database is analyzed, except for privileges that user SYS uses.

Results views

Capture results can be seen in the following views

Views	Description
DBA_PRIV_CAPTURES	Lists information about existing privilege analysis policies
DBA_[UN]USED_PRIVS	Lists the privileges that have [not] been used for reported privilege analysis policies
DBA_[UN]USED_OBJPRIVS	Lists the object privileges that have been [not] used for reported privilege analysis policies. It does not include the object grant paths.
DBA_[UN]USED_OBJPRIVS_PATH	Lists the object privileges that have [not] been used for reported privilege analysis policies. It includes the object privilege grant paths.
DBA_[UN]USED_SYSPRIVS	Lists the system privileges that have [not] been used for reported privilege analysis policies. It does not include the system privilege grant paths.
DBA_[UN]USED_SYSPRIVS_PATH	Lists the system privileges that have [not] been used for reported privilege analysis policies. It includes the system privilege grant paths.
DBA_USED_PUBPRIVS	Lists all the privileges for the PUBLIC role that have been used for reported privilege analysis policies
DBA_[UN]USED_USERPRIVS	Lists the user privileges that have [not] been used for reported privilege analysis policies. It does not include the user privilege grant paths.
DBA_[UN]USED_USERPRIVS_PATH	Lists the user privileges that have [not] been used for reported privilege analysis policies. It includes the user privilege grant paths.

Example

For this example I use my own user TOMASZ with privilege DBA.

DBA role includes CAPTURE_ADMIN role WITH ADMIN OPTION which includes grant EXECUTE on package DBMS_PRIVILEGE_CAPTURE.

1. Run following code as user TOMASZ to create new roles role1, rol2 and user TOMASZ1 and with appropriate privileges to show how capture privileges analysis works.

sqlplus tomasz>

--new roles
create role role1;
create role role2;

--simple table
create table tomasz.test_tbl
(
  id number
);  

--new user
create user tomasz1 identified by dbaora;

--grant roles 
grant connect  to tomasz1;
grant resource to tomasz1;
grant role1    to tomasz1;
grant role2    to tomasz1;

--grant sys priv
grant create view to tomasz1;
grant create materialized view to tomasz1;

--grant object privs
grant select on tomasz.test_tbl to tomasz1;
grant insert on tomasz.test_tbl to role1;
grant delete on tomasz.test_tbl to role2;
grant update on tomasz.test_tbl to public;

2. Create new new type “privilege analysis policy” as user TOMASZ. In this case all types. However later I’ll use only one type “CONTEXT”. You can experiment later with other types on your own.

sqlplus tomasz>

begin
  --role
  dbms_privilege_capture.create_capture 
  (
     name        => 'ROLE',
     description => 'role privilege analysis', 
     type        => dbms_privilege_capture.g_role,
     roles       => role_name_list('role1', 'role2')
  );
end;
/

begin
  --context
  dbms_privilege_capture.create_capture 
  (
     name        => 'CONTEXT',
     description => 'context privilege analysis', 
     type        => dbms_privilege_capture.g_context,
     condition   => 
        'sys_context(''USERENV'', ''SESSION_USER'')=''TOMASZ1'''
  );
end;
/

begin
  --role and context
  dbms_privilege_capture.create_capture 
  (
     name        => 'ROLE_CONTEXT',
     description => 'role context privilege analysis', 
     type        => dbms_privilege_capture.g_role_and_context,
     roles       => role_name_list('role1', 'role2'),
     condition   => 
        'sys_context(''USERENV'', ''SESSION_USER'')=''TOMASZ1'''
  );
end;
/

begin
  --database
  dbms_privilege_capture.create_capture 
  (
     name        => 'DATABASE',
     description => 'database privilege analysis', 
     type        => dbms_privilege_capture.g_database
  );
end;
/

list of “privilege analysis policy”

sqlplus tomasz>

select name, enabled, roles, context 
from dba_priv_captures;

NAME         ENABLE ROLES                     CONTEXT
------------ ------ ------------------------- -----------------------
ROLE         N      SYS.ROLE_ID_LIST(114,115)    
CONTEXT      N                                sys_context('USERENV', 'USER')='TOMASZ'
ROLE_CONTEXT N      SYS.ROLE_ID_LIST(114,115) sys_context('USERENV', 'USER')='TOMASZ'
DATABASE     N        

3. Now it’s time to enable capture process as user TOMASZ

sqlplus tomasz>

begin
  DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE('CONTEXT');
end;
/

4. Next step we need to run some “dummy” code as user TOMASZ1 to simulate our application

sqlplus tomasz1>

select * from tomasz.test_tbl;
insert into tomasz.test_tbl values(10);
update tomasz.test_tbl set id=20;
delete from tomasz.test_tbl;

create or replace view tomasz1.simple_vw
as
select count(*) cnt from user_tables;
commit;

5. As user TOMASZ we stop capture process and generate report

sqlplus tomasz>

begin
  DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE('CONTEXT');
end;
/

begin
  DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT('CONTEXT');
end;
/

6. Output of our analysis are stored in capture views

sqlplus tomasz> 

select 
  username, used_role, sys_priv, 
  obj_priv, object_owner, object_name
from dba_used_privs
where capture='CONTEXT';

USERNAME USED_ROLE SYS_PRIV    OBJ_PRIV OBJECT_OWNER OBJECT_NAME
-------- --------- --------    -------- ------------ -----------
TOMASZ1  TOMASZ1               SELECT   TOMASZ       TEST_TBL
TOMASZ1  ROLE1                 INSERT   TOMASZ       TEST_TBL
TOMASZ1  PUBLIC                UPDATE   TOMASZ       TEST_TBL
TOMASZ1  ROLE2                 DELETE   TOMASZ       TEST_TBL
MDSYS    PUBLIC                EXECUTE  SYS          DBMS_STANDARD
MDSYS    PUBLIC                SELECT   SYS          DUAL
TOMASZ1  TOMASZ1   CREATE VIEW            

select username, sys_priv 
from dba_unused_privs
where capture='CONTEXT';

USERNAME   SYS_PRIV
--------   ----------------
TOMASZ1    CREATE INDEXTYPE
TOMASZ1    CREATE OPERATOR
TOMASZ1    CREATE TYPE
TOMASZ1    CREATE TRIGGER
TOMASZ1    CREATE PROCEDURE
TOMASZ1    CREATE SEQUENCE
...

select 
  username, used_role, 
  obj_priv, object_owner obj_owner, object_name obj_name, path
from dba_used_objprivs_path
where capture='CONTEXT';

USERNAME OBJ_PRIV OBJ_OWNER OBJ_NAME PATH
-------- -------- --------- -------- -----------------------
TOMASZ1  SELECT    TOMASZ   TEST_TBL SYS.GRANT_PATH('TOMASZ1')
TOMASZ1  INSERT    TOMASZ   TEST_TBL SYS.GRANT_PATH('TOMASZ1','ROLE1')
TOMASZ1  UPDATE    TOMASZ   TEST_TBL SYS.GRANT_PATH('PUBLIC')
TOMASZ1  DELETE    TOMASZ   TEST_TBL SYS.GRANT_PATH('TOMASZ1','ROLE2')
...

select username, used_role, sys_priv 
from dba_used_sysprivs
where capture='CONTEXT';

USERNAME USED_ROLE SYS_PRIV
-------- --------- -----------
TOMASZ1  TOMASZ1   CREATE VIEW

select user_priv, onuser
from dba_unused_userprivs
where capture='CONTEXT';

USER_PRIV          ONUSER
------------------ ------
INHERIT PRIVILEGES SYS

7. As last step we can clear capture “CONTEXT” results from views by dropping capture process

sqlplus tomasz>

begin
  dbms_privilege_capture.drop_capture( name => 'CONTEXT' );
end;
/

Have a fun 🙂

Tomasz