Column level auditing - Oracle 23ai new feature

Prior to Oracle Database 23ai we don’t have the ability to audit selectively column in the table, we have to enable auditing for entire table even though we may be interested only in few columns that we would like to have enable auditing.
 
A new security feature introduced in Oracle 23ai is column level auditing feature, where we can enable auditing for just a (few)column in a table, without enabling audit at table level which would include all the columns in the table.
 
In Oracle database 23ai we can create unified auditing policies to adjust individual column in table/views. This feature enables us to configure more finer grained and focused audit policies ensuring that auditing is selective enough to reduce the creation of unnecessary audit records while at the same time ensuring all security requirements are met.
 
In this demo we will see about how to enable auditing for a single column, initially created for UPDATE statement and then altered to include SELECT statements, but only for a specific column in the table. Other columns in the table are not enable for auditing.
 
Create schema and grant the necessary privileges.
 
admin@ATP23ai> grant db_developer_role
  2     , select_catalog_role
  3     , audit system
  4     , unlimited tablespace
  5  to demo_user identified by "Good2go1234!";
 
Grant succeeded.
 
admin@ATP23ai> grant create session to app_user identified by "Good2go1234!";
 
Grant succeeded.
 
admin@ATP23ai> grant read on audsys.UNIFIED_AUDIT_TRAIL to demo_user,app_user;
 
Grant succeeded.
 
admin@ATP23ai>
 
create the table and insert some sample rows.
 
admin@ATP23ai> conn demo_user/"Good2go1234!"@atp23_vpn
Connected.
demo-user@ATP23ai> create table if not exists
  2  emp( empno number
  3     , ename varchar2(10)
  4     , hire_date date );
 
Table created.
 
demo-user@ATP23ai>
demo-user@ATP23ai> insert into emp(empno,ename,hire_date)
  2  values (1,'Tom',sysdate),
  3    (2,'Kyte',sysdate+1) ;
 
2 rows created.
 
demo-user@ATP23ai>
demo-user@ATP23ai> select * from emp;
 
     EMPNO ENAME      HIRE_DATE
---------- ---------- -----------
         1 Tom        05-AUG-2024
         2 Kyte       06-AUG-2024
 
Create the audit policy, the policy will audit any UPDATE statement on the HIRE_DATE column, followed by necessary grants to application schema (APP_USER)
 
demo-user@ATP23ai> create audit policy emp_update_policy
  2  actions update(hire_date)
  3  on emp;
 
Audit policy created.
 
demo-user@ATP23ai> audit policy emp_update_policy;
 
Audit succeeded.
 
demo-user@ATP23ai> grant select on emp to app_user;
 
Grant succeeded.
 
demo-user@ATP23ai> grant update(hire_date) on emp to app_user;
 
Grant succeeded.
 
Connect as application schema (APP_USER) and execute SELECT statements and verify the unified audit logs
 
app-user@ATP23ai> select * from demo_user.emp;
 
     EMPNO ENAME      HIRE_DATE
---------- ---------- -----------
         1 Tom        05-AUG-2024
         2 Kyte       06-AUG-2024
 
app-user@ATP23ai> col dbusername for a15
app-user@ATP23ai> col action_name for a20
app-user@ATP23ai> col event_timestamp for a35
app-user@ATP23ai> col sql_text for a50
app-user@ATP23ai>
app-user@ATP23ai> select dbusername
  2     , action_name
  3     , event_timestamp
  4     , sql_text
  5  from unified_audit_trail
  6  where object_name ='EMP'
  7  and object_schema = 'DEMO_USER';
 
no rows selected
 
 
No audit trial exists for SELECT statement issued by app_user, since the audit in-place is only for UPDATE statements.
 
Executing the update statement which modifies the HIRE_DATE column in EMP table.
 
app-user@ATP23ai> update demo_user.emp
  2  set hire_date = sysdate+ 1
  3  where ename ='Tom';
update demo_user.emp
                 *
ERROR at line 1:
ORA-41900: missing SELECT privilege on "DEMO_USER"."EMP"
Help: https://docs.oracle.com/error-help/db/ora-41900/
 
The predicates in the above SQL command will try to place a row level lock for the matching rows in the target table before progressing the updates and since we got only READ privilege on the target table we got the above “missing privilege” error and even those failed UPDATE statement also get audited.
 
