Privilege Analysis sorts under Oracles "Database Vault" option, but can be used without enabling Database Vault.
Note that you need a valid License for Database Vault option to use the privilege analysis package.
To check if it's allready in use:
SELECT NAME , DETECTED_USAGES , FIRST_USAGE_DATE , LAST_USAGE_DATE, LAST_SAMPLE_DATE, CURRENTLY_USED, DESCRIPTION FROM DBA_FEATURE_USAGE_STATISTICS WHERE FIRST_USAGE_DATE IS NOT NULL AND NAME LIKE 'Privilege%';
From my example database, this is the output:
| NAME | DETECTED_USAGES | FIRST_USAGE_DATE | LAST_USAGE_DATE | LAST_SAMPLE_DATE | CURRENTLY_USED | DESCRIPTION |
|---|---|---|---|---|---|---|
| Privilege Capture | 11 |
27.05.2017 03:41:35 | 05.08.2017 03:46:46 | 05.08.2017 03:46:46 | TRUE | Privilege Capture is being used |
Here's how to set it up:
1. Create the capture:
BEGIN
DBMS_PRIVILEGE_CAPTURE.create_capture(
name => 'my_policy',
type => DBMS_PRIVILEGE_CAPTURE.g_context,
condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SCOTT'''
);
END;
/
2. Check that the capture was created:
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;
3. Enable the capture
BEGIN
DBMS_PRIVILEGE_CAPTURE.enable_capture('my_policy');
END;
/
When a representative time has passed, you can disable the capture and generate results:
BEGIN
DBMS_PRIVILEGE_CAPTURE.disable_capture(
name => 'saga2_felles_policy'
);
DBMS_PRIVILEGE_CAPTURE.generate_result(
name => 'saga2_felles_policy'
);
END;
/
You can check the results of the capture by using these queries:
SET LINESIZE 200 COLUMN username FORMAT A20 COLUMN username FORMAT A20 COLUMN used_role FORMAT A30 COLUMN path FORMAT A50 COLUMN sys_priv FORMAT A30 COLUMN obj_priv FORMAT A8 COLUMN object_owner FORMAT A15 COLUMN object_name FORMAT A30 COLUMN object_type FORMAT A11 prompt ================================ prompt Which privileges have been used? prompt Look in DBA_USED_SYSPRIVS prompt ================================ SELECT username, sys_priv FROM dba_used_sysprivs WHERE capture = 'my_policy' ORDER BY username, sys_priv; prompt ================================ prompt How were the privileges granted prompt to the user? prompt Look in DBA_USED_SYSPRIVS_PATH prompt ================================ SELECT username, sys_priv, used_role, path FROM dba_used_sysprivs_path WHERE capture='my_policy' order by username,sys_priv; prompt ================================ prompt What object privileges were necessary? prompt Look in DBA_USED_OBJPRIVS prompt ================================ SELECT username, obj_priv, object_owner, object_name, object_type FROM dba_used_objprivs WHERE capture = 'my_policy'; prompt ================================ prompt How were the object privileges granted to the user? prompt Look in DBA_USED_OBJPRIVS_PATH prompt ================================ SELECT username, obj_priv, object_owner, object_name, used_role, path FROM dba_used_objprivs_path WHERE capture = 'my_policy'; exit
To drop the usage of Privilage Capture all together:
BEGIN
DBMS_PRIVILEGE_CAPTURE.drop_capture(
name => 'my_policy'
);
END;
/
