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 2The 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:
- In host, we specify the name or IP address of the server we need to access.
- In lower_port, we indicate the TCP port where our requests will be handled.
- In ace, we specify the privilege to be granted, who gets it, and the type of user.
- 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.
- In principal_name, we specify the database user, which in our case will be the ADMIN schema.
- 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
- 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
- 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- 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.1 | How To Set Network ACLs in Oracle To Access Packages UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, UTL_INADDR |
| 1209644.1 | ORA-24247 Using UTL_TCP, UTL_HTTP, UTL_SMTP And UTL_MAIL With 11gR1 Or Later |