접속 이력 남기기

접근제한 솔루션이 없는 경우
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;

 

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

CREATE OR REPLACE trigger SYSTEM.logon_trigger after logon on database
begin
DELETE FROM SYSTEM.USER_LOGIN WHERE REPLACE(SUBSTR(DT,1,10),'-') <= TO_CHAR(ADD_MONTHS(SYSDATE,-7),'YYYYMMDD'); --7개월 로그 보유
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;
 
 
또는
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;

 

트리거 활성/비활성

alter trigger 트리거명 enable/disable;

 

 

또다른 트리거

1. 테이블 생성
- 로그인 시간과 유저명, ip, audsid을 저장
- sid 정보를 가져올 수 없어 audsid를 저장한다.  audsid도 유니크한 값이라서 괜찮음
 
 
CREATE TABLE SYSTEM.LOGSTAT(
LOGIN DATE,
USERNAME VARCHAR2(30),
IP VARCHAR2(30),
AUDSID NUMBER,
LOGOUT DATE)
TABLESPACE SYSTEM;
 
 
 
 
3. Login 트리거
- 로그인 시간, 유저명, ip를 저장하는 트리거. SYS유저의 정보는 저장하지 않는다.
- 특정 유저만 필요한 경우 IF 구문을 수정
  
CREATE OR REPLACE TRIGGER LOGONIP AFTER LOGON ON DATABASE
BEGIN
IF SYS_CONTEXT('USERENV','SESSION_USER') NOT IN ('SYS') THEN
INSERT INTO SYSTEM.LOGSTAT(LOGIN, USERNAME, IP, AUDSID)
VALUES(SYSDATE,(SYS_CONTEXT('USERENV','SESSION_USER')),(SYS_CONTEXT('USERENV','IP_ADDRESS')),(SYS_CONTEXT('USERENV','SESSIONID')));
COMMIT;
END IF;
END;
/
 
 
 
 
4. Logout 트리거
- 로그아웃 시간을 update하는 트리거
 
 
CREATE OR REPLACE TRIGGER LOGOFFIP BEFORE LOGOFF ON DATABASE
BEGIN
IF SYS_CONTEXT('USERENV','SESSION_USER') NOT IN ('SYS') THEN
UPDATE SYSTEM.LOGSTAT SET LOGOUT=SYSDATE
WHERE AUDSID=(SYS_CONTEXT('USERENV','SESSIONID'));
COMMIT;
END IF;
END;
/

 

로그아웃 트리거는 어플리케이션(자바)에서 LOGOUT할때 에러가 발생하는 문제로 트리거 DROP함..ㅠㅠ

 

추가 - V$SESSION 테이블의 client_info 테이블에 접속ip넣을 수 있는 트리거 추가 생성함. 

CREATE OR REPLACE Trigger "SYSTEM"."LOGON_AUDIT_TRIGGER"
  after logon on database
begin
   dbms_application_info.set_client_info(sys_context('USERENV', 'IP_ADDRESS'));
end;

 

ip별 실행 쿼리 조회

SELECT
    A.USERNAME AS 계정명,
    A.SCHEMANAME AS 스키마명,
    A.STATUS AS 활성화여부,
    a.Client_Info AS 접속ip,
    a.osuser AS 유저명,
    A.SID AS 세션식별자,
    A.MACHINE AS OS머신명,
    A.PROGRAM AS OS프로그램명,
    A.TYPE AS 세션타입,
    A.LOGON_TIME AS 세션로그인날짜,
    B.FIRST_LOAD_TIME AS 처음실행한시각,
    B.SQL_TEXT  AS 실행쿼리
FROM
    V$SESSION A, V$SQLAREA B
WHERE
    (A.SQL_HASH_VALUE = B.HASH_VALUE  --SQL_HASH_VALUE : 가장 최근 실행된 SQL의 HASH값
    OR A.PREV_HASH_VALUE = B.HASH_VALUE) --PREV_HASH_VALUE : 이전에 실행된 SQL의 HASH값
    AND
    A.SQL_ADDRESS = B.ADDRESS
    --AND A.USERNAME = '계정명' -- 특정 계정의 세션만 조회
    --AND A.STATUS = 'ACTIVE' --활성화된 세션만 조회
 
 
 
 
SELECT
    a.Client_Info,
    TO_CHAR (SID) sid,
    serial# serialNumber,
    SUBSTR (TO_CHAR (last_call_et), 1,  6) executeSeconds,
    userName,
    machine,
    b.sql_text sqlText,
    b.sql_id
FROM
    v$session a,
    v$sqltext b
WHERE
    username NOT IN ('SYSTEM', 'SYS')
    AND a.TYPE != 'BACKGROUND'
    --   AND a.status = 'ACTIVE'
    AND a.sql_address = b.address(+)
    AND a.sql_hash_value = b.hash_value(+)
ORDER BY
    a.last_call_et DESC,
    a.SID,
    a.serial#,
    b.address,
    b.hash_value,
    b.piece,
    b.sql_id
 
 
sql_id 확인 후 v$sql 테이블에서 full text확인 또는
 
 
WITH SQLl AS   (
SELECT
    a.Client_Info,
    TO_CHAR (SID) sid,
    serial# serialNumber,
    SUBSTR (TO_CHAR (last_call_et), 1,  6) executeSeconds,
    userName,
    machine,
    b.sql_text sqlText,
    b.sql_id
FROM
    v$session a,
    v$sqltext b
WHERE
    username NOT IN ('SYSTEM', 'SYS')
    AND a.TYPE != 'BACKGROUND'
    --   AND a.status = 'ACTIVE'
    AND a.sql_address = b.address(+)
    AND a.sql_hash_value = b.hash_value(+)
ORDER BY
    a.last_call_et DESC,
    a.SID,
    a.serial#,
    b.address,
    b.hash_value,
    b.piece,
    b.sql_id)
    SELECT a.*, b.sql_fulltext
    FROM sqll a, 
    v$sql b
WHERE a.sql_id = b.sql_id(+)