ORA-01031 오류 원인과 해결 방법 완벽 가이드

#oracle #ora01031 #ora #dba

ORA-01031란?

ORA-01031: insufficient privileges 에러는 현재 데이터베이스 세션의 사용자가 요청한 작업을 수행하기 위한 권한이 충분하지 않을 때 발생하는 Oracle 에러입니다. 단순한 테이블 조회부터 DDL 실행, 시스템 관리 작업에 이르기까지 권한이 없는 모든 작업에서 발생할 수 있으며, 특히 운영 환경에서 계정 관리가 미흡할 때 자주 마주치는 에러입니다. 개발 초기에는 DBA 권한으로 작업하다가 운영 계정으로 전환하거나, 롤(Role) 기반 권한이 세션 컨텍스트에서 비활성화된 경우에도 동일한 에러가 발생할 수 있습니다.

주요 발생 원인

객체에 대한 직접 권한 미부여
특정 테이블, 뷰, 프로시저 등에 대해 SELECT, INSERT, UPDATE, DELETE, EXECUTE 등의 권한이 해당 사용자에게 직접 부여되지 않은 경우입니다. 특히 다른 스키마 소유의 객체에 접근할 때 자주 발생하며, 개발자가 자신의 스키마가 아닌 타 스키마 테이블에 쿼리를 날릴 때 흔히 겪는 상황입니다.
롤(Role)을 통한 권한 부여 후 스토어드 프로그램에서 사용 시 충돌
Oracle에서는 저장 프로시저(Stored Procedure), 함수(Function), 패키지(Package) 내부에서는 롤을 통해 부여된 권한이 적용되지 않습니다. 직접 권한(Direct Grant)만 인식하기 때문에, 롤로만 권한을 받은 사용자가 PL/SQL 블록 내에서 해당 객체에 접근하면 ORA-01031이 발생합니다.
시스템 권한 미부여 (CREATE, ALTER, DROP 등)
DDL 작업(테이블 생성, 인덱스 생성, 뷰 생성 등)을 수행하려면 해당 시스템 권한이 필요합니다. CREATE TABLE, CREATE VIEW, CREATE PROCEDURE 등의 권한 없이 DDL을 실행하면 이 에러가 발생하며, 특히 신규 계정 생성 직후 기본 권한만 부여된 상태에서 자주 발생합니다.
SYSDBA / SYSOPER 권한 없이 관리자 접속 시도
CONNECT / AS SYSDBA 형태로 접속하려 할 때 해당 OS 사용자가 dba 그룹에 포함되어 있지 않거나, password file에 등록되지 않은 경우에 발생합니다. 원격 접속 시 password file이 존재하지 않아도 동일한 에러가 발생할 수 있습니다.
INVOKER RIGHTS vs DEFINER RIGHTS 혼동
기본적으로 PL/SQL 객체는 DEFINER RIGHTS 방식으로 동작하여 소유자의 권한으로 실행됩니다. 그러나 AUTHID CURRENT_USER로 선언된 INVOKER RIGHTS 프로시저에서는 호출자의 권한을 기준으로 실행되므로, 호출자에게 권한이 없으면 ORA-01031이 발생합니다.

해결 방법

원인 1: 객체 권한 직접 부여

다른 스키마의 테이블이나 객체에 접근 권한을 부여합니다.

-- HR 스키마의 EMPLOYEES 테이블에 대해 APP_USER에게 SELECT 권한 부여
GRANT SELECT ON hr.employees TO app_user;

-- INSERT, UPDATE, DELETE 권한 함께 부여
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO app_user;

-- 특정 컬럼에만 UPDATE 권한 부여 (컬럼 레벨 권한)
GRANT UPDATE (salary, job_id) ON hr.employees TO app_user;

-- 프로시저 실행 권한 부여
GRANT EXECUTE ON hr.process_employee TO app_user;

-- 권한 부여 후 확인
SELECT grantee, table_name, privilege, grantable
FROM dba_tab_privs
WHERE grantee = 'APP_USER'
ORDER BY table_name, privilege;

원인 2: PL/SQL 내 롤 권한 문제 해결

저장 프로그램 내부에서 롤로 부여된 권한은 동작하지 않으므로 직접 권한을 부여합니다.

-- 문제 상황: DEVELOPER_ROLE을 통해 권한을 받은 경우
-- PL/SQL 내부에서 hr.employees 접근 불가

