| 存储过程与角色的关系 | DBA之路：OoNiceDream |

存储过程与角色的关系

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/03/procedure-role.html

在默认情况下（也就是AUTHID DEFINER时），Oracle在执行、编译存储过程时并不检查定义者拥有的角色，只是检查其被显式授予的权限，这样就导致了执行、编译存储过程时报权限不足、表不存在的错误（例二的情况，使用AUTHID CURRENT_USER也不行，详见正文）：

ORA-01031: insufficient privileges
ORA-00942: table or view does not exist

来看两个小例子：
例一：
会话一：创建用户，授以常用的CONNECT、RESOURCE角色

SQL> conn / as sysdba
Connected.
SQL> create user rockey identified by rockey default tablespace TEST;
 
User created.
 
SQL> grant connect,resource to rockey;
 
Grant succeeded.
 
SQL>      
SQL> select * from dba_sys_privs where grantee='CONNECT';
 
GRANTEE                PRIVILEGE                        ADM
---------------------- -------------------------------- ---
CONNECT                CREATE VIEW                      NO
CONNECT                CREATE TABLE                     NO
CONNECT                ALTER SESSION                    NO
CONNECT                CREATE CLUSTER                   NO
CONNECT                CREATE SESSION                   NO
CONNECT                CREATE SYNONYM                   NO
CONNECT                CREATE SEQUENCE                  NO
CONNECT                CREATE DATABASE LINK             NO
 
8 rows selected.
 
SQL> select * from dba_sys_privs where grantee='RESOURCE';
 
GRANTEE                PRIVILEGE                        ADM
---------------------- -------------------------------- ---
RESOURCE               CREATE TYPE                      NO
RESOURCE               CREATE TABLE                     NO
RESOURCE               CREATE CLUSTER                   NO
RESOURCE               CREATE TRIGGER                   NO
RESOURCE               CREATE OPERATOR                  NO
RESOURCE               CREATE SEQUENCE                  NO
RESOURCE               CREATE INDEXTYPE                 NO
RESOURCE               CREATE PROCEDURE                 NO
 
8 rows selected.

显然CONNECT、RESOURCE角色都包含了CREATE TABLE的权限。
会话二：通过创建、执行存储过程，来看看权限不足的产生：

SQL> conn rockey/rockey@TESTDBA 
Connected.
SQL> create table test01(a number);
 
Table created.
 
SQL> 
SQL> create or replace procedure proc_test(table_name in varchar2,
  2                                        field1     in varchar2,
  3                                        datatype1  in varchar2
  4                                        ) as
  5    str_sql varchar2(500);
  6  begin
  7    str_sql := 'create table ' || table_name || '(' || field1 || ' ' ||
  8               datatype1 || ')';
  9    execute immediate str_sql; 
 10  end;
 11  /
 
Procedure created.
 
SQL> exec proc_test('TEST02','A','NUMBER');
BEGIN proc_test('TEST02','A','NUMBER'); END;
 
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "ROCKEY.PROC_TEST", line 9
ORA-06512: at line 1
 
 
SQL>

报了ORA-01031，权限不足。
在会话一中显式地授以CREATE TABLE权限

会话一：

SQL> grant create table to rockey;
 
Grant succeeded.

会话二：

SQL> exec proc_test('TEST02','A','NUMBER');
 
PL/SQL procedure successfully completed.
 
SQL> select TABLE_NAME,TABLESPACE_NAME from user_tables;
 
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST01                         TEST
TEST02                         TEST
 
SQL>

建表成功。

同样的情况，还发生在存储过程中引用其它用户下的对象时，如果没有显示的授权，而是通过角色，间接授权，就会报权限不足或表不存在的错误。
例二：
会话一：

SQL> conn / as sysdba
Connected.
SQL> create user rockey01 identified by rockey01 default tablespace TEST;
 
User created.
 
SQL> grant connect,resource to rockey01;
 
Grant succeeded
 
SQL> create table rockey01.test (a number);
 
Table created.
 
SQL> create role select_rockey01;
 
Role created.
 
SQL> grant select on  rockey01.test to select_rockey01;
 
Grant succeeded.
 
SQL> grant select_rockey01 to rockey;
 
Grant succeeded.

会话二：

SQL> conn rockey/rockey@TESTDBA
Connected.
SQL> select * from rockey01.test;
 
no rows selected
 
SQL> 
SQL> create or replace procedure rockey.proc_test01 as
  2  cursor cur is
  3  select * from rockey01.test;
  4  begin
  5    for rec in cur loop
  6    null;
  7    end loop;
  8  end;
  9  /
 
Warning: Procedure created with compilation errors.
 
SQL> show error
Errors for PROCEDURE ROCKEY.PROC_TEST01:
 
LINE/COL ERROR
-------- --------------------------------------------------
3/1      PL/SQL: SQL Statement ignored
3/24     PL/SQL: ORA-00942: table or view does not exist
SQL>

报ORA-00942表不存在的错误。
在会话一中显式地授权：

SQL> grant select on rockey01.test to rockey;
 
Grant succeeded.

会话二，重新编译成功：

SQL> alter procedure PROC_TEST01 compile;
 
Procedure altered.
 
SQL>

对于例一，可以通过加authid current_user来解决，而例二，则不行，还是要显示地授权：
例一的情况：
会话一：

SQL> revoke create table from rockey;
 
Revoke succeeded.

会话二：

SQL> create or replace procedure proc_test(table_name in varchar2,
  2                                        field1     in varchar2,
  3                                        datatype1  in varchar2
  4                                        ) authid current_user as
  5    str_sql varchar2(500);
  6  begin
  7    str_sql := 'create table ' || table_name || '(' || field1 || ' ' ||
  8               datatype1 || ')';
  9    execute immediate str_sql; 
 10  end;
 11  /
 
Procedure created.
 
SQL> exec proc_test('TEST03','A','NUMBER');
 
PL/SQL procedure successfully completed.

例二的情况：
会话一：

SQL> revoke select on rockey01.test from rockey;
 
Revoke succeeded.

会话二：

SQL> create or replace procedure proc_test01 authid current_user as 
  2  cursor cur is
  3  select * from rockey01.test;
  4  begin
  5    for rec in cur loop
  6    null;
  7    end loop;
  8  end;
  9  /
 
Warning: Procedure created with compilation errors.
 
SQL> show error
Errors for PROCEDURE PROC_TEST01:
 
LINE/COL ERROR
-------- ---------------------------------------------------
3/1      PL/SQL: SQL Statement ignored
3/24     PL/SQL: ORA-00942: table or view does not exist