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