Monday 17 November 2014

ORA-28031 maximum of 148 roles exceeded.

For some features in Map-Administrator (such as deleting an industry model) you need to log in with an Oracle user who has elevated privileges (DBA) compared to MAPSYS. We do not use Oracles SYSTEM user but have one with nearly DBA privileges called GISDBA. Recently we got the following error message when trying to log in with the user GISDBA:

ORA-28031 maximum of 148 roles exceeded.

When you create "guest users" with Security Administrator multiple roles are being created and two roles per guest user are attached to GISDBA.  We have many industry models (30+) and usually we create at least two guest users per industry model (one is read only, the second one allows data manipulation - depending on the complexity of the industry model we have more then 2 guest users).

But there is a limit on how many roles can be attached to one Oracle user - and we have hit the limit. The way to get around this issue is to create a second GISDBA user and to transfer some of the roles. 

select count(granted_role) from USER_ROLE_PRIVS where granted_role like 'TB%';
COUNT(GRANTED_ROLE)
-------------------
76


The example above shows that one GISDBA user now has 76 roles attached which are related to guest users.


Map 2013, SP2

No comments:

Post a Comment