DB접근제어(DB세이퍼) 솔루션을 통해 서비스 DB계정으로 DB접속을 제어하는 로직을 오라클 로그온-트리거를 통해 구현 방법

-- 테스트용 계정
 
 
DROP USER TEST;
 
 
CREATE USER TEST Identified by "#Oora!@34";
 
 
GRANT CONNECT TO TEST;
 
 
  
 
 
DROP USER I_TEST;
 
 
CREATE USER I_TEST identified by "#Oora!@34";
 
 
GRANT CONNECT TO I_TEST;
 
 
  
 
 
  
 
 
-- 한글 메세지
 
 
CREATE OR REPLACE TRIGGER SYS.DENY_USER
 
 
  AFTER LOGON ON DATABASE
 
 
BEGIN
 
 
            IF (SYS_CONTEXT('USERENV','HOST') LIKE 'HOSTWAY\SBC%')
 
 
            THEN
 
 
                IF (SYS_CONTEXT('USERENV','SESSION_USER') IN('DBA','DBMON')) -- DBA 사용 계정이거나
 
 
                OR (SYS_CONTEXT('USERENV','SESSION_USER') LIKE '%ERD') -- ERD툴 계정이거나
 
 
                OR (SYS_CONTEXT('USERENV','SESSION_USER') LIKE'I@_%' ESCAPE '@') -- 개인계정이면 허용
 
 
                THEN
 
 
                    NULL;
 
 
                ELSE  
 
 
                    RAISE_APPLICATION_ERROR(-20001,CHR(10)
 
 
                    ||'[경고] DB접속 시 개인계정(I_XXX) 외 접근 불가'||CHR(10)
 
 
                    ||' - SBC IP : '||SYS_CONTEXT('USERENV','IP_ADDRESS')||'   사용자 : '||SYS_CONTEXT('USERENV','OS_USER')||CHR(10)
 
 
                    ||' - 접속계정 : '||SYS_CONTEXT('USERENV','SESSION_USER')||'  접속모듈 : '||SYS_CONTEXT('USERENV','MODULE')||CHR(10)
 
 
                    ||'예외 사항에 대해서는 DBA에 문의주세요!'
                    );
 
 
            END IF;
 
 
  
 
 
    END IF;
 
 
END;
 
 
/
 
-- DB별 캐릭터셋이 상이한 이슈로 한글이 깨지는 경우 존재
 
 
CREATE OR REPLACE TRIGGER SYS.DENY_USER
 
 
  AFTER LOGON ON DATABASE
 
 
BEGIN
 
 
            IF (SYS_CONTEXT('USERENV','HOST') LIKE 'HOSTWAY\SBC%')
 
 
            THEN
 
 
                IF (SYS_CONTEXT('USERENV','SESSION_USER') IN('DBA','DBMON')) -- DBA 사용 계정이거나
 
 
                OR (SYS_CONTEXT('USERENV','SESSION_USER') LIKE '%ERD') -- ERD툴 계정이거나
 
 
                OR (SYS_CONTEXT('USERENV','SESSION_USER') LIKE'I@_%' ESCAPE '@') -- 개인계정이면 허용
 
 
                THEN
 
 
                    NULL;
 
 
                ELSE  
 
 
                    RAISE_APPLICATION_ERROR(-20001,CHR(10)
 
 
                    ||'[Warning] Unable to
access database with service account.'||CHR(10)
 
 
                    ||' Please use your
personal DB account(I_XXX).'||CHR(10)||CHR(10)
 
 
                    ||' - SBC IP addr : '||SYS_CONTEXT('USERENV','IP_ADDRESS')||'   EMP NO : '||SYS_CONTEXT('USERENV','OS_USER')||CHR(10)
 
 
                    ||' - DB Account : '||SYS_CONTEXT('USERENV','SESSION_USER')||'   Module : '||SYS_CONTEXT('USERENV','MODULE')||CHR(10)||CHR(10)
 
 
                    ||' Contact DBA Team
for more information.'||CHR(10)||CHR(10)
 
 
                    );
 
 
            END IF;
 
 
  
 
 
    END IF;
 
 
END;
 
 
/
 
-- 9i는 ESCAPE함수 에러발생하므로 제거
 
 
CREATE OR REPLACE TRIGGER SYS.DENY_USER
 
 
  AFTER LOGON ON DATABASE
 
 
BEGIN
 
 
            IF (SYS_CONTEXT('USERENV','HOST') LIKE 'HOSTWAY\SBC%')
 
 
            THEN
 
 
                IF (SYS_CONTEXT('USERENV','SESSION_USER') IN('DBA','DBMON')) -- DBA 사용 계정이거나
 
 
                OR (SYS_CONTEXT('USERENV','SESSION_USER') LIKE '%ERD') -- ERD툴 계정이거나
 
 
                OR (SYS_CONTEXT('USERENV','SESSION_USER') LIKE'I%') -- 개인계정이면 허용
 
 
                THEN
 
 
                    NULL;
 
 
                ELSE  
 
 
                    RAISE_APPLICATION_ERROR(-20001,CHR(10)
 
 
                    ||'[Warning] Unable to
access database with service account.'||CHR(10)
 
 
                    ||' Please use your
personal DB account(I_XXX).'||CHR(10)||CHR(10)
 
 
                    ||' - SBC IP addr : '||SYS_CONTEXT('USERENV','IP_ADDRESS')||'   EMP NO : '||SYS_CONTEXT('USERENV','OS_USER')||CHR(10)
 
 
                    ||' - DB Account : '||SYS_CONTEXT('USERENV','SESSION_USER')||CHR(10)||CHR(10)                  
 
 
                    ||' Contact GSDBA Team for more information.'||CHR(10)||CHR(10)
 
 
                    );
 
 
            END IF;
 
 
  
 
 
    END IF;
 
 
END;
 
 
/