This content originally appeared on DEV Community and was authored by Leandro Nuñez
Introduction
This article is based on real problems I had in a production environment in Oracle 11g.
Oracle Database provides powerful networking capabilities, allowing PL/SQL programs to interact with external systems over the network. To enhance security, Oracle introduced Access Control Lists (ACLs) starting from Oracle Database 11g. ACLs enable fine-grained control over network access, ensuring that only authorized users and programs can communicate with specified external hosts and services.
This article provides a comprehensive guide to understanding ACL capabilities in Oracle, how to configure them, and how to send emails using the UTL_MAIL package. We will walk through the steps involved, incorporating practical examples and troubleshooting tips based on common issues encountered.
Table of Contents
- Understanding Oracle ACL Capabilities
-
Configuring ACLs for Network Access
- 2.1 Prerequisites
- 2.2 Creating an ACL
- 2.3 Assigning the ACL to a Host
- 2.4 Granting Privileges to Users
- 2.5 Verifying ACL Configuration
-
Sending Emails Using UTL_MAIL
- 3.1 Enabling UTL_MAIL
- 3.2 Sending a Simple Email
- 3.3 Sending Emails with Attachments
-
Troubleshooting Common Issues
- 4.1 ORA-24247: Network Access Denied by ACL
- 4.2 Function-Based Index Disabled
- 4.3 Issues with Cron Jobs and Background Processes
- Conclusion
1. Understanding Oracle ACL Capabilities
Access Control Lists (ACLs) in Oracle Database are a security feature that controls network access for database users and roles. ACLs are stored as XML files in the Oracle XML DB repository and are managed using the DBMS_NETWORK_ACL_ADMIN and DBMS_NETWORK_ACL_UTILITY packages.
Key Features of ACLs:
- Fine-Grained Control: Specify which users or roles can connect to specific network hosts and ports.
-
Privilege Types: Control privileges like
connect(TCP connections) andresolve(DNS name resolution). - Security Enhancement: Prevent unauthorized network access from PL/SQL code, reducing the risk of external attacks.
2. Configuring ACLs for Network Access
To send emails using PL/SQL packages like UTL_MAIL, you need to configure ACLs to allow network access to your SMTP server. Below are the detailed steps to set up ACLs.
2.1 Prerequisites
-
Administrative Privileges: You need to execute these steps as a user with administrative privileges (e.g.,
SYSDBA). -
SMTP Server Details:
-
Host: The hostname or IP address of your SMTP server (e.g.,
smtp.example.com). -
Port: The port number used by the SMTP server (commonly
25,465, or587).
-
Host: The hostname or IP address of your SMTP server (e.g.,
-
Database User: The database user that will execute the
UTL_MAILpackage (e.g.,APP_USER).
2.2 Creating an ACL
Use the DBMS_NETWORK_ACL_ADMIN.CREATE_ACL procedure to create a new ACL.
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl(
acl => 'utl_mail_acl.xml',
description => 'ACL for UTL_MAIL to access SMTP server',
principal => 'APP_USER', -- Replace with your database username
is_grant => TRUE,
privilege => 'connect',
position => NULL
);
END;
/
Explanation:
-
acl: The name of the ACL file. -
description: A brief description of the ACL's purpose. -
principal: The database user or role to which the privilege is granted. -
is_grant: Set toTRUEto grant the privilege. -
privilege: The network privilege (connectorresolve). -
position: Specifies the order of the ACE (Access Control Entry) in the ACL.
2.3 Assigning the ACL to a Host
Associate the ACL with your SMTP server's host and port using DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL.
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl(
acl => 'utl_mail_acl.xml',
host => 'smtp.example.com', -- Replace with your SMTP server host
lower_port => 25, -- Replace with your SMTP server port
upper_port => 25
);
END;
/
Note: If you want to allow access to all ports, you can omit lower_port and upper_port.
2.4 Granting Privileges to Users
If you need to grant additional privileges (e.g., resolve for DNS resolution), use DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE.
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege(
acl => 'utl_mail_acl.xml',
principal => 'APP_USER', -- Replace with your database username
is_grant => TRUE,
privilege => 'resolve',
position => NULL
);
END;
/
2.5 Verifying ACL Configuration
Commit the Changes:
COMMIT;
Verify the ACL Assignments:
SELECT acl, host, lower_port, upper_port
FROM dba_network_acls
WHERE acl = '/sys/acls/utl_mail_acl.xml';
Verify the Privileges:
SELECT acl, principal, privilege, is_grant
FROM dba_network_acl_privileges
WHERE acl = '/sys/acls/utl_mail_acl.xml';
3. Sending Emails Using UTL_MAIL
The UTL_MAIL package simplifies sending emails from PL/SQL code. It is a wrapper around UTL_SMTP and provides a higher-level API.
3.1 Enabling UTL_MAIL
Before you can use UTL_MAIL, you need to ensure it's installed and configured.
1. Install UTL_MAIL (if not already installed):
Run the utlmail.sql script as the SYS user:
@$ORACLE_HOME/rdbms/admin/utlmail.sql
2. Grant Execute Privilege:
GRANT EXECUTE ON UTL_MAIL TO APP_USER;
3. Set SMTP_OUT_SERVER Parameter:
Set the SMTP_OUT_SERVER parameter to your SMTP server:
ALTER SYSTEM SET SMTP_OUT_SERVER='smtp.example.com:25' SCOPE=SPFILE;
Alternatively, set it in your init.ora or spfile.
4. Restart the Database (if necessary):
If you changed the SMTP_OUT_SERVER in the SPFILE, you might need to restart the database for the change to take effect.
3.2 Sending a Simple Email
Here's how to send a simple email using UTL_MAIL.
BEGIN
UTL_MAIL.send(
sender => 'sender@example.com',
recipients => 'recipient@example.com',
subject => 'Test Email',
message => 'Hello, this is a test email sent using UTL_MAIL.'
);
DBMS_OUTPUT.put_line('Email sent successfully.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Failed to send email: ' || SQLERRM);
END;
/
Explanation:
-
sender: The email address of the sender. -
recipients: The email address of the recipient. You can specify multiple recipients separated by commas. -
subject: The subject line of the email. -
message: The body of the email.
3.3 Sending Emails with Attachments
To send emails with attachments, use the UTL_MAIL.send_attach_varchar2 procedure.
BEGIN
UTL_MAIL.send_attach_varchar2(
sender => 'sender@example.com',
recipients => 'recipient@example.com',
subject => 'Email with Attachment',
message => 'Please find the attached file.',
attachment => 'This is the content of the attachment.',
att_filename => 'attachment.txt',
mime_type => 'text/plain; charset=UTF-8'
);
DBMS_OUTPUT.put_line('Email with attachment sent successfully.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Failed to send email with attachment: ' || SQLERRM);
END;
/
Explanation:
-
attachment: The content of the attachment. -
att_filename: The name of the attachment file. -
mime_type: The MIME type of the attachment (e.g.,text/plainfor plain text files).
4. Troubleshooting Common Issues
4.1 ORA-24247: Network Access Denied by ACL
Error Message:
ORA-24247: network access denied by access control list (ACL)
Cause:
This error occurs when the database user lacks the necessary privileges in the ACL to access the specified network host or port.
Solution:
- Verify ACL Assignments:
SELECT acl, host, lower_port, upper_port
FROM dba_network_acls
WHERE host = 'smtp.example.com';
- Ensure User Has Privileges:
SELECT acl, principal, privilege, is_grant
FROM dba_network_acl_privileges
WHERE principal = 'APP_USER';
- Grant Necessary Privileges:
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege(
acl => 'utl_mail_acl.xml',
principal => 'APP_USER',
is_grant => TRUE,
privilege => 'connect'
);
END;
/
COMMIT;
4.2 Function-Based Index Disabled
Error Message:
ORA-30554: function-based index XDB.XDB$ACL_XIDX is disabled
Cause:
The function-based index XDB.XDB$ACL_XIDX is disabled, which is essential for ACL operations.
Solution:
- Rebuild the Index:
ALTER INDEX XDB.XDB$ACL_XIDX REBUILD;
- Verify the Index Status:
SELECT index_name, status
FROM dba_indexes
WHERE owner = 'XDB' AND index_name = 'XDB$ACL_XIDX';
4.3 Issues with Cron Jobs and Background Processes
When executing PL/SQL code via cron jobs or background processes, you might encounter ACL errors even if the code runs fine interactively.
Possible Causes:
- Different Database User: The cron job might use a different database user without the necessary ACL privileges.
-
Environment Variables: The cron job environment may lack required environment variables like
ORACLE_HOMEandORACLE_SID. - Definer's vs. Invoker's Rights: The stored procedure's execution rights might affect privilege recognition.
Solutions:
-
Ensure the Correct User is Used:
- Check the database user specified in the cron job script.
- Grant the necessary privileges to that user.
Set Environment Variables in the Script:
#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus -s username/password@database <<EOF
-- PL/SQL code here
EOF
- Modify Procedure to Use Invoker's Rights:
CREATE OR REPLACE PROCEDURE your_procedure_name
AUTHID CURRENT_USER
AS
-- Procedure code here
END;
/
5. Conclusion
Configuring Access Control Lists (ACLs) in Oracle Database is essential for securing network operations performed by PL/SQL code. By carefully setting up ACLs, you can control which users have access to external network services like SMTP servers for sending emails.
This article provided a detailed walkthrough of ACL capabilities, how to configure them, and how to send emails using the UTL_MAIL package. We've covered common issues and their solutions to help you troubleshoot problems you might encounter.
Key Takeaways:
- Understand ACLs: Know how ACLs enhance security by controlling network access.
- Configure ACLs Properly: Assign ACLs to the correct hosts and grant necessary privileges to users.
-
Use UTL_MAIL for Email: Simplify email sending in PL/SQL with
UTL_MAIL. - Troubleshoot Effectively: Be prepared to diagnose and fix common issues related to ACLs and email sending.
References:
- Oracle Database Security Guide: Configuring and Administering Network ACLs
- Oracle Database PL/SQL Packages and Types Reference: DBMS_NETWORK_ACL_ADMIN
- Oracle Database PL/SQL Packages and Types Reference: UTL_MAIL
Stay connected
If you enjoyed this article, feel free to connect with me on various platforms:
Your feedback and questions are always welcome.
If you like, you can support my work here:
This content originally appeared on DEV Community and was authored by Leandro Nuñez
Leandro Nuñez | Sciencx (2024-09-25T23:45:27+00:00) Understanding and Configuring Oracle Access Control Lists (ACLs) for Email Sending Using UTL_MAIL. Retrieved from https://www.scien.cx/2024/09/25/understanding-and-configuring-oracle-access-control-lists-acls-for-email-sending-using-utl_mail/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.