[ORACLE] SESSION_ROLES 완벽 가이드: 현재 세션에 활성화된 역할을 정확히 읽고, 점검하고, 통제하는 방법

[ORACLE] SESSION_ROLES

 

SESSION_ROLES는 “지금 이 세션에서 실제로 쓰이고 있는 역할(role)”을 정확히 보여주는 가장 단순하면서도 강력한 뷰입니다. 권한 감사, 최소 권한 원칙(Least Privilege) 점검, 애플리케이션 세션 검증, 운영 이슈 재현 등에서 첫 번째로 확인해야 할 객체죠. 이 글은 오늘 바로 적용할 수 있는 점검 절차와 예제, 그리고 관련 뷰와의 정확한 비교표까지 담았습니다.

핵심 요약: SESSION_ROLES는 “활성화된 역할 목록”을 한 줄에 한 개씩 반환합니다(열: ROLE). 부여는 되었지만 현재 세션에서 꺼져 있는 역할은 보이지 않습니다.

1) 왜 SESSION_ROLES부터 봐야 할까?

• 현재 영향 범위: 지금 실행되는 SQL/PLSQL이 어떤 역할의 시스템/객체 권한에 의해 허용되는지 가늠할 수 있습니다.
• 사고 트레이싱: “테스트에선 권한 오류였는데 운영에선 실행됨” 같은 케이스에서 역할 활성화 차이를 즉시 확인합니다.
• 보안 검증: 애플리케이션 로그인 직후 SET ROLE 정책이 기대대로 적용됐는지 즉석 점검이 가능합니다.

2) 뷰 구조와 반환 컬럼

SESSION_ROLES는 매우 단순합니다.

• ROLE: 현재 세션에서 활성화된 역할 이름

한 행에 하나의 역할이 표시되며, 정렬은 보장되지 않습니다(필요 시 ORDER BY 사용).

3) 바로 쓰는 기본 쿼리

-- 현재 세션에서 활성화된 역할 목록
SELECT role
FROM   session_roles
ORDER  BY role;

-- 특정 중요한 역할이 켜져 있는지 빠르게 진단
SELECT CASE WHEN EXISTS (SELECT 1 FROM session_roles WHERE role = 'DBA')
THEN 'ON' ELSE 'OFF' END AS DBA_ROLE; 

실무 팁: 애플리케이션 커넥션 풀 초기화 SQL에 위 검증을 넣고, 기대와 다르면 즉시 로깅/차단하도록 해두면 운영 사고를 크게 줄일 수 있습니다.

4) 역할 활성화/비활성화 제어(SET ROLE)

-- 세션에서 모든 기본 역할만 활성화
SET ROLE ALL;

-- 특정 역할만 활성화
SET ROLE app_user_role;

-- 비밀번호가 걸린 역할 활성화
SET ROLE secure_role IDENTIFIED BY "s3cr3t";

-- 모든 역할 비활성화 (필요한 최소 권한만 유지)
SET ROLE NONE; 

주의: SET ROLE은 세션 단위입니다. 커넥션 풀을 쓰면 다음 요청에서 다른 세션 상태가 재사용될 수 있으니, 풀 정책과 초기화 SQL을 명확히 하세요.

5) “역할 부여”와 “역할 활성화”는 다릅니다

USER_ROLE_PRIVS/DBA_ROLE_PRIVS에 보이는 것은 부여(grant) 상태이고, SESSION_ROLES는 활성화(enabled) 상태입니다. 기본 역할(default role)이더라도 SET ROLE NONE이면 SESSION_ROLES에서는 사라집니다.

6) 관련 뷰 정확 비교

뷰	핵심 목적	주요 컬럼(요지)	표시 범위	대표 활용
SESSION_ROLES	현재 세션에 활성화된 역할	ROLE	나(현재 세션)	실행 즉시 권한 영향 진단, 최소 권한 점검
SESSION_PRIVS	현재 세션에 유효한 시스템 권한	PRIVILEGE	나(현재 세션)	GRANT 경로 상관없이 지금 가능한 시스템 권한 확인
USER_ROLE_PRIVS	사용자에게 부여된 역할	GRANTED_ROLE, DEFAULT_ROLE 등	나(정적 부여 상태)	부여 현황 감사, 기본 역할 정책 확인
ROLE_SYS_PRIVS	역할이 포함한 시스템 권한	ROLE, PRIVILEGE, ADMIN_OPTION	역할 정의	역할 설계/영향 분석
ROLE_TAB_PRIVS	역할이 가진 객체 권한	ROLE, OWNER, TABLE_NAME, PRIVILEGE	역할 정의	객체 접근 범위 파악

7) 보안 체크리스트

1. 세션 시작 직후 SESSION_ROLES를 조회해 기대 역할만 켜졌는지 확인한다.
2. 중요 트랜잭션 직전 SET ROLE로 필요한 최소 역할만 유지한다(가능하면 SET ROLE NONE 후 필요한 역할만 재활성화).
3. 애플리케이션 계정은 기본 역할 최소화 + 보안 역할(조건부 활성화)을 병행한다.
4. 감사: 로그인/롤 전환 시각, 활성 역할 스냅샷을 애플리케이션 로그에 남긴다.

8) 실무 시나리오 3가지

① 운영과 테스트 동작 불일치

