oracle-存储过程提示ORA-01031: 权限不足错误解决

在调用存储过程,尤其是含有DDL或者动态SQL语句的过程中,经常出现此错误,详细解释如下:

参考:http://blog.sina.com.cn/s/blog_53d588e50100m092.html

默认情况下,在调用存储过程用户的角色是不起作用的,即在执行存储过程时只有Public权限。所以如果被调用的存储过程中如果有execute immediate 'create table..'语句,将会引发ORA-01031: insufficient privileges错误。

     存储过程分为两种,即DR(Definer's Rights ) ProcedureIR(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是有效的。

   如果不指定AUTHID,默认值是DEFINER。另外匿名块(DECLARE BEGIN END;)总是IR Procedure,触发器和视图总是DR Procedure。我们可以通过视图*_PROCEDURES来查看存储过程的AUTHID属性值。

解决办法如下:

方法1:就这个存储过程来说,CREATE TABLE想使用CREATE ANY TABLE权限,而CREATE ANY TABLE权限来自DBA角色,默认情况下,虽然在会话环境中可见,但在存储过程中不可见(无效)。
所以根据上面的第一条规则,可以显示地将CREATE ANY TABLE权限授予cog就可以了,即
GRANT CREATE ANY TABLE TO COG;

方法2:采用调用者权限,由于过程中使用动态SQL,所以可以避开编译时的检查,但在运行时DBA角色生效,即

CREATE OR REPLACE PROCEDURE INSERT_DATA --插入user_客户ID,套餐表,客户宽表
(RTN_ID OUT NUMBER, --返回错误ID
 RTN_STR OUT VARCHAR --返回错误叙述
 ) AUTHID CURRENT_USER IS
...................

注意:第二种方法不能应用于包中的过程:会报出如下错误:PLS-00157: AUTHID 只允许在方案级程序中使用



©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页