Andrew Fraser DBA

Fix for ORA-24247 ACL calling Oracle Reports

Found this error on upgrade to 11gR2 trying to call Oracle Reports from within database:

ERROR at line 1:
ORA-20001: Error for submit_report. Report: ab_batch. Error -29273 ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "MYUSER.MYPROC", line 60
ORA-06512: at line 1

Fix was to add this ACL:

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'myreports.xml',
description => 'Andrew Fraser Feb-2013 for utl_http.request to reports',
principal => 'MYUSER', -- Must be in upper case
is_grant => TRUE,
privilege => 'connect');
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( -- Creates the first target host
acl => 'myreports.xml',
host => 'myserver.mysite.com');
END;
/

More info in Oracle Support Document 1392315.1 (How to Troubleshoot and Solve the ORA-24247 Error?).

Other issues to note with this:

1) For connections to https/ssl – oracle wallets also needs to be configured.

2) If you want to use utl_http, it is also necessary to include ‘resolve’ in addition to the above connect privilege:

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'myreports.xml',
principal => 'MYUSER',
is_grant => true,
privilege => 'resolve');

3) For external sites which are being accessed via a proxy, the proxy must also be included:

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'myreports.xml',
host => 'proxy_server.mysite.com');

4) Tracing is very useful in working out what needs to be added:

alter session set events='24247 trace name errorstack level 3';
alter session set events = '10937 trace name context forever, level 6';

5) You can see what is currently in place in a database using these two views:

select * from dba_network_acls ;
select * from dba_network_acl_privileges ;