Privilege Analysis Examples
Creating a Privilege Analysis Policy Using DBMS_PRIVILEGE_CAPTURE
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
name VARCHAR2,
description VARCHAR2 DEFAULT NULL,
type NUMBER DEFAULT DBMS_PRIVILEGE_CAPTURE.G_DATABASE,
roles ROLE_NAME_LIST DEFAULT ROLE_NAME_LIST(),
condition VARCHAR2 DEFAULT NULL);
Create Privilege Analysis of Database-Wide Privileges
BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
name => 'db_wide_capture_pol',
description => 'Captures database-wide privileges',
type => DBMS_PRIVILEGE_CAPTURE.G_DATABASE);
END;
/
EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('db_wide_capture_pol');
Creaate Privilege Analysis of Roles
BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
name => 'dba_roles_capture_pol',
description => 'Captures DBA and LBAC_DBA role use',
type => DBMS_PRIVILEGE_CAPTURE.G_ROLE,
roles => role_name_list('dba', 'lbac_dba'));
END;
/
EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('dba_roles_capture_pol');
Privilege Analysis of Module use
BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
name => 'psmith_sqlplus_analysis_pol',
description => 'Analyzes PSMITH role priv use for SQL*Plus module',
type => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
condition => 'SYS_CONTEXT(''USERENV'', ''MODULE'')=''sqlplus''
AND SYS_CONTEXT(''USERENV'', ''SESSION_USER'')=''PSMITH''');
END;
/
EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('psmith_sqlplus_analysis_pol');
Create Privilege Analysis of user session
BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
name => 'ANY_priv_analysis_pol',
description => 'Analyzes system privilege use',
type => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'')= ’’APP_USER''');
END;
/
Enabling a Privilege Analysis Policy Using DBMS_PRIVILEGE_CAPTURE
EXEC DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ('ANY_priv_analysis_poll');
or
BEGIN
DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (
name => 'ANY_priv_analysis_pol',
run_name => 'ANY_priv_pol_run_1');
END;
/
DBA_PRIV_CAPTURES - to view privilege analysis policy
Disabling a Privilege Analysis Policy Using DBMS_PRIVILEGE_CAPTURE
EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE ('ANY_priv_analysis_pol');
Generating a Privilege Analysis Report Using DBMS_PRIVILEGE_CAPTURE
DBA_PRIV_CAPTURES - check NAME and ENABLED columns to see if policy is disabled
EXEC DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT ('ANY_priv_analysis_pol');
or
BEGIN
DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (
name => 'ANY_priv_analysis_pol',
run_name => 'ANY_priv_pol_run_1');
END;
/
The results generated are stored in the privilege analysis data dictionary views
Query the data dictionary view to see results of capture
SELECT SYS_PRIV, OBJECT_OWNER, OBJECT_NAME, RUN_NAME FROM DBA_USED_PRIVS WHERE USERNAME = 'APP_USER';
SELECT USERNAME, OBJ_PRIV, USED_ROLE, OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE
FROM DBA_USED_OBJPRIVS
WHERE USERNAME = 'APP_USER';
SELECT USERNAME, SYS_PRIV
FROM DBA_UNUSED_SYSPRIVS
WHERE USERNAME = 'APP_USER';
Views:
DBA_USED_PRIVS
DBA_UNUSED_PRIVS
DBA_USED_OBJPRIVS
DBA_UNUSED_OBJPRIVS
DBA_USED_OBJPRIVS_PATH
DBA_UNUSED_OBJPRIVS_PATH
DBA_USED_SYSPRIVS
DBA_UNUSED_SYSPRIVS
DBA_USED_SYSPRIVS_PATH
DBA_UNUSED_SYSPRIVS_PATH
DBA_USED_PUBPRIVS
DBA_USED_USERPRIVS
DBA_UNUSED_USERPRIVS
DBA_USED_USERPRIVS_PATH
DBA_UNUSED_USERPRIVS_PATH
Dropping a Privilege Analysis Policy Using DBMS_PRIVILEGE_CAPTURE
DBA_PRIV_CAPTURES - check NAME and ENABLED columns to see if policy is disabled
EXEC DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (‘ANY_priv_analysis_pol');
