Auditing Activities with Unified Audit Policies

Activities yang kita bisa audit yaitu diantaranya

1. User Accounts, Roles, and Privileges
2. Object Actions
3. Application Context Value

Berikut adalah syntax untuk membuat unified audit policy

CREATE AUDIT POLICY policy_name
    { {privilege_audit_clause [action_audit_clause ] [role_audit_clause ]}
        | { action_audit_clause  [role_audit_clause ] } 
        | { role_audit_clause }
     }        
    [WHEN audit_condition EVALUATE PER {STATEMENT|SESSION|INSTANCE}] 
    [CONTAINER = {CURRENT | ALL}];

Untuk mengaktifkan audit policy, gunakan perintah seperti berikut:

AUDIT POLICY {policy_name} 
    [by { usernames | USERS with granted roles role_names }] 
    [WHENEVER [NOT] SUCCESSFUL];

Untuk me-disabled audit policy, gunakan perintah berikut:

noaudit POLICY {policy_name} 
    [by { usernames | USERS with granted roles role_names }] 
    [WHENEVER [NOT] SUCCESSFUL];

Untuk melihat bahwa, audit policy telah active kita bisa check di Static Data Dictionary View AUDIT_UNIFIED_ENABLED_POLICIES

select *
from AUDIT_UNIFIED_ENABLED_POLICIES;

Auditing Object Action

Audit object action yaitu tracking object pada database seperti table, view, sequances dan lain-lain, Contoh penggunaanya jika mau track update statement pada table hr.employees seperti berikut:

Jika di jalan maka hasilnya seperti berikut:

Bash> sqlplus system/passwordnyaOracle18@XEPDB1

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Mar 15 13:02:29 2021
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Mon Mar 15 2021 13:02:22 +00:00

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> create audit policy aud_update_hr_employees
actions update on hr.EMPLOYEES,
delete on hr.EMPLOYEES;

SQL> audit policy aud_update_hr_employees
by TOKO_ONLINE, SALES;

SQL> conn offices/project2018@XEPDB1
Connected.

SQL> update hr.employees set
commission_pct = 0.1
where employee_id = 100;

1 row updated.

SQL> commit;

Commit complete.

SQL> select employee_id, commission_pct
  2  from hr.employees
  3  where employee_id = 100;

EMPLOYEE_ID COMMISSION_PCT
----------- --------------
        100             .1

SQL> conn system/passwordnyaOracle18@XEPDB1
Connected.

SQL> select OS_USERNAME, ACTION_NAME, UNIFIED_AUDIT_POLICIES, SQL_TEXT, OBJECT_NAME, OBJECT_SCHEMA
from UNIFIED_AUDIT_TRAIL
where lower(UNIFIED_AUDIT_POLICIES) in ('aud_update_hr_employees')
order by EVENT_TIMESTAMP desc;

+-----------+-----------+-----------------------+--------------------------+-----------+----------+
|OS_USERNAME|ACTION_NAME|UNIFIED_AUDIT_POLICIES |SQL_TEXT                  |OBJECT_NAME|OBJ_SCHEMA|
+-----------+-----------+-----------------------+--------------------------+-----------+----------+
|root       |UPDATE     |AUD_UPDATE_HR_EMPLOYEES|update hr.employees set   |EMPLOYEES  |HR        |
|           |           |                       |commission_pct = 0.1      |           |          |
|           |           |                       |where employee_id = 100   |           |          |

Back to top ↑