Column-Level Auditing in Oracle: audit_condition Explained

INTRO : Oracle Unified Audit Trail does not provide the count of affected rows.

Unified auditing is the way forward to perform Oracle Database auditing. Unified auditing offers more flexibility to perform selective and effective auditing, which helps you focus on activities that really matter to your enterprise. To improve security and compliance, Oracle strongly recommends that you use unified auditing. Starting with Oracle Database 23ai, traditional auditing is desupported.

But currently Oracle Unified Audit mechanism does not provide the affected rows count from an auditied statement. All the mechanism designed as focusing the statement/action run by clients instead of the data.

In Oracle’s current Unified Auditing framework (19c) , column-level monitoring is not available, meaning audit policies can track operations on tables or objects but cannot filter actions based on specific columns. This limitation makes it challenging to narrow down auditing to particular data fields, often resulting in larger volumes of audit logs and potentially capturing more data than needed, which can complicate audit analysis and storage management.

Column level auditing in Oracle Database 23ai

However, with the upcoming Oracle Database 23ai , column-level monitoring will become available in Unified Auditing. This enhancement will allow audit policies to specify exact columns to monitor, enabling more focused and efficient auditing. For example, if only sensitive columns such as PAN (Primary Account Number) or SSN (Social Security Number) need auditing, policies can now be defined to monitor only those columns, reducing the audit trail’s size and improving data relevance. Official documentation about the new feature is available on Oracle Database Security Guide for Oracle Database 23ai and 23ai New Feature – Column Level Audit (Doc ID 2997748.1)

Example Scenario

You have salary data in the salaries column of the HR.EMPLOYEES table, and you need all queries accessing this sensitive data to be audited. In Oracle 19c, with Unified Auditing policies, you can only track access at the table level, not the column level. (However, with Fine-Grained Auditing (FGA), you can audit at the column level.)

I will create a unified auditing policy named ORA_SENSITIVE_DATA which is designed to audit any command executed on sensitive tables by specified roles (a list of human users) allowing us to filter and focus on sensitive actions. This policy should not be enabled for any application users. Instead, it will be enabled at the role level. Any database user that needs tracking should be granted the HUMAN_ROLE.

Using Current Unified Auditing Mechanism in Oracle Database 19c – Track sensitive data column on table level – ORA_SENSITIVE_DATA

SQL> CREATE ROLE human_role;
SQL> GRANT HUMAN_ROLE to sh,ix,bi,oe,pm;

SQL> CREATE AUDIT POLICY ORA_SENSITIVE_DATA ACTIONS ALL ON HR.EMPLOYEES, ALL ON OTHER.OTHER_SENSITIVE_DATA ONLY TOPLEVEL;
SQL> COMMENT ON AUDIT POLICY ORA_SENSITIVE_DATA IS 'Audit policy auditing all sensitive data operations on table level, should not be enabled for application users';

SQL> AUDIT POLICY ORA_SENSITIVE_DATA BY USERS WITH GRANTED ROLES HUMAN_ROLE;	

Using Unified Auditing Mechanism in Oracle Database 23ai – Track sensitive data column on column level – ORA_SENSITIVE_DATA_COLUMN

SQL> CREATE ROLE human_role;
SQL> GRANT HUMAN_ROLE to sh,ix,bi,oe,pm;

SQL> create audit policy ORA_SENSITIVE_DATA_COLUMN actions all(salaries) on hr.employees ONLY TOPLEVEL;
SQL> audit policy ORA_SENSITIVE_DATA_COLUMN;
SQL> COMMENT ON AUDIT POLICY ORA_SENSITIVE_DATA IS 'Audit policy auditing all sensitive data operations on column level, should not be enabled for application users';

SQL> AUDIT POLICY ORA_SENSITIVE_DATA BY USERS WITH GRANTED ROLES HUMAN_ROLE;	

To track sensitive data on specific columns until migrating to the 23ai version (According to the recently update Release Schedule of Current Database Releases (Doc ID 742060.1) not before 2025) , Fine-Grained Auditing policies can also be created. With this approach, it’s possible to detect only the queries that access sensitive data columns.

What is Fine-Grained Auditing?

