Prerequisites to debug PL/SQL in Oracle Database 12c with SQL Developer

ladybug-862107_640

(Picture by Nata-Ap [CC BY-SA 3.0], via Pixabay)

Why I am so obsessed with ACL ? I have no idea ! Actually it is the other way round, ACL are hunting me.
This morning, I helped a fellow developer who wanted to debug his new PL/SQL procedure with SQL Developer.

His first attempt generated errors :

Connecting to the database test_debugger.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( 'his.host', 'random_port' )
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 test_debugger.

I issued this command, thinking it would fix the problem :

GRANT DEBUG CONNECT SESSION TO HIS_SCHEMA ;

But he encountered errors again :

Connecting to the database test_debugger.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( 'his.host', 'random_port' )
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database test_debugger.

So that’s were ACL keep chasing me : I also needed to grant an ACL privilege :

BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
 (
 host => 'his.host',
 ace => xs$ace_type(privilege_list => xs$name_list('jdwp'),
 principal_name => 'HIS_SCHEMA',
 principal_type => xs_acl.ptype_db)
 );
END;
/

According to the documentation, the jdwp privilege is “Used for Java Debug Wire Protocol debugging operations for Java or PL/SQL stored procedures”.

And then it finally worked 🙂

2 thoughts on “Prerequisites to debug PL/SQL in Oracle Database 12c with SQL Developer

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s