Configure the Oracle DB Access Control List to avoid error like ORA-24247: network access denied

Symptoms: 

You try to make external connections from the Oracle Database and receive erros like

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

SQL> select utl_http.request('http://www.yourwebsite.com') from dual;

select utl_http.request('http://www.yourwebsite.com') from dual

*

ERROR at line 1:

ORA-29273: HTTP request failed

ORA-06512: at "SYS.UTL_HTTP", line 1577

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

ORA-06512: at line 1

Cause: 

The reason is that your database does not have permission to make external connections (through the internet - url or public IP).

So you have to configure explicity what hostname, website or public IP is allowed. Morover, you can be more specific on what port to open and what privilege to do so.

Solution:

First of all, you can make sure if there is any privilege for any database user. Empty resultset (that is default) means no privilege, you have to configure.

To ADD permission:

BEGIN

    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(

        host => '*',

--      lower_port => 80,

--      upper_port => 80,

        ace => xs$ace_type(privilege_list => xs$name_list('connect','resolve','http'),     -- HERE you can specify the privileges, the three of there are more than enough

                           principal_name => '<YOUR_DATABASE_USER>',           -- HERE you specify the database user that will make the external calls.

                           principal_type => xs_acl.ptype_db));

END;

/

COMMIT;

To REMOVE an existent privilege, in case you need to remove.

begin

dbms_network_acl_admin.remove_host_ace(

  host => '*',

  ace  =>  xs$ace_type(privilege_list => xs$name_list('http'),

                       principal_name => '<YOUR_DATABASE_USER>',

                       principal_type => xs_acl.ptype_db)); 

end;

/

Once added or removed, execute the query above to make sure what permissions and users are configured.

Hope that helps.