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

This error message means that no access control list has been assigned to the host you (your application) are trying to access, or no required privileges have been granted to the user by adding user to the ACL. Following is an example executed on 12c (12.1.0.2) to show how we create ACL and how to test it.  

SQL> create user C##SALMAN identified by salman; User created. SQL> grant connect to c##salman; Grant succeeded. SQL> grant execute on utl_http to c##salman; Grant succeeded. SQL> conn c##salman/salman Connected. Declare V_req utl_http.req; V_resp utl_http.resp; Begin V_req:=utl_http.begin_request('http://192.231.231.1'); V_resp:=utl_http.get_response(v_req); Utl_http.end_response(v_resp); End; / ERROR at line 1: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1130 ORA-24247: network access denied by access control list (ACL) ORA-06512: at line 6

User C##SALMAN is trying to execute a package which relies on ACL, and since no ACL has been created and no required privilege have been granted to C##SALMAN user, ORA-24247 is being thrown.

In the following I will create an ACL and grant privilege to C##SALMAN and after that no error would be returned.

Login as SYSDBA and execute following code. First create ACL with the privilege assigned to user C##SALMAN (parameter “principal” is used to mention user or role to which privilege on the ACL is being assigned). After that, assign this ACL to the host or a list of hosts by providing hostname or IP of the host, or a range IP address of host to which ACL is being assigned.

BEGIN DBMS_NETWORK_ACL_ADMIN.create_acl ( acl => 'test_acl_file.xml', description => 'Testing ACL functionality', principal => 'C##SALMAN', is_grant => TRUE, privilege => 'connect', start_date => SYSTIMESTAMP, end_date => NULL); COMMIT; END; / PL/SQL procedure successfully completed. BEGIN DBMS_NETWORK_ACL_ADMIN.assign_acl ( acl => 'test_acl_file.xml', host => '192.*', lower_port => NULL, upper_port => NULL); COMMIT; END;  / PL/SQL procedure successfully completed. Query the information form data dictionary SQL> col acl format a50 SQL> col host format a50 SQL> set lines 200 SQL> col principal format a10 SQL> col privilege format a10 SQL> select acl,host from dba_network_acls; ACL                                                            HOST -------------------------------------------------- -------------------------------------------------- /sys/acls/test_acl_file.xml                           192.* SQL> select acl,principal,privilege,is_grant from dba_network_acl_privileges where ACL='/sys/acls/test_acl_file.xml'; ACL                                                PRINCIPAL              PRIVILEGE    IS_GR -------------------------------------------------- ---------- ---------- -------------------------- /sys/acls/test_acl_file.xml               C##SALMAN           connect             true

Now all hosts in range of IP address 192.* have been assigned this ACL. “host” parameter can also have a value of ‘*’ to assign ACL to all IP addresses, or can have a value like ‘192.168.231.12’ to assign ACL to a single host. “lower_port” and “upper_port” parameters in DBMS_NETWORK_ACL_ADMIN.assign_acl are used to restrict access to only a specific range of ports.

Now try executing UTL_HTTP again as user C##SALMAN

Declare V_req utl_http.req; V_resp utl_http.resp; Begin V_req:=utl_http.begin_request('http://192.231.231.1'); V_resp:=utl_http.get_response(v_req); Utl_http.end_response(v_resp); End; / PL/SQL procedure successfully completed.

Same ACL can be assigned to more hosts or a range of IP addresses.  

BEGIN DBMS_NETWORK_ACL_ADMIN.assign_acl ( acl => 'test_acl_file.xml', host => '172.29.38.*', lower_port => NULL, upper_port => NULL); COMMIT; END;  / PL/SQL procedure successfully completed. SQL>  select acl,host from dba_network_acls; ACL                                                         HOST -------------------------------------------------- -------------------------------------------------- /sys/acls/test_acl_file.xml                        172.29.38.* /sys/acls/test_acl_file.xml                        192.*

Adding more users to the ACL

SQL> create user C##TEST identified by test; User created. SQL> grant connect to C##TEST; Grant succeeded. SQL> grant execute on utl_http to C##TEST; Grant succeeded. BEGIN DBMS_NETWORK_ACL_ADMIN.add_privilege ( acl => '/sys/acls/test_acl_file.xml', principal => 'C##TEST', is_grant => TRUE, privilege => 'connect', position => NULL, start_date => SYSTIMESTAMP, end_date => NULL); COMMIT; END; / PL/SQL procedure successfully completed. SQL> select acl,principal,privilege,is_grant from dba_network_acl_privileges where ACL='/sys/acls/test_acl_file.xml'; ACL                                                PRINCIPAL              PRIVILEGE    IS_GR -------------------------------------------------- ---------- ---------- -------------------------- /sys/acls/test_acl_file.xml               C##SALMAN           connect             true /sys/acls/test_acl_file.xml               C##TEST                  connect             true