oracle中角色中权限剥离

Oracle三层权限体系【复习】

1、Oracle的权限体系划分为三个层次：角色role、系统system和对象object。下面进行简单的说明：

ü       对象权限（object privilege），是权限体系的最小粒度对象。特点是权限点在对象上。此处的对象，包括Oracle体系下包括数据表、视图、序列、包、存储过程等十一种对象的权限。每种对象都有对应的权限点。比如，对数据表有select、update等操作权限；

ü       系统权限（system privilege），是描述Oracle用户操作的另一个层面。定义了用户在系统中可以做什么，如Create table之类。注意，一些系统权限和对象权限可能存在重叠的情况。要小心使用。

ü       角色权限（role privilege），是对象权限和系统权限、甚至包括角色权限的复合体。对象权限和系统权限都是粒度很细的权限单元，一类用户往往需要相同的对象权限集合和系统权限集合。如果分别进行设置，就可能存在遗漏的可能，依据职责进行role角色设置，之后将角色附加给用户，是一种更加简单的方法；

2、对象权限object privilege

对象权限是Oracle的基础权限，定义了用户在某个特定对象上可以使用何种权限。注意，此处我们谈到的对象权限，是针对已经存在对象的权限。目前，Oracle支持十一种对象的九方面权限，并不是每类型对象都有全部的九个方面权限。详细如下表：

从上表中可以看出，根据对象的不同，可以进行赋予的权限也是不同的。此外，还有一个隐式的all权限，表示对象可以赋予的全部权限信息。视图方面，可以使用user_tab_privilege和table_privilege等视图检查对象的访问授权情况。

3、系统权限System Privilege：系统权限是Oracle内置定义的，在Oracle10g中有大约160余个系统权限

4、角色权限role privilege：角色权限可以接受各种系统权限和对象权限，也可以包括其他的角色权限。在实际开发过程中，我们自定义角色的场景其实不是很多，因为自定义角色权限后，在开发使用时是有很多的限制的。需要注意的问题是，不同版本的Oracle，预定义角色可能存在差异。

角色role不能循环赋予

//自定义两个空权限角色；

SQL> create role r_t;

Role created

SQL> create role r_m;

Role created

//相互尝试赋予权限；

SQL> grant r_t to r_m;

Grant succeeded

SQL> grant r_m to r_t;

grant r_m to r_t

ORA-01934:检测到循环的角色授权

实验结论显而易见：Oracle在进行grant角色相关授权的时候，会自动进行循环检测，如果发现循环，就禁止掉操作。

角色权限在存储过程中的剔除效应

角色的确是一种很方便的权限集合组织方式，在很多系统中也是广泛应用。但是在Oracle中，使用角色权限role privilege是要格外小心的，特别是进行数据库开发过程中。因为Oracle存储过程等结构对角色权限有剔除效应。

【实验】

//建立实验角色r_cat_role

SQL> create role r_cat_role ;

Role created

//授予系统权限select any dictionary给实验角色，select any dictionary权限可以访问到Oracle的大部分元数据视图。

SQL> grant select any dictionaryto r_cat_role;

Grant succeeded

//创建用户

SQL> create user  mytest  identified by mytest;

User created

SQL> grant create session to mytest;

Grant succeeded

SQL> grant r_cat_role to mytest;

Grant succeeded

SQL> grant create procedure to mytest;

Grant succeeded

SQL> conn mytest/mytest@ora11g;

Connected to Oracle Database11gEnterpriseEdition Release11.2.0.1.0

Connected as mytest

SQL> select count(*) fromdba_objects;

 COUNT(*)

----------

    72282

dba_objects视图是元数据字典的一个组成部分。mytest用户在接受角色权限r_cat_role之后，具有了在SQL中直接使用的权限。下面我们构造存储过程如下：

SQL> create or replace procedure P_TEST_NC

 2 is

 3 n_res number;

 4 begin

 5   select count(*)

 6   into n_res

 7   from dba_objects;

 8

 9   dbms_output.put_line(to_char(n_res));

 10 end P_TEST_NC;

 11 /

Warning: Procedure created with compilation errors

SQL> select * from user_errors;

NAME      TYPE          SEQUENCE      LINE  POSITION TEXT                                    ATTRIBUTE

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

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

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

编译报错，看起来不可思议。明明mytest用户具有dba_objects视图的访问权限，而且在SQL语句直接可以使用。我们说，就是因为访问dba_objects的权限是通过角色授予的。存储过程等Oracle代码结构具有一个特性，就是可以将用户的三层权限（role、system和object）中的role权限剥离掉。

系统权限和对象权限冲突

系统权限中包括一部分与对象有关的内容。如select any table、execute any procedure等。这些和对象权限之间存在一些重叠的部分。

【实验】

SQL> create user mytest identified by mytest;

User created

SQL> grant connect, resource to mytest;

Grant succeeded

SQL> grant select any table to mytest;

Grant succeeded

SQL> conn mytest/mytest@orcl;

Connected to Oracle Database10gEnterpriseEdition Release10.2.0.1.0

Connected as mytest

SQL> select count(*) from scott.emp;

COUNT(*)

----------

      14

尝试可否从scott用户上禁止mytest对m的访问。

SQL> conn scott/tiger@orcl;

Connected to Oracle Database10gEnterpriseEdition Release10.2.0.1.0

Connected as scott

SQL> revoke select on t from mytest;

revoke select on t from mytest

ORA-01927:无法REVOKE您未授权的权限

可见看出，对Oracle的revoke而言，只能revoke授予过的权限。未授予过的权限，或者尝试对系统权限、对象权限语义的拆分，都是不能做到的。

拥有select any table权限，但是对sys用户下的数据表，是不能访问的。这是Oracle内部对于sys用户核心对象的一种保护措施。例如sys用户创建了一张test表，具有select  any  table权限的用户是无法查询sys.test表。

此外具有select  any  table 权限的用户也无法查看sys schema下的系统视图，要想查看，需要把参数o7...设置成true