Oracle 存儲過程 ORA-00942 解析：角色權限在 PL/SQL 中失效與顯式授權方案

錯誤現象

存儲過程中執行動態 SQL 查詢其他用戶表：

CREATE OR REPLACE PROCEDURE print_all_students AS
  strSql VARCHAR2(400) := 'SELECT * FROM jelly.tb_student';
BEGIN
  EXECUTE IMMEDIATE strSql;  -- 或 OPEN CURSOR FOR strSql
  ...
END;

編譯通過，執行拋出：ORA-00942: table or view does not exist。但同 SQL 在 SQL*Plus 直接執行完全正常。

核心原因：角色權限在命名 PL/SQL 塊中失效

角色授予的權限，在函數、存儲過程、觸發器、包中全部失效。 僅直接授予用戶的物件權限（系統/物件權限）在 PL/SQL 執行時生效。

案例中：jelly2 透過 GRANT DBA TO jelly2（角色）獲得查詢 jelly.tb_student 的權限，SQL*Plus 執行時角色啟用，故成功；進入存儲過程後角色停用，權限消失，拋 942。

驗證場景重現

-- SYS 建用戶並授角色
CREATE USER jelly IDENTIFIED BY 123456;
CREATE USER jelly2 IDENTIFIED BY 123456;
GRANT DBA TO jelly;   -- 角色授權
GRANT DBA TO jelly2;  -- 角色授權

-- jelly 建表插入資料
CREATE TABLE tb_student (id VARCHAR2(10), name VARCHAR2(20), age NUMBER(3), sex VARCHAR2(2));
INSERT INTO tb_student VALUES ('68003001','果凍',23,'M'); ...

-- jelly2 執行查詢：成功（角色啟用）
SELECT * FROM jelly.tb_student;

-- jelly2 建存儲過程查詢同表：執行報 ORA-00942（角色失效）

解決方案：顯式授予物件權限

-- 以表擁有者或 DBA 執行
GRANT SELECT ON jelly.tb_student TO jelly2;
-- 或授予系統權限
GRANT SELECT ANY TABLE TO jelly2;

授權後，存儲過程執行即正常：

EXECUTE jelly2.print_all_students;
-- 輸出所有學生資料

最佳實踐原則

1. 生產環境嚴禁對應用用戶授予 DBA/資源角色： 僅按最小權限原則 GRANT SELECT/INSERT/UPDATE/DELETE ON schema.table TO app_user。
2. 跨 Schema 存取： 一律使用顯式物件授權，不依賴角色。
3. 動態 SQL 權限檢查： 開發階段以執行用戶在 SQL*Plus 測試動態 SQL 字串，再封裝入 PL/SQL，可提前發現權限缺口。
4. AUTHID CURRENT_USER (Invoker's Rights) 同樣受角色失效限制，不改變此規則。

總結

ORA-00942 在 PL/SQL 中「SQL*Plus 正常、存儲過程失敗」的經典成因是 **角色權限失效**。解法：**顯式 GRANT 物件/系統權限給執行用戶**，徹底避免角色依賴，符合最小權限安全原則。