Using Email (utl_smtp) in Oracle 11g (results in ORA-24247) – Network Access Control List (ACL).

Using UTL_SMTP to send email from your oracle database has changed in Oracle 11g. Or – to be more precise – accessing the remote network has changed. …

Initializing a remote connection using UTL_SMTP will typically result in error below if you did not configure your database network access lists (ACL) for the user involved. …

-1. the error
=== …

SQL> DECLARE
  2    v_mailsever_host VARCHAR2(30) := 'mail.<domain>.nl';
  3    v_mailsever_port PLS_INTEGER  := 25;
  4    l_mail_conn  UTL_SMTP.CONNECTION;
  5  BEGIN
  6    l_mail_conn := UTL_SMTP.OPEN_CONNECTION( v_mailsever_host, v_mailsever_port);
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
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 line 6

SQL> sho user
USER is "SCOTT"
SQL>

“wat is dit nu weer !!!”

Allright – in Oracle 11g – you have to configure (grant) each and every network access point using so called Access Control Lists (ACL’s). Obviously user SCOTT is not configured to access the network point it’s trying to access here. …

The docs are here in ‘Managing Fine-Grained Access to External Network Services’ – //docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG40012 …

-0. check for current existing ACL’s
Let’s check out all currently presentAccess Control Lists in the database;
=== …

SQL> SELECT host, lower_port, upper_port, acl FROM dba_network_acls ;

no rows selected

SQL>

Allright – none present – let’s create a new network access list using DBMS_NETWORK_ACL_ADMIN …

— privilege – Use ‘connect’ for UTL_TCP, UTL_SMTP, UTL_MAIL and …

Read more →