在儲存過程中使用EXECUTE IMMEDIATE報許可權問題

         EXECUTE IMMEDIATE是Oracle中使用動態SQL的一種方法，可以直接執行，也可以在儲存過程中呼叫。可是我今天在儲存過程中呼叫時遇到許可權不足的問題，然而吧該語句單獨提取到匿名塊中執行完全沒問題：

declare
  -- Local variables here
  v_tempname   varchar2(10);
  v_sourcename varchar2(50);
  createtmp    varchar2(100);
begin
  -- Test statements here
  v_tempname:='ttt1';
  v_sourcename:='user_tables';
  createtmp:='CREATE TABLE '||v_tempname||' AS '  --建立一個與被抽取物件dfr_trade_type表相同表結構的temp表
           ||'SELECT * FROM '||v_sourcename||' WHERE 1=2';
  EXECUTE IMMEDIATE createtmp;   --放這裡執行正常，放儲存過程裡就有問題了
end;

         查了下當前使用者的角色和許可權：

SQL> select * from user_role_privs;

USERNAME   GRANTED_ROLE    ADMIN_OPTION DEFAULT_ROLE   GRANTED
----------------- ------------------------------ -------------------- ------------------------ ---------------
ZY                        CONNECT                          YES                     YES                     NO
ZY                           DBA                                   YES                     YES                     NO
ZY                         RESOURCE                      YES                     YES                     NO

        本人還是dba角色呢，居然在儲存過程裡建立個table還被管著，氣氛！於是帶著不服的心理在網上查個究竟：

        原來是因為預設情況下，資料庫對儲存過程在編譯階段進行許可權檢測，資料庫檢測儲存過程的所有者是否擁有直接賦予的許可權，而不是透過一個角色等間接賦予的許可權。但是在建立儲存過程的時候使用了AUTHID CURRENT_USER這個選項，那麼語句執行的許可權將在執行過程中根據執行者的許可權進行判斷。

        解決的方法有兩個：一是直接授權使用者DDL許可權，二是使用AUTHID CURRENT_USER選項。

        於是我將create anly table的許可權賦予zy使用者試了下：

SQL> grant create any table to zy;

Grant succeeded

         ok啦！

        所以，如果在儲存過程中呼叫EXECUTE IMMEDIATE出現“許可權不足”問題，就需要賦予使用者更多的許可權，比如說在儲存過程中建立或修改觸發器遇到“許可權不足”，就應該執行：
GRANT CREATE ANY TRIGGER TO SCOTT
       其他情況也是一樣的。