Configure Oracle 11.2+ ACL to work with mail server having an internal IP address

oracleoracle-11g-r2

I configured a new Oracle 11.2.0.4 database on CentOS 7.

I verified that I can send email from the database server 192.168.0.1's linux command line, using an 192.168.0.2 mail server (e.g. mail.example.com) on port 26.

The /etc/hosts file on the database server (192.168.0.1) contains the line 

192.168.0.2 host9.example.com mail.example.com

so the database server can map mail.example.com to 192.168.0.2.

I'm trying to send mail using the following command in SQLDeveloper worksheet for schema1,

exec utl_mail.send(sender => 'name@example.com', recipients => 'name@example.com', subject => 'My Title', message => 'my body');

and it works. Then I use the same command minus the exec in a function called by a stored procedure, both in schema1, and it gives error

stack trace: java.sql.SQLException: ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 679
ORA-06512: at "SCHEMA1.MY_FUNC", line 260
ORA-06512: at "SCHEMA1.MY_PROC", line 40
ORA-06512: at line 1

My configuration is as follows. If I select * from dba_network_acls; as SYS I get:

192.168.0.2 26  26  /sys/acls/http_permissions.xml 3948232AFFA4833B3938204804923F39
192.168.0.2 443 443 /sys/acls/http_permissions.xml 3948232AFFA4833B3938204804923F39
192.168.0.2 80  80  /sys/acls/http_permissions.xml 3948232AFFA4833B3938204804923F39
192.168.0.2 25  25  /sys/acls/http_permissions.xml 3948232AFFA4833B3938204804923F39

If I SELECT acl, principal, privilege, is_grant, TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date, TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date FROM dba_network_acl_privileges; as SYS I get:

/sys/acls/http_permissions.xml  SCHEMA1 connect true    13-JAN-2017 
/sys/acls/http_permissions.xml  SCHEMA1 resolve true    13-JAN-2017

If I select * from V$PARAMETER where NAME = 'smtp_out_server' as SYS I get;

3314 smtp_out_server 2 mail.example.com mail.example.com FALSE TRUE IMMEDIATE TRUE FALSE FALSE FALSE FALSE utl_smtp server and port configuration parameter 3948483925

If instead I use a proven mail package from 11.2.0.1 to send mail via UTL_SMTP in schema1, it fails with ORA-24247: network access denied by access control list (ACL) from both SQLDeveloper and function SCHEMA1.MY_FUNC. It fails on this line:

mail_conn := utl_smtp.open_connection('mail.example.com', 26);

Any idea what could be wrong?

UPDATE

If I replace mail.example.com with 192.168.0.2 when setting smtp_out_server so that select * from V$PARAMETER where NAME = 'smtp_out_server' returns

3314 smtp_out_server 2 192.168.0.2 192.168.0.2 FALSE TRUE IMMEDIATE TRUE FALSE FALSE FALSE FALSE utl_smtp server and port configuration parameter 3948483925

then the UTL_MAIL.SEND command from both SQLDeveloper worksheet and function give the error ORA-29279: SMTP permanent error: 550 Access denied - Invalid HELO name (See RFC2821 4.1.3).

Best Answer

Here's what I finally (!) got to work. Simply set smtp_out_server equal to mail.example.com, then add mail.example.com to the ACL using, from SQLDeveloper:

-- leave <lower|upper>_port null to allow access to all ports
exec dbms_network_acl_admin.assign_acl (acl => 'http_permissions.xml', host => '*.example.com'); 
commit;

Notice above that I used *.example.com instead of mail.example.com because doing it this way also allows UTL_HTTP requests to www.example.com (kill 2 birds with one stone). Also, by not specifying upper and lower port bounds, we enable access for all ports. If you want, you can specify the exact ports as done in the original posting above.

Here's the UTL_SMTP related code:

g_mail_conn := UTL_SMTP.OPEN_CONNECTION('mail.example.com', 26);
UTL_SMTP.EHLO(g_mail_conn, 'mail.example.com');
UTL_SMTP.MAIL(g_mail_conn, p_sender_email);
...

Note that I'm using port 26 for SMTP, whereas most of the world uses port 25 (so just update your code accordingly). Also, there's no authentication (e.g. username/password) for the non-SSL port (26; no need, since I'm using an internal IP address).

The UTL_MAIL command also works from SQLDeveloper:

exec utl_mail.send(sender => 'name1@example.com', recipients => 'name2@example.com', message => 'Hi there');
Related Question