Oracle 21c — Auditing for All Editions of an Object

The unified auditing feature in Oracle 21c makes it possible to apply auditing for all editions of an object. Even if a new edition is created in the future, auditing will still be applied to the new edition.

For example, we create the view vw_IRIR in two different editions:

SQL> alter session set edition=IR_EDITION1;
Session altered
SQL> CREATE EDITIONING VIEW vw_IRIR AS select * from usef.tbl1;
View created
SQL> alter session set edition=IR_EDITION2;
Session altered
SQL> CREATE OR REPLACE EDITIONING VIEW vw_IRIR AS select owner from usef.tbl1;
View created
SQL> select object_name, edition_name from user_objects_ae where object_name='VW_IRIR';
OBJECT_NAM EDITION_NAME
---------- --------------
VW_IRIR    IR_EDITION1
VW_IRIR    IR_EDITION2

By creating the following audit policy, if the view vw_IRIR is accessed in any edition, auditing will be performed:

SQL> alter session set edition=IR_EDITION1;
Session altered
SQL> create audit policy audit_editions_1 actions select on usef.VW_IRIR;
Done
SQL> audit policy audit_editions_1;
Audit succeeded

To test this, we run the following query:

SQL> alter session set edition=IR_EDITION1;
Session altered
SQL> select count(*) from usef.VW_IRIR;
  COUNT(*)
----------
    377160
SQL> select a.ACTION_NAME,a.OBJECT_NAME,a.OBJECT_EDITION from UNIFIED_AUDIT_TRAIL a where OBJECT_NAME='VW_IRIR';
ACTION_NAM OBJECT_NAM OBJECT_EDITION
---------- ---------- --------------
SELECT     VW_IRIR    IR_EDITION1

Repeating the query in another edition is also audited:

SQL> alter session set edition=IR_EDITION2;
Session altered
SQL> select count(*) from usef.VW_IRIR;
  COUNT(*)
----------
    377160
SQL> select a.ACTION_NAME,a.OBJECT_NAME,a.OBJECT_EDITION from UNIFIED_AUDIT_TRAIL a where OBJECT_NAME='VW_IRIR';
ACTION_NAM OBJECT_NAM OBJECT_EDITION
---------- ---------- --------------
SELECT     VW_IRIR    IR_EDITION1
SELECT     VW_IRIR    IR_EDITION2

Auditing will also work for the view VW_IRIR in a newly created edition:

SQL> create edition IR_EDITION3;
Done
SQL> alter session set edition=IR_EDITION3;
Session altered
SQL> CREATE OR REPLACE EDITIONING VIEW vw_IRIR AS select object_id from usef.tbl1;
View created
SQL> select count(*) from vw_IRIR;
  COUNT(*)
----------
    377160

SQL> select a.ACTION_NAME,a.OBJECT_NAME,a.OBJECT_EDITION from UNIFIED_AUDIT_TRAIL a where OBJECT_NAME='VW_IRIR';
ACTION_NAM OBJECT_NAM OBJECT_EDITION
---------- ---------- --------------
SELECT     VW_IRIR    IR_EDITION1
SELECT     VW_IRIR    IR_EDITION2
SELECT     VW_IRIR    IR_EDITION3