Network access denied at "SYS.DBMS_DEBUG_JDWP"

Question

When trying to save a trigger I get this error

Connecting to the database XE.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '192.168.56.1', '59537' )
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 XE.

I'm just a beginner in working with DB, how can I fix this?

Answer 1

You say 'save', we can assume you also mean 'compile.' This error wouldn't occur if you were merely compiling the updated pl/sql to the database. You'll get this error when you try to invoke the default PL/SQL debugger (button has a picture of a bug on it.)

The easiest solution for this is as follows:

Get version 20.2 of SQL Developer. In preferences, set debugger mode to DBMS_DEBUG

This uses a normal client connection and avoids the ACL rule, as the database no longer is connecting to your machine.

Disclaimer: I'm the product manager for SQL Developer at Oracle.

Answer 2

The first example didn't work for me. If you really want to get the DBMS_DEBUG_JDWP (Java Debug Wire Protocol) working, this is what you do:

NOTE: DO NOT USE DBMS_DEBUG anymore, it's considered deprecated. While it hasn't been removed, it may be in the future. The Oracle documentation specifically says to use DBMS_DEBUG_JDWP. It's set as the default for a reason ;)

DBMS DEBUG deprecated

Logging on as SYS with SYSDBA. -- Grant the user debug permissions, substituting HR with the user you want to be logged on when you're debugging

GRANT DEBUG ANY PROCEDURE TO hr;
GRANT DEBUG CONNECT SESSION TO hr;
GRANT EXECUTE ON DBMS_DEBUG_JDWP To hr;
COMMIT;
-- Here you want to again substitute the HR user with your user doing the debugging
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
   (host=>'127.0.0.1',
     ace=> SYS.XS$ACE_TYPE(privilege_list=>SYS.XS$NAME_LIST('JDWP'),
               principal_name=>'HR',
               principal_type=>SYS.XS_ACL.PTYPE_DB) 
   );
END;
COMMIT;

Also you want to set your listener.ora to be able to connect to localhost. Also, you also want to add this setting in SQL Developer:

1. Tools

2. Preferences

3. Debugger

4. Prompt For Debugger Host (this will allow you to type 127.0.0.1 when the debugger starts, if you're doing this locally)

NOTE: Make sure you compile for debug!

UPDATE: The example I showed is for situations where the database is a local XE running on your own machine. This is essentially a local debugging session. To debug remotely (Oracle database is running on a remote server), you will substitute the 127.0.0.1 loopback IP with the IP of your machine on the current network. Do an ipconfig if necessary. You'll run the DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE procedure with that IP. You'll use your machine IP on the network when the network IP prompt comes up.

Answer 3

It is about the ACL (as the message says). Here's a walkthrough, see if it helps. I'm using user SCOTT; you'd use your own user.

SQL> show user
USER is "SYS"
SQL>
SQL> SELECT * FROM dba_network_acls;

no rows selected

Create ACL:

SQL> BEGIN
  2     DBMS_NETWORK_ACL_ADMIN.create_acl (
  3        acl          => 'xedba.xml',
  4        description  => 'TCP, SMTP, MAIL, HTTP Access',
  5        principal    => 'SCOTT',
  6        is_grant     => TRUE,
  7        privilege    => 'connect',
  8        start_date   => NULL,
  9        end_date     => NULL);
 10  END;
 11  /

PL/SQL procedure successfully completed.

Assign ACL:

SQL> BEGIN
  2     DBMS_NETWORK_ACL_ADMIN.assign_acl (acl         => 'xedba.xml',
  3                                        HOST        => '*',
  4                                        lower_port  => NULL,
  5                                        upper_port  => NULL);
  6  END;
  7  /

PL/SQL procedure successfully completed.

Add privilege:

SQL> BEGIN
  2     -- SCOTT
  3     DBMS_NETWORK_ACL_ADMIN.add_privilege (acl         => 'xedba.xml',
  4                                           principal   => 'SCOTT',
  5                                           is_grant    => TRUE,
  6                                           privilege   => 'connect',
  7                                           start_date  => NULL,
  8                                           end_date    => NULL);
  9
 10     DBMS_NETWORK_ACL_ADMIN.add_privilege (acl         => 'xedba.xml',
 11                                           principal   => 'SCOTT',
 12                                           is_grant    => TRUE,
 13                                           privilege   => 'resolve',
 14                                           start_date  => NULL,
 15                                           end_date    => NULL);
 16  END;
 17  /

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

Now, you should connect as user which was granted access and run your command again.