Minimum required privileges for PL/SQL debugging with SQL Developer

Preamble

Our new SOX security rules have struck again and people now using their read only personal account would like to debug procedure in live (!!). Normal and logic answer would be: never ever debug in live ! But as an exercise I wanted to see what is bare minimum grants you need to debug your own procedures, functions or packages as well as what you need to grant to someone else to be able to do the debug for you.

This article is not on how to use SQL Developer debugger as tons of articles have already been written by Jeff Smith and others, see references section.

This blog has been written using a 12cR2 (12.2.0.1.0) Enterprise edition database running on Oracle Linux Server release 7.5 and SQL Developer Version 18.2.0.183.

PL/SQL debugging as object owner

I start by creating a test user with minimum privileges:

SQL> CREATE USER test1 IDENTIFIED BY test1;
 
USER created.
 
SQL> GRANT CONNECT,RESOURCE TO test1;
 
GRANT succeeded.

And a test procedure with a bit of display and a variable manipulation:

CREATE OR REPLACE PROCEDURE debug_test
AS
  i NUMBER:=0;
BEGIN
  dbms_output.put_line('First step, i = ' || i);
  i:=i+1;
  dbms_output.put_line('Second step, i = ' || i);
  i:=i+1;
  dbms_output.put_line('Third step, i = ' || i);
  i:=i+1;
  dbms_output.put_line('Fourth step, i = ' || i);
  i:=i+1;
  dbms_output.put_line('Fifth step, i = ' || i);
  i:=i+1;
END;
/

In SQL Developer, connected with test1 account which is procedure, owner you can display line number and place breakpoint using right click mouse button in left margin or simply press F5 at cursor position to insert a breakpoint:

Once done you can press the beetle button to launch the procedure (in my case) in debug mode:

I got this first error message:

Connecting TO the DATABASE server1.domain.com_pdb1_test1.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '192.168.56.1', '58696' )
ORA-01031: insufficient PRIVILEGES
ORA-06512: AT "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: AT line 1
This SESSION requires DEBUG CONNECT SESSION AND DEBUG ANY PROCEDURE USER PRIVILEGES.
Process exited.
Disconnecting FROM the DATABASE server1.domain.com_pdb1_test1.

I am a bit surprised by the display stating that I have to grant DEBUG ANY PROCEDURE high privileges as I am the procedure owner… By the way this is also the shorcut used in many articles but I would like to avoid it as by principle all the ANY privileges are quite high…

As found in official documentation:

System Privilege Name	Operations Authorized
DEBUG CONNECT SESSION	Connect the current session to a debugger.
DEBUG ANY PROCEDURE	Debug all PL/SQL and Java code in any database object. Display information on all SQL statements executed by the application. Note: Granting this privilege is equivalent to granting the DEBUG object privilege on all applicable objects in the database.
DEBUG	Access, through a debugger, all public and nonpublic variables, methods, and types defined on the object type. Place a breakpoint or stop at a line or instruction boundary within the procedure, function, or package. This privilege grants access to the declarations in the method or package specification and body.

System Privilege Name

Operations Authorized

DEBUG CONNECT SESSION

Connect the current session to a debugger.

DEBUG ANY PROCEDURE

Debug all PL/SQL and Java code in any database object. Display information on all SQL statements executed by the application.

Note: Granting this privilege is equivalent to granting the DEBUG object privilege on all applicable objects in the database.

DEBUG

Access, through a debugger, all public and nonpublic variables, methods, and types defined on the object type.

Place a breakpoint or stop at a line or instruction boundary within the procedure, function, or package. This privilege grants access to the declarations in the method or package specification and body.

To solve execution issue on DBMS_DEBUG_JDWP.CONNECT_TCP you have the solution on Ask TOM web site or in My Oracle Support.

Initial situation is no ACL granted for no one:

SQL> SET lines 200 pages 1000
SQL> col host FOR a10
SQL> col acl FOR a50
SQL> col acl_owner FOR a10
SQL> SELECT * FROM dba_network_acls;
 
HOST       LOWER_PORT UPPER_PORT ACL                                                ACLID            ACL_OWNER
---------- ---------- ---------- -------------------------------------------------- ---------------- ----------
*                                NETWORK_ACL_4700D2108291557EE05387E5E50A8899       0000000080002724 SYS
 
SQL> col privilege FOR a10
SQL> col start_date FOR a15
SQL> col end_date FOR a15
SQL> col principal FOR a20
SQL> SELECT * FROM dba_network_acl_privileges;
 
ACL                                                ACLID            PRINCIPAL            PRIVILEGE  IS_GR INVER START_DATE      END_DATE        ACL_OWNER
-------------------------------------------------- ---------------- -------------------- ---------- ----- ----- --------------- --------------- ----------
NETWORK_ACL_4700D2108291557EE05387E5E50A8899       0000000080002724 GSMADMIN_INTERNAL    resolve    TRUE  FALSE                                 SYS
NETWORK_ACL_4700D2108291557EE05387E5E50A8899       0000000080002724 GGSYS                resolve    TRUE  FALSE                                 SYS

Grant ACL privileges on JDWP to connect to the debugging database session with something like:

SQL> EXEC dbms_network_acl_admin.append_host_ace(host=>'*', ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('JDWP'), -
> principal_name=>'TEST1', principal_type=>sys.XS_ACL.PTYPE_DB));
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT * FROM dba_network_acl_privileges;
 
