【怎么办】003 如何加强Oracle数据库安全--监控数据导入导出操作

参考：数据库审计功能https://www.oracle.com/technetwork/cn/database/security/index-085292-zhs.html

--1.创建测试用户和测试表
SQL> create user test identified by test;SQL> grant connect,resource to test;SQL> conn test/testSQL> create table t1 (a number);
--2.创建用于保存监测结果的表
SQL> conn / as sysdbaConnected.SQL> CREATE TABLE system.logon_audit_table     (       logon_timestamp  DATE,       logoff_timestamp DATE,       sid              NUMBER,       serial#          NUMBER,       username         VARCHAR2(30),       machine          VARCHAR2(64),       program          VARCHAR2(64),       osuserid         VARCHAR2(30),       unique_sid       VARCHAR2(24))    / 
--3.创建触发器（Trigger）
--创建logon触发器（Trigger）SQL>  CREATE OR REPLACE TRIGGER logonauditing AFTER LOGON ON database  DECLARE   v_machine      VARCHAR2(64);   v_program      VARCHAR2(64);   v_unique_sid   VARCHAR2(24);   v_osuserid     VARCHAR2(30);   v_sid          NUMBER(4);   v_serial       NUMBER(4);   CURSOR c1 IS    SELECT sid, serial#, osuser, machine, program      FROM v$session     WHERE serial# != 1       AND audsid = USERENV('sessionid')       AND logon_time = (SELECT MAX(logon_time) FROM v$session                          WHERE audsid = USERENV('sessionid'))                          AND (UPPER(program) LIKE 'EXP%'                               OR UPPER(program) LIKE 'IMP%')  ;
  BEGIN    OPEN c1;    FETCH c1 INTO v_sid, v_serial, v_osuserid, v_machine, v_program;    IF c1%FOUND THEN       v_unique_sid := DBMS_SESSION.UNIQUE_SESSION_ID;       INSERT INTO system.logon_audit_table VALUES ( sysdate, null, v_sid,           v_serial, user, v_machine, v_program, v_osuserid, v_unique_sid);    END IF;    CLOSE c1;   END;    /
--创建logoff触发器（Trigger）SQL> CREATE OR REPLACE TRIGGER logoffauditing BEFORE LOGOFF ON database DECLARE  v_machine      VARCHAR2(64);  v_program      VARCHAR2(64);  v_unique_sid   VARCHAR2(24);  v_osuserid     VARCHAR2(30);  v_sid          NUMBER(4);  v_serial       NUMBER(4);

  CURSOR c1 IS    SELECT sid, serial#, osuser, machine, program      FROM v$session     WHERE serial# != 1       AND audsid = USERENV('sessionid')       AND status = 'ACTIVE'       AND (UPPER(program) LIKE 'EXP%' OR UPPER(program) LIKE 'IMP%')  ;

 BEGIN   OPEN c1;   FETCH c1 INTO v_sid, v_serial, v_osuserid, v_machine, v_program;   IF c1%FOUND THEN      v_unique_sid := DBMS_SESSION.UNIQUE_SESSION_ID;      UPDATE system.logon_audit_table SET logoff_timestamp=sysdate        WHERE unique_sid = v_unique_sid;   END IF;   CLOSE c1; END;/ 
--创建同名方便操作SQL> CREATE PUBLIC SYNONYM logon_audit_table FOR system.logon_audit_table;
--4.验证触发器
SQL> !exp test/test FILE=test.dmp OWNER=test.... exporting statisticsExport terminated successfully without warnings.
--5. 确认监测结果
SQL> conn / as sysdbaSQL> set lines 300SQL> select * from logon_audit_table;
LOGON_TIM LOGOFF_TI        SID    SERIAL# USERNAME   MACHINE   PROGRAM                         OSUSERID   UNIQUE_SID--------- --------- ---------- ---------- ---------- -------   ------------------------------- --------- -------------30-APR-20 30-APR-20         47       1215 TEST       <hostname>   exp@<hostname> (TNS V1-V3)    oracle    002F04BF0001
SQL>--6. 根据需要删除触发器和测试表
SQL>CONNECT / AS SYSDBASQL>DROP TRIGGER logonauditing;SQL>DROP TRIGGER logoffauditing;SQL>DROP PUBLIC SYNONYM logon_audit_table;SQL>DROP TABLE system.logon_audit_table;SQL>DROP user test cascade;

参考：MOS文档How to Audit the Export and Import Sessions in the Database (Doc ID 278852.1)

--1. 设置审计选项SQL> conn / as sysdba--将审计结果输出到OS上SQL> alter system set audit_trail=os scope=spfile;--审计dba的操作SQL> alter system set audit_sys_operations=true scope=spfile;SQL> shutdown immediateSQL> startup
--2. 确认审计设置SQL> show parameter audit
NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------audit_file_dest                      string      <DIRECTORY>/adumpaudit_sys_operations                 boolean     TRUEaudit_syslog_level                   stringaudit_trail                          string      OS
--3. 设置审计DBMS_DATAPUMP程序包SQL> audit execute on SYS.DBMS_DATAPUMP ;
--4. 执行数据泵命令，验证审计SQL> create directory exp_test as '<DIRECTORY>';SQL> grant all on directory exp_test  to public;SQL> !expdp test/test directory=exp_test schemas=test;...Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Apr 28 01:26:57 2020 elapsed 0 00:00:22
--5. 关闭审计SQL> noaudit execute on SYS.DBMS_DATAPUMP ;SQL> alter system set audit_sys_operations=false scope=spfile;

参考：MOS文档How to audit Data Pump Jobs? (Doc ID 557894.1)

ACTION COMPONENT=DATAPUMP ALL

--1. 创建审计策略并启用
SQL> CREATE AUDIT POLICY audit_datapump ACTIONS COMPONENT=DATAPUMP ALL;SQL> AUDIT POLICY audit_dp_all_policy BY test;
--2. 执行数据泵命令，验证审计SQL> create directory exp_test as '<DIRECTORY>';SQL> grant all on directory exp_test  to public;SQL> !expdp test/test directory=exp_test schemas=test;...Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Apr 28 01:26:57 2020 elapsed 0 00:00:22
--3. 确认审计结果
SQL> EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
SQL> SET LINESIZE 200SQL> COLUMN event_timestamp FORMAT A30SQL> COLUMN dp_text_parameters1 FORMAT A30SQL> COLUMN dp_boolean_parameters1 FORMAT A30SQL> SELECT event_timestamp,dp_text_parameters1,dp_boolean_parameters1     FROM   unified_audit_trail     WHERE  audit_type = 'Datapump';

--4. 根据需要禁用和删除审计策略SQL> NOAUDIT POLICY audit_dp_all_policy BY test; SQL> DROP AUDIT POLICY audit_datapump;