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
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.
Antoaneta Vatova
Antoaneta Vatova, May 03, 2023 - 7:42 am UTC
I get this error in SQL developer , but not in PL/SQL developer.
I went to preferences in SQL developer > Debuger and choose for Database Debug Protocol > Use dbms_debug.
Afterwards i was wable to debug with sql developer. This worked for me.
May 10, 2023 - 4:38 am UTC
Glad to help