audit truncate table (unified)

BY: Brian Fitzgerald

[oracle@waipio1 wailua20 AUDIT]$ cat AUDIT.TRUNCATE.TABLE.unified.SQL
COLUMN object_schema format a30
COLUMN object_name format a30
COLUMN action_name format a30
SET linesize 200
SET trimspool ON

DROP USER brian CASCADE;
CREATE USER brian IDENTIFIED BY brian;
GRANT CREATE SESSION TO brian;
GRANT CREATE TABLE TO brian;
CONNECT brian/brian;
CREATE TABLE t ( n NUMBER );
TRUNCATE TABLE t;
CONNECT / AS sysdba
EXEC dbms_audit_mgmt.flush_unified_audit_trail;

SELECT object_schema, object_name, action_name
FROM unified_audit_trail
WHERE dbusername = 'BRIAN'
AND action_name = 'TRUNCATE TABLE'
AND event_timestamp > SYSTIMESTAMP - 5/86400;

CREATE AUDIT policy ap actions TRUNCATE TABLE;
AUDIT policy ap;

CONNECT brian/brian

TRUNCATE TABLE t;

CONNECT / AS sysdba

EXEC dbms_audit_mgmt.flush_unified_audit_trail;

SELECT object_schema, object_name, action_name
FROM unified_audit_trail
WHERE dbusername = 'BRIAN'
AND action_name = 'TRUNCATE TABLE'
AND event_timestamp > SYSTIMESTAMP - 5/86400;

NOAUDIT policy ap;
DROP AUDIT policy ap;

quit
[oracle@waipio1 wailua20 AUDIT]$ sqlplus / AS sysdba @ AUDIT.TRUNCATE.TABLE.unified.SQL

SQL*Plus: RELEASE 12.1.0.1.0 Production ON Wed Jun 11 20:55:43 2014

Copyright (c) 1982, 2013, Oracle.  ALL rights reserved.


Connected TO:
Oracle DATABASE 12c Enterprise Edition RELEASE 12.1.0.1.0 - 64bit Production
WITH the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
AND REAL Application Testing options


USER dropped.


USER created.


GRANT succeeded.


GRANT succeeded.

Connected.

TABLE created.


TABLE truncated.

Connected.

PL/SQL PROCEDURE successfully completed.


no ROWS selected


AUDIT policy created.


AUDIT succeeded.

Connected.

TABLE truncated.

Connected.

PL/SQL PROCEDURE successfully completed.


OBJECT_SCHEMA                  OBJECT_NAME                    ACTION_NAME
------------------------------ ------------------------------ ------------------------------
BRIAN                          T                              TRUNCATE TABLE


NOAUDIT succeeded.


AUDIT Policy dropped.