Oracle 存儲過程建表權限不足 (ORA-01031) 解決方案：CREATE ANY TABLE 授權

現象：在存儲過程中使用 `EXECUTE IMMEDIATE 'CREATE TABLE ...'` 報 `ORA-01031: insufficient privileges`。原因：PL/SQL 定義者權限執行時，**角色權限不生效**，使用者必須被**直接授予**系統權限 `CREATE ANY TABLE` (或 `CREATE TABLE` 配合 `ALTER SESSION SET CURRENT_SCHEMA`)。本文提供最小權限解決方案與替代設計模式。

重現場景

-- 當前使用者 SUK (假設有 RESOURCE 角色，含 CREATE TABLE)
CREATE OR REPLACE PROCEDURE p_create_table AS
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE create_table (id INT)';
END;
/

-- 執行
EXEC p_create_table;
-- 報錯：
-- ORA-01031: insufficient privileges
-- ORA-06512: at "SUK.P_CREATE_TABLE", line 3

根本原因分析

解決方案對比

方案一：直接授予 CREATE TABLE (推薦：同 Schema 建表)

-- 以 DBA 或擁有 ADMIN OPTION 的使用者執行
GRANT CREATE TABLE TO suk;

• 優點：最小權限，僅能在自己 Schema 建表。
• 缺點：無法跨 Schema 建表。

方案二：授予 CREATE ANY TABLE (跨 Schema 需求)

GRANT CREATE ANY TABLE TO suk;

• 優點：可在任何 Schema 建表 (需配合 `ALTER SESSION SET CURRENT_SCHEMA` 或指定 `schema.table`)。
• 缺點：高風險權限，違反最小權限，稽核通常不通過。

方案三：改用呼叫者權限 (12c+ / AUTHID CURRENT_USER)

CREATE OR REPLACE PROCEDURE p_create_table
AUTHID CURRENT_USER AS  -- 或 12c+ DEFAULT AUTHID CURRENT_USER
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE create_table (id INT)';
END;
/

• 優點：執行者若有 `CREATE TABLE` (含角色) 即可建表。
• 缺點：權限檢查延遲到執行時，需確保所有呼叫者具備權限。

方案四：封裝為定義者權限但由 DBA 代建 (最安全)

-- DBA 建立通用建表程序，應用僅傳參數
CREATE OR REPLACE PROCEDURE admin_create_table (
  p_owner      IN VARCHAR2,
  p_table_name IN VARCHAR2,
  p_ddl        IN CLOB
) AUTHID DEFINER AS
  PRAGMA AUTONOMOUS_TRANSACTION;  -- 獨立交易
BEGIN
  -- 這裡可加入命名規範檢查、審計日誌、配額驗證
  EXECUTE IMMEDIATE p_ddl;
  COMMIT;
END;
/
-- 授予應用使用者 EXECUTE 權限
GRANT EXECUTE ON admin_create_table TO app_user;

驗證腳本

-- 授權後重新編譯存儲過程 (可選)
ALTER PROCEDURE p_create_table COMPILE;

-- 再次執行
EXEC p_create_table;
-- 應成功

-- 查詢建立的表
SELECT * FROM user_tables WHERE table_name = 'CREATE_TABLE';

最佳實務建議

1. 原則：存儲過程中執行 DDL 屬反模式，優先考慮靜態表結構、GTT (Global Temporary Table)、分區表、JSON/XML 靈活欄位。
2. 若必須動態建表：採用方案四 (管理程序代建)，集中控制、審計、命名規範。
3. 權限最小化：優先 `GRANT CREATE TABLE TO user`，禁用 `CREATE ANY TABLE` 除非極少數跨 Schema 管理場景。
4. 文檔化：記錄每個動態 DDL 程序的用途、呼叫者、權限來源。

理解「角色在定義者權限中失效」是解決 `ORA-01031` 的關鍵。直接授予系統權限或改用呼叫者權限/管理程序代建，即可安全解決存儲過程建表權限問題。