오라클에서 DROP 권한 회수하고 TRUNCATE 권한 위임하는 프로시저 작성하기

오라클 데이터베이스에서 권한 관리는 보안과 운영 효율성 측면에서 매우 중요합니다.
특히, TRUNCATE 명령은 빠른 데이터 삭제가 가능하지만, 롤백이 불가능하기 때문에 신중하게 권한을 부여해야 합니다.

이번 포스팅에서는

• 오라클 프로시저 생성 및 조회 방법
• DROP TABLE ANY 권한 회수 프로시저 작성법
• TRUNCATE 권한 위임을 위한 권한 관리 방법과 프로시저 작성법

을 단계별로 살펴보겠습니다.

---

1. 오라클 프로시저란?

오라클에서 프로시저는 PL/SQL 블록을 재사용 가능하게 만든 저장 프로그램입니다.
복잡한 작업을 간단하게 실행할 수 있으며, 권한을 통해 실행 권한을 제한할 수 있어 DB 보안과 관리에 매우 유용합니다.

---

2. 프로시저 생성 예제

CREATE OR REPLACE PROCEDURE greet_user(p_username IN VARCHAR2) IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, ' || p_username || '! Welcome to Oracle DB.');
END;

• CREATE OR REPLACE PROCEDURE로 프로시저를 새로 만들거나 수정할 수 있습니다.
• DBMS_OUTPUT.PUT_LINE은 메시지 출력에 사용됩니다.

---

3. 생성된 프로시저 조회하기

-- 해당 유저의 프로시저 목록 조회
SELECT OBJECT_NAME, STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE';

-- 프로시저 소스코드 조회
SELECT TEXT
FROM USER_SOURCE
WHERE NAME = '프로시저명'
ORDER BY LINE;

---

4. TRUNCATE 권한 관리 및 보안 고려사항

TRUNCATE 명령은 테이블 데이터를 빠르게 삭제하지만, 롤백이 불가능하여 매우 민감한 권한입니다.
일반적으로 DROP TABLE ANY 같은 시스템 권한과 연관되지만, 너무 광범위한 권한은 보안 위험을 초래할 수 있습니다.

---

5. DROP TABLE ANY 권한 회수 프로시저 작성

특정 사용자에게 부여된 DROP TABLE ANY 권한을 회수하는 프로시저 예시입니다.

CREATE OR REPLACE PROCEDURE revoke_drop_any(p_username IN VARCHAR2) IS
BEGIN
    EXECUTE IMMEDIATE 'REVOKE DROP TABLE ANY FROM ' || p_username;
    DBMS_OUTPUT.PUT_LINE('DROP TABLE ANY 권한이 ' || p_username || '로부터 성공적으로 회수되었습니다.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
END;

• EXECUTE IMMEDIATE로 동적 SQL을 실행합니다.
• 예외처리를 통해 권한 회수 실패 시 에러 메시지를 출력합니다.

---

6. TRUNCATE 권한 위임을 위한 권한 관리 및 프로시저 작성법

오라클에서 직접적으로 TRUNCATE 권한을 부여하는 방법은 제공되지 않습니다.
대신 다음과 같은 접근법을 사용합니다.

아래는 특정 테이블에 대해 TRUNCATE 권한을 줄 수 있는 역할을 생성 후, 해당 역할을 부여하는 예시입니다.

-- 역할 생성 (관리자가 한 번 실행)
CREATE ROLE truncate_role;

-- 역할에 TRUNCATE 권한 부여 (DBA 권한 필요)
GRANT DELETE ANY TABLE TO truncate_role;  -- TRUNCATE 권한은 DELETE 권한과 다름, 권한 정책에 맞춰 수정 필요

-- 역할을 사용자에게 부여하는 프로시저
CREATE OR REPLACE PROCEDURE grant_truncate_role(p_username IN VARCHAR2) IS
BEGIN
    EXECUTE IMMEDIATE 'GRANT truncate_role TO ' || p_username;
    DBMS_OUTPUT.PUT_LINE('truncate_role 역할이 ' || p_username || '에게 부여되었습니다.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
END;

---

마무리 및 참고자료

• 오라클 DB 권한 관리는 보안과 운영에 직결되는 중요한 부분입니다.
• 직접 권한 부여가 어려운 경우, 역할과 저장 프로시저를 활용한 권한 위임 방법을 적극 활용하세요.
• 주기적인 권한 점검과 최소 권한 원칙 준수로 안정적인 DB 운영을 할 수 있습니다.
• Oracle Database PL/SQL Language Reference
• Oracle Security Guide
• Oracle SQL GRANT and REVOKE Syntax

---

궁금하신 점이나 추가 요청은 언제든 댓글로 남겨 주세요!
좋은 하루 되세요 😊