【怎么办】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/test
SQL> create table t1 (a number);


--2.创建用于保存监测结果的表


SQL> conn  as sysdba
Connected.
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 statistics
Export terminated successfully without warnings.


--5. 确认监测结果


SQL> conn  as sysdba
SQL> set lines 300
SQL> 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 SYSDBA
SQL>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 immediate
SQL> startup


--2. 确认审计设置
SQL> show parameter audit


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      <DIRECTORY>/adump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_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 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';




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