fadeshadow
asked on
maximum of 30 enabled roles exceeded
Hi,
I am working in a "security" project, and I have prepared 60 roles (3 per schema, SELECT, OPER and ADMIN)
As you can notice this database have 20+ schemas, each one with its own processes.
Later I may need to create more roles (not per schema but per department) as HR, FINANCE, etc.
The problem is that as soon as I ran the script that created the roles (even without assigning the privileges) the databases do not allowed me to login as SYSTEM indicating: "ORA-01925: maximum of 30 enabled roles exceeded"
Now, why this happened if I havent even assigned the roles to users?
Any help will be gratly appreciated.
Carlos
I am working in a "security" project, and I have prepared 60 roles (3 per schema, SELECT, OPER and ADMIN)
As you can notice this database have 20+ schemas, each one with its own processes.
Later I may need to create more roles (not per schema but per department) as HR, FINANCE, etc.
The problem is that as soon as I ran the script that created the roles (even without assigning the privileges) the databases do not allowed me to login as SYSTEM indicating: "ORA-01925: maximum of 30 enabled roles exceeded"
Now, why this happened if I havent even assigned the roles to users?
Any help will be gratly appreciated.
Carlos
Just as more to seazodiac's post:
To find how large the MAX_ENABLED_ROLES parameter in init.ora to make it, execute the following SQL statements:
---> in 9i :
SQL> SELECT grantee, count(*)
FROM (SELECT grantee, granted_role
FROM dba_role_privs
CONNECT BY PRIOR grantee = granted_role)
GROUP BY grantee
HAVING count(*) = (SELECT max(count(*))
FROM (SELECT grantee, granted_role
FROM dba_role_privs
CONNECT BY PRIOR grantee=granted_role)
GROUP BY grantee);
---> in 8i: as CONNECT BY is not usable with GROUP BY, create a temporary
table that selects the rows from the dictionary view before:
SQL> drop table tempo_roles;
SQL> create table tempo_roles
as select grantee, granted_role
from dba_role_privs;
SQL> select grantee,count(*)
from (SELECT grantee, granted_role
FROM tempo_roles
CONNECT BY PRIOR grantee = granted_role)
where grantee in (select username from dba_users)
group by grantee
having count(*) = (select max(count(*))
from (SELECT grantee, granted_role
FROM tempo_roles
CONNECT BY PRIOR grantee = granted_role)
group by grantee);
to get the highest current number of roles assigned to a user.
To find how large the MAX_ENABLED_ROLES parameter in init.ora to make it, execute the following SQL statements:
---> in 9i :
SQL> SELECT grantee, count(*)
FROM (SELECT grantee, granted_role
FROM dba_role_privs
CONNECT BY PRIOR grantee = granted_role)
GROUP BY grantee
HAVING count(*) = (SELECT max(count(*))
FROM (SELECT grantee, granted_role
FROM dba_role_privs
CONNECT BY PRIOR grantee=granted_role)
GROUP BY grantee);
---> in 8i: as CONNECT BY is not usable with GROUP BY, create a temporary
table that selects the rows from the dictionary view before:
SQL> drop table tempo_roles;
SQL> create table tempo_roles
as select grantee, granted_role
from dba_role_privs;
SQL> select grantee,count(*)
from (SELECT grantee, granted_role
FROM tempo_roles
CONNECT BY PRIOR grantee = granted_role)
where grantee in (select username from dba_users)
group by grantee
having count(*) = (select max(count(*))
from (SELECT grantee, granted_role
FROM tempo_roles
CONNECT BY PRIOR grantee = granted_role)
group by grantee);
to get the highest current number of roles assigned to a user.
ASKER
Thanks to all for your comments, but none of you really response to my question, I will post ONLY the question again to see if I make me understand better:
"As soon as I created 60 roles the databases do not allowed me to login as SYSTEM indicating: "ORA-01925: maximum of 30 enabled roles exceeded""
"Now, why this happened if I havent even assigned the roles to users?"
I really appreciate your comments and I know what the parameter MAX_ENABLED_ROLES means, I also know that it is a static parameter that will require to restart the database, but why I received this message just creating the new roles?
I was thinking that maybe SYSTEM auto self grants any new role, or somethink like that.
BTW baonguyen, I ran the script and returned that System has 53 roles, it is that possible when the max_enabled_roles is 30?
regards again.
Carlos
"As soon as I created 60 roles the databases do not allowed me to login as SYSTEM indicating: "ORA-01925: maximum of 30 enabled roles exceeded""
"Now, why this happened if I havent even assigned the roles to users?"
I really appreciate your comments and I know what the parameter MAX_ENABLED_ROLES means, I also know that it is a static parameter that will require to restart the database, but why I received this message just creating the new roles?
I was thinking that maybe SYSTEM auto self grants any new role, or somethink like that.
BTW baonguyen, I ran the script and returned that System has 53 roles, it is that possible when the max_enabled_roles is 30?
regards again.
Carlos
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As a general database setup, I always set the parameter MAX_ENABLED_ROLES to 125 in the init.ora file. It seems to be enough. And of course System is granted access to roles it creates because it has the GRANT ANY ROLE system privilege because of the DBA role it has.
RWB
RWB
and modify init<SID>.ora file
add this line;
MAX_ENABLED_ROLES = 60
and restart the database