FGA provides detailed auditing of database operations based on specific conditions and allows you to monitor and audit database activities at a very granular level, such as:

• Tracking SELECT statements on a particular table or column.
• Auditing data access based on the values in the data itself (e.g., auditing access to some specific departments working on a secret level mission etc.)
• Defining complex conditions (such as only auditing a specific user or a specific query type)

FGA is particularly useful for sensitive data monitoring and can be applied on a per-column or per-row basis. For example, you might want to audit every time a user queries a particular column of a table if that column contains sensitive data (such as social security numbers, PAN card numbers).

Combining Fine-Grained Auditing and Unified Auditing

There is a common misconception that Fine-Grained Auditing (FGA) and Unified Auditing cannot be used together. While they serve different purposes, FGA and Unified Auditing can indeed be used together to provide a comprehensive auditing solution. Unified Auditing actually supersedes the traditional auditing mechanisms.

Oracle Database 19c ships with mixed mode by default. You may use all auditing mechanism traditional auditing, fine grained auditing and unified auditing. Fine grained auditing records will be stored in DBA_FGA_AUDIT_TRAIL only if you did not enable Unified Auditing. When unified auditing is enabled(relink operation) in Oracle Database, the audit records are populated in the new audit trail and can be viewed from UNIFIED_AUDIT_TRAIL.

Fine-Grained Auditing (FGA) still will be available in Oracle Database 23ai. When you create a Fine-Grained Auditing policy using DBMS_FGA, the system automatically logs the audit results in the Unified Audit Trail (in the unified_audit_trail), ensuring that all audit data is stored in one centralized location.

Using Fine-Grained Auditing for real life examples

By adjusting the audit_condition criteria, you can configure auditing to detect only queries that return rows and access the specified data. Queries that return no rows or do not access the tracked columns will not be audited using this approach.

Each audit policy is applied to the query individually. However, at most one audit record may be generated for each policy, no matter how many rows being returned satisfy that policy’s audit_condition. In other words, whenever any number of rows being returned satisfy an audit condition defined on the table, a single audit record will be generated for each such policy.

All tweaks are made to the audit_condition parameter. There is a helpful document titled “How to Use DBMS_FGA with a Complex audit_condition (Include/Exclude Multiple Users)(Doc ID 832856.1)” that explains the audit_condition attribute. By using it effectively, many tasks can be accomplished; however, sometimes this can be challenging.

Here is a challanging one from ASK TOM answered by Connor Mcdonald.

Requirement : i have to create a oracle FGA policy having multiple values for a particular table t and it has stat column, data is (‘a’,’b’,’c’,’d’,’e’,’f’,’g’,’h’,’i’,’j’,’k’,’l’,’m’,’n’,’o’) and i want exclude stat = ‘a’ or ‘b’ from auditing

and Connor Mcdonald said :

SQL> create table t as
select e.,'a' stat from scott.emp e union all select e.,'b' stat
from scott.emp e
union all
select e.*,'c' stat
from scott.emp e

SQL> grant all on t to scott;

SQL> BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => user,
object_name => 'T',
policy_name => 'SALARY_CHANGE',
audit_column => 'STAT',
audit_condition => q'{regexp_count(STAT,'(a|b)')=0}',
enable => TRUE,
statement_types => 'UPDATE',
audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED );
END;

Here is a challenge I recently faced, along with an example scenario.

Requirement : For specific database users granted with the ‘HUMAN_ROLE’ role, Tracking only queries that return rows from the ‘SALARIES’ column of the ‘EMPLOYEES’ table in the HR schema.

Example  Scenario:

We have two database users.

• USER1_HUMAN has HUMAN_ROLE granted.
• USER2_APP does not have HUMAN_ROLE privilege granted.
• Both Users have required grants to query HR.EMPLOYEES table.

In HR.EMPLOYEES table ;

• There is no employee with employee_id = 999 -> no rows returned.

Query 1 returns 10 rows working in SALES.

SQL> SELECT * FROM HR.EMPLOYEES WHERE DEPARTMENT_ID=1

Query 2 returns 10 rows working in SALES  – returned column is not a sensitive data.

SQL> SELECT EMPLOYEE_ID FROM HR.EMPLOYEES WHERE DEPARTMENT_ID=1 

