在存储过程中使用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
       其他情况也是一样的。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12615085/viewspace-675026/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12615085/viewspace-675026/