[오라클 운영] ROLE 기반 권한 관리 완벽 가이드 - PL/SQL의 함정과 INVOKER rights까지

[오라클 운영] ROLE 기반 권한 관리 완벽 가이드 - PL/SQL의 함정과 INVOKER rights까지

테스트 환경: Oracle 11g / 12c / 19c / 21c

오라클 권한 관리에서 ROLE은 핵심 도구입니다. 사용자가 100명, 객체가 1,000개인 환경에서 직접 GRANT를 일일이 관리하면 운영이 마비됩니다. ROLE을 쓰면 권한을 그룹화해서 사용자 단위가 아닌 역할 단위로 관리할 수 있습니다.

그런데 ROLE을 운영하다 보면 다음과 같은 황당한 상황을 만나게 됩니다.

• 분명히 ROLE에 SELECT 권한을 줬는데 프로시저가 ORA-00942
• 똑같은 SQL인데 SQL*Plus에서는 되고 PL/SQL에서는 안 됨
• ROLE을 부여했는데 다음 세션에서 권한이 사라짐
• 직접 GRANT로 바꾸니 갑자기 해결됨

이번 글에서는 ROLE의 기본부터 시작해서, PL/SQL에서 ROLE이 무효화되는 보안 설계 이유, DEFINER vs INVOKER rights의 트레이드오프, 그리고 운영 환경에서 ROLE을 안전하게 쓰는 표준 패턴까지 정리했습니다.

이 글은 ORA-00942 글의 "원인 4 - PL/SQL 내부 ROLE 무효화"의 후속 심화편입니다.

 

ROLE이란 무엇인가

ROLE은 권한들의 묶음(이름 있는 권한 집합) 입니다. 사용자에게 권한을 직접 부여하는 대신, 권한을 ROLE에 부여하고 ROLE을 사용자에게 부여합니다.

직접 부여 vs ROLE 비교

-- ❌ 직접 부여 방식 (사용자 100명 × 객체 50개 = 5,000번 GRANT)
GRANT SELECT ON hr.employees TO user1;
GRANT SELECT ON hr.departments TO user1;
GRANT SELECT ON hr.employees TO user2;
GRANT SELECT ON hr.departments TO user2;
-- ... 무한 반복

-- ✅ ROLE 방식 (객체 50번 + 사용자 100번 = 150번)
CREATE ROLE ROLE_HR_READ;
GRANT SELECT ON hr.employees TO ROLE_HR_READ;
GRANT SELECT ON hr.departments TO ROLE_HR_READ;
-- ... 객체별 1회씩

GRANT ROLE_HR_READ TO user1;
GRANT ROLE_HR_READ TO user2;
-- ... 사용자별 1회씩

운영 환경에서는 ROLE 사용이 표준이며, 권한 회수/감사도 훨씬 간편해집니다.

 

ROLE 기본 문법

생성 / 부여 / 회수 / 삭제

-- 생성
CREATE ROLE ROLE_HR_READ;

-- 비밀번호 보호 ROLE (활성화 시 비밀번호 필요)
CREATE ROLE ROLE_HR_ADMIN IDENTIFIED BY secret_pwd;

-- 권한 부여
GRANT SELECT ON hr.employees TO ROLE_HR_READ;
GRANT EXECUTE ON hr.pkg_payroll TO ROLE_HR_READ;

-- 시스템 권한도 부여 가능
GRANT CREATE SESSION TO ROLE_HR_READ;

-- 사용자에게 ROLE 부여
GRANT ROLE_HR_READ TO scott;

-- ROLE을 다른 ROLE에 부여 (계층 구조)
GRANT ROLE_HR_READ TO ROLE_HR_MANAGER;

-- 회수
REVOKE ROLE_HR_READ FROM scott;

-- 삭제
DROP ROLE ROLE_HR_READ;

활성화 / 비활성화

ROLE은 부여된 후에도 세션 단위로 활성/비활성 상태를 가집니다.

-- 현재 활성 ROLE 확인
SELECT * FROM session_roles;

-- 특정 ROLE만 활성화 (나머지는 비활성)
SET ROLE ROLE_HR_READ;

-- 모든 ROLE 활성화
SET ROLE ALL;

-- 비밀번호 보호 ROLE 활성화
SET ROLE ROLE_HR_ADMIN IDENTIFIED BY secret_pwd;

-- ROLE 모두 비활성화
SET ROLE NONE;

