Two features of a 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