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