请简述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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29635878/viewspace-1175224/，如需转载，请注明出处，否则将追究法律责任。