-- 운영에서만 INSERT가 성공한다면
SELECT role FROM session_roles ORDER BY role;

-- 운영엔 'APP_RW_ROLE'이 활성화, 테스트엔 NONE일 수 있음
-- 해결: 테스트 초기화 SQL에 'SET ROLE APP_RW_ROLE;' 명시

② 커넥션 풀 재사용으로 인한 권한 누수

-- 풀 획득 시 항상 상태를 리셋
SET ROLE NONE;
SET ROLE APP_RO_ROLE;  -- 필요한 최소 권한만
-- 검증
SELECT COUNT(*) FROM session_roles WHERE role NOT IN ('APP_RO_ROLE');

③ 보안 역할(secure application role) 검증

-- 조건을 만족해야만 활성화되는 역할 설계 시
-- 세션 컨텍스트를 세팅한 뒤
-- (예: DBMS_SESSION.SET_CONTEXT 호출 후)
SET ROLE SECURE_APP_ROLE;
SELECT role FROM session_roles WHERE role = 'SECURE_APP_ROLE';

활성화 실패 시 정책/컨텍스트 조건을 재점검합니다.

9) 트러블슈팅: 자주 만나는 오류/상황

• 역할이 보이지 않는다: 기본 역할이 아니거나 SET ROLE NONE 상태일 수 있음. 로그인 트리거, 프로파일, 애플리케이션 초기화 SQL을 확인.
• 비밀번호가 필요한 역할: SET ROLE role IDENTIFIED BY "pwd" 구문 사용. 잘못된 비밀번호 또는 정책 위반 시 활성화 실패.
• Granted인데 활성화 안 됨: 보안 역할(조건부)일 수 있음. 세션 컨텍스트/정책 조건을 먼저 만족시키세요.

10) 권한 영향 경로 추적(빠른 분석 절차)

1. SESSION_ROLES에서 활성 역할 목록 확보
2. 각 역할에 대해 ROLE_SYS_PRIVS/ROLE_TAB_PRIVS 조회
3. SESSION_PRIVS로 “지금 가능한 시스템 권한”을 교차 검증

-- 1) 활성 역할
WITH r AS (
  SELECT role FROM session_roles
)
-- 2) 시스템 권한 영향
SELECT r.role, p.privilege
FROM   r
JOIN   role_sys_privs p ON p.role = r.role
ORDER  BY r.role, p.privilege;

-- 3) 객체 권한 영향
SELECT r.role, t.owner, t.table_name, t.privilege
FROM   session_roles r
JOIN   role_tab_privs t ON t.role = r.role
ORDER  BY r.role, t.owner, t.table_name;

11) 테스트 자동화에 넣어두면 좋은 단정문(assertion)

-- 기대 역할만 켜져 있어야 하는 경우
DECLARE
  v_unexpected_count INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO   v_unexpected_count
  FROM   session_roles
  WHERE  role NOT IN ('APP_RO_ROLE','APP_AUDIT_ROLE');  -- 허용 목록

IF v_unexpected_count > 0 THEN
RAISE_APPLICATION_ERROR(-20001, '예상치 못한 역할 활성화 감지');
END IF;
END;
/ 

12) 성능 관점의 주의

• 조회 비용: SESSION_ROLES 자체는 매우 작습니다. 다만 이를 기반으로 거대 카탈로그(ROLE_TAB_PRIVS 등)와 조인할 때 불필요한 전수 탐색을 피하도록 필터링/인덱스 사용을 고려하세요.
• 빈번한 SET ROLE: 초당 다수의 SET ROLE은 컨텍스트 스위칭 오버헤드가 생길 수 있습니다. 역할 전환 빈도를 최소화하세요.

13) 보안 운영 체크리스트 표(요약)

항목	권장	설명
기본 역할	최소화	로그인 시 자동 활성화되는 권한을 최소화
보안 역할	핵심 트랜잭션에 필수	컨텍스트/정책 조건을 만족해야만 활성화
커넥션 풀	세션 리셋	획득 시 SET ROLE NONE 후 필요한 역할만 활성화
로그	활성 역할 스냅샷	로그인/권한 전환 시점에 역할 목록 기록
자동 점검	테스트에 assertion	예상 외 역할 활성화 시 빌드/배포 차단

14) 자주 쓰는 관리 쿼리 모음

-- 현재 세션의 역할 수
SELECT COUNT(*) AS role_count FROM session_roles;

-- 특정 패턴의 역할만 활성화되었는지 검사
SELECT LISTAGG(role, ',') WITHIN GROUP (ORDER BY role) AS roles
FROM   session_roles
WHERE  role LIKE 'APP_%' ESCAPE '';

-- 활성 역할 > 시스템 권한 매핑 요약
SELECT r.role, COUNT(*) AS sys_privs
FROM   session_roles r
JOIN   role_sys_privs p ON p.role = r.role
GROUP  BY r.role
ORDER  BY sys_privs DESC; 

---

출처

• Oracle Database Reference: Data Dictionary → SESSION_ROLES (해당 뷰 설명 및 컬럼)
• Oracle Database SQL Language Reference: SET ROLE (역할 활성화/비활성화 구문)
• Oracle Database Security Guide: Roles and Privileges, Secure Application Roles (역할 설계 및 보안 모범 사례)