Thanks for the question, Rajeshwaran.

Asked: May 02, 2016 - 2:01 pm UTC

Last updated: January 20, 2020 - 2:55 am UTC

Version: 12.1.0.2

Viewed 50K+ times! This question is

You Asked

sys@ORA12C> create user demo identified by demo
  2  default tablespace users
  3  quota unlimited on users;

User created.

sys@ORA12C> grant create session,create procedure to demo;

Grant succeeded.

sys@ORA12C> grant debug connect session to demo;

Grant succeeded.

sys@ORA12C> grant debug any procedure to demo;

Grant succeeded.

sys@ORA12C> grant execute on DBMS_DEBUG_JDWP to demo;

Grant succeeded.

sys@ORA12C> begin
  2     dbms_network_acl_admin.append_host_ace
  3     (host=>'LT035221',
  4      ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('JDWP') ,
  5                     principal_name=>'DEMO',
  6                     principal_type=>sys.XS_ACL.PTYPE_DB) );
  7  end;
  8  /

PL/SQL procedure successfully completed.

sys@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C> create or replace procedure p as
  2  begin
  3     dbms_output.put_line('Hello_World');
  4  end;
  5  /

Procedure created.

demo@ORA12C> alter procedure p compile debug ;

Procedure altered.

demo@ORA12C>

When tried to debug from SQL Developer got this error message

Connecting to the database DEMO_ORA12C.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '10.191.61.86', '60590' )
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 DEMO_ORA12C.

Could you help us to understand what privilege is missing to debug this procedure ?

and Connor said...

Your steps look fine, so most likely cause is a name resolution one. For example,

SQL> drop user demo cascade;

User dropped.

SQL> create user demo identified by demo
  2   default tablespace users
  3   quota unlimited on users;

User created.

SQL> grant create session,create procedure to demo;

Grant succeeded.

SQL> grant debug connect session to demo;

Grant succeeded.

SQL> grant debug any procedure to demo;

Grant succeeded.

SQL> grant execute on DBMS_DEBUG_JDWP to demo;

Grant succeeded.

SQL> begin
  2         dbms_network_acl_admin.append_host_ace
  3         (host=>'laptop01',
  4          ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('JDWP') ,
  5                         principal_name=>'DEMO',
  6                         principal_type=>sys.XS_ACL.PTYPE_DB) );
  7      end;
  8  /

PL/SQL procedure successfully completed.

SQL> conn demo/demo
Connected.

SQL> create or replace procedure p as
  2      begin
  3         dbms_output.put_line('Hello_World');
  4      end;
  5      /

Procedure created.

SQL> alter procedure p compile debug ;

Procedure altered.

That all looks fine, but when I try the debugger, I get:

Connecting to the database demo_np12.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '127.0.0.1', '61625' )
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 demo_np12.

In my case, you can see I wanted to connect to 127.0.0.1, so "laptop01" must not be resolving to that. So I added the ACL I needed:

SQL> begin
  2         dbms_network_acl_admin.append_host_ace
  3         (host=>'127.0.0.1',
  4          ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('JDWP') ,
  5                         principal_name=>'DEMO',
  6                         principal_type=>sys.XS_ACL.PTYPE_DB) );
  7      end;
  8  /

PL/SQL procedure successfully completed.

and then the debugger worked fine.

Hope this helps.

Rating

(14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

IP Based ACL

Rajeshwaran Jeyabal, May 04, 2016 - 5:33 am UTC

Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '10.191.61.86', '60590' )

when i connect to the network, the IP changes for each connection. so in that case do i need to make ACL for each IP changes? can't that be done based on host name?

May 04, 2016 - 6:45 am UTC

I wasn't suggesting to use the IP, I was saying that there an issue somewhere in the resolution of the IP and hence ability to use that definition.

For example, I connected via SQL Dev using a tnsnames.ora entry which nominated a different hostname in my hosts file (which resolves in this case to 192.168.1.10)

Now when I enable debug on SQL Dev, the connection call was:

Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '192.168.1.10', '64344' )

So as long as the hostname you are using, resolves to the name IP address from both SQL Dev *and* the database, I think you should be OK.

Hope this helps.

IP Based ACL

Rajeshwaran Jeyabal, May 04, 2016 - 11:19 am UTC

For example, I connected via SQL Dev using a tnsnames.ora entry which nominated a different hostname in my hosts file (which resolves in this case to 192.168.1.10)

