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'
