Column Level Audit in Oracle 23ai

Oracle Database support auditing at column level and Audit actions at column level introduced in Oracle 23ai Database. Using this feature, you can Audit column level for tables and views.

Before Oracle 21c and before you can audit at table level. Oracle deprecated traditional auditing in Oracle 21c, and it is desupported from Oracle 23ai.

In Oracle 23ai user can perform audit actions at individual column level by creating audit policies. You can audit action on a Tableau or view column using “CREATE AUDIT POLICY” Statement

For example,

• Audit all actions on a table

    SQL> CREATE AUDIT POLICY aud_all_act_emp ACTIONS ALL ON SCOTT.EMP;

When using ACTIONS ALL user should be cautious. Because enabling on an online transaction processing (OLTP) workload. Will lead large number of audit records.

• Audit action on a column

    SQL> CREATE AUDIT POLICY aud_col_bon_act_emp ACTIONS SELECT (BONOUS) ON                SCOTT.EMP;

Users can query UNIFIED_AUDIT_TRAIL dictionary view to view audit events information

SQL> SELECT OBJECT_NAME,SQL_TEXT FROM UNIFIED_AUDIT_TRAIL WHERE OBJECT_NAME = 'EMP';

OBJECT_NAME     SQL_TEXT
------------------------ ----------------------------------------------------------------
EMP                         SELECT BONUS FROM EMP WHERE EMPNO=123

You can also audit actions on SYS objects using CREATED AUDIT POLICTY statement.

SQL> CREATE AUDIT POLICY aud_col_sys_obje ACTIONS SELECT ON SYS.TABLES;

To audit recursive actions, use ONLY TOPLEVEL clause in the CREATE AUDIT POLICY statement.

User can audit PL/SQL packages, functions, procedures and triggers, pls consider below points

• You can audit standalone PL/SQL packages, functions and procedures 
• Auditing a PL/SQL package will audit all functions and procedures within the package. You cannot audit individual producers and functions within the PL/SQL package. 
• Auditing all executions will enable auditing all triggers, functions and procedures within PL/SQL packages.
• Auditing EXECUTE operation on a PL/SQL stored procedure or function, will determine the success or failure of the operation auditing purpose.

For a list of Object-level Database action audit options, you can refer Oracle documentation here

Thanks & Regards,

https://oracleracexpert.com