Max_enabled_roles
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Max_enabled_roles

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    Max_enabled_roles

    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.
    Last edited by JMac; 10-02-2008 at 08:18 AM.

  2. #2
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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?

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    yeah, read that somewhere today, Thanks PAVB.

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

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by JMac
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Quote Originally Posted by PAVB
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width