app-user@ATP23ai> select dbusername
  2     , action_name
  3     , event_timestamp
  4     , sql_text
  5  from unified_audit_trail
  6  where object_name ='EMP'
  7  and object_schema = 'DEMO_USER';
 
DBUSERNAME      ACTION_NAME          EVENT_TIMESTAMP                     SQL_TEXT
--------------- -------------------- ----------------------------------- --------------------------------------------------
APP_USER        UPDATE               05-AUG-24 11.36.07.947411 AM        update demo_user.emp
                                                                         set hire_date = sysdate+ 1
                                                                         where ename ='Tom'
 
 
once we provide the required privilege, the UPDATE statement works fine and it get audited as well.
 
app-user@ATP23ai> conn demo_user/"Good2go1234!"@atp23_vpn
Connected.
demo-user@ATP23ai> grant select on emp to app_user;
 
Grant succeeded.
 
demo-user@ATP23ai> conn app_user/"Good2go1234!"@atp23_vpn
Connected.
app-user@ATP23ai> update demo_user.emp
  2  set hire_date = sysdate+ 1
  3  where ename ='Tom';
 
1 row updated.
 
app-user@ATP23ai> commit;
 
Commit complete.
 
app-user@ATP23ai>
app-user@ATP23ai> select dbusername
  2     , action_name
  3     , event_timestamp
  4     , sql_text
  5  from unified_audit_trail
  6  where object_name ='EMP'
  7  and object_schema = 'DEMO_USER';
 
DBUSERNAME      ACTION_NAME          EVENT_TIMESTAMP                     SQL_TEXT
--------------- -------------------- ----------------------------------- --------------------------------------------------
APP_USER        UPDATE               05-AUG-24 11.37.31.241909 AM        update demo_user.emp
                                                                         set hire_date = sysdate+ 1
                                                                         where ename ='Tom'
 
APP_USER        UPDATE               05-AUG-24 11.36.07.947411 AM        update demo_user.emp
                                                                         set hire_date = sysdate+ 1
                                                                         where ename ='Tom'
 
Now lets alter the audit policy to include the auditing of SELECT statement on the HIRE_DATE column.
 
demo-user@ATP23ai> alter audit policy emp_update_policy
  2  add actions select(hire_date)
  3  on emp;
 
Audit policy altered.
 
demo-user@ATP23ai> conn app_user/"Good2go1234!"@atp23_vpn
Connected.
app-user@ATP23ai> select dbusername
  2     , action_name
  3     , event_timestamp
  4     , sql_text
  5  from unified_audit_trail
  6  where object_name ='EMP'
  7  and object_schema = 'DEMO_USER';
 
DBUSERNAME      ACTION_NAME          EVENT_TIMESTAMP                     SQL_TEXT
--------------- -------------------- ----------------------------------- --------------------------------------------------
APP_USER        UPDATE               05-AUG-24 11.37.31.241909 AM        update demo_user.emp
                                                                         set hire_date = sysdate+ 1
                                                                         where ename ='Tom'
 
APP_USER        UPDATE               05-AUG-24 11.36.07.947411 AM        update demo_user.emp
                                                                         set hire_date = sysdate+ 1
                                                                         where ename ='Tom'
 
 
app-user@ATP23ai> select ename,hire_date from demo_user.emp;
 
ENAME      HIRE_DATE
---------- -----------
Tom        06-AUG-2024
Kyte       06-AUG-2024
 

app-user@ATP23ai> select dbusername

  2     , action_name

  3     , event_timestamp

  4     , sql_text

  5  from unified_audit_trail

  6  where object_name ='EMP'

  7  and object_schema = 'DEMO_USER';

DBUSERNAME  ACTION_NAME  EVENT_TIMESTAMP                SQL_TEXT

----------- ------------ ------------------------------ ------------------------------------------

APP_USER    SELECT       05-AUG-24 11.40.32.429573 AM   select ename,hire_date from demo_user.emp

APP_USER    UPDATE       05-AUG-24 11.37.31.241909 AM   update demo_user.emp

                                                        set hire_date = sysdate+ 1

                                                        where ename ='Tom'

APP_USER    UPDATE       05-AUG-24 11.36.07.947411 AM   update demo_user.emp

                                                        set hire_date = sysdate+ 1

                                                        where ename ='Tom'