-- 잘못된 방식 (롤을 통한 권한 - PL/SQL 내부에서 미동작)
GRANT SELECT ON hr.employees TO developer_role;
GRANT developer_role TO app_user;

-- 올바른 방식 (직접 권한 부여)
GRANT SELECT ON hr.employees TO app_user;

-- 현재 세션에서 활성화된 롤 확인
SELECT * FROM session_roles;

-- 사용자에게 직접 부여된 권한 확인
SELECT grantee, owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee = 'APP_USER';

-- 롤을 통해 부여된 권한 확인 (PL/SQL 내에서는 미적용)
SELECT role, owner, table_name, privilege
FROM role_tab_privs
WHERE role IN (
    SELECT granted_role FROM dba_role_privs WHERE grantee = 'APP_USER'
);

원인 3: 시스템 권한 부여

DDL 실행에 필요한 시스템 권한을 부여합니다.

-- 기본 개발 계정 생성 및 권한 부여 예시
CREATE USER dev_user IDENTIFIED BY "SecurePass123!";

-- 접속 권한 부여
GRANT CREATE SESSION TO dev_user;

-- 객체 생성 관련 시스템 권한 부여
GRANT CREATE TABLE TO dev_user;
GRANT CREATE VIEW TO dev_user;
GRANT CREATE PROCEDURE TO dev_user;
GRANT CREATE SEQUENCE TO dev_user;
GRANT CREATE TRIGGER TO dev_user;
GRANT CREATE SYNONYM TO dev_user;

-- 테이블스페이스 쿼터 설정 (UNLIMITED 주의 - 운영환경에서는 제한 권장)
ALTER USER dev_user QUOTA 500M ON users;

-- 부여된 시스템 권한 전체 확인
SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE grantee = 'DEV_USER'
ORDER BY privilege;

-- 특정 권한 회수
REVOKE CREATE TABLE FROM dev_user;

원인 4: SYSDBA 접속 문제 해결

-- [OS 레벨] oracle 유저가 dba 그룹에 포함되어 있는지 확인 (Linux/Unix)
-- $ id oracle
-- uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba)

-- password file 존재 여부 확인 (Oracle 19c 기준)
-- $ORACLE_HOME/dbs/orapw{SID} 파일 존재 여부 확인

-- password file 재생성 (SYS 비밀번호 재설정)
-- $ orapwd file=$ORACLE_HOME/dbs/orapwORCL password=새비밀번호 entries=10

-- 원격 SYSDBA 접속 허용 여부 확인
SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE;

-- 필요 시 파라미터 변경 (EXCLUSIVE 또는 SHARED)
-- ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

-- SYSDBA 권한을 가진 사용자 목록 확인
SELECT * FROM v$pwfile_users;

-- 특정 사용자에게 SYSDBA 권한 부여
GRANT SYSDBA TO dba_user;

원인 5: INVOKER RIGHTS 프로시저 권한 문제

-- INVOKER RIGHTS 프로시저 예시 (AUTHID CURRENT_USER)
CREATE OR REPLACE PROCEDURE get_employee_info(p_emp_id IN NUMBER)
AUTHID CURRENT_USER  -- 호출자 권한으로 실행
AS
    v_name VARCHAR2(100);
BEGIN
    -- 호출자에게 hr.employees SELECT 권한이 없으면 ORA-01031 발생
    SELECT first_name || ' ' || last_name
    INTO v_name
    FROM hr.employees
    WHERE employee_id = p_emp_id;

    DBMS_OUTPUT.PUT_LINE('직원명: ' || v_name);
END;
/

-- 해결책 1: 호출자에게 직접 권한 부여
GRANT SELECT ON hr.employees TO calling_user;

-- 해결책 2: DEFINER RIGHTS 방식으로 변경 (기본값)
CREATE OR REPLACE PROCEDURE get_employee_info(p_emp_id IN NUMBER)
-- AUTHID DEFINER (기본값, 생략 가능)
AS
    v_name VARCHAR2(100);
BEGIN
    SELECT first_name || ' ' || last_name
    INTO v_name
    FROM hr.employees
    WHERE employee_id = p_emp_id;

    DBMS_OUTPUT.PUT_LINE('직원명: ' || v_name);
END;
/

권한 문제 전반 진단 쿼리


sql
-- 특정 사용자의 모든 권한 현황 종합 조회
SELECT 'SYSTEM PRIVILEGE' AS priv_type,
       privilege AS privilege_name,
       NULL AS object_name,
       admin_option AS extra_option

DEV Community