Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
debuggin Sql developer
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.
Answers
-
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.
-
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 ProductionMy version of SQL Developer is:
18.3.0.277 version
277.2354 Internal version -
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.