今天同事下午問我ORA-01031問題，在測試資料庫上，在儲存過程中用EXECUTE IMMEDIATE 'CREATE TABLE '出現ORA-01031錯誤。

一、我先確認一下使用者許可權：

SQL> select * from dba_role_privs

2 where grantee = 'MAHANSO';

GRANTEE GRANTED_ROLE ADMIN_ DEFAUL

-------------------- -------------------- ------ ------

MAHANSO DBA NO YES

MAHANSO RESOURCE NO YES

MAHANSO CONNECT NO YES

dba許可權都有，沒有問題啊。

二、test下儲存過程。找到出現問題程式碼：

EXECUTE IMMEDIATE 'create table FUND_MONEY_GBAK as select * from FUND_MONEY';

我放到匿名過程中可以執行。

declare

begin

EXECUTE IMMEDIATE 'create table FUND_MONEY_GBAK as select * from FUND_MONEY';

end;

匿名的儲存過程可以執行：EXECUTE IMMEDIATE 'create table FUND_MONEY_GBAK as select * from FUND_MONEY';

滿腦袋問號。

三、在網上找到詳細解釋：

詳細地址轉自：http://blog.sina.com.cn/s/blog_53d588e50100m092.html

預設情況下，在呼叫儲存過程使用者的角色是不起作用的，即在執行儲存過程時只有Public許可權。所以如果被呼叫的儲存過程中如果有execute immediate 'create table..'語句，將會引發ORA-01031: insufficient privileges錯誤。

儲存過程分為兩種，即DR(Definer's Rights ) Procedure和IR(Invoker's Rights ) Procedure。為什麼會有兩種儲存過程呢？其實考慮完下面的問題就清楚了。比如說使用者hrch建立了刪除表tar_table的儲存過程drop_table()，當使用者hrch呼叫時，即刪除使用者hrch下的表tar_table；如果是另一個使用者scott呼叫呢？是刪除使用者scott下的tar_table表呢，還是刪除使用者hrch下的tar_table呢？另外，如果儲存過程中包含建表語句，不管是使用者hrch還是使用者scott呼叫都會失敗，因為Public沒有建表許可權，除非為Public grant建表許可權。所以，儲存過程的呼叫者會面臨兩個問題：

儲存過程的名稱解析環境

儲存過程的執行許可權

這兩個問題可以在定義儲存過程時，透過指定AUTHID 屬性，即定義DR Procedure 和IR Procedure來解決。

DR Procedure

1、定義

CREATE OR REPLACE procedure DEMO(ID in NUMBER) AUTHID DEFINER as

...

BEGIN

...

END DEMO;

2、名稱解析環境為定義該儲存過程的使用者所在的Schema。

3、執行該儲存過程時只有Public許可權。

IR Procedure

1、定義

CREATE OR REPLACE procedure DEMO(ID in NUMBER) AUTHID CURRENT_USER as

...

BEGIN

...

END DEMO;

2、名稱解析環境為呼叫該儲存過程的使用者所在的Schema。

3、執行該儲存過程時擁有呼叫者的所有許可權，即呼叫者的Role是有效的。

如果不指定AUTHID，預設值是DEFINER。另外匿名塊(DECLARE BEGIN END;)總是IR Procedure，觸發器和檢視總是DR Procedure。我們可以透過檢視*_PROCEDURES來檢視儲存過程的AUTHID屬性值。

注：儲存過程、包、都適用。

解決執行儲存過程出現許可權不足問題（ORA-01031）

相關文章