Table of contents
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. |
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
Stefan says:
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:
Welcome Stefan: happy to have helped !