Tuesday, August 25, 2015

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




No comments:

Post a Comment

Popular Posts - All Times