:

ORA-24247: Network Access Denied by Access Control List (ACL)

ORA-24247-access-control-list

The Problem

After upgrading one of our Oracle databases from 10g to 11g, and executing a package to test sending out email, I received the following error message:

BEGIN Mis_Pkg.prcTestEmail; END;
*
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 246
ORA-06512: at "SYS.UTL_SMTP", line 127
ORA-06512: at "SYS.UTL_SMTP", line 150
ORA-06512: at "MIS_PKG", line 1175
ORA-06512: at "MIS_PKG", line 1207
ORA-06512: at line 1

The Cause

Fine grained auditing, enhanced in Oracle 11g, means access to certain packages (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR) now require specific access lists to be defined for security reasons instead of granting this access to PUBLIC and allowing all users access to them.

A step in the right direction for security, but it just adds one more item to your list of jobs to maintain.

The Solution

In order to allow access to any of the above mentioned packages, you will need to explicitly grant it via Access Control Lists. Below is an example of how to create one, and assign the user USER1 the privilege to use the UTL_SMTP pacakge, and therefore send email.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'UTL_SMTP.xml',
    description  => 'ACL for utl_smtp package',
    principal    => 'USER1',
    is_grant     => TRUE,
    privilege    => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl         => 'UTL_SMTP.xml',
    host        => '<mail_server_ip>');
END;
/

COMMIT;

It should be noted that there are different options available within this configuration. For example, the privilege level can be changed from “connect” to “resolve”, or even granted in addition to. To get more details regarding which of these is more appropriate for your requirements, please read the Oracle ACL documentation, which has some must read information contained within it.

You may also be encountering the error ORA-29278: SMTP transient error: 421 Service not available, which I have more details notes in my other post.

Like it, share it...

Category: 11g


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *