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> 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 To extend the policy to audit both the mobile and id columns, we can modify the policy with the following command: SQL> CREATE AUDIT POLICY Col_pol_Mobile2 ACTIONS select(mobile,ID) ON vahid.person; Audit policy created. We can verify the updated policy with this query: SQL> select audit_option,object_schema,object_name,object_type,column_name from audit_unified_policies where policy_name like '%COL_POL_VW_MOBILE_ID%'; AUDIT_OPTION OBJECT_SCHEMA OBJECT_NAME OBJECT_TYP COLUMN_NAME ------------ ------------- ------------ ---------- --------------- SELECT VAHID VW_PERSON VIEW MOBILE SELECT VAHID VW_PERSON VIEW ID Vahid Yousefzadeh Oracle Database Administrator Telegram channel :https://t.me/oracledb

Mar 23, 2025 - 04:26

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> 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

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

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

We can verify the updated policy with this query:

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

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