Recommended # Database Role

[mfarouk]

What is the recommended number of roles that could be enabled for database user ?

[sreddy]

-Unlimited- in the sense that there is limitation but, we never hit that limitation. Its some 9-digit figure..I guess. You can find it if you search on 'jmodic' threads.

[akkerend]

The init.ora parameter MAX_ENABLED_ROLES specifies the maximum number of database roles that users can enable, including roles contained within other roles. The default is 20, the maximum is 148.

The actual number of roles users can enable is 2 plus the value of MAX_ENABLED_ROLES, because each user has two additional roles, PUBLIC and the user's own role.

[sreddy]

SORRY. My posting refers to the number of roles allowed on the database not for a single user. Thanks akkerend ! for pointing out..

[mfarouk]

Originally posted by sreddy
-Unlimited- in the sense that there is limitation but, we never hit that limitation. Its some 9-digit figure..I guess. You can find it if you search on 'jmodic' threads.

I ask about the recommended number of roles can be enabled for user session, not the max number of roles
which is 148 + 2, which can set using parameter MAX_ENABLED_ROLES.

I think the recommended number of roles to achieve good performance is 80, thanks.

[jmodic]

Originally posted by mfarouk
I think the recommended number of roles to achieve good performance is 80, thanks. [/B]

May I ask where did you get this magic number 80? And what influence on the performance the number of granted roles could possibly have? Can you provide any facts to support your assumption?

IMHO you've firstly invented an irrelevant question and then invented even more irrelevant answer for it....

My ansver to your question would be: users should be granted exactly those privileges that he needs to perform his job. No more and no less. In how many roles those privileges are spread depends on many factors. But the number of roles should have no particular influence on the performance.

[pando]

I didnt know number of roles affects perfomance...? I think NOT
80 Roles ia a lot you know.... if it was 80 privileges then fine but 80 rols.... I have not seen any user using more than 4 or 5 roles

[mfarouk]

Firstly i would to thank u about your magic answer
1-for the 80 number is come from many discussion with oracle mans (DBA's and
DEVL) in big oracle offices.
2-For invented answer to may question ,because i want to discuss to number with
dbasupport guys
2- this for your vague answer.

[akkerend]

And why do you have a better performance with 80 roles than with 20 or 100?
If the number of roles affect the performance, does the number of privileges also affects the performance?
Without proof that the number of roles does affect the performance, i don't think it does.

[pando]

From all the perfomance tuning books, papers I have read none states that you can tune perfomance with number of roles, actually none of them states that number of roles affects perfomance, these are not vague answers. Simply we say what we have faced , seen experienced.

There is no such recommendation for your question, one example

if you need to grant 50 roles to many users you might as well create a role that contains those 50 roles. In this case how many role we have? One...

And actually if number of roles affect perfomance we are more than happy hearing why