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 INTOTABLE1 SELECT * FROM USER_B.TABLE1;
EXECUTE IMMEDIATE ' INSERT INTOTABLE1 SELECT * FROM USER_B.TABLE1';
end;