Oracle 12c ORA-24247 network access denied by access control list (ACL) when using FTP

Question

I am working with Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production and trying to use the an ftp package (UTL_TCP). Here the procedure:

declare 
    l_conn UTL_TCP.connection;
    l_list ftp.t_string_table   := ftp.t_string_table(); 
begin
    l_conn := ftp.login('ftp.myhost.com', 21, 'user', 'password');
    ftp.nlst(p_conn => l_conn,p_dir  => 'outbox',p_list => l_list);
    ftp.logout(l_conn);
end;

The error is raised for ftp.nlst.

I configured the ACLs in this way:select * from dba_network_acls; returns

HOST           | LOWER_PORT | UPPER_PORT | ACL                   | ACLID            | ACL_OWNER
-----------------------------------------------------------------------------------------------
ftp.myhost.com |  null      | null       | /myaclspath/myacl.xml | 00000000800027C5 | SYS
*              |  null      | null       | NETWORK_ACL_5A1900... | 0000000080002724 | SYS

and select * from dba_network_acl_privileges returns

ACL                   | ACLID            | PRINCIPAL | PRIVILEGE | IS_GRANT | INVERT | START_DATE | END_DATE | ACL_OWNER
------------------------------------------------------------------------------------------------------------------------
/myaclspath/myacl.xml | 00000000800027C5 | MYDBUSER  | resolve   | true     | false  |            |          |SYS
/myaclspath/myacl.xml | 00000000800027C5 | MYDBUSER  | connect   | true     | false  |            |          |SYS

I read about a similar error here and check the setup of the ACL as here but I can't figure it out why the network access is denied after login.

Thanks in advance fr any suggestion.

Answer 1

Answering my own question for the benefit of others.

Reviewing the steps found here I inspect the ftp package and realized that each "command" send a PASV before execution. In this case the PASV change the connection established with login.

The login success because of the ACL for ftp.myhost.com but every following ftp command fail because it is not sent as ftp.myhost.com but as 111.222.333.444 that is the relative IP. Adding an ACL for the IP resolve the ORA-24247 error.

My solution is clearly mentioned here and it is my fault to not double check the right documentation but I would like to share here the issue.