기본 활성화 설정

사용자가 접속 시 자동으로 활성화할 ROLE을 설정할 수 있습니다.

-- scott이 접속하면 ROLE_HR_READ만 자동 활성화
ALTER USER scott DEFAULT ROLE ROLE_HR_READ;

-- 모든 ROLE 자동 활성화 (기본값)
ALTER USER scott DEFAULT ROLE ALL;

-- 자동 활성화 없음
ALTER USER scott DEFAULT ROLE NONE;

 

★ PL/SQL에서 ROLE이 무효화되는 이유 (가장 중요한 부분)

ROLE 운영의 가장 큰 함정입니다. 다른 한국어 블로그에서 거의 다루지 않는 영역이에요.

증상

-- 1) ROLE에 SELECT 권한 부여
GRANT SELECT ON hr.employees TO ROLE_HR_READ;
GRANT ROLE_HR_READ TO scott;

-- 2) scott으로 접속해서 직접 SQL 실행 → ✅ 잘 됨
SELECT COUNT(*) FROM hr.employees;
-- COUNT(*)
-- ------
--    107

-- 3) 같은 SQL을 프로시저로 만들기 → ❌ 컴파일 시 ORA-00942
CREATE OR REPLACE PROCEDURE proc_count_emp AS
    v_cnt NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_cnt FROM hr.employees;
END;
/
-- 컴파일 에러: PL/SQL: ORA-00942: table or view does not exist

권한은 분명히 있는데 PL/SQL 안에서만 안 됩니다.

왜 이런 설계인가 - 보안 의도

오라클의 PL/SQL은 기본적으로 DEFINER rights(정의자 권한) 으로 동작합니다. 그리고 DEFINER rights PL/SQL이 컴파일될 때 ROLE은 모두 disable되어 평가됩니다.

왜? 보안 때문입니다. 시나리오를 생각해 봅시다.

• 사용자 A가 ROLE_READ를 통해 데이터에 접근 가능
• 사용자 A가 ROLE 권한으로 프로시저 생성
• 나중에 DBA가 사용자 A에게서 ROLE_READ 회수
• → 프로시저는 여전히 ROLE 권한으로 동작해버림 (보안 구멍)

이런 시나리오를 막기 위해 DEFINER rights는 직접 부여된 권한만 인정합니다. ROLE을 통한 우회를 차단하는 보안 메커니즘이에요.

해결 방법 3가지

방법 1: 직접 GRANT (가장 흔한 해결)

-- 객체 소유자(HR)에서 실행
GRANT SELECT ON employees TO scott;

-- 이후 프로시저 재컴파일
ALTER PROCEDURE proc_count_emp COMPILE;
-- → 정상 컴파일됨

방법 2: INVOKER rights로 변경

CREATE OR REPLACE PROCEDURE proc_count_emp
AUTHID CURRENT_USER  -- ← 호출자 권한으로 실행, ROLE 인식
AS
    v_cnt NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_cnt FROM hr.employees;
END;
/

AUTHID CURRENT_USER를 명시하면 호출자의 권한 + ROLE이 인식됩니다.

방법 3: Dynamic SQL 사용 (우회 기법, 일반적이지 않음)

CREATE OR REPLACE PROCEDURE proc_count_emp AS
    v_cnt NUMBER;
BEGIN
    -- 동적 SQL은 런타임에 권한 체크
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM hr.employees' INTO v_cnt;
END;
/

다만 이 방식은 가독성이 떨어지고 SQL 인젝션 위험이 있어 추천하지 않습니다.

 

DEFINER rights vs INVOKER rights

두 모델의 핵심 차이를 표로 정리합니다.

항목 DEFINER (기본) INVOKER (AUTHID CURRENT_USER)

실행 권한	프로시저 소유자 권한	프로시저 호출자 권한
ROLE 인식	❌ 무효화	✅ 인식
객체 해석	소유자 스키마 기준	호출자 스키마 기준
같은 코드, 다른 사용자	항상 같은 동작	사용자별 다른 동작 가능
보안 책임	소유자	호출자
일반 운영 권장	비즈니스 로직	공통 유틸리티

시나리오별 선택 가이드

DEFINER rights를 써야 할 때:

• 비즈니스 로직 (특정 테이블만 다루는 작업)
• 보안 캡슐화 (호출자에게 객체 접근 권한 없이 결과만 제공)
• 일관된 동작이 필요한 경우

