If there are problems with ACLs (Access Control Lists) in Oracle, the note
SRDC - How to Collect Standard Information for Access Control Lists (ACLs) (Doc ID 1905572.1) is an important starting point.
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';
But the only suggestion is to open a SR at oracle and send the results in.
Sometimes, it already helps to understand parts of the trace file to identify an issue - no need to invest precious time on MOS.
First the events itself. Their meaning are:
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.
and
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.
Unfortunately I did not find a comprehensive documentation of these events levels, maybe this is area for some other posts.
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
With no ACLS at all, it's obvious.
According tho the documentation of
UTL_HTTP:
This package is an invoker's rights package. The invoking user will need the connect privilege granted in the access control list assigned to the remote network host to which he wants to connect, as well as the use-client-certificates or the use-passwords privilege 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;
/
With this privilege, let's see what the ACL trace file says. (I have to set event 10937 to level
6 (not
7) in my lab env - lvl
7 did not generate anything).
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
According to the last line, everything is fine - access is
granted. But there is more!
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.
SQL> select name, id from sys.xs$obj where id = 2147483693 ;
NAME ID
_______ _____________
HTTP 2147483693
This means Oracle first checks if the HTTP privilege is present - as I did not grant it, it's not there.
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
Now it's clear why the first block is denied: for this host that privilege is just not granted.
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.
