DB 유관 시스템 파악을 위해 netstat 이나, 서버보안 솔루션, tcp 모니터링 등 여러 방법이 있을수 있는데,
DB 자체적으로 로그인 트리거를 걸어 username 까지 확인하기 위한 방법입니다.
-- 테이블 생성
CREATE TABLE logon_history
(
USERNAME VARCHAR2(30 BYTE),
HOST VARCHAR2(64 BYTE),
IP VARCHAR2(40 BYTE),
LOGIN_DT DATE
);
-- LOGON TRIGGER 생성
CREATE OR REPLACE TRIGGER logon_history_trigger AFTER LOGON ON DATABASE
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO logon_history
VALUES( SUBSTRB(SYS_CONTEXT('USERENV', 'session_user'), 1, 30), SUBSTRB(sys_context('userenv','host'), 1, 64), sys_context('userenv','ip_address'), SYSDATE );
COMMIT;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
/
-- 접속 테스트해보고 문제있을때는 트리거 비활성화
-- ALTER TRIGGER tsql.logon_history_trigger DISABLE;
-- 시간대별 접속내역 취합
SELECT TO_CHAR(login_dt, 'YYYYMMDD') login_dt,
username,
HOST,
ip,
COUNT(*) cnt,
MIN(TO_CHAR(login_dt, 'YYYYMMDD')) min_login_dt,
MAX(TO_CHAR(login_dt, 'YYYYMMDD')) max_login_dt
FROM logon_history
GROUP BY username,
HOST,
ip,
TO_CHAR(login_dt, 'YYYYMMDD');
-- gv$session 취합
SELECT username, COUNT(1) OVER (PARTITION BY username) cnt,
last_call_et, logon_time, status, event, server, osuser,
program, sql_id, module
FROM gv$session
WHERE TYPE <> 'BACKGROUND'
--AND PROGRAM NOT LIKE 'oracle@%'
AND USERNAME NOT IN ('SYSTEM', 'SYS')
ORDER BY username, last_call_et;