Oracle 儲存程序中啟用角色權限

問題現象

使用者擁有 DBA 角色，但在儲存程序中執行 EXECUTE IMMEDIATE 'CREATE TABLE ...' 仍報 ORA-01031: insufficient privileges。原因：定義者權限程序預設停用角色，僅啟用直接授予的系統/物件權限。

解決方案：AUTHID CURRENT_USER

-- 修改前：定義者權限，角色失效
CREATE OR REPLACE PROCEDURE p_create_table IS
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE test_tbl (id NUMBER)';
END;
/

-- 修改後：呼叫者權限，角色生效
CREATE OR REPLACE PROCEDURE p_create_table
AUTHID CURRENT_USER IS
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE test_tbl (id NUMBER)';
END;
/

驗證結果

SQL> EXEC p_create_table;
PL/SQL procedure successfully completed.

原理對比

注意事項

• AUTHID CURRENT_USER 會導致程序內未限定 Schema 的物件引用依呼叫者 Schema 解析，需謹慎設計。
• 若需同時兼顧封裝性與角色權限，可將敏感操作拆分為 AUTHID CURRENT_USER 的薄包裝程序。
• 12c+ 可搭配 BEQUEATH CURRENT_USER 於 View 實現類似效果。

替代方案：顯式授權

若不想改變權限模型，需逐一授予系統權限：

GRANT CREATE TABLE TO app_user;
GRANT CREATE SEQUENCE TO app_user;
-- ...

但維護成本高，易遺漏。