Site icon IT Tutorial

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

Hi,

I got ” ORA-24247: network access denied by access control list (ACL)  ” error in Oracle database.

 

 

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

 

Details of error are as follows.

SQL> select utl_http.request('https://www.ittutorial.org') 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: 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.

 

 

The ORA-24247: network access denied by access control list (ACL)  error is related with the missing of Access Control list.

 

You should grant the permission related ACL or create the new ACL.

 

 

network access denied by access control list (ACL)


Check your ACL if it is running properly or not. Give the related permissions for the users as follows.

 

grant execute on utl_http to username;
grant execute on utl_smtp to username;
grant execute on  utl_tcp to username;

 

You can create an ACL for Public Users as follows. You can create for any user by changing PUBLIC with Related user.

 

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => '/sys/acls/utl_http.xml',
description => 'Allow mail',
principal => 'PUBLIC',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/

 

 

 

 

You can add “Connect” privilige as follows.

BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => '/sys/acls/utl_http.xml',
principal => 'PUBLIC',
is_grant => true,
privilege => 'connect');
COMMIT;
END;
/

 

You can add “Resolve” privilige as follows.

BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => '/sys/acls/utl_http.xml',
principal => 'PUBLIC',
is_grant => true,
privilege => 'resolve');
COMMIT;
END;
/

 

You can Add Host and Port to assign network hosts to the ACL

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => '/sys/acls/utl_http.xml',
host => '192.168.63.63',
lower_port => 34,
upper_port => 63);
COMMIT;
END;
/

 

You can list all ACLS and their hosts and ports in the Oracle database with the following query.

 

SELECT * FROM DBA_NETWORK_ACLS;

 

[MSD1]/home/oracle $ sqlplus / as sysdba


SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jan 28 15:18:21 2020
Version 18.7.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.7.0.0.0

SQL> desc DBA_NETWORK_ACLS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 HOST                                      NOT NULL VARCHAR2(1000)
 LOWER_PORT                                         NUMBER(5)
 UPPER_PORT                                         NUMBER(5)
 ACL                                                VARCHAR2(4000)
 ACLID                                              RAW(8)
 ACL_OWNER                                          VARCHAR2(128)

 

 

 

You can list All ACL and their priviliges using the following query.

SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

 

 

SQL> desc DBA_NETWORK_ACL_PRIVILEGES;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ACL                                                VARCHAR2(4000)
 ACLID                                              RAW(8)
 PRINCIPAL                                          VARCHAR2(128)
 PRIVILEGE                                          VARCHAR2(128)
 IS_GRANT                                           VARCHAR2(5)
 INVERT                                             VARCHAR2(5)
 START_DATE                                         TIMESTAMP(6)
 END_DATE                                           TIMESTAMP(6)
 ACL_OWNER                                          VARCHAR2(128)


 

You can read the following post to learn more details about Oracle ACL ( Access Control List )

What is the Access Control List ( ACL ) and How to Create and Grant an ACL in Oracle Database

 

 

Do you want to learn Oracle SQL, then read the following articles.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course

 

Exit mobile version