Thanks Connor. This helps.

In my case, database server is available on my laptop (and i connect to this DB server using SQL Dev from the same laptop - so client and server are same to me). without connected to the network my IP address is 127.0.0.1
but when connected to network and the IP changes dynamically.

Today my IP Address is

sys@ORA12C> $ipconfig

Windows IP Configuration


Ethernet adapter Local Area Connection 2:

   Connection-specific DNS Suffix  . : xxx.com
   IPv4 Address. . . . . . . . . . . : 10.191.61.36
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   Default Gateway . . . . . . . . . :

Please see above , when i posted this question my IP address was 10.191.61.86

so do i need to make two ACL entries like this ?

Day#1 - When i walk into office and connect to network, got this IP Address as 10.191.61.36 - make an ACL entry for this IP (10.191.61.36)

Day#2 - When i walk into office and connect to network, got this IP Address as 10.191.61.86 - again make an ACL entry for this IP (10.191.61.86)

Is that a right approach ( to make ACL entry for each IP address) ?

May 04, 2016 - 11:37 am UTC

I have similar challenges (because I jump between many networks).

What I did for my local database, was change the listener to listen on both the hostname *and* localhost, eg

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost )(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = my_computer_name)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

so when I'm doing stuff on my local database, everything is done via localhost.

Hope this helps.

IP Based ACL

Rajeshwaran Jeyabal, May 05, 2016 - 6:37 am UTC

so when I'm doing stuff on my local database, everything is done via localhost.

Thanks Connor. This helps much. made the below changes to listener and it work fine, when away from network ( everything is done via localhost a.k.a 127.0.0.1 )

But when connected to network things fail - today my IP Address is 10.191.52.69, when i started to debug from connection over network it fails.

Connecting to the database DEMO_ORA12C.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '10.191.52.69', '56589' )
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 DEMO_ORA12C.

Workaround for now
1) Grant DBA TO demo; then things works fine either inside/outside the network.
2) just keep looking for IP changes and add those IP's (10.242.144.87) to ACL for each changes.

for now moving with option-1, let me know if any permanent workarounds available

C:\Users\179818>lsnrctl status listener_ora12c

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 05-MAY-2016 11:57:21

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1524)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_ORA12C
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date                05-MAY-2016 11:28:12
Uptime                    0 days 0 hr. 29 min. 12 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\Vnameit\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File         D:\app\Vnameit\diag\tnslsnr\LT035221\listener_ora12c\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1524ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LT035221.cts.com)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1532)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=LT035221.cts.com)(PORT=5500))(Security=(my_wallet_dire
Services Summary...
Service "ORA12CXDB" has 1 instance(s).
  Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "ORA12c" has 2 instance(s).
  Instance "ORA12c", status UNKNOWN, has 1 handler(s) for this service...
  Instance "ora12c", status READY, has 1 handler(s) for this service...
The command completed successfully

May 05, 2016 - 10:13 am UTC

I'm still confused...

If

a) there is an ACL for (say) "LT035221.cts.com"

b) your normal SQL Dev connection either directly refers to host LT035221.cts.com (or the tnsnames entry you are using points to LT035221.cts.com)

then even the IP address changes, and even if SQL Dev refers to the IP address not the hostname...I don't see why it would not work. Because the both the database server, and your machine would resolve the name LT035221.cts.com to the same IP address now ?

It sounds more like a DNS issue of some sort.

I'll ask around.

IP Based ACL

Rajeshwaran Jeyabal, May 05, 2016 - 7:00 am UTC

With DBA privilege revoked from DEMO schema

SQL-DEV fails (with below error) , but when tried to debug from PL/SQL Developer 7.0.1.1066 - no error it went fine.

things work different between clients, when connected to same database server.

Connecting to the database DEMO_ORA12C.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '10.191.52.69', '63425' )
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 DEMO_ORA12C.

May 05, 2016 - 12:03 pm UTC

And just double checking - when you did the ACL, it was the fully qualified name (ie, it matched the hostname from the ipconfig command ?)

Addenda:

As a workaround, you could try the "Prompt for debugger host" in Preferences, and manually enter the hostname/ip address

IP Based ACL

Rajeshwaran Jeyabal, May 05, 2016 - 11:59 am UTC

And just double checking - when you did the ACL, it was the fully qualified name (ie, it matched the hostname from the ipconfig command ?)

Here is my TNS Entry

