Oracle 12c 新特性 --- 新增对数据泵操作的审计跟踪

概念

Oracle Data Pump commands can now be audited. This provides more complete auditing of operations performed against the database.
 
现在可以对Oracle数据泵的命令进行审计。这为对数据库执行的操作提供了更完整的审计。
 
About Auditing Oracle Data Pump Events
 
The CREATE AUDIT POLICY statement COMPONENT clause must be set to DATAPUMP to create Oracle Data Pump unified audit policies.
 
You can audit Data Pump export (expdp) and import (impdp) operations.
 
As with all unified auditing, you must have the AUDIT_ADMIN role before you can audit Oracle Data Pump events.
 
To access the audit trail, query the UNIFIED_AUDIT_TRAIL data dictionary view. The Data Pump-specific columns in this view begin with DP_.
 
创建审计策略语句组件子句必须设置为DATAPUMP创建Oracle数据泵统一审计策略。
 
您可以审核数据泵导出(expdp)和导入(impdp)操作。
 
与所有的统一审计一样，在审计Oracle数据泵事件之前，必须有AUDIT_ADMIN角色。
 
要访问审计跟踪，请查询UNIFIED_AUDIT_TRAIL数据字典视图。这个视图中的数据泵特定的列从DP_开始。
 
Configuring a Unified Audit Policy for Oracle Data Pump                             
The ACTIONS COMPONENT clause in the CREATE AUDIT POLICY statement can be used to create an Oracle Data Pump event unified audit policy.
 
    Use the following syntax to create a unified audit policy for Oracle Data Pump:
 
        CREATE AUDIT POLICY policy_name
        ACTIONS COMPONENT=DATAPUMP { EXPORT | IMPORT | ALL };

实验

1） 当该策略应用于用户时，他们的数据泵工作将出现在审计跟踪中。以下政策审核所有数据泵操作。该策略应用于test用户。
SQL> conn test/test@pdbcndba
Connected.
 
SQL>  CREATE AUDIT POLICY audit_dp_all_policy ACTIONS COMPONENT=DATAPUMP ALL;
 
Audit policy created.
 
SQL> AUDIT POLICY audit_dp_all_policy BY test; 
 
Audit succeeded.
2）运行以下数据泵命令
[oracle@host1 ~]$ expdp test/test@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp logfile=expdat.log tables=leo2 LOGTIME=ALL
 
Export: Release 12.1.0.2.0 - Production on Sat Aug 5 18:03:39 2017
 
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
05-AUG-17 18:03:41.234: Starting "TEST"."SYS_EXPORT_TABLE_01":  test/********@pdbcndba DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp logfile=expdat.log tables=leo2 LOGTIME=ALL 
05-AUG-17 18:03:41.572: Estimate in progress using BLOCKS method...
05-AUG-17 18:03:42.228: Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
05-AUG-17 18:03:42.261: Total estimation using BLOCKS method: 72 MB
05-AUG-17 18:03:44.340: Processing object type TABLE_EXPORT/TABLE/TABLE
05-AUG-17 18:03:44.750: Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
05-AUG-17 18:03:44.787: Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
05-AUG-17 18:03:48.157: . . exported "TEST"."LEO2"                               831.3 KB    6886 rows
05-AUG-17 18:03:48.358: Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
05-AUG-17 18:03:48.358: ******************************************************************************
05-AUG-17 18:03:48.359: Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
05-AUG-17 18:03:48.364:   /backup/expdat.dmp
05-AUG-17 18:03:48.376: Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Sat Aug 5 18:03:48 2017 elapsed 0 00:00:08
 
3）检查审计跟踪显示数据泵工作被审计。
SQL> conn test/test@pdbcndba
Connected.
 
SQL> EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
 
PL/SQL procedure successfully completed.
 
SQL> SET LINESIZE 200
SQL> COLUMN event_timestamp FORMAT A30
SQL> COLUMN dp_text_parameters1 FORMAT A30
SQL> COLUMN dp_boolean_parameters1 FORMAT A30
SQL> SELECT event_timestamp,
       dp_text_parameters1,
       dp_boolean_parameters1
FROM   unified_audit_trail
WHERE  audit_type = 'Datapump';
 
EVENT_TIMESTAMP             DP_TEXT_PARAMETERS1        DP_BOOLEAN_PARAMETERS1
------------------------------  ------------------------------ ------------------------------
05-AUG-17 06.03.41.553994 PM    MASTER TABLE:  "TEST"."SYS_EXP MASTER_ONLY: FALSE, DATA_ONLY:
                ORT_TABLE_01" , JOB_TYPE: EXPO  FALSE, METADATA_ONLY: FALSE,
                RT, METADATA_JOB_MODE: TABLE_E DUMPFILE_PRESENT: TRUE, JOB_RE
                XPORT, JOB VERSION: 12.1.0.2.0 STARTED: FALSE
                    , ACCESS METHOD: AUTOMATIC, DA
                TA OPTIONS: 0, DUMPER DIRECTOR
                Y: NULL REMOTE LINK: NULL, TA
                BLE EXISTS: NULL, PARTITION OP
                TIONS: NONE