INVOKER rights를 써야 할 때:

• 공통 유틸리티 (예: 모든 사용자가 자기 데이터를 다루는 함수)
• DBA용 진단 스크립트
• ROLE 권한이 반드시 필요한 경우
• SQL 없는 순수 PL/SQL 패키지 (DBMS_OUTPUT 등)

실전 예제

DEFINER rights 적합 케이스 (기본값):

-- HR 부서의 직원 통계를 모든 사용자에게 제공
-- 하지만 employees 테이블 자체 접근은 금지
CREATE OR REPLACE PROCEDURE get_emp_count(p_dept_id NUMBER, p_count OUT NUMBER)
-- AUTHID DEFINER (기본값, 생략 가능)
AS
BEGIN
    SELECT COUNT(*) INTO p_count
    FROM employees
    WHERE department_id = p_dept_id;
END;
/

-- 모든 사용자가 EXECUTE 권한만 받으면 됨
GRANT EXECUTE ON get_emp_count TO PUBLIC;

INVOKER rights 적합 케이스:

-- 호출자의 스키마에서 자기 테이블 통계를 보여주는 공통 유틸리티
CREATE OR REPLACE PROCEDURE my_table_stats
AUTHID CURRENT_USER  -- 호출자 권한
AS
BEGIN
    FOR rec IN (SELECT table_name, num_rows FROM user_tables) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.table_name || ': ' || rec.num_rows);
    END LOOP;
END;
/

이 프로시저는 SCOTT이 실행하면 SCOTT의 테이블, HR이 실행하면 HR의 테이블을 보여줍니다.

 

★ INVOKER rights의 보안 함정 - INHERIT PRIVILEGES (12c+)

12c부터 추가된 보안 메커니즘인데, 한국어 자료에 거의 다뤄지지 않는 부분입니다.

무엇이 문제인가

INVOKER rights 프로시저는 호출자의 권한으로 실행됩니다. 이게 보안적으로 위험할 수 있어요.

시나리오:

• 낮은 권한 사용자 LOW_USER가 INVOKER rights 프로시저 작성
• DBA가 이 프로시저를 호출함 (DBA의 권한으로 실행됨)
• LOW_USER가 이 프로시저 안에 악의적 코드를 심으면 DBA 권한으로 동작

해결: INHERIT PRIVILEGES 권한

12c부터 INVOKER rights 프로시저를 실행하려면 소유자가 호출자의 권한을 상속받을 수 있어야 합니다.

-- LOW_USER가 SCOTT의 권한을 상속받을 수 있도록 허용
GRANT INHERIT PRIVILEGES ON SCOTT TO LOW_USER;

-- 모든 사용자의 권한 상속 가능 (DBA만 부여)
GRANT INHERIT ANY PRIVILEGES TO LOW_USER;

이 권한이 없으면 INVOKER rights 프로시저 실행 시 ORA-06598: insufficient INHERIT PRIVILEGES privilege 가 발생합니다.

기본 정책

12c 신규 설치 시 기본적으로:

GRANT INHERIT PRIVILEGES ON 모든사용자 TO PUBLIC;

이 설정이 들어가 있습니다. 그래서 평소엔 문제없이 동작합니다. 다만 보안 강화 환경에서 이걸 REVOKE했다면 ORA-06598 사고가 발생할 수 있습니다.

실무 권장

-- DBA가 모든 사용자에게서 PUBLIC INHERIT 회수
REVOKE INHERIT PRIVILEGES ON SCOTT FROM PUBLIC;

-- 신뢰할 수 있는 사용자에게만 명시적으로 부여
GRANT INHERIT PRIVILEGES ON SCOTT TO trusted_user;

운영 환경의 보안 정책에 따라 결정하세요.

 

ROLE 표준 운영 패턴

권장 ROLE 계층 구조

사용자
  ↓ GRANT
ROLE_HR_MANAGER (관리자)
  ↓ 포함
ROLE_HR_WRITE (쓰기)
  ↓ 포함
ROLE_HR_READ (읽기)
  ↓ GRANT
객체 권한 (SELECT, INSERT, ...)

구현 예제

-- 1) 기본 읽기 ROLE
CREATE ROLE ROLE_HR_READ;
GRANT SELECT ON hr.employees TO ROLE_HR_READ;
GRANT SELECT ON hr.departments TO ROLE_HR_READ;

