【Oracle】ORA-01031 完全ガイド｜insufficient privileges（権限不足）の原因・ロールと PL/SQL の落とし穴・対処法まで解説

-- 現在ログイン中のユーザーを確認する
SELECT USER FROM DUAL;

-- システム権限（CREATE TABLE・CONNECT など）を確認する
SELECT privilege, admin_option
FROM   USER_SYS_PRIVS
ORDER  BY privilege;

-- オブジェクト権限（特定テーブルへの SELECT・INSERT など）を確認する
SELECT owner, table_name, privilege, grantable
FROM   USER_TAB_PRIVS
WHERE  grantee = USER
ORDER  BY owner, table_name, privilege;

-- 現在のセッションで有効な権限（ロール経由を含む）を確認する
-- SESSION_PRIVS: ロール経由の権限も含めて現在セッションで有効な権限
SELECT privilege FROM SESSION_PRIVS ORDER BY privilege;

-- 付与されているロールを確認する
SELECT granted_role, default_role
FROM   USER_ROLE_PRIVS
ORDER  BY granted_role;

-- ロール経由で付与されているオブジェクト権限を確認する（DBA 権限が必要）
SELECT r.granted_role, tp.owner, tp.table_name, tp.privilege
FROM   USER_ROLE_PRIVS r
JOIN   ROLE_TAB_PRIVS tp ON r.granted_role = tp.role
ORDER  BY r.granted_role, tp.owner, tp.table_name;

-- ユーザー APP_USER に CONNECT・RESOURCE ロールが付与されている
-- RESOURCE ロールには CREATE TABLE などの権限が含まれる

-- NG: ロール経由の権限でプロシージャ内の DDL は実行できない
CREATE OR REPLACE PROCEDURE create_temp_table AS
BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE work_temp (id NUMBER)';
    -- ORA-01031 が発生する（RESOURCE ロール経由の CREATE TABLE は無効）
END;
/

-- 対処: CREATE TABLE 権限を直接 GRANT する
-- ※DBA がコマンドを実行する
GRANT CREATE TABLE TO app_user;  -- ロール経由ではなく直接付与する

-- 確認: USER_SYS_PRIVS に CREATE TABLE が直接付与されているかどうか確認
SELECT privilege FROM USER_SYS_PRIVS WHERE privilege = 'CREATE TABLE';

-- 別テーブルを参照する場合もロール権限は無効
CREATE OR REPLACE PROCEDURE read_other_schema AS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM other_schema.some_table;
    -- ORA-01031 が発生する（ロール経由の SELECT 権限は DEFINER RIGHTS では無効）
END;
/

-- 対処: 直接権限を付与する（DBA または所有者が実行）
GRANT SELECT ON other_schema.some_table TO app_user;

-- AUTHID CURRENT_USER (INVOKER RIGHTS) にすれば呼び出し元の権限で動く
CREATE OR REPLACE PROCEDURE read_other_schema
AUTHID CURRENT_USER   -- ← 呼び出しユーザーの権限（ロール含む）で実行される
AS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM other_schema.some_table;
    -- 呼び出しユーザーが other_schema.some_table の SELECT 権限を持っていれば成功する
END;
/

-- オブジェクト権限の付与（DBA または所有者が実行）
GRANT SELECT ON hr.employees TO app_user;                       -- 1テーブルへの SELECT
GRANT SELECT, INSERT, UPDATE ON hr.orders TO app_user;          -- 複数権限の一括付与
GRANT SELECT ON hr.employees TO app_user WITH GRANT OPTION;     -- 再付与権限付き
GRANT SELECT ANY TABLE TO app_user;                             -- 任意テーブルの SELECT（強力）
GRANT EXECUTE ON hr.process_pkg TO app_user;                    -- パッケージの実行権限

-- スキーマ内の全テーブルへの SELECT を一括付与する（PL/SQL ループ）
BEGIN
    FOR t IN (SELECT table_name FROM dba_tables WHERE owner = 'HR') LOOP
        EXECUTE IMMEDIATE 'GRANT SELECT ON HR.' || t.table_name || ' TO APP_USER';
    END LOOP;
END;
/

-- システム権限の付与
GRANT CREATE SESSION TO app_user;      -- ログイン権限
GRANT CREATE TABLE   TO app_user;      -- テーブル作成
GRANT CREATE VIEW    TO app_user;      -- ビュー作成
GRANT CREATE PROCEDURE TO app_user;    -- プロシージャ・関数・パッケージ作成
GRANT CREATE SEQUENCE  TO app_user;    -- シーケンス作成
GRANT CREATE TRIGGER   TO app_user;    -- トリガー作成

-- 権限の剥奪
REVOKE SELECT ON hr.employees FROM app_user;
REVOKE CREATE TABLE FROM app_user;

-- 付与した権限を確認する（DBA 権限が必要）
SELECT grantee, owner, table_name, privilege, grantable
FROM   DBA_TAB_PRIVS
WHERE  owner = 'HR'
ORDER  BY grantee, table_name;