Max_enabled_roles

[JMac]

I've had a 9.2.0.6 database running for 7 years. Within the last 2 days when I try and connect as SYSTEM I get an error :

ORA-01925: MAXIMUM OF 30 ENABLED ROLES EXCEEDED

So, I connected as SYS and looked this user's roles:

> desc dba_role_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)

>
> Select count(*) from dba_role_privs
2 where grantee = 'SYSTEM';

COUNT(*)
----------
20

> SELECT granted_role, default_role FROM dba_role_privs
2 WHERE grantee = 'SYSTEM';

GRANTED_ROLE DEF
------------------------------ ---
DBA YES
QA_ROLE YES
AC_OWNER YES
RG_0_ROLE YES
RG_1_ROLE YES
RG_2_ROLE YES
RG_R_ROLE YES
ADMIN_ROLE YES
LOGIN_ROLE YES
FUSION_ROLE YES
INITIAL_ROLE YES

GRANTED_ROLE DEF
------------------------------ ---
DISTRIB_0_ROLE YES
DISTRIB_1_ROLE YES
DISTRIB_2_ROLE YES
DISTRIB_E_ROLE YES
DISTRIB_R_ROLE YES
INST_ADMIN_ROLE YES
SBS_LOADER_ROLE YES
SHIP_QUERY_ROLE YES
AQ_ADMINISTRATOR_ROLE YES

20 rows selected.

> SHOW parameter MAX_ENABLED

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_enabled_roles integer 30
>
>
>
> connect system
Enter password:
ERROR:
ORA-01925: maximum of 30 enabled roles exceeded


Warning: You are no longer connected to ORACLE.
>
>
> show user
USER is ""
>

I know I can set the init.ora param as high as 148, but that's not my issue with this.

(1) Why has it just started to happen now?

(2) Why, if the user only has 20 roles, is SYSTEM exceeding 30 enabled roles?


Edit: There are 45 roles actually defined in the system. If this is what is causing the issue, all well and good - though the user in question SYSTEM is shown as only having 20 roles in DBA_ROLE_PRIVS.

However, why has it only now just started to happen? No new roles have been added for several years.

[JMac]

Okay.

I've revoked about a dozen or roles from SYSTEM and can now connect. Hurrah!

However...

Why did this suddenly start to happen?
Why does DBA_ROLE_PRIVS appear to be lying?

[PAVB]

Your role count is wrong.

max_enabled_roles count includes roles granted to roles so, if any of the roles granted to the account have roles granted to it you have to add those additional roles on your head-count.

[JMac]

yeah, read that somewhere today, Thanks PAVB.

it still doesn't explain HOW this started happening only this week.

[PAVB]

it still doesn't explain HOW this started happening only this week.

I can see two alternatives...

1- Somebody granted some roles this week and... kaboom
2- Somebody altered init file setting max_enabled_roles down to 30 -from whatever >30 setup you had before - then this week you recycled the instance, got the new setup and... kaboom.

[JMac]

I can see two alternatives...

1- Somebody granted some roles this week and... kaboom
2- Somebody altered init file setting max_enabled_roles down to 30 -from whatever >30 setup you had before - then this week you recycled the instance, got the new setup and... kaboom.

(1) I very much doubt it. In fact I'd say impossible.
(2) Impossible

Which leaves me no clearer as to what actually occurred. I may take this up with support