Troubleshoot: ORA-24247 when trying to send an email

It may sound made up, but there are still installations running Oracle 10.2, and it was precisely in one of these, just upgraded to 19c, that a problem developed for which they wanted my assistance: a rarely used application, which sends an alert email as part of its steps, quit working.

Fortunately, the solution is quite simple, as we will see below. Keep reading.

The problem

Until Oracle Database 11gR1, you only needed to grant execute permissions to use the UTL_INADDR, UTL_SMTP, UTL_MAIL, UTL_TCP, and UTL_HTTP packages; however, now you must utilize a specific configuration known as an Access Control List (ACL).

Without this configuration, all interactions with external network services will be blocked, resulting in the error ORA-24247: network access denied by access control list (ACL).

BEGIN
  UTL_MAIL.send(
    sender     => 'admin@acme.com',
    recipients => 'larry@acme.com',
    cc         => 'scott@acme.com',
    bcc        => 'tom@acme.com',
    subject    => 'Warning!',
    message    => 'Oracle 23ai on-prem is still not released');
END;
/

BEGIN
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 432
ORA-06512: at "SYS.UTL_TCP", line 19
ORA-06512: at "SYS.UTL_TCP", line 295
ORA-06512: at "SYS.UTL_SMTP", line 164
ORA-06512: at "SYS.UTL_SMTP", line 201
ORA-06512: at "SYS.UTL_MAIL", line 427
ORA-06512: at "SYS.UTL_MAIL", line 679
ORA-06512: at line 2

The solution

To obtain full permissions to use the UTL_INADDR, UTL_SMTP, UTL_MAIL, UTL_TCP, and UTL_HTTP packages, you must now additionally grant permission through the use of the DBMS_NETWORK_ACL_ADMIN package, as follows:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'smtp.acme.com', 
    lower_port => 25,
    ace        => xs$ace_type( 
                    privilege_list => xs$name_list( 'smtp' ),
                    principal_name => 'ADMIN',
                    principal_type => xs_acl.ptype_db
                  )
  ); 
END;
/

Where:

  1. In host, we specify the name or IP address of the server we need to access.
  2. In lower_port, we indicate the TCP port where our requests will be handled.
  3. In ace, we specify the privilege to be granted, who gets it, and the type of user.
    1. In privilege_list, we might specify connect, but that would not only allow access to UTL_MAIL and UTL_SMTP, but also to UTL_TCP and UTL_HTTP, so it is recommended to specify smtp for better control.
    2. In principal_name, we specify the database user, which in our case will be the ADMIN schema.
    3. In principal_type, we will always indicate ptype_db, since we are configuring a database user.

 

Lastly, we make sure that the code is functioning normally again, a sign that the issue has been fixed.

BEGIN
  UTL_MAIL.send(
    sender     => 'admin@acme.com',
    recipients => 'larry@acme.com',
    cc         => 'scott@acme.com',
    bcc        => 'tom@acme.com',
    subject    => 'Advertencia!',
    message    => 'Oracle 23ai on-prem sigue sin liberarse');
END;
/

PL/SQL procedure successfully completed.

Additional tasks

  1. You can determine which users need extra configuration (ACL) by using this query.:
SELECT DISTINCT owner, referenced_name 
  FROM dba_dependencies
 WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP')
   AND owner NOT IN ('SYS','PUBLIC','GSMADMIN_INTERNAL','ORACLE_OCM')
 ORDER BY owner, referenced_name;
 
OWNER    REFERENCED_NAME
-------- ---------------
ADMIN    UTL_HTTP
ADMIN    UTL_MAIL
SCOTT    UTL_HTTP
SCOTT    UTL_SMTP
SCOTT    UTL_TCP
  1. You can check the privileges already granted by running the following query:
SELECT host,
       lower_port,
       upper_port,
       principal,
       principal_type,
       privilege
  FROM dba_host_aces
 WHERE host = 'smtp.acme.com';
 
HOST          LOWER_PORT UPPER_PORT PRINCIPAL PRINCIPAL_TYPE PRIVILEGE
------------- ---------- ---------- --------- -------------- ---------
smtp.acme.com         25         25 ADMIN     DATABASE       SMTP
  1. The following command can be used to revoke a privilege that has already been granted:
BEGIN
  DBMS_NETWORK_ACL_ADMIN.remove_host_ace (
    host       => 'smtp.acme.com', 
    lower_port => 25,
    ace        => xs$ace_type( 
                    privilege_list => xs$name_list( 'smtp' ),
                    principal_name => 'ADMIN',
                    principal_type => xs_acl.ptype_db
                  ),    
    remove_empty_acl => TRUE); 
END;
/

This is just one of many cases where ACL applies. To explore more situations, check out this article by Tim Hall. However, keep in mind that it focuses on the procedure used with Oracle 11gR1 and 11gR2, since the procedure has changed since Oracle 12cR1, as detailed in this updated article.

In addition to what has already been said, I recommend that you read the notes below:

958129.1How To Set Network ACLs in Oracle To Access Packages UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, UTL_INADDR
1209644.1ORA-24247 Using UTL_TCP, UTL_HTTP, UTL_SMTP And UTL_MAIL With 11gR1 Or Later

Recent Posts

MView - Current
If you find huge materialized view logs, follow these steps to diagnose the problem and purge their contents.
slow mview refresh
If materialized view fast refreshes are slow and you are using Oracle 19c, you can make a small change to optimize them.
Grid OOP patching - install golden image
Learn to patch Grid Infrastructure for Single Instance, with out-of-place mode and using a gold image.
oop patch grid siha - patch new OH
Learn how to patch Grid Infrastructure for Single Instance, aka Oracle Restart, using out-of-place patching.
Out of place patching Oracle RAC
Learn how to patch Oracle RAC database in out-of-place mode and use Gold Image for maximum simplicity and minimum effort.
Hide column contents
Learn how to implement the masking of column contents dynamically and without having to modify your applications.

Need Help?

Fill in these details and I will be in touch as soon as possible.