1. Home
  2. Knowledge Base
  3. Oracle 23c
  4. Oracle Database 23c New Feature – Column Level Auditing
  1. Home
  2. Knowledge Base
  3. Security
  4. Oracle Database 23c New Feature – Column Level Auditing

Oracle Database 23c New Feature – Column Level Auditing

In pre-Oracle Database 23c database versions, we did not have the ability to audit selectively any ‘sensitive’ columns in a table – we had to enable auditing for the entire table even though we were maybe interested in only a few columns where we would like to have enabled auditing.

A new security feature introduced in Oracle 23c is the Column Level Audit feature – where we can enable auditing for just a column or set of columns in a table – without enabling auditing at the table level which would include all the columns in the table.

In Oracle Database 23c we can create unified auditing policies to audit just individual columns in tables and 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 example, we enable auditing for only a single column in the EMPLOYEES table – DOJ.

The audit policy is initially created only for UPDATE statements – and then altered to even include SELECT statements – but only for the DOJ column in the table.

Other columns in the EMPLOYEES table are not enabled for auditing!

Let us look at the example.

Create the HR user.

SQL> create user HR identified by oracle123##;

User created.


SQL> grant dba to HR;

Grant succeeded.

Create the EMPLOYEES table and insert rows in the table – note we are inserting TWO rows in the same INSERT statement using the Oracle 23c multi-row insert new feature.

SQL> create table HR.Employees
 (EMP_ID number, FIRST_NAME varchar2(20), last_name varchar2(20), DOJ date);

SQL>  insert into HR.Employees
    values
     (1001,'John','Smith','01-FEB-2022'),
  (1002,'Fred','Smith','01-JAN-2023');

2 rows created.

SQL> commit;

Commit complete.

Create the AUDIT POLICY on the EMPLOYEES table. The audit policy will audit any UPDATE statements on the DOJ column.

SQL> create audit policy update_doj_employees
actions update(DOJ) on HR.employees;
 
Audit policy created.

SQL> audit policy update_doj_employees;

Audit succeeded.

Create the APP_USER1 user.

SQL> create user APP_USER1 identified by oracle123##;

User created.

SQL> grant all on HR.employees to app_user1;

Grant succeeded.

SQL> grant connect,resource to app_user1;

Grant succeeded.

Connect as APP_USER1. Execute a SELECT statement which accesses the DOJ column in the EMPLOYEES table.

SQL> select first_name,last_name,doj from hr.employees where first_name='Fred';

FIRST_NAME           LAST_NAME            DOJ
-------------------- -------------------- ---------
Fred                 Smith                01-JAN-23

Query the Unified Audit Trail.

Note: no audit entry exists for the SELECT statement issued by APP_USER1 on the DOJ column in the EMPLOYEES table.

Note: the CREATE TABLE statement has been audited due to the pre-defined audit policies which exist in the database (CREATE ANY TABLE in ORA_SECURECONFIG policy).

SQL> column event_timestamp format a30
SQL> column dbusername format a10
SQL> column action_name format a20
SQL> column object_schema format a10
SQL> column object_name format a20
SQL> column sql_text format a40

SQL> select dbusername,action_name,event_timestamp from unified_audit_trail
   where object_name='EMPLOYEES';

DBUSERNAME ACTION_NAME          EVENT_TIMESTAMP
---------- -------------------- ------------------------------
HR        CREATE TABLE         12-MAR-24 06.03.39.056987 AM

Connect as APP_USER1. Execute a SELECT and UPDATE statement which accesses the DOJ column in the EMPLOYEES table.

SQL> update hr.employees set doj=sysdate where first_name='Fred';

1 row updated.

SQL> commit;

Commit complete.

SQL> select first_name,last_name,doj from hr.employees where first_name='Fred';

FIRST_NAME           LAST_NAME            DOJ
-------------------- -------------------- ---------
Fred                 Smith                12-MAR-24

Query the Unified Audit Trail.

Note on this occasion an audit entry exists for the INSERT statement issued by APP_USER1 on the DOJ column in the EMPLOYEES table.

SQL> column event_timestamp format a30
SQL> column dbusername format a10
SQL> column action_name format a20

SQL> select dbusername,action_name,event_timestamp from unified_audit_trail
   where object_name='EMPLOYEES';

DBUSERNAME ACTION_NAME          EVENT_TIMESTAMP
---------- -------------------- ------------------------------
HR        CREATE TABLE         12-MAR-24 06.03.39.056987 AM
APP_USER1  UPDATE               12-MAR-24 06.19.36.687876 AM

ALTER the Audit Policy on the EMPLOYEES table. Include the auditing of even SELECT statements on the DOJ column.

SQL> alter audit policy update_doj_employees
    add actions select(DOJ) on HR.employees;

Audit policy altered.

Connect as the APP_USER1 user.

Execute a SELECT statement on the EMPLOYEES table which accesses the DOJ column.

SQL> select first_name, doj from HR.employees where first_name='John';

FIRST_NAME           DOJ
-------------------- ---------
John                 01-FEB-22

Query the Unified Audit Trail.

Note on this occasion an audit entry also exists for the SELECT statement issued by APP_USER1 on the DOJ column in the EMPLOYEES table – in addition to the earlier INSERT statement which had also been audited.

SQL>  column event_timestamp format a30
SQL>  column dbusername format a10
SQL>  column action_name format a20
SQL>  col sql_text format a100
SQL>  set lines 300


SQL>  select dbusername,action_name,event_timestamp,sql_text from    unified_audit_trail
where object_name='EMPLOYEES';

DBUSERNAME ACTION_NAME          EVENT_TIMESTAMP                SQL_TEXT
---------- -------------------- ------------------------------ ----------------------------------------------------------------------------------------------
HR        CREATE TABLE         12-MAR-24 06.03.39.056987 AM   create table HR.Employees
                                                               (EMP_ID number, FIRST_NAME varchar2(20), last_name var

APP_USER1  SELECT               12-MAR-24 06.25.26.691393 AM   select first_name, doj from HR.employees where first_name='John'
APP_USER1  UPDATE               12-MAR-24 06.19.36.687876 AM   update hr.employees set doj=sysdate where first_name='Fred'
Updated on March 27, 2024

Was this article helpful?

Related Articles

Leave a Comment