Oracle 角色的两个特性和误区

Oracle 角色的两个特性和误区

Oracle角色的两个特性

1）角色的延迟性
   延迟生效

   立即让角色生效的方法：
     set role 角色名;
     例如：
     set role resource;
   延迟回收
   下面用实验证明延迟回收：
   会话（1）scott：
   sys@ORCL> conn scott/oracle
   Connected.
   scott@ORCL> select * from user_role_privs;

   USERNAME                       GRANTED_ROLE                   ADM DEF OS_
   ------------------------------ ------------------------------ --- --- ---
   SCOTT                          CONNECT                        NO  YES NO
   SCOTT                          RESOURCE                       NO  YES NO
  
   会话（2）sys：
   idle> conn / as sysdba
   Connected.
   sys@ORCL> revoke resource from scott;  --回收resource角色

   Revoke succeeded.
  
   回到会话（1）scott：
  
   scott@ORCL> select * from session_roles;  --resource角色依然健在

   ROLE
   ------------------------------
   CONNECT
   RESOURCE

   scott@ORCL> select * from user_role_privs;

   USERNAME                       GRANTED_ROLE                   ADM DEF OS_
   ------------------------------ ------------------------------ --- --- ---
   SCOTT                          CONNECT                        NO  YES NO
  
   此时，为了保证数据库的安全，应该连带把scott所在的会话给杀掉！
  
2）角色的默认性
   如果该角色是default role，那么即便是有密码，但若重新登入，则密码形如虚设。
   所以，有密码的角色都必须不能是default role
   会话1：sys
   sys@ORCL> drop role r1;

   Role dropped.

   sys@ORCL> create role r1 identified by r1;

   Role created.

   sys@ORCL> grant select on hr.t to r1;

   Grant succeeded.

   sys@ORCL> grant r1 to scott;

   Grant succeeded.
  
   sys@ORCL> alter user scott default role connect;

   User altered.

  
   会话2：scott
   scott@ORCL> select GRANTED_ROLE,DEFAULT_ROLE from user_role_privs;

   GRANTED_ROLE                   DEF
   ------------------------------ ---
   CONNECT                        YES
   R1                             NO

   scott@ORCL> select * from hr.t;

   NAME
   --------------------
   think

   scott@ORCL> exit;
   Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
   With the Partitioning, OLAP and Data Mining options
   [oracle@localhost ~]$ sqlplus /nolog

   SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 2 22:00:21 2012

   Copyright (c) 1982, 2005, Oracle.  All rights reserved.

   idle> conn scott/oracle
   Connected.
   scott@ORCL> select GRANTED_ROLE,DEFAULT_ROLE from user_role_privs;

   GRANTED_ROLE                   DEF
   ------------------------------ ---
   CONNECT                        YES
   R1                             NO

   scott@ORCL> select * from hr.t;
   select * from hr.t
                 *
   ERROR at line 1:
   ORA-00942: table or view does not exist
  
   会话1：sys
   sys@ORCL> alter user scott default role connect,r1;

   User altered.
  
   会话2：scott
   scott@ORCL> select GRANTED_ROLE,DEFAULT_ROLE from user_role_privs;

   GRANTED_ROLE                   DEF
   ------------------------------ ---
   CONNECT                        YES
   R1                             YES

   scott@ORCL> select * from hr.t;
   select * from hr.t
                 *
   ERROR at line 1:
   ORA-00942: table or view does not exist

   scott@ORCL> set role r1 identified by r1;

   Role set.
  
   scott@ORCL> select * from hr.t;
  
   NAME
   --------------------
   think
  
   scott@ORCL> exit;
   Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
   With the Partitioning, OLAP and Data Mining options
   [oracle@localhost ~]$ sqlplus /nolog
  
   SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 2 22:05:16 2012
  
   Copyright (c) 1982, 2005, Oracle.  All rights reserved.
  
   idle> conn scott/oracle
   Connected.
   scott@ORCL> select GRANTED_ROLE,DEFAULT_ROLE from user_role_privs;
  
   GRANTED_ROLE                   DEF
   ------------------------------ ---
   CONNECT                        YES
   R1                             YES
  
   scott@ORCL> select * from hr.t;
  
   NAME
   --------------------
   think