ora-28031 maximum of 148 enabled roles exceeded

if you face an error like this:

Image

 

Description of the Problem: 

When a new session is initiated Oracle will attempt to enable all the default roles of the account if their number will be less than ( (value of the MAX_ENABLED_ROLES parameter) – 2 ). The default value of MAX_ENABLED_ROLES is 150. If the account is granted (directly or recursively) more than 148 roles then the session creation will fail with ORA-28031.

SQL> show parameter max_enabled_roles 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

max_enabled_roles                    integer     150

Image

To Resolve this error:

You need to remove the role from the user as a “default” one.

So, the only way is to revoke then re-grant the role again without the “default option”. 

REVOKE RL_SELECT_EMP FROM EMPLOYEE;

GRANT RL_ SELECT_EMP TO EMPLOYEE;

 

 

2 thoughts on “ora-28031 maximum of 148 enabled roles exceeded

  1. Revoking and granting the role is not the only way to change a default role to non-default; simply choose a role, such as DBA, for the user and alter the user to set only that role as default:
    SQL> select * from user_role_privs;
    USERNAME GRANTED_ROLE ADM DEF OS_
    —————————— —————————— — — —
    BINGNORFER DBA NO YES NO
    BINGNORFER EXECUTE_CATALOG_ROLE NO YES NO
    BINGNORFER SELECT_CATALOG_ROLE NO YES NO
    SQL> alter user bingnorfer default role dba;
    User altered.
    SQL> select * from user_role_privs;
    USERNAME GRANTED_ROLE ADM DEF OS_
    —————————— —————————— — — —
    BINGNORFER DBA NO YES NO
    BINGNORFER EXECUTE_CATALOG_ROLE NO NO NO
    BINGNORFER SELECT_CATALOG_ROLE NO NO NO
    SQL>
    At that point you can alter other roles to be included in that default assignment. Alternatively you can select the roles you DO want as default and alter the user accordingly:
    SQL> alter user bingnorfer default role dba, execute_catalog_role;
    User altered.
    SQL> select * from user_role_privs;
    USERNAME GRANTED_ROLE ADM DEF OS_
    —————————— —————————— — — —
    BINGNORFER DBA NO YES NO
    BINGNORFER EXECUTE_CATALOG_ROLE NO YES NO
    BINGNORFER SELECT_CATALOG_ROLE NO NO NO
    SQL>
    Revoking and re-granting the role is not necessary.
    David Fitzjarrell

    • Thanks David, you have mentioned in your post how to “specify” certain roles to be as “default”, but you didnt specify how to revoke the default property without revoking and re-granting.

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