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

==============
Issue
==============

When calling    UTL_SMTP.open_connection(mailhost, 25), 

There is the error:

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at "MANAGER.SEND_MAIL", line 16
ORA-06512: at line 3
View program sources of error stack?

==============
Solution
==============
Before Oracle 11g access to network services was controlled by granting privileges on packages such as UTL_HTTP, UTL_TCP, UTL_SMTP, and UTL_MAIL.

GRANT EXECUTE ON UTL_HTTP TO MANAGER;
GRANT EXECUTE ON UTL_TCP TO MANAGER;
GRANT EXECUTE ON UTL_SMTP TO MANAGER;
GRANT EXECUTE ON UTL_MAIL TO MANAGER;

After 11.1 Oracle introduced Application Control Lists (ACL) as part of their Application Security and has now added Application Control Entry (ACE).
If you run into the ORA-24247: network access denied by access control list (ACL) error you can use DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE procedure to resolve the issue.

One has to create an ACE using the DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE procedure to grant access control privileges to a user. 

The procedure will append an access control entry with specified privilege to the ACL for the given host. 

If the ACL does not exist it will create it. 

The syntax:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
  host => '10.20.30.40',
  LOWER_PORT => NULL,
  UPPER_PORT => NULL,
  ACE => XS$ACE_TYPE(PRIVILEGE_LIST => xs$name_list('smtp'),
                       PRINCIPAL_NAME => 'MANAGER',
                       principal_type => xs_acl.ptype_db)
 );
 END;
 /

=============

Oracle 11 issue

=============

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

 
In oracle before 12.1, the above would fails with error  

ORA-06550: line 6, column 10:
PLS-00201: identifier 'XS$ACE_TYPE' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
   
Cause:
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE procedure is introduced starting 12c database. 
In 11.2.0.4 database it does not exists and it will not work.

Need to use the following syntax for creating ACL in 11.2.0.4 database

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL
 (
 acl => 'manager.xml',
 description => 'Mail Usage',
 principal => 'MANAGER',
 is_grant => TRUE,
privilege => 'connect');
END;
/
commit;
BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( 
   acl => 'manager.xml',
   host => '10.20.30.40');
  commit;
END;
/

=============

DBA_XXX tables

=============

SELECT * FROM DBA_NETWORK_ACLS;

HOST           LOWER_PORT UPPER_PORT ACL                    ACLID

-------------- ---------- ---------- ---------------------- ------------------------

10.20.20.200                         /sys/acls/manager.xml  EF39990944A18542E0533456

10.20.20.200 - is the IP of the server which hosts oracle service.

SELECT acl, aclid, principal, privilege FROM DBA_NETWORK_ACL_PRIVILEGES;

ACL                    ACLID                    PRINCIPAL            PRIVILEGE

---------------------- ------------------------ -------------------- ----------

/sys/acls/manager.xml  EF39990944A18542E0533456 MANAGER              connect

=============

General

=============

How to check if smtp host is open to connection

in case of failure to connect to smtp server:

ORA-20000: ORA-29278: SMTP transient error: 421 Service not available
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "MANAGER.SEND_MAIL", line 39
ORA-06512: at line 3

What to check:

check snmp service is running
/etc/rc.d/init.d/snmpd status
snmpd (pid 2099) is running...

check snmp server is reachable and correct
in case no connection:
snmpwalk -Os -c public -v 2c 10.20.30.44 iso.3.6.1.2.1.1.1
Timeout: No Response from 10.20.30.44

telnet 10.20.30.40 25
Trying 10.20.30.40...
Connected to 10.20.30.40.
Escape character is '^]'.
220 GER-IT-EX16-01.mydomain.local Microsoft ESMTP MAIL Service ready at Wed, 7 Dec 2022 09:38:24 +0100