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

I created a SOLR utility package in Oracle but was getting an HTTP request failed error during run time.

SQL> select solr_utl.get_response('SolrServer.traderonline.com','8080','CycleAds','select?indent=on&version=2.2&q=*%3A*&fq=&start=0&rows=5&fl=UNIQUE_ID&qt=&wt=json&explainOther=&hl.fl=&echoParams=none') from dual;


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

It turned out the network packages like UTL_HTTP, UTL_SMTP, etc are more restricted in Oracle 11g. Before, you can just grant execute on this packages and you're ready to roll. However, in 11g, it's a little more granular.

In order for a database user to use the network packages, they should be given access. To accomplish this, the dbms_network_acl_admin package should be used. Run this packages as the sysdba.

First, we need to create the Access Control List (ACL). In this example, we are going to use the UTL_HTTP network package.

begin
  dbms_network_acl_admin.create_acl(
    acl => 'utl_http.xml',
    description => 'enables http to be used',
    principal => 'USERNAME',
    is_grant => true,
    privilege => 'connect'
  );
end;
/

Then we need to assign the ACL.

begin
  dbms_network_acl_admin.assign_acl(
    acl => 'utl_http.xml',
    host => 'SolrServerHostName',
    lower_port => SolrServerPort
  );
end;
/

Finally, grant the privilege.

begin
  dbms_network_acl_admin.add_privilege(
    acl => 'utl_http.xml',
    principal => 'USERNAME',
    is_grant => true,
    privilege => 'connect'
  );
end;
/

After all have been granted, I tried to run the select statement again and finally got the following output:

{
  "responseHeader":{
    "status":0,
    "QTime":9},
  "response":{"numFound":145594,"start":0,"docs":[
      {
        "UNIQUE_ID":"98855261"},
      {
        "UNIQUE_ID":"98407600"},
      {
        "UNIQUE_ID":"99051763"},
      {
        "UNIQUE_ID":"98406901"},
      {
        "UNIQUE_ID":"98413603"}]
  }}

To drop the ACL, the following can be used:

begin
  dbms_network_acl_admin.drop_acl(acl=>'utl_http.xml');
end;
/

Each network package should have their own Access Control List.