[Oracle] ORA-01031: insufficient privileges (권한 부족) 원인 분석 및 해결

by

ORA-01031: insufficient privileges 오류는 Oracle 데이터베이스에서 특정 작업을 수행하는 데 필요한 권한이 현재 세션(사용자)에 부여되지 않았을 때 발생하는 문제입니다. 이 오류는 단순한 SELECT 구문부터 데이터 구조 변경(DDL), 시스템 설정 변경에 이르기까지 광범위한 작업에서 나타날 수 있습니다.

이번 포스팅에서는 ORA-01031 오류가 발생하는 주요 시나리오를 통해 Oracle의 권한 관리 체계를 이해하고, 명확한 해결 방안을 제시합니다.

Oracle 권한의 종류: 시스템 권한과 객체 권한

ORA-01031 오류를 이해하기 위해서는 먼저 Oracle의 두 가지 주요 권한 유형을 구분해야 합니다.

  • 시스템 권한 (System Privileges): 데이터베이스 전반에 걸쳐 특정 유형의 작업을 수행할 수 있는 권한입니다. 예를 들어, 데이터베이스에 접속(CREATE SESSION), 테이블을 생성(CREATE TABLE), 다른 사용자의 테이블을 조회(SELECT ANY TABLE)하는 등의 권한이 여기에 속합니다. 이 권한은 주로 DBA가 사용자에게 부여합니다.
  • 객체 권한 (Object Privileges): 특정 테이블, 뷰, 프로시저 등 하나의 객체(Object)에 대해 특정 작업을 수행할 수 있는 권한입니다. 예를 들어, SCOTT 사용자의 EMP 테이블에 대한 SELECT, INSERT, UPDATE, DELETE 권한 등이 있습니다. 이 권한은 객체의 소유자(Owner)가 다른 사용자에게 직접 부여할 수 있습니다.

ORA-01031 오류는 이 두 가지 유형의 권한 중 현재 수행하려는 작업에 필요한 특정 권한이 없을 때 발생합니다.

주요 발생 시나리오 및 해결 방안

1. 객체에 대한 접근/변경 권한이 없는 경우

가장 흔한 경우로, 다른 스키마(사용자) 소유의 테이블, 뷰, 시퀀스 등의 객체에 접근하려 할 때 발생합니다.

  • 발생 예시: USER_A가 USER_B 소유의 SALES 테이블을 조회하려고 할 때
-- USER_A 세션에서 실행 
SELECT * FROM USER_B.SALES; 
-- ORA-01031 발생
  • 원인 분석: USER_B가 USER_A에게 SALES 테이블에 대한 SELECT 객체 권한을 부여하지 않았습니다.
  • 해결 방안: 객체 소유자인 USER_B가 USER_A에게 필요한 권한을 직접 부여해야 합니다.
-- USER_B 세션에서 실행 
GRANT SELECT ON SALES TO USER_A; 

-- INSERT, UPDATE, DELETE 권한이 모두 필요하다면 
GRANT INSERT, UPDATE, DELETE ON SALES TO USER_A; 

-- 모든 권한을 부여하려면 
GRANT ALL ON SALES TO USER_A;

2. 테이블 생성, 인덱스 생성 등 DDL 작업 권한이 없는 경우

새로운 테이블을 만들거나 기존 테이블의 구조를 변경하는 등의 DDL(Data Definition Language) 작업을 수행하기 위해서는 적절한 시스템 권한이 필요합니다.

  • 발생 예시: 신규로 생성된 사용자 USER_C가 테이블 생성을 시도할 때
-- USER_C 세션에서 실행 
CREATE TABLE MY_TABLE (ID NUMBER); 
-- ORA-01031 발생
  • 원인 분석: USER_C에게 테이블을 생성할 수 있는 CREATE TABLE 시스템 권한이 부여되지 않았습니다.
  • 해결 방안: DBA 권한을 가진 사용자가 USER_C에게 CREATE TABLE 시스템 권한을 부여합니다.
