標準監査とトリガーを使用して監査した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ファイルの削除だったりは気をつける。
- 参考