關於Oracle預存程序執行許可權問題的解決

在資料庫系統中預存程序是必不可少的利器，預存程序是預先編譯好的為實現一個複雜功能的一段Sql語句集合。它的優點我就不多說了，說一下我碰到的問題吧。我在項目開發的過程中需要用預存程序來實現一個功能，其中涉及到判斷一張表是否已經建立，沒有建立就由預存程序來建立這張表。

CREATE OR REPLACE PROCEDURE TestProcIS  flag number;BEGIN  select count(*) into flag from all_tables where table_name='TEMP3';  if (flag=0) then     execute immediate 'create global temporary table TEMP3 on commit preserve rows as select * from BUSI_ECONTRACT';  else     execute immediate 'insert into TEMP3 select * from BUSI_ECONTRACT';  end if;END ;

寫這段預存程序比較簡單，在測試執行的過程中，系統出現如下提示：

從錯誤提示我們定位到錯誤，發現預存程序在執行 Create table語句時，許可權不足。我嘗試著把預存程序改成匿名預存程序在PL/SQL中執行，語句既然通過了。這說明這段語句沒有問題，問題出現在執行預存程序中。我使用的是DBA帳號登入系統，按理應該不存在許可權不足的問題呀。問題出現再哪裡呢。通過上網查閱資料，發現Oracle對於執行預存程序有和Sql-Server不一樣的規定，這個規定造成了執行建表語句許可權不足。

Oracle規定，在預設的情況下，在調用預存程序使用者的角色不起作用，即在執行預存程序時只有Public許可權。所以在調用Create table時，會有許可權不足的提示。

預存程序分為兩種，即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是有效。

我們碰到的問題只需要使用IR Procedure就能很快解決問題啦，我把代碼修改為如下：

CREATE OR REPLACE PROCEDURE TestProc AUTHID current_user IS  flag number;BEGIN  select count(*) into flag from all_tables where table_name='TEMP3';  if (flag=0) then     execute immediate 'create global temporary table TEMP3 on commit preserve rows as select * from BUSI_ECONTRACT';  else     execute immediate 'insert into TEMP3 select * from BUSI_ECONTRACT';  end if;END ;

執行預存程序，順利通過。