접속 이력로그 남기기

접근제한 솔루션이 없는 경우

oracle db내 접속한 유저(DB,OS) 및 접속 시간, 접속한 IP, 접속한 방법(Tool) 테이블로 로그 남기기

 

1. 테이블 생성

--  db login 정보  이력 table 생성

CREATE TABLE SYSTEM.USER_LOGIN
(
    LOGINOUT    VARCHAR2(20),
    DT          VARCHAR2(50),
    SID         VARCHAR2(10),
    DB_USER     VARCHAR2(50),
    OSUSER      VARCHAR2(50),
    HOST        VARCHAR2(200),
    INT_IP      VARCHAR2(20),
    APPINFO     VARCHAR2(200)
)
TABLESPACE SYSTEM
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOCOMPRESS;

 

2. 트리거 생성

-- db login 할때,  table에  login user 정보 를 insert 할  trigger 작성

CREATE OR REPLACE trigger SYSTEM.logon_trigger after logon on database

begin

INSERT INTO SYSTEM.USER_LOGIN ( LOGINOUT , DT, SID, DB_USER, OSUSER, HOST, INT_IP, APPINFO ) 
SELECT 'LOGON',
to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), --시간
sys_context('USERENV', 'SID'), -- 세션번호
sys_context('USERENV', 'SESSION_USER'), --DB접속유저
sys_context('USERENV', 'OS_USER'), --OS유저
sys_context('USERENV', 'HOST'), --접속장비
nvl(sys_context('userenv','IP_ADDRESS'),'192.168.1.149'), -- 접속IP null일경우 리스너 안통하고 로컬서버에서 접속      
nvl(sys_context('USERENV', 'MODULE'), 'local system check')--DBMS_APP_INFO 모률이름 null일경우 oracle 자체 system 체크
      FROM DUAL;
     
end;

※. 트리거 설명 로그인/로그아웃 내역 조회

텔넷으로 접속한 세션들은 ip 정보가 null 로 아무것도 보이지가 않는데,

sqlnet을 통해 들어온 클라이언트만 ip가 보이는건지 궁금합니다
listener를 통했냐 아니냐의 차이입니다.
telnet으로 들어간서버에서 접속시에도 sqlplus 로 접속시@TNS 를 사용하면 보일겁니다.

listener 없이 로컬에서 접속한 경우의 아이피는 null로 표시됩니다.
리스너를 통해서 접속한것만 ip를 확인할 수 있습니다

 

3. DBMS에 로그인한 이력남는 테이블에 남는 정보

-> 리스너를 통하여 접속한 이력정보

※. 트리거 다른거

- db login 할때,  table에  login user 정보 를 insert 할  trigger 작성

create or replace trigger logon_trigger after logon on database

begin

INSERT INTO USER_LOGINOUT ( LOGINOUT , DT, SID, DB_USER, OSUSER, HOST, IPINFO, APP_INFO ) 
SELECT 'LOGON',
to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), --시간
sys_context('USERENV', 'SID'), -- 세션번호
sys_context('USERENV', 'SESSION_USER'), --DB접속유저
sys_context('USERENV', 'OS_USER'), --OS유저
sys_context('USERENV', 'HOST'), --접속장비
sys_context('userenv','IP_ADDRESS'), -- 접속IP        
sys_context('USERENV', 'MODULE') --DBMS_APP_INFO 모률이름
      FROM DUAL A;
end;

/

 

- db logout 할때,  table에  login user 정보 를 insert 할  trigger 작성

create or replace trigger logout_trigger before logoff on database

begin

INSERT INTO USER_LOGINOUT ( LOGINOUT , DT, SID, DB_USER, OSUSER, HOST, IPINFO, APP_INFO ) 
SELECT 'LOGOFF',
to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), --시간
sys_context('USERENV', 'SID'), -- 세션번호
sys_context('USERENV', 'SESSION_USER'), --DB접속유저
sys_context('USERENV', 'OS_USER'), --OS유저
sys_context('USERENV', 'HOST'), --접속장비
sys_context('userenv','IP_ADDRESS'), -- 접속IP        
sys_context('USERENV', 'MODULE') --DBMS_APP_INFO 모률이름
      FROM DUAL A;

end;