12c Unified Auditing expdp example

Summary

Oracle 12c introduces "unified all" so all the various audit activities :

• Ability to Audit Any Role.
• Auditing Application Context Values.
• Auditing Oracle Database Real Application Security Events.
• Auditing Oracle Recovery Manager Events.
• Auditing Oracle Database Vault Events.
• Auditing Oracle Label Security Events.
• Auditing Oracle Data Mining Events.
• Auditing Oracle Data Pump Events.
• Auditing Oracle SQL*Loader Direct Load Path Events.
• Moving Operating System Audit Records into the Unified Audit Trail. https://blogs.oracle.com/imc/entry/oracle_database_12c_new_unified

can be viewed in the single sys.unified_audit_trail table.

As above you can now audit datapump operations, below I show how to set this up and some sample output from the new sys.unified_audit_trail table.

Details - datapump example

I was initially following:  http://oracle-base.com/articles/12c/data-pump-enhancements-12cr1.php

So I set up my new audit policy and applied to the scott

SQL> CONN / AS SYSDBA

Connected.

SQL> CREATE AUDIT POLICY audit_dp_all_policy ACTIONS COMPONENT=DATAPUMP ALL;

Audit policy created.

SQL> AUDIT POLICY audit_dp_all_policy BY scott;

Audit succeeded.

SQL> EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

PL/SQL procedure successfully completed.

and kicked off my export:

[oracle@ora12c66 ~]$ expdp system/qwe123@ora12c66/orcl12c tables=scott.emp directory=TEMP_DIR

Export: Release 12.1.0.1.0 - Production on Wed Mar 18 20:20:52 2015

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/********@ora12c66/orcl12c tables=scott.emp directory=TEMP_DIR

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

. . exported "SCOTT"."EMP"                               8.742 KB      14 rows

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

 /tmp/expdat.dmp

Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Wed Mar 18 20:21:13 2015 elapsed 0 00:00:20

and initially this failed to generate a unified_audit_trail record:

SET LINESIZE 200

COLUMN event_timestamp FORMAT A30

COLUMN dp_text_parameters1 FORMAT A30

COLUMN dp_boolean_parameters1 FORMAT A30

SQL> SELECT event_timestamp, dp_text_parameters1, dp_boolean_parameters1 FROM  sys.unified_audit_trail WHERE  audit_type = 'Datapump';

no rows selected

my problem was that I was only auditing on the user_name scott (i.e. not the scott schema objects)

SQL> col USER_NAME form a30

SQL> col POLICY_NAME form a30

SQL> select * from AUDIT_UNIFIED_ENABLED_POLICIES where POLICY_NAME like '%DP%';

USER_NAME       POLICY_NAME      ENABLED_ SUC FAI

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

SCOTT       AUDIT_DP_ALL_POLICY      BY       YES YES

i.e. not the user_name system and after

SQL> AUDIT POLICY audit_dp_all_policy BY system;

Audit succeeded.

SQL> select * from AUDIT_UNIFIED_ENABLED_POLICIES where POLICY_NAME like '%DP%';

USER_NAME       POLICY_NAME      ENABLED_ SUC FAI

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

SCOTT       AUDIT_DP_ALL_POLICY      BY       YES YES

SYSTEM       AUDIT_DP_ALL_POLICY      BY       YES YES

and rerun my expdp:

SQL> SELECT event_timestamp, dp_text_parameters1, dp_boolean_parameters1 FROM  sys.unified_audit_trail WHERE  audit_type = 'Datapump';

EVENT_TIMESTAMP       DP_TEXT_PARAMETERS1      DP_BOOLEAN_PARAMETERS1

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

18-MAR-15 08.28.36.157025 PM   MASTER TABLE:  "SYSTEM"."SYS_E MASTER_ONLY: FALSE, DATA_ONLY:

      XPORT_TABLE_01" , JOB_TYPE: EX  FALSE, METADATA_ONLY: FALSE,

      PORT, METADATA_JOB_MODE: TABLE DUMPFILE_PRESENT: TRUE, JOB_RE

      _EXPORT, JOB VERSION: 12.1.0.0 STARTED: FALSE

      .0, ACCESS METHOD: AUTOMATIC,

      DATA OPTIONS: 0, DUMPER DIRECT

      ORY: NULL  REMOTE LINK: NULL,

      TABLE EXISTS: NULL, PARTITION

      OPTIONS: NONE

manually making this a bit more readable:

EVENT_TIMESTAMP
---------------------------------------------------------------------------
18-MAR-15 08.28.36.157025 PM

DP_TEXT_PARAMETERS1
--------------------------------------------------------------------------------
MASTER TABLE:  "SYSTEM"."SYS_EXPORT_TABLE_01" , JOB_TYPE: EXPORT, METADATA_JOB_M
ODE: TABLE_EXPORT, JOB VERSION: 12.1.0.0.0, ACCESS METHOD: AUTOMATIC, DATA OPTIO
NS: 0, DUMPER DIRECTORY: NULL  REMOTE LINK: NULL, TABLE EXISTS: NULL, PARTITION
OPTIONS: NONE

DP_BOOLEAN_PARAMETERS1
--------------------------------------------------------------------------------

MASTER_ONLY: FALSE, DATA_ONLY: FALSE, METADATA_ONLY: FALSE, DUMPFILE_PRESENT: TR UE, JOB_RESTARTED: FALSE

alternatively I could have selected all users:

SQL> AUDIT POLICY audit_dp_all_policy;

Audit succeeded.

SQL> select * from AUDIT_UNIFIED_ENABLED_POLICIES where POLICY_NAME like '%DP%';

USER_NAME       POLICY_NAME      ENABLED_ SUC FAI

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

SCOTT       AUDIT_DP_ALL_POLICY      BY       YES YES

SYSTEM       AUDIT_DP_ALL_POLICY      BY       YES YES

ALL USERS       AUDIT_DP_ALL_POLICY      BY       YES YES

Strangle it appears that my

SQL> select value from v$option where PARAMETER = 'Unified Auditing';

VALUE

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

FALSE

I guess this refers to some more global / default level auditing?