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

When trying to access a remote XML file from a Oracle 11G Database you might end up with a ORA-24247 error message. The message will state something like "ORA-24247: network access denied by access control list (ACL)". For example I tried to execute the following:

SELECT HTTPURITYPE('http://rss.slashdot.org/Slashdot/slashdot').getxml() orana_feed from dual;

The result of this was the error message shown below:

Error starting at line 1 in command:
SELECT HTTPURITYPE('http://rss.slashdot.org/Slashdot/slashdot').getxml() orana_feed from dual
Error report:
SQL Error: ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1674
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.HTTPURITYPE", line 34
ORA-06512: at "SYS.HTTPURITYPE", line 97
29273. 00000 - "HTTP request failed"
*Cause: The UTL_HTTP package failed to execute the HTTP request.
*Action: Use get_detailed_sqlerrm to check the detailed error message.
Fix the error and retry the HTTP request.

Accoording to metalink this is due to the following: "A new security measure is introduced in Oracle 11g Release 1 (11.1) for the following network-related PL/SQL packages: UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL, and UTL_INADDR. The invoker of those packages needs additional privileges to connect to an external host or to resolve the name or the IP address of a host. The packages check the invoker for the necessary privileges only when the calls are made at runtime and raises an exception if the invoker lacks the privileges. This new security measure is implemented by the XML DB access control list (ACL) mechanism and, therefore, requires XML DB to be installed and Configure network Access Control Lists (ACLs) in the database before these packages can work as they were working in prior releases.".

So to prevent every user to be able to connect from within the database to any external source by executing PL/SQL code new security mechanisms are in place. For a user to be able to connect to a external source he has to be listed in a Access Control List as granted to access a certain external location. For me to be able to access the the RSS feed from rss.slasdot.com I have to be granted in a ACL to do so. Oracle created the mechanism in such a way that you, as a DBA, will be able to precisely tell which user will have access to what. You can create multiple ACL's and assign those to roles and/or users. You can also state on which ports a user can connect. This way you will have all the freedom to decide who can access what and this will prevent user in a Oracle 11G database to access remote sources.

The first step is to create a ACL, to do so we will make use of the dbms_network_acl_admin package and the create_acl function inside this package. As shown below I will create a Access Control List which I name rssslashdot.xml, as first principal I enter my own username and the privilege I get is to connect. I could have created a start and end date to limit the time I can use it. Lets say I can only use it in the first month of next year I would be able to set this by using the start_date and end_date.

dbms_network_acl_admin.create_acl (
acl => 'rssslashdot.xml',
description => 'rss feed location for slashdot',
principal => 'JLOUWERS',
is_grant => true,
privilege => 'connect',
start_date => null,
end_date => null
);

Only be able to connect is not enough, I also need to be able to resolve the hostname which I later will enter so part of the script has to be that I grant this user jlouwers a the resolve role. This can be done by using the add_privilege function in the dbms_network_acl_admin package:

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

Now all we have to do is enter the information about what I can access so at the host I enter rss.slashdot.org which will enable everyone (the user jlouwers in this case) who is granted to this ACL to connect to this host. I could also provide a port range in which I can operate. If you do not set anything you can use all ports but from a security perspective it could be good to limit this to a range of ports, this can be done by using the lower_port and upper_port variables.

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'rssslashdot.xml',
host => 'rss.slashdot.org');

So this are basilcy all the parts you need to create a ACL, if put all this in a single script you will get something like the script below:

BEGIN
dbms_network_acl_admin.create_acl (
acl => 'rssslashdot.xml',
description => 'rss feed location for slashdot',
principal => 'JLOUWERS',
is_grant => true,
privilege => 'connect',
start_date => null,
end_date => null
);

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

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'rssslashdot.xml',
host => 'rss.slashdot.org');

END;
/
COMMIT;


To get more information about how this new functions are working a great resource is the "Oracle Database PL/SQL Packages and Types Reference 11g Release 1 (11.1)" which you can find at the Oracle website. When you need to check which user can access what remote location the best way is to start looking in sys.dba_network_acl_privileges and sys.dba_network_acls