오라클 로그인 실패 기록 트리거

DBA 업무 수행 중 로그인 실패 하는 유저를 추적하기 위한 용도
가급적 SYS 계정으로 생성할 것(타계정시 권한 부여 해야함)

실패 로그 테이블 생성

CREATE TABLE FAILED_LOGON_HISTORY ( 
LOGONDT DATE,
USERNAME VARCHAR2(20),
IPADDR VARCHAR2(15),
DBNAME VARCHAR2(15),
OSUSER VARCHAR2(20),
MACINE VARCHAR2(50),
PROGRAM VARCHAR2(50));

트리거 생성

'ORA-1017: invalid username/password; logon denied' 를 IS_SERVERERROR(1017)로 감지
V$SESSION 사용자 정보를 에서 캡쳐하여 로그 테이블에 기록함.

CREATE OR REPLACE TRIGGER TRG_FAILED_LOGON 
                    AFTER SERVERERROR ON DATABASE
BEGIN
-- ORA-1017: invalid username/password; logon denied
IF (IS_SERVERERROR(1017)) THEN
INSERT INTO FAILED_LOGON_HISTORY
SELECT SYSDATE AS LOGONDT, SYS_CONTEXT('USERENV' ,'AUTHENTICATED_IDENTITY') AS USERNAME
     , NVL(SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),'Unknown IP') AS IPADDR
     , ORA_DATABASE_NAME AS DBNAME
     , OSUSER
     , MACHINE
     , PROGRAM
  FROM V$SESSION
 WHERE SID = TO_NUMBER(SUBSTR(DBMS_SESSION.UNIQUE_SESSION_ID,1 ,4), 'xxxx')
   AND SERIAL# = TO_NUMBER(SUBSTR(DBMS_SESSION.UNIQUE_SESSION_ID,5 ,4), 'xxxx');

COMMIT;
END IF;
END;
/