Amsterdam DBA notes: 12

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

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?

Amsterdam DBA notes

Wednesday, March 18, 2015

12c Unified Auditing expdp example

Summary

Details - datapump example