kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

標準監査とトリガーを使用して監査したSQL文を1つのファイルに集約する

ざっくりこんなことがしたい

指定したユーザのみSQL監査(例:KANSA)、KANSAユーザにはselect権限のみ
↓
ログインした段階でログイン情報をテキストに出力
↓
ログオフした段階でいままで実行したORAUSERユーザのオブジェクトへのクエリをテキストに出力
↓
それをfluentdで送信

監査設定

audit_trailの設定

alter system set audit_trail =xml, extended scope=spfile;
ORACLE再起動

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ------------------------------------------
audit_file_dest                      string      /opt/oracle/admin/orcl/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      XML, EXTENDED      

監査対象の専用ユーザ作成

  • めんどうなのでSELECT権限はオブジェクト単位でなく全テーブルで設定
CREATE USER kansa IDENTIFIED BY kansa DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CREATE SESSION TO kansa;
GRANT SELECT ANY TABLE TO kansa;

監査の有効化

  • めんどうなのでオブジェクト単位でなく全SQL監査するように設定
AUDIT ALL STATEMENTS BY kansa BY ACCESS WHENEVER SUCCESSFUL;

この時点で監査設定は有効になっているので、KANSAユーザでselectを行うとaudit_file_destに監査ファイルが出力されるが、セッションごとにファイルが作成される。 そのためflunetdで送るにはちょっとつらい。

[oracle@oracle-db ~]$ ls -ltr /opt/oracle/admin/orcl/adump
合計 624
-rw-r----- 1 oracle oinstall  5640  1月  8 10:26 2016 ORCL_ora_25545_20160108102622212388644963.xml
-rw-r----- 1 oracle oinstall  5640  1月  8 10:42 2016 ORCL_ora_25991_20160108104047170853890781.xml
-rw-r----- 1 oracle oinstall  1168  1月  8 11:25 2016 ORCL_ora_25956_20160108103944714927661437.xml
-rw-r----- 1 oracle oinstall   740  1月  8 11:40 2016 ORCL_ora_27032_20160108112550390602280905.xml
-rw-r----- 1 oracle oinstall   740  1月  8 11:42 2016 ORCL_ora_27407_20160108114221333527548223.xml
-rw-r----- 1 oracle oinstall   740  1月  8 11:44 2016 ORCL_ora_27466_20160108114426026375942714.xml
-rw-r----- 1 oracle oinstall  5590  1月  8 11:45 2016 ORCL_ora_27486_20160108114455128049772570.xml

flunetdで送るには一つのファイルに集約する必要があり、UTL_FILEを使用しログオントリガーとログオフトリガーで対応する。

トリガーの設定

集約するディレクトリを作成

CREATE DIRECTORY kansa_dir AS '/data/kansa';
GRANT READ ON DIRECTORY kansa_dir TO kansa ;
GRANT WRITE ON DIRECTORY kansa_dir TO kansa ;

ログオントリガーの作成

create or replace trigger LOGON_KANSA
after logon on KANSA.schema --KANSAユーザのみに作成
DECLARE 
  buff varchar2(400);
  vAudsid number;
  vSid number;
  vModl varchar2(64);
  FH   UTL_FILE.FILE_TYPE;
BEGIN
  vAudsid := SYS_CONTEXT('USERENV','SESSIONID');
  vSid  := SYS_CONTEXT('USERENV','SID');
  vModl := SYS_CONTEXT('USERENV','module');
  -- SQLPLUSのみログイン可能に設定
  IF (vModl not like 'sqlplus%') then
    RAISE_APPLICATION_ERROR(-20000,'Only SQLPLUS can logon user KANSA');
  END IF;
  --'log.txt'にログインした情報を出力
  FH := UTL_FILE.FOPEN('kansa_dir','log.txt','A');
  for cl in (select 
              'LOGON ' as status 
              ,to_char(s.LOGON_TIME,'YYMMDD HH24:MI:SS') as LOGON_TIME
              ,s.sid
              ,s.serial#
              ,s.AUDSID
              ,p.SPID
              ,p.PROGRAM
              ,vModl as module 
              ,s.machine
              from v$session s join V$PROCESS p on s.PADDR = p.ADDR
              where s.AUDSID=vAudsid 
              and s.SID=vSid
              ) loop
    buff :=  cl.LOGON_TIME || ' ' ||
             cl.AUDSID || ' ' ||
             cl.status || ' ' ||
             cl.SPID || ' '  || 
             cl.module || ' '  ||
             cl.machine;
    utl_file.put_line(FH,buff);
  end loop;
       utl_file.fclose(FH);
  EXCEPTION WHEN OTHERS THEN
       UTL_FILE.FCLOSE_ALL;
       RAISE ;
