角色是我们使用Oracle进行权限管理时候的一个重要方法。通过设置不同类型的角色，组织不同角色可以使用的系统权限和对象权限。之后简单的将不同角色附加给不同用户，这样在管理和维护上都有着很多优势。

但是，实际我们进行开发应用系统时，要慎用role进行权限管理。因为在Oracle中，在一些场景下使用role角色是受到限制的。其中一个不可忽视的问题就是存储过程SP对角色权限的无效现象。

基础说明

先让我们一起看看Oracle权限体系的结构。在Oracle中，用户的权限分为两个层次：对象权限（object privileges）和系统权限（system privileges）。

对象权限简单说，就是设置的用户可以对哪个对象（数据表、视图、存储过程等）进行何种类型的操作（select、execute等）。这种层次的权限集中在对象层面上，使用grant和revoke进行权限的授权和收回。格式为：grant/revoke on to/from 。

系统权限则是从另一个角度对操作进行的划分。主要是规定该用户在系统中能做什么？比如，创建数据表、查询数据字典、修改存储过程等等。格式通常为：grant/revoke to/from 。

对象权限和系统权限是用户权限在不同角度划分。以横纵的方式进行权限管理可以让设置更加游刃有余。不过，有一定要注意，无论是对象权限还是系统权限，都是规定用户可以做什么，而不是规定了用户不可以做什么。此外，用户任何操作，在没有显示指定权限说可以这样做的情况下，默认是认为不可以的。这点很重要，充分避免了设置出现矛盾的情况。

对象权限和系统权限是系统预定义的，除了对象名称外，通常不会有自定义的情况。

而角色权限（role privileges）相当于对对象权限和系统权限的一种归纳。根据不同数据库使用场景和工作角色，Oracle预定义了很多的角色，用户可以根据实际情况进行自定义的角色空间也有很多。

初步介绍完基础，我们进行试验，看看使用角色给我们带来的一些困扰。

实验环境准备

我们使用Oracle 10gR2版本。

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE        10.2.0.1.0   Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

准备一个全新的用户Test，初始只赋予resource和connect权限。

Connected as SYS

SQL> create user test

  2    identified by test;

User created

SQL> grant resource to test;

Grant succeeded

SQL> grant connect to test;

Grant succeeded

Connect角色是允许用户登录到Oracle数据库服务器上，其中包括了create session类的系统权限。Resource是默认schema使用的角色之一，允许用户进行简单对象创建，Schema内部对象访问等操作。准备了一个相对干净的环境。

默认用户行为

我们以dba_objects数据字典作为研究对象。

SQL> conn test/test@orcl;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as test

SQL> select count(*) from dba_objects;

select count(*) from dba_objects

ORA-00942: 表或视图不存在

此处，Oracle对没有权限访问的对象，并不是告诉你说该对象没有权限访问，而是报告说对象不存在。很简单的提示变化，却体现着Oracle跟深层次的信息保护屏蔽作用。因为，对象是否存在，也是一种信息。报错提示信息，很可能会存在信息、结构泄露的漏洞。

显然，仅拥有connect和resource权限，是不能够访问dba_objects视图的。

角色权限select_catalog_role

在预定义角色中，有select_catalog_role角色，作用是赋予进行数据字典视图select权限。

首先切换到sys用户，进行用户test权限的赋予。

SQL> conn sys/sysacca@orcl as sysdba;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as SYS

SQL> grant select_catalog_role to test;

Grant succeeded

SQL> select * from dba_role_privs where grantee='TEST';

GRANTEE            GRANTED_ROLE   ADMIN_OPTION DEFAULT_ROLE

------------------------------ ------------------------------ ------------ ------------

TEST                           RESOURCE                NO           YES

TEST                           SELECT_CATALOG_ROLE   NO           YES

TEST                           CONNECT                 NO           YES

回到test用户，进行测试。

SQL> conn test/test@orcl;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as test

SQL> select count(*) from dba_objects;

  COUNT(*)

----------

     51355

设置角色之后，用户可以访问到字典视图dba_objects了。那么，下面我们要编写一个存储过程，中间包括对dba_objects对象的访问。

SQL> create or replace procedure p_test_nc

  2  is

  3  i number;

  4  begin

  5    select count(*)

  6    into i

  7    from dba_objects;

  8 

  9    dbms_output.put_line(to_char(i));

 10  end p_test_nc;

 11  /

Warning: Procedure created with compilation errors

出现了报错，该代码没有任何特殊之处。结合刚刚test用户已经可以访问dba_objects的现象，错误是不大会出现的。我们具体查看错误类型信息。

SQL> col name for a15;

SQL> select name, type, sequence,line,position,text from user_errors;

NAME            TYPE           SEQUENCE       LINE   POSITION TEXT

--------------- ------------ --------- --------------------------------------------------------

P_TEST_NC       PROCEDURE     1  7    8 PL/SQL: ORA-00942: 表或视图不存在

P_TEST_NC       PROCEDURE     2  5    3 PL/SQL: SQL Statement ignored

第七行的对象报错不存在，显然是dba_objects导致的。那么，test已经拥有了select_catalog_role的角色信息，而且在command窗口中可以访问到dba_objects。而在存储过程中却不能成功呢？

Select any dictionary权限

我们先留待问题，换一个角度来实现这个SP的构建。Oracle具有一个select any dictionary的系统权限，表示的是可以对数据字典进行访问查询。我们观察一下将其赋给test用户后的效果。

首先还是使用sys用户进行权限设置。

SQL> conn sys/sysacca@orcl as sysdba;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as SYS

SQL> revoke select_catalog_role from test;

Revoke succeeded

SQL> grant select any dictionary to test;

Grant succeeded

SQL> select * from dba_role_privs where grantee='TEST';

GRANTEE             GRANTED_ROLE    ADMIN_OPTION DEFAULT_ROLE

------------------------------ ------------------------------ ------------ ------------

TEST                           RESOURCE                       NO           YES

TEST                           CONNECT                        NO           YES

通过sys用户，已经取消了select_catalog_role角色在test上的起效，并且赋予了select any dictionary系统权限给test。下面观察实验现象。

SQL> conn test/test@orcl;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as test

SQL> select count(*) from dba_objects;

  COUNT(*)

----------

     51356

SQL> create or replace procedure p_test_nc

  2  is

  3  i number;

  4  begin

  5    select count(*)

  6    into i

  7    from dba_objects;

  8 

  9    dbms_output.put_line(to_char(i));

 10  end p_test_nc;

 11  /

Procedure created

SP被顺利编译，说明权限设置是问题的关键。

通过这个小例子和实验，我们可以做出下面的结论：

ü         在进行一般的SQL操作或者匿名块的时候，系统权限、对象权限和role所起到的效果一样的，没有差异；

ü         但是，当在一个存储过程类封装代码的时候，用户执行时角色权限是失效的，之后系统和对象权限是起效的；

ü         综合上述，我们在设置权限的时候，尽量避免使用role进行设置；