Query 3 returns no rows.

SQL> SELECT * FROM HR.EMPLOYEES WHERE EMPLOYEE_ID=999

Example 1 : Tracking all queries to the ‘SALARIES’ column in the ‘EMPLOYEES’ table within the HR schema.

Policy 1: ORA_FGA_SENSITIVE_DATA1

No audit_condition :  A NULL value for audit_condition causes audit to happen even if no rows are processed, so that all actions targeting the salaries column of the table will be audited.

BEGIN
  DBMS_FGA.ADD_POLICY(
   object_schema      => 'HR',
   object_name        => 'EMPLOYEES',
   policy_name        => 'ORA_FGA_SENSITIVE_DATA1',
   audit_column       => 'SALARY',
   enable             =>  TRUE,
   statement_types    => 'INSERT, UPDATE, SELECT, DELETE');
END;

In this example, all queries to the salaries column of the hr.employees table will be tracked, regardless of whether data is returned. This tracking will apply to all database users and applications.

Example 2 : Tracking queries that only return rows and access the ‘SALARIES’ column of the ‘EMPLOYEES’ table within the HR schema.

Policy 2: ORA_FGA_SENSITIVE_DATA2

audit_condition :  ‘salary is not null’

BEGIN
  DBMS_FGA.ADD_POLICY(
   object_schema      => 'HR',
   object_name        => 'EMPLOYEES',
   policy_name        => 'ORA_FGA_SENSITIVE_DATA2',
   audit_condition => 'salary is not null',
   audit_column       => 'SALARY',
   enable             =>  TRUE,
   statement_types    => 'INSERT, UPDATE, SELECT, DELETE');
END;

In this example, all queries to the salaries column of the hr.employees table will be tracked only if they return data. This tracking will apply to all database users and applications.”

Example 3 : For specific database users granted with the ‘HUMAN_ROLE’ role, tracking all queries accessing the ‘SALARIES’ column of the ‘EMPLOYEES’ table within the HR schema.

Policy 3: ORA_FGA_SENSITIVE_DATA3

audit_condition : SYS_CONTEXT(‘SYS_SESSION_ROLES’,’HUMAN_ROLE’)=’TRUE’

BEGIN
  DBMS_FGA.ADD_POLICY(
   object_schema      => 'HR',
   object_name        => 'EMPLOYEES',
   policy_name        => 'ORA_FGA_SENSITIVE_DATA3',
   audit_condition => ' SYS_CONTEXT(''SYS_SESSION_ROLES'',''HUMAN_ROLE'')=''TRUE''',
   audit_column       => 'SALARY',
   enable             =>  TRUE,
   statement_types    => 'INSERT, UPDATE, SELECT, DELETE');
END;

In this example, all queries to the salaries column of the hr.employees table will be tracked, regardless of whether data is returned. This tracking will apply only to database users granted with HUMAN_ROLE.

Example 4 : For specific database users granted with the ‘HUMAN_ROLE’ role, Tracking queries that only return rows and access the ‘SALARIES’ column of the ‘EMPLOYEES’ table within the HR schema

Policy 4: ORA_FGA_SENSITIVE_DATA4

audit_condition: CONCAT(SYS_CONTEXT(‘SYS_SESSION_ROLES’,’HUMAN_ROLE’),NVL2(SALARY,’1′,’0′))=’TRUE1′

BEGIN
  DBMS_FGA.ADD_POLICY(
   object_schema      => 'HR',
   object_name        => 'EMPLOYEES',
   policy_name        => 'ORA_FGA_SENSITIVE_DATA4',
  audit_condition => 'CONCAT(SYS_CONTEXT(''SYS_SESSION_ROLES'',''HUMAN_ROLE''),NVL2(SALARY,''1'',''0''))=''TRUE1''',
   audit_column       => 'SALARY',
   enable             =>  TRUE,
   statement_types    => 'INSERT, UPDATE, SELECT, DELETE');
END;

In this example, all queries to the salaries column of the hr.employees table will be tracked only if they return data. This tracking will apply only to database users granted with HUMAN_ROLE.

The requirement is met with the help of a complex audit_condition.

Hope it helps.