Scenario 3 – Creating Audit Policies
Here we will see how to create different audit policies tailored to your organization’s needs, auditing privileges, actions, and roles under specific conditions.
Creating Audit Policies: Part 1 – Privilege Audit Policy
In this subsection, we’ll walk through the process of creating a privilege audit policy tailored to your organization’s needs.
1. Creating a Privilege Audit Policy
Begin by creating an audit policy to audit the user OE for specific system privileges using the SELECT ANY TABLE or CREATE LIBRARY privileges for each statement executed. Grant the SELECT ANY TABLE privilege to the user OE:
SQL> CREATE AUDIT POLICY aud_syspriv_pol PRIVILEGES SELECT ANY TABLE, CREATE LIBRARY
WHEN SYS_CONTEXT(‘USERENV’,’SESSION_USER’) = ‘OE’ EVALUATE PER STATEMENT;
SQL> GRANT SELECT ANY TABLE TO oe;
2. Enabling the Audit Policy
Once the audit policy is created, enable it:
SQL> AUDIT POLICY aud_syspriv_pol;
3. Viewing Audit Policy Options
View the audit policy options to ensure they are correctly set:
SQL> COL AUDIT_OPTION FORMAT A20
SQL> COL POLICY_NAME FORMAT A18
SQL> SELECT POLICY_NAME, AUDIT_OPTION, CONDITION_EVAL_OPT
FROM AUDIT_UNIFIED_POLICIES WHERE POLICY_NAME = ‘AUD_SYSPRIV_POL’;
4. Verifying Policy Status
Confirm that the audit policy is enabled:
SQL> COL USER_NAME FORMAT A10
SQL> COL POLICY_NAME FORMAT A18
SQL> SELECT POLICY_NAME, ENABLED_OPT, USER_NAME, SUCCESS, FAILURE
FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME = ‘AUD_SYSPRIV_POL’;
5. Performing an Audited Operation
Execute audited operations using the user accounts:
SQL> CONNECT hr/oracle_4U
SQL> SELECT * FROM sh.sales;
SQL> CONNECT oe/oracle_4U
SQL> SELECT * FROM hr.employees;
6. Viewing Audit Data
Access the resulting audit data to review the audited operations:
SQL> CONNECT system/oracle_4U
SQL> COL ACTION_NAME FORMAT A16
SQL> COL POLICY_NAME FORMAT A18
SQL> COL SYSTEM_PRIVILEGE_USED FORMAT A20
SQL> SELECT DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED
FROM unified_audit_trail WHERE DBUSERNAME IN (‘HR’,’OE’);
7. Flushing Audit Trail
If necessary, flush the unified audit trail to ensure all audit data is available:
SQL> CONN / AS SYSDBA
SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
8. Viewing Audit Data Again
Once the audit data is flushed to disk, review it again:
SQL> SELECT DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED
FROM unified_audit_trail WHERE DBUSERNAME IN (‘HR’,’OE’);
By following these steps, you can effectively create and manage privilege audit policies tailored to your organization’s requirements.
Creating Audit Policies: Part 2 – Action Audit Policy
In this subsection, we’ll guide you through the process of creating an action audit policy to monitor specific user actions within your database environment.
1. Creating an Action Audit Policy
Start by creating an audit policy to monitor select, update, and delete operations on the HR.EMPLOYEES table:
SQL> CREATE AUDIT POLICY aud_action_pol ACTIONS SELECT, UPDATE, DELETE ON hr.employees;
2. Enabling the Audit Policy
Next, enable the audit policy for all users except OE:
SQL> AUDIT POLICY aud_action_pol EXCEPT oe;
3. Viewing Audit Policy Options
Verify the audit policy options to ensure they are correctly configured:
SQL> COL AUDIT_OPTION FORMAT A20
SQL> COL POLICY_NAME FORMAT A18
SQL> COL OBJECT_NAME FORMAT A18
SQL> SELECT POLICY_NAME, AUDIT_OPTION, OBJECT_NAME
FROM AUDIT_UNIFIED_POLICIES WHERE POLICY_NAME = ‘AUD_ACTION_POL’;
4. Verifying Policy Status
Confirm that the audit policy is enabled:
SQL> COL USER_NAME FORMAT A10
SQL> COL POLICY_NAME FORMAT A18
SQL> SELECT POLICY_NAME, ENABLED_OPT, USER_NAME, SUCCESS, FAILURE
FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME = ‘AUD_ACTION_POL’;
5. Performing an Audited Operation
Execute audited operations by creating a new user (DEV) and granting appropriate privileges:
SQL> CREATE USER DEV IDENTIFIED BY oracle_4U;
SQL> GRANT CREATE SESSION TO DEV;
SQL> CONNECT hr/oracle_4U
SQL> GRANT DELETE ON hr.employees TO DEV;
SQL> CONNECT dev/oracle_4U
SQL> DELETE FROM hr.employees;
SQL> CONNECT oe/oracle_4U
SQL> SELECT COUNT(*) FROM hr.employees;
6. Viewing Audit Data
Access the resulting audit data to review the audited operations:
SQL> CONNECT system/oracle_4U
SQL> SET PAGES 100
SQL> COL DBUSERNAME FORMAT A8
SQL> COL ACTION_NAME FORMAT A8
SQL> COL “DATE” FORMAT A20
SQL> COL SYSTEM_PRIVILEGE_USED FORMAT A18
SQL> COL UNIFIED_AUDIT_POLICIES FORMAT A22
SQL> SELECT UNIFIED_AUDIT_POLICIES, DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED,
TO_CHAR(EVENT_TIMESTAMP,’DD-MON-YY HH:MI’) “DATE” FROM unified_audit_trail
WHERE DBUSERNAME IN (‘DEV’,’OE’) AND ACTION_NAME NOT IN (‘LOGON’, ‘LOGOFF’)
ORDER BY 4;
7. Flushing Audit Trail
If necessary, flush the unified audit trail to ensure all audit data is available:
SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
8. Viewing Audit Data Again
Once the audit data is flushed to disk, review it again:
SQL> SELECT UNIFIED_AUDIT_POLICIES, DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED,
TO_CHAR(EVENT_TIMESTAMP,’DD-MON-YY HH:MI’) “DATE” FROM unified_audit_trail
WHERE DBUSERNAME IN (‘DEV’,’OE’) AND ACTION_NAME NOT IN (‘LOGON’, ‘LOGOFF’)
ORDER BY 4;
By following these steps, you can effectively create and manage action audit policies to monitor specific user actions within your Oracle database environment.
Creating Audit Policies: Part 3 – Role Audit Policy
In this subsection, we’ll guide you through the process of creating role audit policies to monitor users’ usage of specific roles within your Oracle database environment.
1. Creating a Role Audit Policy
Start by creating an audit policy to monitor all users using the MGR_ROLE role:
SQL> CREATE USER JIM IDENTIFIED BY oracle_4U;
SQL> CREATE ROLE MGR_ROLE;
SQL> GRANT CREATE TABLESPACE TO MGR_ROLE;
SQL> GRANT MGR_ROLE, CREATE SESSION TO JIM;
SQL> CREATE AUDIT POLICY aud_role_pol ROLES mgr_role;
2. Creating another Role Audit Policy
Create an audit policy to monitor all users as soon as they use the DBA role. Create a DBA_JUNIOR user granted the DBA role:
SQL> CREATE USER DBA_JUNIOR IDENTIFIED BY oracle_4U;
SQL> GRANT DBA TO DBA_JUNIOR;
SQL> CREATE AUDIT POLICY aud_dba_pol ROLES dba;
3. Enabling the Audit Policies
Enable the audit policies whenever the execution is completed successfully only:
SQL> AUDIT POLICY AUD_ROLE_POL WHENEVER SUCCESSFUL;
SQL> AUDIT POLICY AUD_DBA_POL WHENEVER SUCCESSFUL;
4. Viewing Audit Policy Options
Verify the audit policy options to ensure they are correctly configured:
SQL> COL AUDIT_OPTION FORMAT A20
SQL> COL POLICY_NAME FORMAT A18
SQL> SELECT POLICY_NAME, AUDIT_OPTION, CONDITION_EVAL_OPT
FROM AUDIT_UNIFIED_POLICIES WHERE POLICY_NAME IN (‘AUD_ROLE_POL’,’AUD_DBA_POL’);
5. Verifying Policy Status
Confirm that the audit policies are enabled:
SQL> COL USER_NAME FORMAT A10
SQL> COL POLICY_NAME FORMAT A18
SQL> SELECT POLICY_NAME, ENABLED_OPT, USER_NAME, SUCCESS, FAILURE
FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME IN (‘AUD_ROLE_POL’,’AUD_DBA_POL’);
6. Performing Audited Operations
Execute audited operations for both role type audited policies:
SQL> CONNECT JIM/oracle_4U
SQL> CREATE TABLESPACE test DATAFILE ‘/tmp/test01.dbf’ SIZE 10M;
SQL> CONNECT DBA_JUNIOR/oracle_4U
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=200;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=100;
7. Viewing Audit Data
Access the resulting audit data to review the audited operations:
SQL> CONNECT system/oracle_4U
SQL> SET PAGES 100
SQL> COL DBUSERNAME FORMAT A10
SQL> COL ACTION_NAME FORMAT A17
SQL> COL UNIFIED_AUDIT_POLICIES FORMAT A30
SQL> COL SYSTEM_PRIVILEGE_USED FORMAT A12
SQL> SELECT UNIFIED_AUDIT_POLICIES, DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED
FROM unified_audit_trail WHERE DBUSERNAME IN (‘JIM’,’DBA_JUNIOR’)
AND ACTION_NAME NOT IN (‘LOGON’, ‘LOGOFF’)
AND (UNIFIED_AUDIT_POLICIES LIKE ‘%AUD_ROLE_POL%’ OR UNIFIED_AUDIT_POLICIES LIKE ‘%AUD_DBA_POL%’);
8. Flushing Audit Trail
If necessary, flush the unified audit trail to ensure all audit data is available:
SQL> EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
9. Viewing Audit Data Again
Once the audit data is flushed to disk, review it again:
SQL> SELECT UNIFIED_AUDIT_POLICIES, DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED
FROM unified_audit_trail WHERE DBUSERNAME IN (‘JIM’,’DBA_JUNIOR’)
AND ACTION_NAME NOT IN (‘LOGON’, ‘LOGOFF’)
AND (UNIFIED_AUDIT_POLICIES LIKE ‘%AUD_ROLE_POL%’ OR UNIFIED_AUDIT_POLICIES LIKE ‘%AUD_DBA_POL%’);
By following these steps, you can effectively create and manage role audit policies to monitor users’ usage of specific roles within your Oracle database environment.