-- SYS 또는 DBA 권한을 가진 사용자 세션에서 실행 
GRANT CREATE TABLE TO USER_C;
  • 또한, 테이블을 생성하기 위해서는 데이터를 저장할 공간(Tablespace)에 대한 사용 권한(Quota)도 필요할 수 있습니다.
-- USERS 테이블스페이스에 무제한 용량을 할당 
ALTER USER USER_C QUOTA UNLIMITED ON USERS;

3. 프로시저, 함수, 트리거 실행 시 권한 문제

프로시저나 함수 내부에서 다른 객체에 접근할 때 권한 문제가 발생할 수 있습니다. 이는 Oracle의 권한 적용 방식(Definer’s Rights vs Invoker’s Rights)과 관련이 있어 조금 더 복잡합니다.

  • 발생 예시: USER_B가 소유한 프로시저 UPDATE_SALES 내부에서 HR 스키마의 EMPLOYEES 테이블을 수정하는 로직이 있다고 가정합니다. USER_A가 USER_B로부터 UPDATE_SALES 프로시저의 EXECUTE 권한을 받아 실행했지만 ORA-01031이 발생합니다.
  • 원인 분석: 기본적으로 프로시저는 **생성자 기준 권한(Definer’s Rights)**으로 실행됩니다. 즉, 프로시저를 실행하는 USER_A가 아니라 프로시저의 소유자인 USER_B가 HR.EMPLOYEES 테이블을 수정할 권한이 있어야 합니다.
  • 해결 방안: HR 사용자가 프로시저의 소유자인 USER_B에게 EMPLOYEES 테이블에 대한 UPDATE 권한을 부여해야 합니다. 중요한 점은 **WITH GRANT OPTION**을 사용하여 권한을 직접 부여해야 하며, ROLE을 통한 간접적인 권한 부여는 프로시저 내에서 인정되지 않는다는 것입니다.
-- HR 사용자 세션에서 실행 
GRANT UPDATE ON EMPLOYEES TO USER_B;

4. SYSDBA / SYSOPER 권한이 필요한 작업을 일반 사용자로 시도하는 경우

데이터베이스 시작/종료, 파라미터 변경, 특정 시스템 뷰 조회 등은 강력한 관리자 권한(SYSDBA 또는 SYSOPER)을 요구합니다.

  • 발생 예시: 일반 사용자로 접속하여 데이터베이스를 종료하려고 할 때
-- 일반 사용자 세션 
SHUTDOWN IMMEDIATE; 
-- ORA-01031 발생
  • -- 일반 사용자 세션 SHUTDOWN IMMEDIATE; -- ORA-01031 발생
  • 원인 분석: SHUTDOWN 명령어는 SYSDBA 또는 SYSOPER 시스템 권한이 있는 사용자만 실행할 수 있습니다.
  • 해결 방안: 데이터베이스에 접속할 때 관리자 권한으로 접속해야 합니다.
-- SQL*Plus 또는 터미널에서 접속 시 
CONNECT sys/password AS SYSDBA

권한 문제 해결을 위한 진단 쿼리

문제가 발생했을 때, 현재 사용자에게 어떤 권한이 부여되어 있는지 확인하는 것은 문제 해결의 핵심입니다.

-- 현재 사용자에게 부여된 모든 시스템 권한 확인
SELECT * FROM SESSION_PRIVS;

-- 현재 사용자에게 부여된 모든 롤(ROLE) 확인
SELECT * FROM SESSION_ROLES;

-- 특정 롤(예: CONNECT)에 부여된 시스템 권한 확인
SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'CONNECT';

-- 현재 사용자가 접근 가능한 모든 객체 확인
SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = '객체명';

-- 특정 테이블에 대해 현재 사용자에게 부여된 객체 권한 확인
SELECT * FROM USER_TAB_PRIVS_RECD WHERE TABLE_NAME = '테이블명';

ORA-01031 오류는 Oracle의 정교한 보안 모델이 정상적으로 작동하고 있다는 증거이기도 합니다. 이 오류를 통해 사용자와 객체 간의 권한 관계를 명확히 이해하고, 최소한의 권한만 부여하는 보안 원칙(Principle of Least Privilege)을 적용하는 계기로 삼을 수 있습니다.