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