2012年06月15日

Oracle SEでログオントリガを使ってログイン履歴を取得する

環境:Oracle Standard Edition 10.2.0.x

Oracleの監査機能(Audit_trail)を使ってデータベースへのログイン履歴を残すことができるが、audit_trail=db としてデータベースに履歴を残す場合はsystem表領域が膨らんでしまいメンテナンス性がよろしくなく、audit_trail=os(またはxml) とした場合はテキストベースとなってしまい検索が非常に面倒である。
audit_trail=dbとして、履歴が格納されるテーブルをsystem表領域以外にできれば問題解決であるが(実際には可能)いろいろと面倒だしsystem表領域をイジくるのは嫌な感じである。

そこでオラクルの「データベーストリガ」の「AFTER LOGON」を利用してログイン履歴を取得する方法をご紹介する。

注意点として、履歴を残すテーブルの表領域がいっぱいになったりすると誰もログインができなくなってしまうという「諸刃の剣」であることを認識頂きたい。

ログ格納用のテーブルを作る。スキーマは管理専用のスキーマとしたほうがいい。
CREATE TABLE HOGEHOGE.SESSION_AUDIT
(
LOG_DATE TIMESTAMP(6) DEFAULT SYSTIMESTAMP,
EVENT_NAME VARCHAR2(32),
LOGON_USER VARCHAR2(32),
TERMINAL VARCHAR2(32) DEFAULT USERENV('TERMINAL'),
SESSION_USER VARCHAR2(32) DEFAULT SYS_CONTEXT('USERENV','SESSION_USER'),
OS_USER VARCHAR2(64) DEFAULT SYS_CONTEXT('USERENV','OS_USER'),
HOST VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','HOST'),
IP VARCHAR2(32) DEFAULT SYS_CONTEXT('USERENV','IP_ADDRESS'),
SESSION_ID VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','SESSIONID'),
SID VARCHAR2(256),
SERIAL# VARCHAR2(256),
OBJECT_TYPE VARCHAR2(256),
OBJECT_NAME VARCHAR2(256),
YMD VARCHAR2(8) DEFAULT TO_CHAR(SYSDATE,'YYYYMMDD'),
HH24 VARCHAR2(2) DEFAULT TO_CHAR(SYSDATE,'HH24')
)
PCTFREE 10
MAXTRANS 255
TABLESPACE TABLESPACE_XXXX
STORAGE(INITIAL 100M MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT)
NOCACHE
LOGGING
/


次にトリガーを作成する。
CREATE OR REPLACE TRIGGER LOGON_AUDIT
AFTER LOGON
ON DATABASE
DECLARE
V_SID VARCHAR2(255);
V_SERIAL# VARCHAR2(255);
BEGIN
IF USER NOT IN ('SYSMAN','DBSNMP') THEN
SELECT SID, SERIAL# INTO V_SID,V_SERIAL# FROM v$SESSION
WHERE
AUDSID = SYS_CONTEXT('USERENV','SESSIONID')
AND SID = (SELECT DISTINCT SID FROM v$MYSTAT)
;
INSERT INTO HOGEHOGE.SESSION_AUDIT(EVENT_NAME, LOGON_USER,SID,SERIAL#)
VALUES (ORA_SYSEVENT, USER,V_SID,V_SERIAL#);
COMMIT;
END IF;
END;


SYSMANとDBSNMPのログも取得すると、かなりの数のログが出てしまうので上記トリガーでは省いている。
SYSMANとDBSNMPを使ってログインし悪用する者がいる可能性もあるので、心配な人は全部取得するようにしたほうがいいかも。
ログオフの情報も取得できるのだが、時間切れ(セッションタイムアウト)の場合はうまく取得できないようだし、1分程度でログイン・ログオフを繰り返すような場合もログオフの記録が漏れたりすることがあったのであまり信憑性がないかもしれない。

タグ:Oracle
posted by まきすけ at 19:36 | Comment(0) | TrackBack(0) | Linuxとか