Monday, July 09, 2012

max_enabled_roles (Part 2)

This example was tested on Oracle 9. It shows the use of the max_enabled_roles initialisation parameter, which was deprecated in Oracle 10. For the purposes of the test, this database has max_enabled_roles set to 5:

SQL> select value from v$parameter
  2  where name = 'max_enabled_roles'
  3  /

VALUE
-----
5

SQL>

To test valid and invalid scenarios, we need 6 roles:

SQL> create role role1
  2  /

Role created.

SQL> create role role2
  2  /

Role created.

SQL> create role role3
  2  /

Role created.

SQL> create role role4
  2  /

Role created.

SQL> create role role5
  2  /

Role created.

SQL> create role role6
  2  /

Role created.

SQL>

Create a user and grant 5 of these roles to him:

SQL> grant create session,
  2  role1, role2, role3, role4, role5
  3  to andrew identified by reid
  4  /

Grant succeeded.

SQL>

When a user logs in, Oracle enables all roles which have been granted explicitly to him:

SQL> conn andrew/reid
Connected.
SQL>

A user can see his enabled roles in the SESSION_ROLES view:

SQL> select role from session_roles
  2  /

ROLE
------------------------------
ROLE1
ROLE2
ROLE3
ROLE4
ROLE5

SQL>

Now grant a 6th role to the user:

SQL> conn / as sysdba
Connected.
SQL> grant role6 to andrew
  2  /

Grant succeeded.

SQL>

This stops the user logging on as he would then have more than 5 enabled roles:

SQL> conn andrew/reid
ERROR:
ORA-01925: maximum of 5 enabled roles exceeded 

Warning: You are no longer connected to ORACLE.
SQL>

Now change the user so that he only has 4 enabled roles:

SQL> conn / as sysdba
Connected.
SQL> alter user andrew default role
  2  all except role5, role6
  3  /

User altered.

SQL>

This allows the user to login again:

SQL> conn andrew/reid
Connected.
SQL> select role from session_roles
  2  /

ROLE
------------------------------
ROLE1
ROLE2
ROLE3
ROLE4

SQL>

... but if he tries to enable all 6 roles, he gets an ORA-01925 again:

SQL> set role all
  2  /
set role all
*
ERROR at line 1:
ORA-01925: maximum of 5 enabled roles exceeded

SQL> set role role1, role2, role3, role4, role5, role6
  2  /
set role role1, role2, role3, role4, role5, role6
*
ERROR at line 1:
ORA-01925: maximum of 5 enabled roles exceeded

SQL>

... and his list of enabled roles remains unchanged:

SQL> select role from session_roles
  2  /

ROLE
------------------------------
ROLE1
ROLE2
ROLE3
ROLE4

SQL>

No comments: