Purpose:
Usually a Privilege can be treated as unused privilege if this is not being used by user or pre-running programs at database level.
During new application development phase or Existing Application Enhancement phase, some database administrators may grant many powerful system privileges and roles like “SELECT ANY TABLE”, “EXECUTE ANY PROCEDURE” and “DBA” to application developers. Those grants will be the reason for compromising database security.
Once the application starts working, it is too difficult to identify which privilege can be revoked or which can’t be. To come out from such situation, “Privilege Analysis” will be helpful for database administrators.
Steps to do Privilege Analysis:
“CAPTURE_ADMIN” role is required to do Privilege analysis.
SQL> show user
USER is “SYS”
SQL> CREATE USER db_admin IDENTIFIED BY Oracle123;
User created.
SQL> CREATE USER appl_user IDENTIFIED BY Oracle123;
User created.
SQL> GRANT CREATE SESSION, CAPTURE_ADMIN TO db_admin;
Grant succeeded.
SQL> GRANT CREATE SESSION, READ ANY TABLE TO appl_user;
Grant succeeded.
Following procedures plays an important role to capture info about privileges.
- DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE
- DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE
- DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE
- DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT
- DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE
In Our example, “READ ANY TABLE” Privilege being granted to user “APPL_USER”.
Once we enabled CAPTURE, the objects being accessed by “APPL_USER” will be captured in Data Dictionary View “DBA_USED_PRIVS”.
- Before generating report, we should disable capture process.
- Dropping a privilege analysis policy deletes the data captured by the policy.
- The CREATE_CAPTURE procedure can be used to capture privileges for analysis.
SQL> conn db_admin/Oracle123
Connected.
SQL> BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
name => ‘User_Priv_Analysis_Policy’,
description => 'Analyzes system privilege use',
type => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'')=''APPL_USER''');
END;
/
PL/SQL procedure successfully completed.
- G_DATABASE: Captures all the privileges used in the entire database, except SYS privileges.
- G_ROLE: Captures privileges for the sessions that have the roles enabled. If you enter DBMS_PRIVILEGE_CAPTURE.G_ROLEfor the type parameter, then you must also specify the roles parameter. For multiple roles, separate each role name with a comma.
- G_CONTEXT: Captures privileges for the sessions that have the condition specified by the conditionparameter evaluating to TRUE.
- G_ROLE_AND_CONTEXT: Captures privileges for the sessions that have the role enabled and the context condition evaluating to TRUE.
- The ENABLE_CAPTURE procedure enables a privilege policy and creates a capture run name for it.
SQL> BEGIN DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE ( name => ‘User_Priv_Analysis_Policy’, run_name => ‘User_Priv_Policy_run_1’); END; / PL/SQL procedure successfully completed. SQL> conn appl_user/Oracle123 Connected. SQL> SELECT FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE SALARY > 12000 ORDER BY SALARY DESC; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Steven King 24000 Neena Kochhar 17000 Lex De Haan 17000 John Russell 14000 Karen Partners 13500 Michael Hartstein 13000 Nancy Greenberg 12008 Shelley Higgins 12008 8 rows selected.
- The DISABLE_CAPTURE procedure disables a privilege analysis policy.
SQL> conn db_admin/Oracle123 Connected. SQL> EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (‘User_Priv_Analysis_Policy’); PL/SQL procedure successfully completed.
- The GENERATE_RESULT procedure generates a report showing the results of a privilege capture.
SQL> BEGIN DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT ( name => ‘User_Priv_Analysis_Policy’, run_name => ‘User_Priv_Policy_run_1’); END; / PL/SQL procedure successfully completed.
- The DROP_CAPTURE procedure drops a privilege analysis policy.
SQL> conn db_admin/Oracle123 Connected. SQL> EXEC DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (‘User_Priv_Analysis_Policy’);
Find the system privileges that appl_user used and the objects on which he used them during the privilege analysis period.
SQL> show user USER is "DB_ADMIN" SQL> col username format a10 col sys_priv format a16 col object_owner format a13 col object_name format a23 col run_name format a27 SQL> set linesize 200 SQL> SELECT SYS_PRIV, OBJECT_OWNER, OBJECT_NAME, RUN_NAME FROM DBA_USED_PRIVS WHERE USERNAME = 'APPL_USER'; SYS_PRIV OBJECT_OWNER OBJECT_NAME RUN_NAME ---------------- ------------- ----------------------- --------------------------- CREATE SESSION USER_PRIV_POLICY_RUN_1 SYS DBMS_APPLICATION_INFO USER_PRIV_POLICY_RUN_1 SYS DUAL USER_PRIV_POLICY_RUN_1 READ ANY TABLE HR EMPLOYEES USER_PRIV_POLICY_RUN_1
The following data dictionary views will be helpful to view privilege analysis information.
DBA_PRIV_CAPTURES DBA_USED_PRIVS DBA_UNUSED_GRANTS 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
Conclusion:
Similar way, we can identify the objects being accessed by specific user for whom we did enabled capture process. We can capture information at role level and database level too.
