DDL TRIGGER 감사 기능

DDL 트리거를 사용하면 간단한 감사 기능을 구현할 수 있다.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1 : TUNA
CREATE TABLE t1 (
    ts         TIMESTAMP
  , login_user VARCHAR2(30)
  , sysevent   VARCHAR2(20)
  , obj_owner  VARCHAR2(30)
  , obj_type   VARCHAR2(20)
  , obj_name   VARCHAR2(30)
  , ip_address VARCHAR2(255)
  , os_user    VARCHAR2(255)
  , terminal   VARCHAR2(255)
  , host       VARCHAR2(255));

감사 기능을 수행할 DDL 트리거를 생성하자. IF 문에 예외 이벤트를 정의했다. 그 외의 이벤트는 테이블에 로그를 남긴 후 에러를 발생시킨다.

-- 2 : SYS
CREATE OR REPLACE TRIGGER trg1
    BEFORE DDL ON SCOTT.SCHEMA
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    IF (        ora_sysevent = 'COMMENT'
        OR (    ora_sysevent = 'CREATE'
            AND ora_dict_obj_type IN ('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE'))
        OR (    ora_sysevent = 'TRUNCATE'
            AND ora_dict_obj_name IN ('')))
    THEN
        NULL;
    ELSE
        INSERT
          INTO tuna.t1
               (ts, login_user, sysevent, obj_owner, obj_type, obj_name, ip_address, os_user, terminal, host)
        VALUES (SYSTIMESTAMP
              , ORA_LOGIN_USER
              , ORA_SYSEVENT
              , ORA_DICT_OBJ_OWNER
              , ORA_DICT_OBJ_TYPE
              , ORA_DICT_OBJ_NAME
              , SYS_CONTEXT ('USERENV', 'IP_ADDRESS')
              , SYS_CONTEXT ('USERENV', 'OS_USER')
              , SYS_CONTEXT ('USERENV', 'TERMINAL')
              , SYS_CONTEXT ('USERENV', 'HOST')
               );

        COMMIT;

        RAISE_APPLICATION_ERROR (-20000, 'insufficient privileges');
    END IF;
END trg1;

SCOTT 유저로 로그인하여 CREATE TABLE 문을 실행해보자. trg1 트리거에 의해 에러가 발생한다.

-- 3 : SCOTT
CREATE TABLE t1 (c1 NUMBER);
ORA-00604: 순환 SQL 레벨 1 에 오류가 발생했습니다
ORA-20000: insufficient privileges

t1 테이블을 조회하면 로그를 확인할 수 있다.

-- 4 : TUNA
SELECT * FROM t1;

TS                  LOGIN SYSEVE OBJ_O OBJ_T OB IP_ADDRESS OS_USER TERMINAL   HOST
------------------- ----- ------ ----- ----- -- ---------- ------- ---------- --------------------
2012-05-01 17:26:26 SCOTT CREATE SCOTT TABLE T1 127.0.0.1  HRJEONG HRJEONG-PC WORKGROUP\HRJEONG-PC

1개의 행이 선택되었습니다.