1. Is anyone using the 12c unified audit trail to track changes on a table as say opposed a flashback archive or a trigger based solution to an self built audit table? Any report on performance or whether its suitable? I have a number of tables that have trigger based solutions and some with Flashback tables but looking at the Unified audit trail as a possible solution to auditing requirements. Traditionally the old style audit trail would be a bit clunky and might only turn it on for a set period of auditing.
2. Im testing the trail and I cant get data to go into the trail, I know the trail is on, I can see the policy is created, other entries get to the trail but not for an update or insert policy I've created. , any idea what am I missing, see below.
SQL> select value from v$option where parameter = 'Unified Auditing';
VALUE
----------------------------------------------------------------
TRUE
SQL> create table t_aud_test
2 as select * from dba_objects
3 where rownum < 2;
Table created.
SQL>
SQL> CREATE AUDIT POLICY test_audit_policy
2 ACTIONS INSERT ON t_aud_test,
3 UPDATE ON t_aud_test;
Audit policy created.
SQL>
SQL>
SQL> -- enable the policy
SQL> AUDIT POLICY test_audit_policy;
Audit succeeded.
SQL>
SQL> -- verify the policy is enabled
SQL> set line 180
SQL> col user_name format a20
SQL> col policy_name format a20
SQL> select * from audit_unified_enabled_policies
2 where policy_name = 'TEST_AUDIT_POLICY';
USER_NAME POLICY_NAME ENABLED_ SUC FAI
-------------------- -------------------- -------- --- ---
ALL USERS TEST_AUDIT_POLICY BY YES YES
SQL>
SQL> -- verify the policy has update and insert
SQL> set line 180
SQL> col audit_option format a20
SQL> col object_name format a20
SQL> select policy_name, audit_option, object_name, object_type
2 from AUDIT_UNIFIED_POLICIES
3 where policy_name in ('TEST_AUDIT_POLICY');
POLICY_NAME AUDIT_OPTION OBJECT_NAME OBJECT_TYPE
-------------------- -------------------- -------------------- -----------------------
TEST_AUDIT_POLICY INSERT T_AUD_TEST TABLE
TEST_AUDIT_POLICY UPDATE T_AUD_TEST TABLE
SQL>
-- insert some data
SQL> insert into t_aud_test
2 (select * from dba_objects where rownum < 10);
9 rows created.
SQL> commit;
Commit complete.
-- Flush the trail to get any data out
SQL> EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;
PL/SQL procedure successfully completed.
SQL>
SQL> -- and check the trail for any entries
SQL> col system_privilege_used format a20
SQL> col object_name format a20
SQL> col action_name format a20
SQL> col event_timestamp format a30
SQL>
SQL> select event_timestamp, action_name, system_privilege_used, object_name
2 from unified_audit_trail
3 where object_name = 'TEST_AUDIT_POLICY'
4 or object_name = 'T_AUD_TEST'
5 order by event_timestamp;
EVENT_TIMESTAMP ACTION_NAME SYSTEM_PRIVILEGE_USE OBJECT_NAME
------------------------------ -------------------- -------------------- --------------------
27-FEB-17 05.01.06.821664 PM CREATE AUDIT POLICY AUDIT SYSTEM TEST_AUDIT_POLICY
27-FEB-17 05.01.23.172419 PM AUDIT AUDIT SYSTEM TEST_AUDIT_POLICY
So we have the policy creation and the enable of the policy but nothing else. The insert isn't getting in, what could I be missing?