ORA-24247: network access denied by access control list (ACL) tips Oracle Error Tips by Donald Burleson

Question:  I am having an issue in Apex 3.1;  My application is set up to generate an email whenever the Assigned_To field is changed.

This is working fine and it is reflected in APEX ADMIN mail queue.   When I press "Send all Mail" in ADMIN I get the message "Mail sent Successfully" at the top of the screen BUT ORA-24247: network access denied by access control list (ACL) appears next to the email and it is never sent.

I have tried to grant network privileges based on the APEX installation manual but the PL/SQL coding generates an error message.

Answer:  Also note these details on building an ACL list. 

  select * from dba_network_acls;

The Oracle docs note the following about ORA-24247:

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

Cause: No access control list (ACL) has been assigned to the target host or the privilege necessary to access the target host has not been granted to the user in the access control list.

Action: Ensure that an access control list (ACL) has been assigned to the target host and the privilege necessary to access the target host has been granted to the user.

Your application will encounter an ORA-24247 error if it relies on one of the network packages and no proper ACL has been created. For the use of the following packages it is mandatory to have an ACL for the application user in place in 11g:

UTL_TCP

UTL_SMTP

UTL_MAIL

UTL_HTTP

UTL_INADDR

Oracle Network gives an example of ORA-24247 when using various security measures.

In this example, you are specifying that "the user SCOTT can call only the host www.proligence.com and only for the ports 22 through 55, and not outside it." Now, let's try it:

SQL> grant execute on utl_http to scott
  2  /
 
Grant succeeded.
 
SQL> conn scott/tiger

Connected.

SQL> select utl_http.request('http://www.pro.com') from dual;
select utl_http.request('http://www.pro.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

Note the error "ORA-24247: network access denied by access control list (ACL)." The user called the http server on port 80, which is outside the allowed range 22-55. Therefore the action was prevented.