oracle存储过程用户授权 关于Oracle存储过程执行权限问题的解决

在数据库系统中存储过程是必不可少的利器，存储过程是预先编译好的为实现一个复杂功能的一段sql语句集合它的优点我就不多说了，说一下我碰到的问题吧我在项目开发的过程中需要用存储过程来实现一个功能，其中涉及到判断一张表是否已经建立，没有建立就由存储过程来建立这张表，接下来我们就来聊聊关于oracle存储过程用户授权 关于Oracle存储过程执行权限问题的解决?以下内容大家不妨参考一二希望能帮到您!

oracle存储过程用户授权 关于Oracle存储过程执行权限问题的解决

在数据库系统中存储过程是必不可少的利器，存储过程是预先编译好的为实现一个复杂功能的一段sql语句集合。它的优点我就不多说了，说一下我碰到的问题吧。我在项目开发的过程中需要用存储过程来实现一个功能，其中涉及到判断一张表是否已经建立，没有建立就由存储过程来建立这张表。

CREATE OR REPLACE PROCEDURE TestProc

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 ;

写这段存储过程比较简单，在测试执行的过程中，系统出现如下提示：

execute TestProc;

ERROR at line 1:

ORA-01031: insufficient privileges

ORA-06512: at "TestProc", line 7

ORA-06512: at line 1

从错误提示我们定位到错误，发现存储过程在执行 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就能很快解决问题啦，我把代码修改为如下：

[sql] view plaincopy

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 ;

执行存储过程，顺利通过。

,