max_enabled_roles – nice touch

Some people get very excited with roles, and quickly the number of roles proliferates to huge numbers…Until this happens

ORA-28031: maximum of 148 enabled roles exceeded

But in helping someone out on AskTom, I just found a nice touch in 11.2.0.4.  I had granted my account 200 roles (since the max_enabled_roles parameter is capped at 148), fully expecting to get the standard error when attempting to connect.  But I didn’t – all was fine.  You can see what is going on via the following demo.


SQL> begin
  2  for i in 1 .. 200 loop
  3    execute immediate 'create role rr'||i;
  4    execute immediate 'grant rr'||i||' to scott';
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> conn scott/tiger
Connected.

SQL> select count(*) from session_roles;

  COUNT(*)
----------
       148

So even though I granted 200 roles, when I queried SESSION_ROLES, I did not see all 200 , I only saw 148.

My alert.log shows this

Wed Nov 30 10:32:35 2016
Maximum of 148 enabled roles exceeded for user SCOTT. Not loading all the roles.

Some people might prefer an explicit error, but hopefully any good alert log monitoring would quickly pick this up.  And since if you exceed 148 there is no workaround (except to reduce the number of roles granted), I think avoiding the error is a better strategy.

Advertisements

3 thoughts on “max_enabled_roles – nice touch

  1. Is this a SQL-only nice touch? I get an exception when I try to gather statistics:

    > EXEC DBMS_STATS.GATHER_TABLE_STATS( ‘SCOTT’, ‘EMP’ ) ;

    ORA-28031: maximum of 150 enabled roles exceeded
    ORA-06512: at “SYS.DBMS_STATS”, line 24281
    ORA-06512: at “SYS.DBMS_STATS”, line 24332
    ORA-06512: at line 1

  2. I still remember that in older database versions (I think it was 8i ) we got an error when trying to connect,
    if more than “n” roles were granted (as default roles).

    To tell the truth, I don’t see any logical reason to limit the number of enabled roles …
    but, if such as limit does exist, then it is better to raise an error already when attempting to enable those roles (like on connect, if all the roles are default roles) than to arbitrarily enable 148 of them,
    which might require not easy debugging for other errors that might be subsequently raised.

    The parameter max_enabled_roles is documented as deprecated, but the hard limit of 148 user roles
    is documented under the SET ROLE command … so it looks like the trend is to maintain this limit,
    rather than remove it …

    Best Regards,
    Iudith

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s