[Oralce] 트리거를 사용해서 Invalid Object 접근/사용 이력 기록하기

Audit Trail 기능을 사용해서는 Invalid Object의 접근/사용 이력을 기록할 수 없기 때문에 Schema Trigger를 생성해서 Invalid Object의 접근/사용 이력을 기록하는 방법을 알아보겠습니다.

1. 로그 기록용 테이블 및 권한 부여(COM 스키마)
2. Trigger 생성(TGMADM 스키마)

1. 로그 기록용 테이블 및 권한 부여(COM 스키마)

• 로그 기록용 테이블을 생성합니다.

  CREATE TABLE COM.TB_LOG_INVALID_OBJ_ERR (
      LOG_DATE    DATE DEFAULT SYSDATE,
      DB_USER     VARCHAR2(30),
      SESSION_USER  VARCHAR2(30),
      TERMINAL    VARCHAR2(100),
      IP_ADDRESS  VARCHAR2(50),
      ERROR_CODE  NUMBER,
      OBJ_OWNER   VARCHAR2(30),
      OBJ_NAME    VARCHAR2(128),
      OBJ_TYPE    VARCHAR2(30), 
      OBJ_STATUS  VARCHAR2(10),
      ERROR_MSG   VARCHAR2(1000),
      SQL_TEXT  VARCHAR2(4000)
  );
  

• Trigger를 생성한 스키마에게 위에서 생성한 테이블의 INSERT, SELECT 권한을 부여합니다.

  GRANT INSERT, SELECT ON COM.TB_LOG_INVALID_OBJ_ERR TO TGMADM;
  

2. Trigger 생성(TGMADM 스키마)

• 로그 기록용 Schema Trigger를 생성합니다.

  CREATE OR REPLACE TRIGGER "TGMADM"."TRG_LOG_INVALID_OBJ_ERR" 
  AFTER SERVERERROR ON DATABASE
  DECLARE
      PRAGMA AUTONOMOUS_TRANSACTION; -- 독립적인 트랜잭션으로 처리하라고 지시하는 컴파일러 지시어
      
      v_sql_text   ORA_NAME_LIST_T;
      v_stmt       VARCHAR2(4000);
      v_count      PLS_INTEGER;
      v_msg        VARCHAR2(1000);
      v_full_obj   VARCHAR2(200);
      v_owner      VARCHAR2(30);
      v_name       VARCHAR2(128);
      v_type       VARCHAR2(30);
      v_status     VARCHAR2(10); -- 크기 약간 확장 (NOT_FOUND 대비)
      v_start      NUMBER;
      v_end        NUMBER;
  BEGIN
      -- 감시 대상 에러 코드
      -- 4063/6575 : Invalid 객체 접근
      -- 6550 : PL/SQL 코드 오류
      -- 942 : 객체 없음 또는 권한 부족
      -- 1542 : 테이블스페이스 오프라인
      -- 376 : 데이터 파일 읽기 불가
      IF (IS_SERVERERROR(4063) OR IS_SERVERERROR(6575) OR IS_SERVERERROR(6550) 
          OR IS_SERVERERROR(942) OR IS_SERVERERROR(1542) OR IS_SERVERERROR(376)) THEN
          
          v_msg := ORA_SERVER_ERROR_MSG(1);
      
          -- SQL 텍스트 추출
          v_count := ORA_SQL_TXT(v_sql_text);
          IF v_count IS NOT NULL THEN
              FOR i IN 1..LEAST(v_count, 50) LOOP
                  v_stmt := v_stmt || v_sql_text(i);
              END LOOP;    
          END IF;
          v_stmt := SUBSTR(v_stmt, 1, 4000);
          
          -- 객체 타입 식별 (대소문자 무관하게 탐색)
          IF    UPPER(v_msg) LIKE '%VIEW %'         THEN v_type := 'VIEW';
          ELSIF UPPER(v_msg) LIKE '%PROCEDURE %'    THEN v_type := 'PROCEDURE';
          ELSIF UPPER(v_msg) LIKE '%PACKAGE BODY %' THEN v_type := 'PACKAGE BODY';
          ELSIF UPPER(v_msg) LIKE '%PACKAGE %'      THEN v_type := 'PACKAGE';
          ELSIF UPPER(v_msg) LIKE '%FUNCTION %'     THEN v_type := 'FUNCTION';
          ELSE                                           v_type := 'UNKNOWN/PLSQL';
          END IF;
          
          -- 따옴표 기반 객체명 파싱
          v_start := INSTR(v_msg, '"', 1, 1);
          v_end   := INSTR(v_msg, '"', 1, 2);
          
          IF v_start > 0 AND v_end > v_start THEN
              v_full_obj := SUBSTR(v_msg, v_start + 1, v_end - v_start - 1);
              IF INSTR(v_full_obj, '.') > 0 THEN
                  v_owner := UPPER(SUBSTR(v_full_obj, 1, INSTR(v_full_obj, '.') - 1));
                  v_name  := UPPER(SUBSTR(v_full_obj, INSTR(v_full_obj, '.') + 1));
              ELSE
                  v_owner := ORA_DICT_OBJ_OWNER; -- 추출 실패 시 현재 컨텍스트 활용
                  v_name  := UPPER(v_full_obj);
              END IF;
  
              -- 객체 상태 조회 (DBA_OBJECTS 접근 권한 필요)
              BEGIN
                  SELECT STATUS INTO v_status
                  FROM DBA_OBJECTS
                  WHERE OWNER = NVL(v_owner, OWNER) -- OWNER 파싱 실패 대비
                    AND OBJECT_NAME = v_name
                    AND ROWNUM = 1;
              EXCEPTION
                  WHEN NO_DATA_FOUND THEN v_status := 'NOT_FOUND';
                  WHEN OTHERS THEN v_status := 'ERROR';
              END;
          END IF;
          
          -- 로그 기록
          INSERT INTO COM.TB_LOG_INVALID_OBJ_ERR (
              LOG_DATE, DB_USER, SESSION_USER, TERMINAL, IP_ADDRESS, 
              ERROR_CODE, OBJ_OWNER, OBJ_NAME, OBJ_TYPE, OBJ_STATUS, 
              ERROR_MSG, SQL_TEXT
          ) VALUES (
              SYSDATE,
              ORA_LOGIN_USER, 
              SYS_CONTEXT('USERENV', 'SESSION_USER'),  
              SYS_CONTEXT('USERENV', 'TERMINAL'),  
              SYS_CONTEXT('USERENV', 'IP_ADDRESS'), 
              ORA_SERVER_ERROR(1),
              v_owner, 
              v_name, 
              v_type,
              v_status, 
              v_msg,
              v_stmt
          );
  
          COMMIT; 
      END IF;
  EXCEPTION
      WHEN OTHERS THEN
          ROLLBACK; -- 자율 트랜잭션 롤백
          NULL;
  END;
  /