Oracle 12c Capture Privilege Usage

From Oracle 12.1 we have the ability to "record" the usage of permissions in our application and then we can narrow the permissions only to the minimal requirement.

Installation

CREATE USER YOSSI IDENTIFIED BY YOSSI;
GRANT DBA, RESOURCE TO YOSSI;

A user defined condition, when user is YOSSI (type = G_CONTEXT).

BEGIN
    DBMS_PRIVILEGE_CAPTURE.create_capture ( name => 'yossi_pol', TYPE => DBMS_PRIVILEGE_CAPTURE.g_context, condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''YOSSI''' );
    DBMS_PRIVILEGE_CAPTURE.enable_capture ( 'yossi_pol' );
END;
/

Verify that the capture is defined and enabled

COLUMN ROLES                FORMAT a20
COLUMN CONTEXT              FORMAT a30
COLUMN ENABLED              FORMAT a7
  SELECT name
,        TYPE
,        enabled
,        roles
,        context
    FROM dba_priv_captures
ORDER BY name;

Work with user YOSSI (run the application)...

Stop the capture and generate the results

BEGIN
    DBMS_PRIVILEGE_CAPTURE.disable_capture ( 'yossi_pol' );
    DBMS_PRIVILEGE_CAPTURE.generate_result ( 'yossi_pol' );
END;
/

Query system privileges results

  SELECT username, sys_priv
    FROM dba_used_sysprivs
   WHERE capture = 'yossi_pol'
ORDER BY username, sys_priv;

Query object privileges results

SELECT username
,      obj_priv
,      object_owner
,      object_name
,      object_type
  FROM dba_used_objprivs
 WHERE capture = 'yossi_pol';

Investigate system privileges

  SELECT username
,        sys_priv
,        used_role
,        PATH
    FROM dba_used_sysprivs_path
   WHERE capture = 'yossi_pol'
ORDER BY username, sys_priv;

Investigate object privileges

SELECT username
,      obj_priv
,      object_owner
,      object_name
,      used_role
,      PATH
  FROM dba_used_objprivs_path
 WHERE capture = 'yossi_pol';

At the end - drop the capture definition

BEGIN
    DBMS_PRIVILEGE_CAPTURE.drop_capture ( 'yossi_pol' );
END;
/