end;
/
  • ログインすると
[oracle@oracle-db ~]$  sqlplus kansa/kansa

SQL*Plus: Release 11.2.0.4.0 Production on 水 1月 13 13:24:08 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
に接続されました。
KANSA@oracle-db>
  • ログイン情報が出力される
[oracle@oracle-db kansa]$ tail -F  log.txt

160113 13:24:08 9912245 LOGON  5992 sqlplus@oracle-db (TNS V1-V3) oracle-db

ログオフトリガーの作成

create or replace trigger LOGOFF_KANSA
BEFORE  logoff on KANSA.schema --KANSAユーザのみに作成
DECLARE
  buff varchar2(400);
  vAudsid number;
  vSid number;
  vModl varchar2(64);
  FH   UTL_FILE.FILE_TYPE;
BEGIN
  FH := UTL_FILE.FOPEN('kansa_dir','log.txt','A');
  vAudsid := SYS_CONTEXT('USERENV','SESSIONID');
  vSid  := SYS_CONTEXT('USERENV','SID');
  vModl := SYS_CONTEXT('USERENV','module');
-- 今まで実行したSQLをテキストに出力
  for c in (select 
              to_char(EXTENDED_TIMESTAMP,'YYMMDD HH24:MI:SS') as time,
              nvl(SQL_TEXT,'NULL') as SQL_TEXT,
              nvl(SQL_BIND,'NULL') as SQL_BIND
              from V$XML_AUDIT_TRAIL 
              where SESSION_ID=vAudsid 
              and DB_USER='KANSA' 
              and OBJECT_SCHEMA = 'ORAUSER' 
              order by STATEMENTID
          ) loop
    if (c.SQL_TEXT <> 'NULL') then
      utl_file.put_line(FH, c.time || ' ' || vAudsid || ' ' || c.SQL_TEXT);
    end if;
    if (c.SQL_BIND <> 'NULL') then
      utl_file.put_line(FH,  '                ' || vAudsid || ' SQL_BIND: ' || c.SQL_BIND);
    end if;
  end loop;
--ログオフ情報をテキストに出力
  for cl in (select 
              'LOGOFF' as status 
              ,to_char(sysdate,'YYMMDD HH24:MI:SS') as LOGOFF_TIME
              ,s.sid
              ,s.serial#
              ,s.AUDSID
              ,p.SPID
              ,p.PROGRAM
              ,vModl as module 
              from v$session s join V$PROCESS p on s.PADDR = p.ADDR
              where s.AUDSID=vAudsid 
              and s.SID=vSid
              ) loop
       buff := cl.LOGOFF_TIME  || ' ' ||
               cl.AUDSID || ' ' || 
               cl.status|| ' ' || 
               cl.SPID || ' '  
               || cl.module ;
       utl_file.put_line(FH,buff);
  end loop;
       utl_file.fclose(FH);
  EXCEPTION WHEN OTHERS THEN
       UTL_FILE.FCLOSE_ALL;
       RAISE ;
end;
/
  • いくつかクエリを実行して、ログオフさせる
KANSA@oracle-db>select * from TEST_TABLE where rownum < 2;
KANSA@oracle-db>variable bind number;
KANSA@oracle-db>exec bind := 1;
KANSA@oracle-db>select * from TEST_TABLE where id = :bind;
KANSA@oracle-db>exit
  • ログオフと同時にクエリ、バインド変数とログオフ情報が出力される
[oracle@oracle-db kansa]$ tail -F  log.txt

160113 13:24:08 9912245 LOGON  5992 sqlplus@oracle-db (TNS V1-V3) oracle-db
160113 13:41:18 9912245 select * from TEST_TABLE where rownum < 2
160113 13:43:15 9912245 select * from TEST_TABLE where id = :bind
                9912245 SQL_BIND:  #1(1):1
160113 13:43:28 9912245 LOGOFF 5992 SQL*Plus

あとは、よしなにfluentdでlog.txtを監視させてslackやらなんやらに送信してあげれば監査可能。
log.txtのローテーションだったり、監査xmlファイルの削除だったりは気をつける。

  • 参考

第32回 標準監査の基本的な使い方