[오라클] DDL 로그 트리거

오라클 DDL 로그 트리거

많은 사람들이 같은 DB에서 작업을 할때, DB에 문제가 생겼을 때 어떤 사람이 했는지 찾을 수 없다. 

접속한 유저가 날린 SQL문을 추적하는 DDL 로그 트리거를 통해 이러한 문제를 해결할 수 있다. 

 

1. sys 로 접속 후 트리거를 만드는 계정에 권한 주기

-- 권한부여 
GRANT SELECT ON sys.v_$session TO DBADM;

-- 부여 확인
SELECT *
 FROM DBA_TAB_PRIVS
WHERE GRANTEE ='DBADM'
  AND TABLE_NAME LIKE 'V_$SESSION'  
 ;

 

2. 조회 결과 테이블 생성

DROP TABLE DBADM.TB_DDL_HISTORY CASCADE CONSTRAINTS; 

CREATE TABLE DBADM.TB_DDL_HISTORY
(
	EXEC_DTM		DATE,
    USERNAME		VARCHAR2(128 BYTE),
    ACTION_EVNET	VARCHAR2(100 BYTE),
    OBJECT_TYPE		VARCHAR2(32 BYTE),
    OBJECT_OWNER 	VARCHAR2(128 BYTE),
    OBJECT_NAME 	VARCHAR2(128 BYTE),
    PROGRAM			VARCHAR2(48 BYTE),
    MACHINE			VARCHAR2(64 BYTE),
    IP_ADDRESS		VARCHAR2(45 BYTE),
    OSUSER			VARCHAR2(30 BYTE),
    SQL_ID			VARCHAR2(13 BYTE),
    SQL_TEXT		VARCHAR2(4000 BYTE)
) TABLESPACE TS_DBADM_DN01;

3. INDEX 생성

CREATE INDEX DBADM.IX_DDL_HISTORY_01 ON DBADM.TB_DDL_HISTORY(OBJECT_NAME);

CREATE INDEX DBADM.IX_DDL_HISTORY_02 ON DBADM.TB_DDL_HISTORY(OBJECT_OWNER);

CREATE INDEX DBADM.IX_DDL_HISTORY_03 ON DBADM.TB_DDL_HISTORY("EXEC_DTM" DESC);

4. TRIGGER 생성

CREATE OR REPLACE TRIGGER TRIGGER_DDL_HISTORY
AFTER DDL
ON DATABASE
DECLARE
	l_program 	VARCHAR2(48);
    l_machine 	VARCHAR2(64);
    l_osuser	VARCHAR2(30);
    l_username 	VARCHAR2(128);
    l_sql_id	VARCHAR2(13);
    l_sql_arr	ORA_NAME_LIST_T;
    l_arr_cnt	BINARY_INTEGER := 0;
    l_sql_len	BINARY_INTEGER := 0;
    l_sql_text 	VARCHAR(4000);
    
    
BEGIN 
	--SQL_TEXT 4000자 이하로 자르기
    l_arr_cnt := ORA_SQL_TXT(l_sql_arr);
    
    FOR i in 1..l_arr_cnt LOOP
    	l_sql_text := l_sql_text || SUBSTRB(l_sql_arr(i),1,4000 -l_sql_len);
        l_sql_len := LENTHB(l_sql_text);
        IF l_sql_len >= THEN
        	EXIT;
        END IF;
    END LOOP;
    
    --WITH(materialize)와 같은 내부 DDL 제외
    
    IF ( ORA_DICT_OBJ_NAME NOT LIKE 'SYS_TEMP%' AND ORA_DICT_OBJ_NAME NOT LIKE 'ORA_TEMP%' )
    
    THEN
    	SELECT ss.program,
        	   ss.machine,
               ss.osuser,
               ss.username,
               ss.sql_id
          INTO l_program,
          	   l_machine,
               l_osuser,
               l_username,
               l_sql_id
          FROM sys.v$session as
         WHERE ss.sid = SYS_CONTEXT('USERENV','SID')
           AND ROWNUM = 1;
           
      	   IF l_osuser NOT IN('oracle','system') THEN
           	INSERT INTO DBADM.TB_DDL_HISTORY
            		(
                	exec_dtm,
                	username,
                	action_event,
                	object_type,
                	object_owner,
                	object_name,
                	program,
                	machine,
                	ip_address,
                	osuser,
                	sql_id,
                	sql_text
                	)
            VALUES  (
            		SYSDATE,
                    ORA_LOGIN_USER,
                    ORA_SYSEVENT,
                    ORA_DICT_OBJ_TYPE,
                    ORA_DICT_OBJ_OWNER,
                    ORA_DICT_OBJ_NAME,
                    l_program,
                    l_machine,
                    SYS_CONTEXT('USERENV','IP_ADDRESS')
                    l_osuser,
                    l_sql_id,
                    l_sql_text );
        END IF;
    END IF;
EXCEPTION WHEN OTHERS THEN 
	NULL;
END;
/