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;

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...