PL/SQL中角色和用户权限的区别

一般都认为Oracle中应该尽量把权限赋予角色而不是用户，这样管理和修改起来都比较方便。

我一直认为这里面没啥区别，不过今天遇到一个例子，才发现在PL/SQL中赋予角色的权限是被屏蔽的。也就是说即使当前用户所属的角色具有某个权限，在PL/SQL中也是没用的，必须显式赋予用户权限才行。

例子如下：

C:\Program Files\H2\bin>sqlplus sys/123456 as sysdba;

SQL*Plus: Release 10.2.0.3.0 - Production on 星期一 8月 9 16:37:43 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> -- Create the user
SQL> create user TEST_DBA
  2    identified by "123"
  3    default tablespace USERS
  4    temporary tablespace TEMP
  5    profile DEFAULT;

用户已创建。

SQL> -- Grant/Revoke role privileges
SQL> grant dba to TEST_DBA;

授权成功。

SQL> -- Grant/Revoke system privileges
SQL> grant unlimited tablespace to TEST_DBA;

授权成功。

SQL> connect test_dba/123
已连接。
SQL> CREATE OR REPLACE PROCEDURE select_row IS
  2
  3  BEGIN
  4    dbms_sql.parse(c=> dbms_sql.open_cursor,STATEMENT => 'select count(0) fro
m scott.emp',language_flag => dbms_sql.native);
  5  END;
  6  /

过程已创建。

SQL> exec select_row;
BEGIN select_row; END;

*
第 1 行出现错误:
ORA-00942: 表或视图不存在
ORA-06512: 在 "SYS.DBMS_SYS_SQL", line 909
ORA-06512: 在 "SYS.DBMS_SQL", line 39
ORA-06512: 在 "TEST_DBA.SELECT_ROW", line 4
ORA-06512: 在 line 1


SQL> exec dbms_sql.parse(c=> dbms_sql.open_cursor,STATEMENT => 'select count(0)
from scott.emp',language_flag => dbms_sql.native);

PL/SQL 过程已成功完成。

 

这里 test_dba用户具有dba的角色，dba角色有select any table的权限，但test_dba用户自己并没有select any table的权限。所以尽管test_dba通过dba角色可以select scott.emp表，但对于他编译的stored procedure，按照

这里4楼 的说法

写道

因为PL/SQL编译时采用的是前联编技术，即编译时就对引用求值，GRANT是个DDL语句，而角色可以在不同的会话设置不同的，是在运行时才能确定其的值，所以为了保证前联编正常执行，ORACLE将角色都禁止了。

test_dba的角色在执行select_row时是不起作用的。要想能够执行，还得单独给用户权限

SQL> connect scott/tiger;
已连接。
SQL> grant select on emp to test_dba;

授权成功。

SQL> connect test_dba/123;
已连接。
SQL> exec select_row;

PL/SQL 过程已成功完成。