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>