Differences between oracle 10g and 11g role passwords

A role is a set of related permissions. The main purpose of a role is to simplify permission management.

Once the permissions of this set exceed the minimum requirements of users, it may bring security risks to the database.

Role password Test

In oracle 10 Gb, regardless of whether the role has a password, as long as you grant the role to a user, by default, the permissions in these roles are owned by the user.

In oracle 11g, the role password is slightly corrected. If a role has a password and you grant the role with the password to a user, the default situation is

The user cannot have all permissions under the role with a password. Only after the set role has a password

The permissions under the role are only available in the current session. However, all other roles are temporarily invalid and the modification is only valid in the current session.

Syntax:------ Valid only for the current session

SET ROLE

   { role [ IDENTIFIED BY password ]     [, role [ IDENTIFIED BY password ] ]...   | ALL [ EXCEPT role [, role ]... ]   | NONE   } ;

Test the role with a password in oracle 10g

SYS @ ORCL> select * from v $ version;


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod
PL/SQL Release 10.2.0.1.0-Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0-Production
NLSRTL Version 10.2.0.1.0-Production

1. Create two roles role_01 without a password role_02 with a password

SYS @ ORCL> create role role_01;


Role created.


SYS @ ORCL> create role role_02 identified by oracle;


Role created.

2. Grant the role_01 connection and table creation permissions to the role.
SYS @ ORCL> grant connect, create table to role_01;


Grant succeeded.

3. Grant the role role_02 access and view creation permissions.
SYS @ ORCL> grant connect, create view to role_02;


Grant succeeded.

4. Create a test user tyger
SYS @ ORCL> create user tyger identified by tyger quota unlimited on users;


User created.

5. Grant the two roles to tyger
SYS @ ORCL> grant role_01, role_02 to tyger;


Grant succeeded.

6. Connect to the user for testing
SYS @ ORCL> conn tyger/tyger
Connected.
TYGER @ ORCL> create table t (x int );


Table created.


TYGER @ ORCL> insert into t values (1 );


1 row created.


TYGER @ ORCL> commit;


Commit complete.


TYGER @ ORCL> select * from t;


X
----------
1


TYGER @ ORCL> create view view_t as select * from t;


View created.


TYGER @ ORCL> select * from tab;


TNAME TABTYPE CLUSTERID
-----------------------------------------------
VIEW_T VIEW
T TABLE

7. Check the role of the current user. The DEFAULT_ROLE values of both roles are "YES,Both roles take effect.
TYGER @ ORCL> desc user_role_privs;
Name Null? Type
-----------------------------------------------------------------------------
USERNAME VARCHAR2 (30)
GRANTED_ROLE VARCHAR2 (30)
ADMIN_OPTION VARCHAR2 (3)
DEFAULT_ROLE VARCHAR2 (3)
OS _GRANTED VARCHAR2 (3)


TYGER @ ORCL> col username for a10
TYGER @ ORCL> col granted_role for a20
TYGER @ ORCL> col default_role for a20
TYGER @ ORCL> select username, granted_role, default_role from user_role_privs;


USERNAME GRANTED_ROLE DEFAULT_ROLE
--------------------------------------------------
TYGER ROLE_01 YES
TYGER ROLE_02 YES

8. view the permissions of the current session
TYGER @ ORCL> select * from session_privs;


PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE VIEW

9. Application of set ---- set inCurrent sessionSet role status
TYGER @ ORCL> set role ROLE_01;


Role set.

10. Check the role of the current user.
TYGER @ ORCL> select username, granted_role, default_role from user_role_privs;


USERNAME GRANTED_ROLE DEFAULT_ROLE
--------------------------------------------------
TYGER ROLE_01 YES
TYGER ROLE_02 YES

11. Check the current session permission. You do not have the create view permission. The reason is:Set role role_01 only takes effect for the current session
TYGER @ ORCL> select * from session_privs;


PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE


TYGER @ ORCL> create view view_2 as select * from t;
Create view view_2 as select * from t
*
ERROR at line 1:
ORA-01031: insufficient privileges



12. Make role_02 take effect, and role_01 becomes invalid at the same time.YesWe provide the password because we used the password when creating the role.
TYGER @ ORCL> set role role_02;
Set role role_02
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'Role _ 02'

TYGER @ ORCL> set role role_02 identified by oracle;


Role set.

13. Check the permissions of the current user.
TYGER @ ORCL> select username, granted_role, default_role from user_role_privs;


USERNAME GRANTED_ROLE DEFAULT_ROLE
--------------------------------------------------
TYGER ROLE_01 YES
TYGER ROLE_02 YES

14. Check the current session. You do not have the create table permission.
TYGER @ ORCL> select * from session_privs;


PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE VIEW


TYGER @ ORCL> create table t1 (x int );
Create table t1 (x int)
*
ERROR at line 1:
ORA-01031: insufficient privileges


15. log on to the session again and restore all permissions of the connected user to the original state.
TYGER @ ORCL> conn tyger/tyger;
Connected.
TYGER @ ORCL> select * from session_privs;


PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE VIEW

Summary: After the oracle 10g role is granted to the user regardless of whether the role has a password, the user has all the permissions of the role.

Test the role with a password in oracle 11g

[Oracle @ ora11gr2 ~] $ Sqlplus/as sysdba


SQL * Plus: Release 11.2.0.1.0 Production on Wed Mar 19 15:28:13 2014


Copyright (c) 1982,200 9, Oracle. All rights reserved.




Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SYS @ ORA11G> select * from v $ version;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production

1. Create the role tyger_ro1 without a password tyger_ro2 with a password
SYS @ ORA11G> create role tyger_ro1;


Role created.


SYS @ ORA11G> create role tyger_ro2 identified by oracle;


Role created.


SYS @ ORA11G> grant connect, create table to tyger_ro1;


Grant succeeded.


SYS @ ORA11G> grant connect, create view to tyger_ro2;


Grant succeeded.


SYS @ ORA11G> create user tyger identified by tyger quota unlimited on users;


User created.


SYS @ ORA11G> grant tyger_ro1, tyger_ro2 to tyger;


Grant succeeded.


SYS @ ORA11G> conn tyger/tyger
Connected.
TYGER @ ORA11G> create table t (x int );


Table created.


TYGER @ ORA11G> insert into t values (1 );


1 row created.


TYGER @ ORA11G> commit;


Commit complete.

2.In this case, the problem occurs. role_02 clearly has a create view and is assigned to tyger. Why isn't it here?
TYGER @ ORA11G> create view view_t as select * from t;
Create view view_t as select * from t
*
ERROR at line 1:
ORA-01031: insufficient privileges




3. Check that the default_role of tyger_ro2 is NO.Is the role role_02 invalid ???
TYGER @ ORA11G> col username for a10
TYGER @ ORA11G> col granted_role for a20
TYGER @ ORA11G> col default_role for a20
TYGER @ ORA11G> select username, granted_role, default_role from user_role_privs;


USERNAME GRANTED_ROLE DEFAULT_ROLE
--------------------------------------------------
TYGER TYGER_RO1 YES
TYGER TYGER_RO2 NO

4. view the permissions of the current session,You do not have the create view permission.
TYGER @ ORA11G> select * from session_privs;


PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE

5. Set the tyger_ro2 permission to take effect
TYGER @ ORA11G> set role tyger_ro2 identified by oracle;


Role set.

6. the permissions of the current user remain unchanged.
TYGER @ ORA11G> select username, granted_role, default_role from user_role_privs;


USERNAME GRANTED_ROLE DEFAULT_ROLE
--------------------------------------------------
TYGER TYGER_RO1 YES
TYGER TYGER_RO2 NO

7. The current session uses the create view permission, but does not have the create table permission.
TYGER @ ORA11G> select * from session_privs;


PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE VIEW


TYGER @ ORA11G> create view view_t as select * from t;


View created.


TYGER @ ORA11G> select * from tab;


TNAME TABTYPE CLUSTERID
-----------------------------------------------
T TABLE
VIEW_T VIEW


TYGER @ ORA11G> create table t1 (x int );
Create table t1 (x int)
*
ERROR at line 1:
ORA-01031: insufficient privileges


8. log on to the session again and restore the original permissions.

TYGER @ ORA11G> conn tyger/tyger
Connected.
TYGER @ ORA11G> create table t1 (x int );


Table created.


TYGER @ ORA11G> select username, granted_role, default_role from user_role_privs;


USERNAME GRANTED_ROLE DEFAULT_ROLE
--------------------------------------------------
TYGER TYGER_RO1 YES
TYGER TYGER_RO2 NO

Summary:

In oracle 11g, the role with a password is granted to the user, which is invalid by default. When set role takes effect, the permissions of other roles are invalid and only valid for the current session.