C:\Users\179818>tnsping ora12c

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 05-MAY-2016 17:19:37

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
D:\app\Vnameit\product\12.1.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LT035221)(PORT = 1522)) (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1532)) (CONNECT_DATA = (SERVER =
EDICATED) (SERVICE_NAME = ORA12c)))
OK (400 msec)

Here is my ACL entries.

C:\Users\179818>sqlplus rajesh/oracle@ora12c

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 5 17:21:42 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu May 05 2016 17:17:51 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

rajesh@ORA12C> column host format a30
rajesh@ORA12C> select host from dba_host_acls;

HOST
------------------------------
*
pc143855
10.191.61.36
lt035221
127.0.0.1
lt035221.cts.com

6 rows selected.

rajesh@ORA12C> select host from dba_network_acls;

HOST
------------------------------
*
pc143855
10.191.61.36
lt035221
127.0.0.1
lt035221.cts.com

6 rows selected.

Here is my IP for today.

rajesh@ORA12C> $ipconfig

Windows IP Configuration


PPP adapter network:

   Connection-specific DNS Suffix  . :
   IPv4 Address. . . . . . . . . . . : 100.65.200.99
   Subnet Mask . . . . . . . . . . . : 255.255.255.255
   Default Gateway . . . . . . . . . : 0.0.0.0

with DBA privilege got revoked from DEMO schema and debugging from SQL DEV through this message

Connecting to the database DEMO_ORA12C.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '100.65.200.99', '64106' )
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 DEMO_ORA12C.

From Pl/SQL Developer 7.0.1.1066 - connected through DEMO schema (using above TNS entry) and debugged the same procedure , but no errors this time.

Does this info helps ?

May 06, 2016 - 1:28 am UTC

Hmm.... I cant see anything that would be amiss. I'll keep hunting.

IP Based ACL

Rajeshwaran Jeyabal, May 05, 2016 - 12:51 pm UTC

As a workaround, you could try the "Prompt for debugger host" in Preferences, and manually enter the hostname/ip address

Thanks. That worked for me.

PL/SQL Debugger in 12.2 database from SQL Dev 4.2

Rajeshwaran, May 03, 2017 - 8:03 am UTC

Here is my demo from 12.2 database.

rajesh@ORA12C> grant connect,resource,debug connect session
  2  to demo2
  3  identified by demo2 ;

Grant succeeded.

rajesh@ORA12C> alter user demo2 default tablespace ts_data;

User altered.

rajesh@ORA12C> alter user demo2 quota unlimited on ts_data;

User altered.

rajesh@ORA12C> conn demo2/demo2@ora12c
Connected.
demo2@ORA12C> create or replace procedure p
  2  as
  3  begin
  4     dbms_output.put_line('Hello_World');
  5  end;
  6  /

Procedure created.

demo2@ORA12C> conn sys/Password-1@ora12c as sysdba
Connected.
sys@ORA12C>  begin
  2    dbms_network_acl_admin.append_host_ace
  3    (host=>'PC143855',
  4     ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('JDWP') ,
  5                           principal_name=>'demo2',
  6                           principal_type=>sys.XS_ACL.PTYPE_DB) );
  7    end;
  8  /

PL/SQL procedure successfully completed.

sys@ORA12C> select host from dba_network_acls;

HOST
--------------------------------------------------------------------------
*
localhost
lt035221
pc143855

sys@ORA12C>

using the above suggested workaround, Now tried to debug the procedure "P" from SQL Dev 4.2 and when it prompts for the "Debugger Host" - provided this value LT035221, then got this error message from SQL Developer.

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

any help to resolve this error ?

May 29, 2017 - 7:01 am UTC

This must be a network name resolution issue somewhere. I ran your demo pretty much verbatim, only changing the hostname, and it is all fine

SQL> select * from dba_network_acls
  2  @pr
==============================
HOST                          : localhost
LOWER_PORT                    :
UPPER_PORT                    :
ACL                           : /sys/acls/oracle-sysman-ocm-Resolve-Access.xml
ACLID                         : 0000000080002760
ACL_OWNER                     : SYS

PL/SQL procedure successfully completed.


SQL> drop user demo2 cascade;

User dropped.

SQL> grant connect,resource,debug connect session
  2  to demo2
  3  identified by demo2 ;

Grant succeeded.

SQL> alter user demo2 default tablespace users;

User altered.

SQL> alter user demo2 quota unlimited on users;

User altered.

