When working with Oracle Database, sending emails directly from PL/SQL procedures using UTL_MAIL or UTL_SMTP is a common requirement. Developers often use these packages to send alerts, notifications, or automated reports from database applications.
However, many Oracle users encounter the following error when attempting to send emails:
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 679
ORA-06512: at line 2
If you have seen this error, don’t worry. It is a common issue in Oracle Database versions 11g and later. In this blog, we will explain what causes the ORA-24247 error, why Oracle introduced this restriction, and how to properly resolve it using Access Control Lists (ACLs).
Understanding the ORA-24247 Error
The ORA-24247 error occurs when a database user tries to access a network resource without proper permissions defined in Oracle’s Network Access Control List (ACL).
In earlier Oracle versions (before 11g), database users could access network resources directly using packages like:
UTL_MAILUTL_SMTPUTL_TCPUTL_HTTP
However, starting from Oracle 11g, Oracle introduced a security model that restricts network access from the database unless explicitly allowed through ACLs. This change prevents unauthorized network connections from within the database.
Therefore, when a user attempts to send an email using UTL_MAIL without the required ACL privileges, Oracle raises the ORA-24247 error.
Example Scenario That Causes the Error
Consider the following PL/SQL block that attempts to send an email:
BEGIN
UTL_MAIL.SEND(
sender => 'info@dbainsight.org',
recipients => 'info@dbainsight.org',
subject => 'MAIL from ADMIN of dbainsight',
message => 'Do visit dbainsight'
);
END;
/
When this block is executed without proper ACL configuration, Oracle returns the error:
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 679
ORA-06512: at line 2
This simply means that the database user does not have permission to connect to the SMTP server.
Why Oracle Uses ACL for Network Access
Oracle introduced the ACL mechanism to improve database security.
Without ACLs, any database user could potentially connect to external servers, which could lead to security risks such as:
- Unauthorized data transfers
- Access to external systems
- Network misuse from within the database
ACLs ensure that only authorized users and schemas can access specific network resources.
Step-by-Step Solution to Fix ORA-24247
To resolve this issue, you must configure the database to allow the user to access the SMTP server.
Step 1: Configure the SMTP Server
First, ensure the database knows which SMTP server should be used for sending emails.
Run the following command as a DBA:
ALTER SYSTEM SET smtp_out_server='mailhost.dbainsight.com' SCOPE=BOTH;
This parameter defines the SMTP server that Oracle will use for sending emails.
Step 2: Create an Access Control List (ACL)
Next, create an ACL that allows the user to access network resources.
For example, if the schema APPUSER needs permission to send emails, create the ACL as follows:
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => 'send_mail.xml',
description => 'Allow mail to be sent',
principal => 'USER',
is_grant => TRUE,
privilege => 'connect'
);
END;
/
This step creates an ACL file named send_mail.xml and grants the connect privilege to the user.
Step 3: Grant Additional Privileges
In most cases, the user also needs the resolve privilege to resolve hostnames.
Run the following commands:
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'send_mail.xml',
principal => 'USER',
is_grant => TRUE,
privilege => 'connect'
);
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'send_mail.xml',
principal => 'USER',
is_grant => TRUE,
privilege => 'resolve'
);
END;
/
These privileges allow the database user to connect to and resolve external hosts.
Step 4: Assign the ACL to the SMTP Host
Now assign the ACL to the SMTP host and port.
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'send_mail.xml',
host => '*',
lower_port => 25,
upper_port => 25
);
END;
/
Here:
*means the ACL applies to all hosts- Port 25 is the standard SMTP port
You may replace * with your specific mail server hostname for better security.
Step 5: Commit the Changes
Finally, commit the configuration:
COMMIT;
Testing the Fix
After configuring the ACL, connect using the application schema and try sending the email again.
CONNECT appuser
BEGIN
UTL_MAIL.SEND(
sender => 'info@dbainsight.org',
recipients => 'info@dbainsight.org',
subject => 'MAIL from ADMIN of learnomate',
message => 'Do visit learnomate'
);
END;
/
If everything is configured correctly, you should see:
PL/SQL procedure successfully completed.
This means the email has been sent successfully.
Best Practices for Oracle Network ACL Configuration
When configuring ACLs, consider the following best practices:
1. Avoid Using Wildcards When Possible
Instead of using '*' for the host, specify the exact SMTP server.
Example:
mailhost.dbainsight.com
This improves security by limiting access to a specific server.
2. Grant Only Required Privileges
Avoid granting unnecessary privileges. Typically, only these are required:
connectresolve
3. Use Dedicated ACL Files
Create separate ACL files for different purposes, such as:
- Email services
- HTTP web services
- External APIs
This keeps permissions organized and easier to manage.
4. Monitor ACL Configurations
Regularly check configured ACLs using:
SELECT * FROM DBA_NETWORK_ACLS;
SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;
These views help verify which users have network access.
Final Thoughts
The ORA-24247: Network Access Denied by Access Control List (ACL) error is a common issue when working with Oracle’s email packages like UTL_MAIL or UTL_SMTP. Although it may seem confusing at first, the error simply indicates that Oracle’s security mechanism is preventing unauthorized network access.
By properly configuring the SMTP server and granting the required privileges through Oracle Network ACLs, you can safely enable database users to send emails.
Understanding how ACLs work not only helps resolve this error but also ensures your Oracle database remains secure while interacting with external systems.
If you regularly build PL/SQL-based applications that require email notifications, setting up ACLs correctl
