debuggin Sql developer

3833528

When trying to debug a database package this does not allow it because it shows the following error:

Conectando a la base de datos XXX_AC_XXX_LXXX.

Ejecutando PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( 'xxx.xxx.xxx.xxx', 'xxxxx' )

ORA-28031: se ha excedido el máximo de 150 roles activados

ORA-06512: en "SYS.DBMS_DEBUG_JDWP", línea 68

ORA-06512: en línea 1

El proceso ha terminado.

Desconectando de la base de datos XXX_AC_XXX_LXXX.

The point is that the number of roles that I have assigned to the user are the necessary and several users that even exceed the 350 roles which is normal within my business.

In my concept I think these validations should not be necessary to perform a simple debug, is there any way to suppress this validation without having to delete the roles to users who have more than 150 roles?

Thanks for the attention lent.

Glen Conway

What version of the Oracle database do you use?  What version of SQL Developer?

Your question is a bit puzzling as MAX_ENABLED_ROLES has been deprecated since at least Oracle 10g, and has been removed as an initialization parameter in 12c.

https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams117.htm#REFRN10104

Every role you grant to a user is treated as a default role, and Oracle will attempt to enable all default roles for a user at connect time. But the value of MAX_ENABLED_ROLES has long been capped at 150.  Apparently there was an improvement in behavior for 11.2.0.4 that halts enabling of granted, default roles at the cap and permits the connection to proceed without error (but records a warning in the database alert log):

https://connor-mcdonald.com/2016/11/30/max_enabled_roles-nice-touch/

Not sure if that improvement also applies to JDBC connections like SQL Developer uses.

What you should do is grant as many roles as required to your users, but then employ the ALTER USER statement to specify only a limited number of roles as default roles, as in:

Hope this helps.

3833528

Hello thank you very much for your answer, but it is very useful for the moment because I can not revoke roles to users I have, no other solution can be applied

My database version is:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

My version of SQL Developer is:
18.3.0.277 version
277.2354 Internal version

Glen Conway

Actually you would not be revoking the roles, only reducing the number of roles declared as default roles.  In fact, given the blog by Connor McDonald on 11.2.0.4 behavior, only 150 roles ever get enabled at the same time so there is absolutely no benefit to granting more than that as default.  Then the question becomes, which of the 350 or more are getting enabled and might a user lack a privilege in one of the non-enabled roles that is truly needed?

Since sqlplus allows you to connect to 11.2.0.4 with more than 150 default roles (but only enabling the "first" 150), and SQL Developer (using JDBC connectivity) does not, you may have a case for logging a bug against the JDBC driver.

My advice would be to investigate if changing to a different version of the JDBC driver helps.  For example, if you are using SQL Developer 18.3 with the default JDBC (Thin) driver, try configuring Preferences -> Database -> Advance to enable an OCI/Thick driver using an installed Oracle Home or Instant client.  If you have already configured an Oracle client, whether Thin or Thick, that is not 18.3, then upgrade to 18.3.  If none of these work, log an SR with MOS and try to get a bug logged against JDBC.

Anyway, if some users actually require more than 148 roles (+ PUBLIC and their own role) to be enabled, then your organization has gone wildly against any kind of best practices.  This is a wake-up call to begin rethinking roles and privileges in your organization.  Here is a link to the Oracle documentation for MAX_ENABLED_ROLES in 9i R2: https://docs.oracle.com/cd/B10501_01/server.920/a96536/ch1110.htm.  At least since 9i R2 the limit has been 148 (possibly true of 8i, too).  And 10g R2 (where the deprecation began, if not even earlier in 10g R1) was released back in July of 2005.

This is not a technical issue for you to try to work out on your own.  It is a management issue.  Involve managers.  My 2 cents.

Best wishes on getting some resolution for this, whether internal to your org or from Oracle Support.