標準監査とトリガーを使用して監査したSQL文を１つのファイルに集約する
ざっくりこんなことがしたい
指定したユーザのみ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ファイルの削除だったりは気をつける。
- 参考