ACL                                                ACLID            PRINCIPAL            PRIVILEGE  IS_GR INVER START_DATE      END_DATE        ACL_OWNER
-------------------------------------------------- ---------------- -------------------- ---------- ----- ----- --------------- --------------- ----------
NETWORK_ACL_4700D2108291557EE05387E5E50A8899       0000000080002724 GSMADMIN_INTERNAL    resolve    TRUE  FALSE                                 SYS
NETWORK_ACL_4700D2108291557EE05387E5E50A8899       0000000080002724 GGSYS                resolve    TRUE  FALSE                                 SYS
NETWORK_ACL_4700D2108291557EE05387E5E50A8899       0000000080002724 TEST1                JDWP       TRUE  FALSE                                 SYS

It was still failing for a grant issue, I have simply granted:

SQL> GRANT debug CONNECT SESSION TO test1;
 
GRANT succeeded.

And it worked, well almost as my breakpoint has not been taken into account:

Connecting TO the DATABASE server1.domain.com_pdb1_test1.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '192.168.56.1', '62965' )
Debugger accepted connection FROM DATABASE ON port 62965.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
FIRST step, i = 0
SECOND step, i = 1
Third step, i = 2
Fourth step, i = 3
Fifth step, i = 4
Process exited.
Disconnecting FROM the DATABASE server1.domain.com_pdb1_test1.
Debugger disconnected FROM DATABASE.

Simply because you have to compile debug the source code, as TEST1 user:

SQL> ALTER PROCEDURE debug_test compile debug;
 
PROCEDURE altered.

We can now see that debug mode as been activated for our test procedure:

SQL> col plsql_debug FOR a10
SQL> col plsql_warnings FOR a15
SQL> col plscope_settings FOR a20
SQL> SELECT plsql_debug,plsql_warnings,plscope_settings
     FROM dba_plsql_object_settings
     WHERE owner='TEST1'
     AND name='DEBUG_TEST';
 
PLSQL_DEBU PLSQL_WARNINGS  PLSCOPE_SETTINGS
---------- --------------- --------------------
TRUE       DISABLE:ALL     IDENTIFIERS:NONE

And when executing again the execution stop at my breakpoint (more can be added) and the nice thing is that you can have the value of each variables (i in my case is equal to 2):

PL/SQL debugging with another account

I create a second test user and the goal will be to do debugging with this account giving it minimum privileges:

SQL> CREATE USER test2 IDENTIFIED BY test2;
 
USER created.
 
SQL> GRANT CONNECT,RESOURCE TO test2;
 
GRANT succeeded.

The minimum you have to grant is debug on procedure, execute permission and the right to use the debugger:

SQL> GRANT debug ON debug_test TO test2;
 
GRANT succeeded.
 
SQL> GRANT EXECUTE ON debug_test TO test2;
 
GRANT succeeded.
 
SQL> GRANT debug CONNECT SESSION TO test2;
 
GRANT succeeded.

It failed for error we have seen above:

SQL> EXEC dbms_network_acl_admin.append_host_ace(host=>'*', ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('JDWP'), -
> principal_name=>'TEST2', principal_type=>sys.XS_ACL.PTYPE_DB));
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT * FROM dba_network_acl_privileges;
 
ACL                                                ACLID            PRINCIPAL            PRIVILEGE  IS_GR INVER START_DATE      END_DATE        ACL_OWNER
-------------------------------------------------- ---------------- -------------------- ---------- ----- ----- --------------- --------------- ----------
NETWORK_ACL_4700D2108291557EE05387E5E50A8899       0000000080002724 GSMADMIN_INTERNAL    resolve    TRUE  FALSE                                 SYS
NETWORK_ACL_4700D2108291557EE05387E5E50A8899       0000000080002724 GGSYS                resolve    TRUE  FALSE                                 SYS
NETWORK_ACL_4700D2108291557EE05387E5E50A8899       0000000080002724 TEST1                JDWP       TRUE  FALSE                                 SYS
NETWORK_ACL_4700D2108291557EE05387E5E50A8899       0000000080002724 TEST2                JDWP       TRUE  FALSE                                 SYS

And it worked as before, with a user not owning the code… Granting execute on PL/SQL code to a read only user raise anyways some security questions…

References

Remote Debugging Fails with ORA-24247 (Doc ID 1932482.1)
SQL Developer Debugger Raises ACL Error Against 12 Database (Doc ID 1627194.1)
Which privilege is needed to see the package body source owned by another user (Doc ID 746978.1)
Developing and Debugging PL/SQL using Oracle SQL Developer
Everything you ever wanted to know about the PL/SQL Debugger

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

2 thoughts on “Minimum required privileges for PL/SQL debugging with SQL Developer”

Stefan says:
February 7, 2020 at 15:40
Thank you Yannick, debugging is still s.th. I am not really familiar with but it is very powerful. I learned more about compiling with debug thanks to your explanations.
- Yannick Jaquier says:
  February 10, 2020 at 12:11
  Welcome Stefan: happy to have helped !

IT World

Minimum required privileges for PL/SQL debugging with SQL Developer

Table of contents

Preamble

PL/SQL debugging as object owner

PL/SQL debugging with another account

References

About Post Author

Yannick Jaquier

Related Posts:

2 thoughts on “Minimum required privileges for PL/SQL debugging with SQL Developer”

Stefan says:

Yannick Jaquier says:

Leave a Reply Cancel Reply