expdp操作审计测试

expdp操作审计测试

出于管理的需要，特别是在管理比较粗放，部门交叉复杂的系统，往往数据导出操作比较随意，开发或者相关业务管理人员都有权限，这样对于数据库的安全管理十分不利

为了监控expdp行为，我们可以通过两种方式来监控和审计该行为，以确认是否是合法用户的合法操作。

[oracle@rac1 trace]$ expdp "'/ as sysdba'" directory=DATA_PUMP_DIR dumpfile=scott4.dmp schemas=scott;

Export: Release 12.2.0.1.0 - Production on Tue Mar 30 15:10:21 2021

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=scott4.dmp schemas=scott
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."B" 8.937 MB 71267 rows
. . exported "SCOTT"."A" 8.937 MB 71266 rows
. . exported "SCOTT"."EMP" 8.773 KB 14 rows
. . exported "SCOTT"."EMP2" 8.773 KB 14 rows
. . exported "SCOTT"."DEPT" 6.023 KB 4 rows
. . exported "SCOTT"."SALGRADE" 5.953 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/oracle/db/base/product/12.2/rdbms/log/scott4.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Mar 30 15:11:27 2021 elapsed 0 00:01:04

方式1：在告警日志中，记录了job的执行信息
DM00 started with pid=70, OS id=957, job SYS.SYS_EXPORT_SCHEMA_01
2021-03-30T14:42:24.060247+08:00

DW00 started with pid=74, OS id=975, wid=1, job SYS.SYS_EXPORT_SCHEMA_01
2021-03-30T14:43:03.439915+08:00

方法2：通过字典动态监控expdp行为，可以将该监视功能纳入监控管理平台，以判断是否是合法行为。
SQL> select job_name,operation,state from dba_datapump_jobs

JOB_NAME OPERATION STATE
-------------------- -------------------- --------------------
SYS_EXPORT_SCHEMA_01 EXPORT DEFINING

SQL> select job_name,operation,state from dba_datapump_jobs


JOB_NAME OPERATION STATE
-------------------- -------------------- --------------------
SYS_EXPORT_SCHEMA_01 EXPORT EXECUTING


SQL> /

JOB_NAME OPERATION STATE
-------------------- -------------------- --------------------
SYS_EXPORT_SCHEMA_01 EXPORT NOT RUNNING

此时可以监控operation字段，其中state字段显示了该job的状态，初始阶段，执行阶段和结束阶段。