ORA-24247: network access denied by access control list (ACL)

In this post I want to talk about a common error with Oracle 11g, especially 11.2.0.1.0. We were getting this error on all versions of our application after upgrading from 10.2 to 11.2 oracle database on Linux 5.4 64 bit.

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)

Details

This problem is bug 13375884 associated with 11g databases but corrected in latest patch sets. Don't panic! Just follow instructions.

cause

The problem is common to Oracle 11g because prior to 11gR1, you can run PL/SQL code making calls to Oracle Supplied Packages without error. These packages include:

•UTL_TCP
•UTL_HTTP
•UTL_SMTP
•UTL_MAIL

However, after upgrading to a 11g version, this error comes because of inaccessible external network privilege for users calling the packages.

In 11gR1 the Oracle Database enhanced the level of security when users attempt to access External Network Services by implementing Access Control Lists (ACL) using the new DBMS_NETWORK_ACL_ADMIN package. The PL/SQL packages listed above were affected. For more information on this change to Oracle Database Security please review Oracle documentation.


see Oracle documentation on Fine Grain access control at ACL.

Temporary fix

To resolve this issue temporarily, run this script to grant privilege to the user so that the user can access external network service.

BEGIN

-- Only uncomment the following line if ACL "network_services.xml" has already been created
--DBMS_NETWORK_ACL_ADMIN.DROP_ACL('network_services.xml');

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => 'network_services.xml',
description => 'NETWORK ACL',
principal => 'QUEEN',
is_grant => true,
privilege => 'connect');

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

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'network_services.xml',
host => '*');

COMMIT;

END;


permanent fix

Apply patch 11.2.0.3.0 or later to permanently resolve this network issue.

1. Download the patchset from Oracle Metalink. The detail is "Patch 10404530: 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER"

2. unzip the software

3. run Installer

4. Run DBUA to configure database to higher version

More on this later.