로그인 트리거 생성 스크립트

해당 DB에 트리거를 생성하면 DB에 접속하는 세션들에 대한 접속정보를 테이블에 저장할 수 있습니다.

트리거 내의 아래 절에 원하는 스키마를 넣으면 원하는 스키마만 접속했을때 정보를 기록할 수 있습니다.

IF SYS_CONTEXT('USERENV','SESSION_USER') IN ('DBADM','DBMGNT','HIWARE','KMDAPP','DBMON','KMDOWN','SERVERI','SHERPA','SQLCANVASS') THEN 

1.테이블 생성

drop table YSBAE.logoninfo purge;
CREATE TABLE YSBAE.logoninfo TABLESPACE TS_KMDD01
AS
select
sysdate                                login
,sys_context('USERENV','IP_ADDRESS')   IP_ADDRESS
,sys_context('USERENV','SERVER_HOST')  SERVER_HOST
,sys_context('USERENV','HOST')         HOST
,sys_context('USERENV','OS_USER')      OS_USER
,sys_context('USERENV','SESSION_USER')   SESSION_USER
,sys_context('USERENV','CURRENT_USER')   CURRENT_USER
,sys_context('USERENV','CURRENT_SCHEMA') CURRENT_SCHEMA
,sys_context('USERENV','SID')          SID
,sys_context('USERENV','CLIENT_INFO')  CLIENT_INFO
,sys_context('USERENV','MODULE')       MODULE
,sys_context('USERENV','SERVICE_NAME') SERVICE_NAME
,sys_context('USERENV','TERMINAL')     TERMINAL
from dual
where 1=2;


2.트리거 생성
DROP TRIGGER YSBAE.LOGON;
CREATE OR REPLACE TRIGGER YSBAE.LOGON
AFTER LOGON ON DATABASE
DECLARE
pragma autonomous_transaction;
BEGIN
IF SYS_CONTEXT('USERENV','SESSION_USER') IN ('DBADM','DBMGNT','HIWARE','KMDAPP','DBMON','KMDOWN','SERVERI','SHERPA','SQLCANVASS') THEN
INSERT INTO YSBAE.logoninfo
select 
sysdate                                     login
    ,sys_context('USERENV','IP_ADDRESS')   IP_ADDRESS
    ,sys_context('USERENV','SERVER_HOST')  SERVER_HOST
    ,sys_context('USERENV','HOST')         HOST
    ,sys_context('USERENV','OS_USER')      OS_USER
    ,sys_context('USERENV','SESSION_USER')   SESSION_USER
    ,sys_context('USERENV','CURRENT_USER')   CURRENT_USER
    ,sys_context('USERENV','CURRENT_SCHEMA') CURRENT_SCHEMA
    ,sys_context('USERENV','SID')          SID
    ,sys_context('USERENV','CLIENT_INFO')  CLIENT_INFO
    ,sys_context('USERENV','MODULE')       MODULE
    ,sys_context('USERENV','SERVICE_NAME') SERVICE_NAME
    ,sys_context('USERENV','TERMINAL')     TERMINAL
	from dual;
COMMIT;
END IF;
END;
/

트리거 활성화/비활성화

활성화
alter trigger LOGON enable;

비활성화

alter trigger LOGON disable;