存储过程访问其他用户表权限不足问题

qingyun

两个用户 USER_A,USER_B
赋予了DBA权限；  其他权限未选；


两个用户都有一张一模一样的表名 TABLE1;


于是在USER_A为当前用户的情况下：

执行：  INSERT INTO  TABLE1 SELECT * FROM USER_B.TABLE1   没问题；

执行：
   BEGIN
      INSERT INTO  TABLE1 SELECT * FROM USER_B.TABLE1;
   END;

也没问题；

执行：
BEGIN
    EXECUTE IMMEDIATE   ' INSERT INTO  TABLE1 SELECT * FROM USER_B.TABLE1';
END;

也没问题；

但是就是不能些存储过程：


create procedure   xxx
as
begin
    INSERT INTO  TABLE1 SELECT * FROM USER_B.TABLE1;   
    EXECUTE IMMEDIATE   ' INSERT INTO  TABLE1 SELECT * FROM USER_B.TABLE1';
end;

上面两句话，任意一句都有问题；

提示   USER_B.TABLE1 不存在；


oracle是用的 oracle 11gR2  

qingyun

办法有了，登陆到B用户，执行：
GRANT SELECT ANY TABLE TO USER_A  WITH ADMIN OPTION;

存储过程的权限，和 匿名 PL/SQL 的权限不一样；

经常遇到  begin... end; 可以； 但是写入到存储过程里就不行；

对这个权限原理换不是太明白；


要想让当前用户肆无忌惮的做任何事情；必须把Toad权限选项里的，处了dba的其他200多个权限勾上；那就可以了；我经常这么做；否者经常遇到 sql语句写的好好的；
一旦跑到存储过程了，就提示权限不足；

qingyun

比如在A用户下，想创建B用户的表；
begin
EXECUTE IMMEDIATE ' create table USER_B.test (d number)';
end;
这个没问题；

但是 弄成存储过程就不行了，提示没权限；

于是在B用户下执行：

GRANT CREATE TABLE TO USER_A WITH ADMIN OPTION;

然后再回到A用户下，执行：
create procedre xx
as
begin
EXECUTE IMMEDIATE ' create table USER_B.test (d number)';
end;
--该过程里因为是动态语句，所以存储过程创建没问题，但是运行的时候，仍然抱权限不足的错误；

qingyun

用：
SELECT * FROM SYS.USER_TAB_PRIVS;
查询出当前用户“额外”赋予的权限；

xgghxkhuang

楼主有时间可以研究一下steven的plsql程序设计，里面专门有一节讨论这个问题。
使用存储过程的时候分成两种权限：
1.定义者权限
2.调用者权限
默认的情况下使用的是定义者权限。
定义者权限要求所有权限是直接授权的，通过角色授权的无效。就好像完全定在了你调用的那个用户下了，你用角色授予的权限完全失效。
而调用者权限就是为了解决定义者权限的一些局限设计的，它访问的是用户所具有的权限包括角色授权的权限。
楼主的问题可以通过调用者权限和动态sql解决，楼主可以百度一下调用者权限。

dingjun123

可以参考steven pl/sql programming
23.4. Execution Authority Models

YYNGH123

Oracle规定，在默认的情况下，在调用存储过程用户的角色不起作用，即在执行存储过程时只有Public权限。所以在调用Create table时，会有权限不足的提示。

存储过程分为两种，即DR(Definer's Rights ) Procedure和IR(Invoker's Rights ) Procedure。比如说用户sh创建了删除表mytable的存储过程drop_table()，当用户sh调用时，删除用户sh下的表mytable；如果是另一个用户scott调用呢？是删除用户scott下的mytable表呢，还是删除用户sh的mytable呢？另外，如果存储过程中包含建表语句，不管是用户sh还是用户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 procedure   xxx AUTHID CURRENT_USER
as
begin
    INSERT INTO  TABLE1 SELECT * FROM USER_B.TABLE1;   
    EXECUTE IMMEDIATE   ' INSERT INTO  TABLE1 SELECT * FROM USER_B.TABLE1';
end;

lastwinner

qingyun是老江湖碰到老问题了

solomon_007

敏而好学，不耻下问，是以谓之'青云'也

qingyun

呵呵，一直没怎么研究过权限，公司自己的项目，都是赋予了TOAD里大概263个权限全部勾选上；所以什么权限都有；
安全性很重要；不过为了省事，更多的是关注方便性；
oracle基础知识都是见缝插针的去学习；做项目也一直没有时间去系统学习；关注的最多的还是SQL,存储过程上的使用技巧；
毕竟大部分精力还是做前段开发

没想到已经被认为老江湖了，呵呵，惭愧惭愧。