Basically it suggests to enable these events.
alter session set events '10937 trace name context forever, level 7';
alter session set events '24247 trace name errorstack level 3';
10937, 00000, "trace name context forever"
// * Cause:
// * Action: When set, this event enables tracing of PL/SQL packages.
// * Comment: Setting this event could result in some performance slowdown.
24247, 00000, "network access denied by access control list (ACL)"
// *Cause: No access control list (ACL) has been assigned to the target
// host or the privilege necessary to access the target host has not
// been granted to the user in the access control list.
// *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.
A first try with a newly created user (grants: connect, resource & alter session) fails as expected:
SQL> select utl_http.request(url => 'http://www.google.com') from dual;
Error starting at line : 1 in command -
select utl_http.request(url => 'http://www.google.com') from dual
Error report -
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1530
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1470
ORA-06512: at line 1
According tho the documentation of UTL_HTTP:
This package is an invoker's rights package. The invoking user will need theconnectprivilege granted in the access control list assigned to the remote network host to which he wants to connect, as well as theuse-client-certificatesor theuse-passwordsprivilege to authenticate himself with the remote Web server using the credentials stored in an Oracle wallet.
So connect it is:
BEGIN
DBMS_NETWORK_ACL_ADMIN.append_host_ace (
host => 'www.google.com',
lower_port => 80,
upper_port => 443,
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name => 'BERX',
principal_type => xs_acl.ptype_db));
END;
/
SQL> alter session set events '10937 trace name context forever, level 6';
Session altered.
SQL> alter session set events '24247 trace name errorstack level 3';
Session altered.
SQL> select utl_http.request(url => 'http://www.google.com') from dual;
UTL_HTTP.REQUEST(URL=>'HTTP://WWW.GOOGLE.COM')____________________________________________________________________________
<html code="" itemscope="" itemtype="http://schema.org/WebPage" lan...
The select is fine now - and what's in the trace?
Trace file /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_5483.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Build label: RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
ORACLE_HOME: /u01/app/oracle/product/19.0.0/dbhome_1
System name: Linux
Node name: localhost.localdomain
Release: 5.4.17-2011.5.3.el8uek.x86_64
Version: #2 SMP Wed Jul 29 22:09:11 PDT 2020
Machine: x86_64
Instance name: cdb1
Redo thread mounted by this instance: 1
Oracle process number: 53
Unix process pid: 5483, image: oracle@localhost.localdomain
*** 2021-05-02T07:43:38.309047+00:00 (PDB1(3))
*** SESSION ID:(270.62847) 2021-05-02T07:43:38.309125+00:00
*** CLIENT ID:() 2021-05-02T07:43:38.309135+00:00
*** SERVICE NAME:(pdb1) 2021-05-02T07:43:38.309144+00:00
*** MODULE NAME:(java@localhost.localdomain (TNS V1-V3)) 2021-05-02T07:43:38.309153+00:00
*** ACTION NAME:() 2021-05-02T07:43:38.309162+00:00
*** CLIENT DRIVER:(jdbcoci : 19.3.0.0.0) 2021-05-02T07:43:38.309170+00:00
*** CONTAINER ID:(3) 2021-05-02T07:43:38.309210+00:00
psdnop: BERX 120 3 www.google.com:80
psdnopCheckAcl: priv 2147483693 cnt 1
psdnopCheckAcl: aclid 2147493757 status 3
psdnop: denied
psdnop: BERX 120 2 www.google.com:80
psdnopCheckAcl: priv 2147483690 cnt 1
psdnopCheckAcl: aclid 2147493757 status 1
psdnop: granted
the first line of interest is psdnop: BERX 120 3 www.google.com:80. The username is obvious (well, not so if I think about PLSQL with PL/SQL AUTHID CURRENT_USER|DEFINER ).
120 is the USER_ID of user BERX.
I have no idea what the 3rd parameter of psdnop is - any suggestion is very welcome!
The last entry is the host & port I try to connect to.
For me it's the summary what will be checked against ACLs.
The last line of this first block is psdnop: denied. Even in the 2nd block it's granted, I still
The second line in this block is psdnopCheckAcl: priv 2147483693 cnt 1. It checks if an ACL is granted. But which one?
It's first parameter tells us it's a priviliege the number is 2147483693. Unfortunately it's not written in the tracefile, so we need to identify this privilege.
It's first parameter tells us it's a priviliege the number is 2147483693. Unfortunately it's not written in the tracefile, so we need to identify this privilege.
SQL> select name, id from sys.xs$obj where id = 2147483693 ;
NAME ID
_______ _____________
HTTP 2147483693
The last 2 parameters of psdnopCheckAcl are unclear: cnt 1 - I have no explanation.
In case you don't want to query sys.xs$obj for a given privilege, have a peek in $ORACLE_HOME/rdbms/admin/catts.sql. The 3 important parts there (for privilege HTTP) are
RESERVED_ID NUMBER := 2147483648;
...
PRIV_HTTP NUMBER := RESERVED_ID + 45;
...
-- Create http privilege
insert into xs$obj(name, owner, tenant, id, type, status, flags)
values('HTTP', DEFAULT_OWNER, DEFAULT_TENANT,
PRIV_HTTP,4,1,1);
Next line is psdnopCheckAcl: aclid 2147493757 status 3. Again a check, just for an aclid now. Also this one can be identified, in our case:
SQL> select * from nacl$_HOST where acl#=2147493757;
HOST LOWER_PORT UPPER_PORT ACL#
_________________ _____________ _____________ _____________
www.google.com 80 443 2147493757
To crosscheck on the second (granted) block:
SQL> select name, id from sys.xs$obj where id = 2147483690 ;
NAME ID
__________ _____________
CONNECT 2147483690
This is by far not a comprehensive introduction into ACL traces, but at least it might help to identify, WHAT is missing when hitting ORA-24247.
