(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, on a non-CDB 12.1 Oracle Database.

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 🙂