Two features and misunderstandings of Oracle roles

Two features of Oracle role

1) role Delay
Delayed

How to enable the role to take effect immediately:
Set role name;
For example:
Set role resource;
Delay recovery
The following experiment demonstrates the delay recovery:
Session (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

Session (2) sys:
Idle> conn/as sysdba
Connected.
Sys @ ORCL> revoke resource from scott; -- reclaim the resource role

Revoke succeeded.

Back to session (1) scott:

Scott @ ORCL> select * from session_roles; -- The resource role is still alive

ROLE
------------------------------
CONNECT
RESOURCE

Scott @ ORCL> select * from user_role_privs;

USERNAME GRANTED_ROLE adm def OS _
---------------------------------------------------------------------
SCOTT CONNECT NO YES NO

In this case, to ensure database security, we should kill the session where scott is located!

2) default role
If the role is a default role, the password is a false one if it has a password but is logged on again.
Therefore, roles with passwords must not be default role.
Session 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.

Session 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,200 5, 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

Session 1: sys
Sys @ ORCL> alter user scott default role connect, r1;

User altered.

Session 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,200 5, 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