SQL> conn demo2/demo2
Connected.

SQL> create or replace procedure p
  2  as
  3  begin
  4     dbms_output.put_line('Hello_World');
  5  end;
  6  /

Procedure created.

SQL> conn / as sysdba
Connected.

SQL> begin
  2    dbms_network_acl_admin.append_host_ace
  3    (host=>'XPS13',
  4     ace=> sys.xs$ace_type(privilege_list=>sys.XS$NAME_LIST('JDWP') ,
  5                           principal_name=>'DEMO2',
  6                           principal_type=>sys.XS_ACL.PTYPE_DB) );
  7    end;
  8  /

PL/SQL procedure successfully completed.

SQL> select * from dba_network_acls
  2  @pr
==============================
HOST                          : localhost
LOWER_PORT                    :
UPPER_PORT                    :
ACL                           : /sys/acls/oracle-sysman-ocm-Resolve-Access.xml
ACLID                         : 0000000080002760
ACL_OWNER                     : SYS
==============================
HOST                          : xps13
LOWER_PORT                    :
UPPER_PORT                    :
ACL                           : NETWORK_ACL_09418BA3122B4B599A7D8246E549B2E3
ACLID                         : 0000000080002769
ACL_OWNER                     : SYS

PL/SQL procedure successfully completed.

and then in SQL Dev

SQLDEV_DEBUG

any update to the above question

Rajeshwaran, Jeyabal, May 23, 2017 - 4:28 am UTC

Team - did i missing anything above? are you looking for more information, that i could help here ?

May 24, 2017 - 12:35 am UTC

Adding reviews to an existing review does not make it get answered faster. In fact...its more likely to push down to the bottom of the list :-)

The issue is with mortal accoutns

Greg, January 16, 2018 - 5:25 pm UTC

SQL> conn / as sysdba
Connected.

I am trying to debug ACLs. I believe that you get a free pass when you are using an elevated account. The problem is only found when you use a mortal account like the demo user and application. The discussion has been informative based on debugging approaches.

ACLs to all

Greg, January 16, 2018 - 8:26 pm UTC

Thanks for the posting. I finally figured out my issues. I had created ACLs for the user that was granted execute to a package. I finally created a set ACLs for the owner of the package. That solved the "ORA-24247: network access denied by access control list (ACL)" for me. You may want to put this item in your check list.

There has got to be a better way to hunt this error down. The action item makes sense now but it sure is opaque during trouble shooting.
*Action: Ensure that an access control list (ACL) has been assigned to the target host and the privilege necessary to access the target host has been granted to the user.

Works great

A reader, May 09, 2019 - 6:20 am UTC

the answer works great

May 10, 2019 - 8:40 am UTC

Glad we could help.

Great help on WEB Thanks TOM

Pralhad Patil, July 31, 2019 - 10:22 am UTC

Its great and Finally we got someone who take cares onward. Thanks Oracle and thanks ASK "Tom"

Oracle ADB

Martin Mierke, December 13, 2019 - 6:24 pm UTC

Hi,

for some reason I'm not able to get the debugger to work on the Autonomous Transaction Database. Even with the ACL in place and privileges set. Any suggestions?

Br Martin

January 06, 2020 - 11:56 am UTC

Sorry - currently those ports are blocked by the firewalls.

Place a request

Martin Mierke, January 16, 2020 - 12:33 pm UTC

Can we somehow place a request to enable debugging in the ADB.

January 20, 2020 - 2:55 am UTC

Please raise an SR with Support.

The requests carry a lot more weight if they come from customers as opposed to myself.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

IP Based ACL

IP Based ACL

IP Based ACL

IP Based ACL

IP Based ACL

IP Based ACL

PL/SQL Debugger in 12.2 database from SQL Dev 4.2

any update to the above question

The issue is with mortal accoutns

ACLs to all

Works great

Great help on WEB Thanks TOM

Oracle ADB

Place a request

More to Explore

PL/SQL demos

PL/SQL docs

ORA-24247 while debugging from SQL Developer

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

IP Based ACL

IP Based ACL

IP Based ACL

IP Based ACL

IP Based ACL

IP Based ACL

PL/SQL Debugger in 12.2 database from SQL Dev 4.2

any update to the above question

The issue is with mortal accoutns

ACLs to all

Works great

Great help on WEB Thanks TOM

Oracle ADB

Place a request

More to Explore

PL/SQL demos

PL/SQL docs