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?

Barty

Answer 1

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.

Miskolc

Answer 2

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.

Trapan

Thanks Jeff, that was much easier than the other debug option. I also had to compile for debug before it would really work. The Oracle documentation is generally good, but only seems to give you the happy path. And the happy path is rarely confronted in Oracle ;) – Station

Compile for debug, yes required to really use the debugger – Trapan

@Trapan is there any workaround for SQL Developer version 18.1? – Ulema

Answer 3

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.

Station

This worked for me on Oracle 19c. I also had to change the host IP as the OP mentions to make this work against a server instance. – Isolate

This worked for me on SQL Developer v21x on 19c. With DBMS_DEBUG_JDWP setup correctly as above trigger and proc debugging breakpoints get hit. The, as a test enabled "USE DBMS_DEBUG" and the breakpoints were NOT hit. So this backs up what Charles Owen is saying above. – Isolate