max of roles exceeded

[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.

[marist89]

No, it is because you have created > 20 roles. Check out http://technet.oracle.com/docs/produ...ch195.htm#5685 for details.

[rshivagami]

Got it.
Thanks.

[jmodic]

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

[julian]

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)

[julian]

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.