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从4个方面实战Oracle的密码操作理解redo（7）oracle redo并行机制的原理介绍相关资讯 Oracle基础

Oracle基础介绍及常用相关SQL*PLUS （03月11日）
IMP-00008: unrecognized （09/02/2012 10:03:25）
在Oracle数据库上设置限制ip地址访（09/02/2012 09:59:55）

Oracle中删除用户遇到的问题（09/08/2012 20:01:42）
Oracle rac11.2.0.3.0的vip在重启（09/02/2012 10:00:39）
查看Oracle某个进程的环境变量（08/31/2012 11:54:36）

本文评论查看全部评论（0）