max of roles exceeded
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: max of roles exceeded

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    max roles exceeded

    Hi all,

    When I try to login with the SYS username, I get a 'Maximum of 20 Enabled Roles Exceeded'. Is this because the SYS schema has more than 20 roles assigned to it ?

    Thanks,
    Shiva.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    No, it is because you have created > 20 roles. Check out http://technet.oracle.com/docs/produ...ch195.htm#5685 for details.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Got it.
    Thanks.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by marist89
    No, it is because you have created > 20 roles.
    I would not agree completely. Even though you might have MAX_ENABLED_ROLES=20, you can have virtually unlimited number of roles created on the database and yet you still might never encounter that error.

    A very precise diagnosis of the rshivagami's problem would be:
    By default, when you create a role, that role is automaticaly granted to the creator! In your case your user SYS has created more than 20 roles (many of them vere created during instalation) and all those roles are by default also granted to SYS. But in fact user SYS doesn't actually require any of those roles!

    So you can:
    a) revoke those roles (some or all of them) from SYS
    b) when creating new roles, if the creator doesn't require that role, revoke it from him/her immediately after the creation
    c) increase MAX_ENABLED_ROLES
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    So you can:
    a) revoke those roles (some or all of them) from SYS
    b) when creating new roles, if the creator doesn't require that role, revoke it from him/her immediately after the creation
    c) increase MAX_ENABLED_ROLES
    Right. I would suggest you go for option c)


  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: max roles exceeded

    Originally posted by rshivagami
    Hi all,

    When I try to login with the SYS username, I get a 'Maximum of 20 Enabled Roles Exceeded'. Is this because the SYS schema has more than 20 roles assigned to it ?

    Thanks,
    Shiva.
    You are using the default value for MAX_ENABLED_ROLES, that is 20. Raise it up a bit, having in mind that you have the Oracle hard limit of 148.

    You might be curious, why so strange number like 148, why not 150 for example. Note that MAX_ENABLED_ROLES includes roles contained within other roles.

    Each user has two extra roles, PUBLIC and his/hers own role. Thus, if you set MAX_ENABLED_ROLES to 148 you can have at most 150 roles.




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