Column Level Audit in Oracle 23ai

Unified Auditing is enabled by default in Oracle 23ai, and this version introduces new features, including column-level auditing. This feature allows auditing specific column(s) of a table or view.

For example, if we want to audit every SELECT operation on the mobile column, we can create the following audit policy:

SQL> CREATE AUDIT POLICY Col_pol_Mobile ACTIONS select(mobile) ON vahid.person;
Audit policy created.

Next, we activate the created policy:

SQL> AUDIT POLICY Col_Pol_Mobile;
Audit succeeded.

Once this policy is set, any attempt to view data in the mobile column will be logged:

SQL> show user
USER is "JAMSHID"
SQL> select mobile from vahid.person where name='Vahid' and last_name='Yousefzadeh';
    MOBILE
----------
9146581254

SQL> select dbusername,action_name,object_schema,object_name from unified_audit_trail where unified_audit_policies like '%COL_POL_MOBILE%';
DBUSERNAME      ACTION_NAM OBJECT_SCHEMA   OBJECT_NAME
--------------- ---------- --------------- ------------
JAMSHID         SELECT     VAHID            PERSON

If the user JAMSHID tries to view a column other than mobile (e.g., id), it will not trigger an audit:

SQL> show user
USER is "JAMSHID"
SQL>  select id from usef.person where name='Vahid' and last_name='Yousefzadeh';
        ID
----------
         3

SQL> select count(*) from unified_audit_trail where unified_audit_policies like '%COL_POL_MOBILE%';
  COUNT(*)
----------
         1

To extend the policy to audit both the mobile and id columns, we can modify the policy with the following command:

SQL> ALTER AUDIT POLICY Col_Pol_Mobile ADD ACTIONS select(ID) ON vahid.person;
Audit policy altered.

SQL> select audit_option,object_schema,object_name,object_type,column_name from audit_unified_policies where policy_name like '%COL_POL_MOBILE%';
AUDIT_OPTION OBJECT_SCHEMA OBJECT_NAME  OBJECT_TYP COLUMN_NAME
------------ ------------- ------------ ---------- ---------------
SELECT       VAHID          PERSON       TABLE      MOBILE
SELECT       VAHID          PERSON       TABLE      ID

Vahid Yousefzadeh
Oracle Database Administrator
Telegram channel :https://t.me/oracledb