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

  • Published on Jan 30 2013
  • # 77,316
  • 11g/oracle

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 UTL_HTTP access. Use ‘resolve’ for UTL_INADDR name/IP resolution. The text for the privilege is case sensitive. A list of all available network access privileges is in the DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE reference.

-1. create new list
===
Here we create a new ACL list ‘acl_test_for_scott.xml’ with default initial grantee user SCOTT.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'acl_test_for_scott.xml', 
    description  => 'A test of the ACL functionality',
    principal    => 'SCOTT',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  COMMIT;
END;
/

Now check if we can find this new ACL in the data dictionary:

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

no rows selected

?? Why does my newly created ACL ‘acl_test_for_scott.xml’ fail to show up?

OK… explained here – https://forums.oracle.com/forums/thread.jspa?threadID=2208522#jive-message-10494920 – new ACL’s will only be visible once a at least one access points has been assigned to the ACL (imho this is a bug).

OK next is adding an access point to our ACL.

-2. add access point to the new ACL
Add a network access point to the ACL list;

BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl => 'acl_test_for_scott.xml',
    host => 'mail.<domain>.nl', 
    lower_port => 80,
    upper_port => NULL); 
END;
/

No sparky, mailservers usually listen on port 25…
OK, add another access point.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl => 'acl_test_for_scott.xml',
    host => 'mail.********.nl', 
    lower_port => 25,
    upper_port => NULL); 
  COMMIT;

-4. add another database user to the ACL list

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl => 'acl_test_for_scott.xml', 
    principal => 'PANNENKOEK', 
    is_grant => TRUE, 
    privilege => 'connect');
END;
/

-5. retrieve our new configuration from the dictionary

— query all access points (per ACL)

SQL> l
  1* select * from dba_network_acls
SQL> /

HOST       LOWER_PORT UPPER_PORT ACL
---------- ---------- ---------- ------------------------------
mail.**.nl         80         80 /sys/acls/acl_test_for_scott.xml
mail.**.nl         25         25 /sys/acls/acl_test_for_scott.xml

SQL>

— query ACL grants

SQL> l
  1  SELECT acl,
  2         principal,
  3         privilege,
  4         is_grant,
  5         TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
  6         TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
  7* FROM   dba_network_acl_privileges
SQL> /

ACL                                 PRINCIPAL  PRIVILE IS_GR START_DATE  END_DATE
------------------------------      ---------- ------- ----- ----------- -----------
/sys/acls/acl_test_for_scott.xml    SCOTT      connect true  30-JAN-2013
/sys/acls/acl_test_for_scott.xml    PANNENKOEK connect true  30-JAN-2013

Now test again if user SCOTT can create a TCP connection – succeeds;

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  /

PL/SQL procedure successfully completed.

I used the oracle-base.com send_mail procedure to quickly test: //www.oracle-base.com/articles/misc/email-from-oracle-plsql.php

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

  1. Dmitry says:

    Thanks mate. Oracle docs are not that clear about this stuff

  2. norfolktec says:

    hello! thk u so much! this solution worked for me and spared me much frustration.. :-)

  3. Aj says:

    THank you. It worked like a charm.

  4. kris says:

    Thanks You – this Article helped me alot and saved lot of time. the information was very helpful.

  5. pagreda says:

    Perfect, thanks

  6. Ahsin says:

    thanks a lot working fine

  7. Jon says:

    Thank you, this was extremely helpful. Got me sending mail in just a few minutes.

  8. Chris says:

    Thx so much, needed more time as Jon (“sending mail in just a few minutes”), but well, it worked in the end…
    Oracle is best in “reinventing the wheel” :)

  9. kovid says:

    Dear Sir,
    I am able to send mails indide my domain but not to outside domain like gmail,yahoo, 3ds.com etc
    please help
    It gives an error
    smtp permanent error :unable to relay 550 5.7.1 etc

  10. erika aguilera says:

    Hello my friend
    i have a problem with step five , I have created the procedure, but
    this line: UTL_SMTP.CONNECTION l_mail_conn ;
    it has error: PLS 00201 : identifier ‘ UTL_SMTP “must be declared
    i am sorry for my english

  11. tsousa says:

    kovid,

    did you solve your problem?

    I’ve exactly the same error.

    Best Regards

  12. betchay says:

    Not able to receive email if I am using the server

  13. Prasad says:

    This will help lot. Thanks…

  14. Big Craig says:

    Tears, tears of relief for the line
    select host, lower_port, acl from dba_network_acls;
    cause I couldn’t find where to get the ACL from

  15. J.Santichai says:

    I found this ACL issue, This article very helpful to solve my issue.
    Thank you for your best article

  16. shalala says:

    Thank you so much for this article, clear and VERY helpful!

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>