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', |
|
<strong> lower_port => 25, |
|
</strong> 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
Thanks mate. Oracle docs are not that clear about this stuff
hello! thk u so much! this solution worked for me and spared me much frustration.. :-)
Thanks~~~
THank you. It worked like a charm.
Thanks You – this Article helped me alot and saved lot of time. the information was very helpful.
Perfect, thanks
thanks a lot working fine
Thank you, this was extremely helpful. Got me sending mail in just a few minutes.
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” :)
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
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
kovid,
did you solve your problem?
I’ve exactly the same error.
Best Regards
Not able to receive email if I am using the server
This will help lot. Thanks…
Really appreciate you sharing this article. Keep writing. Hausrath
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
I found this ACL issue, This article very helpful to solve my issue.
Thank you for your best article
Thank you so much for this article, clear and VERY helpful!