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:

plsql_debugging01
plsql_debugging01

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

plsql_debugging02
plsql_debugging02

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.

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):

plsql_debugging03
plsql_debugging03

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

Yannick Jaquier on LinkedinYannick Jaquier on RssYannick Jaquier on Twitter
Yannick Jaquier
Find more about me on social media.
This entry was posted in Oracle and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>