insufficient privileges in procedure

ORA-01031: insufficient privileges是一个非常常见的错误,就是表示没有权限。但是在存储过程中oracle处理直接赋予的权限和间接通过roles来赋予的权限时会引起误解。通过以下例子来比较两者之间的区别。

下面这个例子以execute immediate来做示范,用于在存储过程中执行动态语句。

首先创建一个用户,赋予基本的角色权限。

create user test identified by test;

grant connect,resource to test;

此时用户test包含了连接数据库、创建表和其他connect、resource角色所赋予其的权限。

在普通会话中,test能正常连接数据库和创建表

SQL> conn test/test@pdb18c
Connected.

SQL> create table test(id number);

Table created.

创建一个简单的存储过程,通过EXECUTE IMMEDAITE来动态创建表

create or replace procedure proc_test
as 
begin
    EXECUTE IMMEDIATE 'create table test1(id number)';
end;
/

执行这个存储过程

SQL> exec proc_test;
BEGIN proc_test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST.PROC_TEST", line 4
ORA-06512: at line 1

现在得到了一个ORA-01031错误,但是之前确实是有创建表的权限。这里有一个原因:在存储过程中角色是被禁用的,因此任何角色赋予用户的权限都不会生效,对应的权限必须要直接赋予用户。

直接赋予test创建表的权限,然后重新执行存储过程成功

SQL> grant create table to test;

Grant succeeded.

SQL> conn test/test@pdb18c
Connected.

SQL> exec proc_test;

PL/SQL procedure successfully completed.