-- 2) 쓰기 ROLE (읽기 권한 포함)
CREATE ROLE ROLE_HR_WRITE;
GRANT ROLE_HR_READ TO ROLE_HR_WRITE;  -- 계층 구조
GRANT INSERT, UPDATE, DELETE ON hr.employees TO ROLE_HR_WRITE;

-- 3) 관리자 ROLE (쓰기 권한 포함)
CREATE ROLE ROLE_HR_MANAGER;
GRANT ROLE_HR_WRITE TO ROLE_HR_MANAGER;
GRANT EXECUTE ON hr.pkg_admin TO ROLE_HR_MANAGER;

-- 4) 사용자에게 적절한 레벨 부여
GRANT ROLE_HR_READ TO analyst_user;
GRANT ROLE_HR_WRITE TO operator_user;
GRANT ROLE_HR_MANAGER TO admin_user;

이렇게 하면 사용자 역할 변경 시 ROLE 한 줄만 변경하면 됩니다.

PL/SQL 호출 사용자를 위한 별도 처리

-- PL/SQL 안에서 사용할 권한은 직접 GRANT
GRANT SELECT ON hr.employees TO batch_user;
GRANT SELECT ON hr.departments TO batch_user;

-- 일반 조회용은 ROLE로
GRANT ROLE_HR_READ TO batch_user;

같은 사용자에게 ROLE과 직접 GRANT를 병행하는 것이 운영 환경의 일반적 패턴입니다.

 

자주 발생하는 함정과 진단 쿼리

함정 1: 권한이 있는지 헷갈릴 때

-- 현재 사용자의 직접 부여 권한
SELECT * FROM user_tab_privs;

-- ROLE 통해 받은 권한
SELECT * FROM role_tab_privs
WHERE role IN (SELECT granted_role FROM user_role_privs);

-- 두 가지 합쳐서 보기 (실효 권한)
SELECT 'DIRECT' AS source, table_name, privilege, NULL AS via_role
FROM   user_tab_privs
UNION ALL
SELECT 'ROLE', rtp.table_name, rtp.privilege, urp.granted_role
FROM   role_tab_privs rtp
JOIN   user_role_privs urp ON rtp.role = urp.granted_role;

함정 2: PL/SQL 컴파일 시 ORA-00942

원인 90%는 ROLE 권한 의존. 위에서 다룬 3가지 해결 방법 적용.

함정 3: ROLE이 활성화되지 않음

-- 부여된 ROLE 확인
SELECT * FROM user_role_privs;

-- 활성 ROLE 확인
SELECT * FROM session_roles;

-- 차이가 있으면 비활성 상태
SET ROLE 부여된_role_명;

함정 4: 보안 검토 시 ROLE 계층 추적

-- 특정 ROLE을 받은 모든 사용자/ROLE 추적 (재귀)
SELECT LEVEL, grantee, granted_role
FROM   dba_role_privs
START  WITH granted_role = 'ROLE_HR_READ'
CONNECT BY PRIOR grantee = granted_role;

이 쿼리로 ROLE 변경 시 영향받는 사용자를 미리 파악할 수 있습니다.

 

마무리

ROLE은 오라클 권한 관리의 표준 도구지만, PL/SQL의 DEFINER rights와 함께 쓸 때 발생하는 함정을 모르면 운영 사고로 직결됩니다. 핵심을 다시 한 번 정리하면:

1. DEFINER rights PL/SQL에서 ROLE 권한은 무효화됨 — 보안 설계이며 의도된 동작
2. PL/SQL용 권한은 직접 GRANT가 표준 — ROLE이 아니라 직접 부여
3. INVOKER rights (AUTHID CURRENT_USER) — ROLE 인식 가능, 단 보안 영향 고려
4. 12c+ INHERIT PRIVILEGES — INVOKER rights 보안 강화 메커니즘 숙지
5. 계층 구조 — READ → WRITE → ADMIN 같은 단계로 설계

운영 환경에서 ROLE을 도입할 때 가장 좋은 방법은 초기 설계 단계에서 PL/SQL 호출 패턴을 미리 식별하는 것입니다. 어떤 사용자가 PL/SQL을 통해 권한을 사용할지, 어떤 권한이 ROLE로 충분하고 어떤 권한은 직접 부여가 필요한지 미리 정리하면 ORA-00942 사고의 90%는 예방 가능합니다.

비슷한 ROLE 운영 사례나 더 좋은 패턴이 있다면 댓글로 공유해 주세요.