Implementing Audit Policies in Oracle Database

Posted on Jan 23, 2025 in Mathematics

Creating a Privilege Audit Policy

Steps:

1. Create user xyz1 and grant user resource, connect, create, and drop any table privileges.
2. Grant the user xyz1 the privilege to select any table:

   GRANT SELECT ANY TABLE TO xyz1;

3. Create policy:

   CREATE AUDIT POLICY aud100

   PRIVILEGES select any table, create library

   WHEN 'SYS_CONTEXT (''USERENV'', ''SESSION_USER'') = ''xyz1'' '

   EVALUATE PER STATEMENT;

4. Enable Policy:

   AUDIT POLICY aud100;

5. View the audit policy options:

   SELECT POLICY_NAME, AUDIT_OPTION, CONDITION_EVAL_OPT

   FROM AUDIT_UNIFIED_POLICIES

   WHERE POLICY_NAME ='aud100';

6. Verify that the audit policy is enabled:

   SELECT POLICY_NAME, ENABLED_OPT, USER_NAME, SUCCESS, FAILURE

   FROM AUDIT_UNIFIED_ENABLED_POLICIES

   WHERE POLICY_NAME ='aud100';

7. Test the policy by performing an audit operation such as selecting a table:

   CONNECT xyz1/password

   SELECT * FROM <schema>.<table_name>;

8. View the auditing data using a Unified_Auditing_Trail query:

   SELECT DBUSERNAME, ACTION_NAME, SYSTEM_PRIVILEGE_USED from unified_audit_trail where DBUSERNAME in ('xyz1');

Creating an Action Audit Policy

Create an audit policy that will audit any user performing any select or update operation on any object using an object or system privilege, or deleting rows from the XYZ1.CUSTOMER table.

1. CREATE AUDIT POLICY audit_actions_policy1

   ACTIONS select, update, delete on xyz1.customer;

2. Enable the audit policy for all users:

   AUDIT POLICY audit_actions_policy1;

3. View the audit policy options:

   SELECT POLICY_NAME, AUDIT_OPTION, OBJECT_NAME

   FROM AUDIT_UNIFIED_POLICIES

   WHERE POLICY_NAME ='audit_actions_policy1';

4. Verify that the audit policy is enabled:

   SELECT POLICY_NAME, ENABLED_OPT, USER_NAME, SUCCESS, FAILURE

   FROM AUDIT_UNIFIED_ENABLED_POLICIES

   WHERE POLICY_NAME ='audit_actions_policy1';

5. Perform an audited operation. First, create a new user xyz2 and grant appropriate privileges to xyz2 to execute operations:

   CREATE USER xyz2 IDENTIFIED BY abc123;

   GRANT create session TO xyz2;

   CONNECT xyz1/xxxxxxxx

   GRANT delete ON xyz1.customer TO xyz2;

   CONNECT xyz2/abc123;

   SELECT * FROM xyz1.customer;

   DELETE xyz1.customer;

6. View the resulting audit data:

   CONNECT system/xxxxxxxxx

   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 ('xyz2')

   AND ACTION_NAME not in ('LOGON', 'LOGOFF')

   ORDER BY 4;

   If the audited data is still in memory, you cannot see it. We do not want to wait until the background process flushes the data to disk.

   EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL

   Now you can view the resulting audit data.

   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 ('xyz2')

   AND ACTION_NAME not in ('LOGON', 'LOGOFF')

   ORDER BY 4;

Creating a Role Audit Policy

Create an audit policy that will audit all users while using the manager role.

1. CREATE USER xyz3 IDENTIFIED BY abc123;

2. CREATE ROLE MANAGER;

3. GRANT create tablespace TO MANAGER;

4. GRANT MANAGER, create session TO xyz3;

5. CREATE AUDIT POLICY audit_roles_policy1

   ROLES MANAGER;

Create an audit policy that will audit all users as soon as these users use the DBA role.

Create a DBA_JUNIOR user granted the DBA role.

1. CREATE USER DBA_JUNIOR IDENTIFIED BY aaabbcc123;

2. GRANT DBA TO DBA_JUNIOR;

3. CREATE AUDIT POLICY audit_dba_policy1

   ROLES dba;

4. Enable the audit policies whenever the execution is completed successfully only:

   AUDIT POLICY audit_roles_policy1 WHENEVER SUCCESSFUL;

   AUDIT POLICY audit_dba_policy1 WHENEVER SUCCESSFUL;

5. View the audit policy options.