Auditing Enhancements in Oracle Database 23ai

This post describes some of the auditing enhancements in Oracle database 23ai.

◉ Desupport of Traditional Auditing

Traditional auditing was deprecated in Oracle 21c, and has been desupported in Oracle 23ai. Make sure you are using Unified Auditing. 

◉ Audit Individual Columns for Tables and Views

In Oracle 23ai we can create audit policies on individual columns of tables and views, which allows us to thin out the contents of the audit trail by ignoring actions that don't affect the columns of interest. For a table or view column we can audit the following actions, as described here.

ALL, ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, SELECT, UPDATE

To demonstrate this we create a test table.

conn testuser1/testuser1@//localhost:1521/freepdb1

drop table if exists audit_test_tab purge;

create table audit_test_tab (

  id  number generated always as identity,

  col1 varchar2(10),

  col2 varchar2(10),

  col3 varchar2(10)

);

insert into audit_test_tab (col1, col2) values ('apple', 'banana');

commit;

We connect to a privileged user and create a new audit policy. We want to audit updates on COL1 or COL2, and queries of COL2. Notice we supply a comma-separated list of columns the audited action applies to.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

noaudit policy test_audit_policy;

drop audit policy test_audit_policy;

create audit policy test_audit_policy

  actions update(col1, col2) on testuser1.audit_test_tab,

          select(col2) on testuser1.audit_test_tab

  container = current;

audit policy test_audit_policy;

We check the audit trail for actions against the table, and we can see there are no actions audited.

column event_timestamp format a30

column dbusername format a10

column action_name format a20

column object_schema format a10

column object_name format a20

column sql_text format a40

select event_timestamp,

       dbusername,

       action_name,

       object_schema,

       object_name,

       sql_text

from   unified_audit_trail

where  object_name = 'AUDIT_TEST_TAB'

order BY event_timestamp;

no rows selected

SQL>

We perform some operations against the test table, some of which are auditable actions.

conn testuser1/testuser1@//localhost:1521/freepdb1

-- Not audited.

insert into audit_test_tab (col1, col2) values ('apple2', 'banana2');

update audit_test_tab

set    col3 = 'pear'

where  col3 is null;

commit;

select id from audit_test_tab;

        ID

----------

         1

         2

SQL>

-- Audited.

update audit_test_tab

set    col1 = 'apple1'

where  col1 = 'apple';

update audit_test_tab

set    col2 = 'banana1'

where  col2 = 'banana';

select col2 from audit_test_tab;

COL2

----------

banana1

banana2

SQL>

We check the audit trail.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

column event_timestamp format a30

column dbusername format a10

column action_name format a20

column object_schema format a10

column object_name format a20

column sql_text format a40

select event_timestamp,

       dbusername,

       action_name,

       object_schema,

       object_name,

       sql_text

from   unified_audit_trail

where  object_name = 'AUDIT_TEST_TAB'

order BY event_timestamp;

EVENT_TIMESTAMP                DBUSERNAME ACTION_NAME          OBJECT_SCH OBJECT_NAME          SQL_TEXT

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

14-JUN-23 19.31.17.231940 PM   TESTUSER1  UPDATE               TESTUSER1  AUDIT_TEST_TAB       update audit_test_tab

                                                                                               set    col1 = 'apple1'

                                                                                               where  col1 = 'apple'

14-JUN-23 19.31.17.248862 PM   TESTUSER1  UPDATE               TESTUSER1  AUDIT_TEST_TAB       update audit_test_tab

                                                                                               set    col2 = 'banana1'

                                                                                               where  col2 = 'banana'

14-JUN-23 19.31.17.252646 PM   TESTUSER1  SELECT               TESTUSER1  AUDIT_TEST_TAB       select col2 from audit_test_tab

SQL>

Notice only those actions on the specified columns were audited. The query of the ID column, and the updates to the COL3 column were not audited.

Source: oracle-base.com