DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on Edited on

Column Level Audit in Oracle AI Database 26ai

#oracle #database #data #linux

Unified Auditing is enabled by default in Oracle AI Database 26ai, 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.
Enter fullscreen mode Exit fullscreen mode

Next, we activate the created policy:

SQL> AUDIT POLICY Col_Pol_Mobile;
Audit succeeded.
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 
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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 
SQL> select count(*) from unified_audit_trail where unified_audit_policies like '%COL_POL_MOBILE%';
  COUNT(*)
----------
         1
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode 
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
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)