Remember your dreams

Remember your dreams
Remember your dreams

Thursday, October 14, 2010

ORA-24247: network access denied by access control list (ACL) - Enabling UTL_MAIL

I first came across this issue when migrating from version 10 to 11gR2. It continues to rear its ugly head even as I migrate to version 12.2.0.1 causing my my email alert package to no longer work.

I use a custom mail package to send email to users and alerts to the DBA. 

Executing returns the following error:
ORA-24247: network access denied by access control list (ACL).

What is this ACL business all about? It all started as a new feature in 11g and now just a feature in 12c. Oracle likes to call it Fine-Grain Access to External Network Services.

I am going to focus on my need to get the utl_mail.send procedure working again. This is how to resolve the issue and
more on enabling UTL_MAIL.

This original information I used to resolve this came from the 11gR2 documentation, but as I recently discovered still holds true in 12.2.0.1. For the 11gR2 documentation, including writing a custom mail package of your own, look to the following Oracle documentation.

Oracle® Database Security Guide
11g Release 2 (11.2)

Part Number E16543-02


If this link does not work for you,
Google for "Verifying Security Access with Auditing"

Search for "
Install and Configure the UTL_MAIL PL/SQL Package" within the document.
You may see an error like the following:

ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 2

or

ERROR at line 1:
ORA-24247: network access denied by

access control list (ACL)


Let's start by testing the utl_mail.send procedure.

SQL> begin

utl_mail.send(

sender => 'hackworthho@lilly.com',

recipients => 'hackworthho@lilly.com',

subject => 'Subject',

message => 'Test Message');

end;

/

utl_mail.send(

*

ERROR at line 2:

ORA-06550: line 2, column 1:

PLS-00201: identifier 'UTL_MAIL.SEND' must be declared

ORA-06550: line 2, column 1:

PL/SQL: Statement ignored

For starters the utl_mail package is not installed.
UTL_MAIL is not installed in by default 11g and 12c.
You must install it manually.

Install and Configure the UTL_MAIL PL/SQL Package
% sqlplus / as sysdba
SQL> @?/rdbms/admin/utlmail.sql

SQL> @?/rdbms/admin/prvtmail.plb

If are not going to be sys then be sure to grant execute to utl_mail package to the user you are going to be executing the mail send procedure as. In this example I'll use HOWARD and the user HOWARD will own the custom mail package. Replace user HOWARD with your desired user.
GRANT EXECUTE ON SYS.UTL_MAIL TO HOWARD;

Now determine your smtp outgoing mail server. Once you have this you need to configure the database parameter set smtp_out_server.
Set the SMTP_OUT_SERVER parameter
SQL> alter system set smtp_out_server = smtp server:port scope=both;

As in
SQL> alter system set smtp_out_server = 'smtp-server.com:25’ scope=both;

System altered.

Now you need to create and configure the ACL (Access Control List).
Configuring the ACL (Access Control List) File for Network Services.


Before you can use PL/SQL network utility packages such as UTL_MAIL, you must configure an access control list (ACL) file that enables fine-grained access to external network services.
To configure an access control list for the e-mail alert.

Start by creating the ACL
SQL> begin
dbms_network_acl_admin.create_acl (
acl => 'utl_mail.xml',
description => 'Enables mail to be sent',
principal => 'HOWARD',is_grant => true,
privilege => 'connect');
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

Assign the access control list to the outgoing SMTP network host for your e-mail server.

SQL> begin

dbms_network_acl_admin.assign_acl (

acl => 'utl_mail.xml',

host => 'your smtp-server.com',

lower_port => 25);

end;

/


PL/SQL procedure successfully completed.


SQL> commit;

Commit complete.


Finally grant permission to use it,
SQL> begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_mail.xml',
principal => 'HOWARD',
is_grant => TRUE,
privilege => 'connect');
end;
/

Let's try the utl_mail send package again and see if it works.


SQL> begin
utl_mail.send(
sender => 'hackworthho@lilly.com',
recipients => 'hackworthho@lilly.com',
subject => 'Subject',
message => 'Test Message from HOWARD');
end;
/
PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.
Looks like we are back in business and I did receive an email.


To Drop the ACL and start over issue the drop ACL command:
SQL> begin
DBMS_NETWORK_ACL_ADMIN.drop_acl(acl=>'utl_mail.xml');
end;
/
SQL> commit;

Commit complete.

Here are some useful queries you can use to verify the information on your database:

SELECT host,lower_port,upper_port,acl,DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(aclid, 'HOWARD', 'connect'),
1, 'GRANTED', 0, 'DENIED', null) PRIVILEGE FROM dba_network_acls
WHERE host IN (SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.domains('your smtp server.com')))
ORDER BY DBMS_NETWORK_ACL_UTILITY.domain_level(host) desc, lower_port, upper_port;

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;

select host, lower_port, upper_port, acl,DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid,null,'connect') GRANTED from dba_network_acls;

12 comments:

  1. Thanks,
    Simple solution no blablabla.
    Maurice

    ReplyDelete
  2. Thanks for the comment. I hope you got it to work for you. One of Oracle's many enhancements in security. LOL.
    ~ Howard

    ReplyDelete
  3. Good info, worked for me

    ReplyDelete
  4. Your solution works very well. Can a role be named instead of a user?

    ReplyDelete
  5. Works very well. Thank you. Can a role be named instead of a user?

    ReplyDelete
    Replies
    1. Allen, thanks for your feedback. I have not tried it with a role but am interested to know if it will work. I'll try it when I have time and update this post with the results. Thanks.

      Delete
  6. Man, you saved my day !

    ReplyDelete
  7. Hey I followed all the steps as per the portal. But still when i tried to execute the utl_mail.send , I am getting the below error

    Error starting at line 1 in command:
    begin
    UTL_MAIL.SEND(
    sender => 'Harsh.X.Shah@jpmorgan.com',
    recipients => 'Harsh.X.Shah@jpmorgan.com',
    subject => 'Subject',
    message => 'Test Message from HOWARD');
    END;
    Error report:
    ORA-24247: network access denied by access control list (ACL)
    ORA-06512: at "SYS.UTL_MAIL", line 654
    ORA-06512: at "SYS.UTL_MAIL", line 671
    ORA-06512: at line 2.

    Could you please help?

    ReplyDelete
  8. It looks like you have installed the UTL_MAIL package but you have not correctly set up the ACL. Run the queries at the end of this post to verify and delete the ACL and start over if necessary.

    ReplyDelete
  9. Nicely done. Simple, straight forward instructions and easy to follow. I am able to test it out without a glitch. Thanks.

    ReplyDelete
  10. I didn't see any error message while execute SEND. And, I commit it. But, I still didn't receive the email.

    What could be the problem?

    Thanks

    ReplyDelete
  11. If it does not error when you test from SQLPLUS then it is working. Check you spam or quarantine filter. If not there you can check the mail log on the database server itself. This should provide you information as to where the message is being caught up.

    ReplyDelete

Proactive Oracle DBA

This is a series of posts. I am working this to share some of the many scripts I schedule to automatically run to alert me of any current o...