TheLazy.Dev
Portfolio GitHub

Blog

oracle |database |tutorial |sql

Understanding and Configuring Oracle Access Control Lists (ACLs) for Email Sending Using UTL_MAIL

leandro_nnz

Leandro Nuñez

Posted on September 25, 2024

Understanding and Configuring Oracle Access Control Lists (ACLs) for Email Sending Using UTL_MAIL

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

  1. Understanding Oracle ACL Capabilities
  2. Configuring ACLs for Network Access
  3. Sending Emails Using UTL_MAIL
  4. Troubleshooting Common Issues
  5. 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) and resolve (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, or 587).
  • Database User: The database user that will execute the UTL_MAIL package (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;
/
Enter fullscreen mode Exit fullscreen mode

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 to TRUE to grant the privilege.
  • privilege: The network privilege (connect or resolve).
  • 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;
/
Enter fullscreen mode Exit fullscreen mode

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;
/
Enter fullscreen mode Exit fullscreen mode

2.5 Verifying ACL Configuration

Commit the Changes:

COMMIT;
Enter fullscreen mode Exit fullscreen mode

Verify the ACL Assignments:

SELECT acl, host, lower_port, upper_port
FROM   dba_network_acls
WHERE  acl = '/sys/acls/utl_mail_acl.xml';
Enter fullscreen mode Exit fullscreen mode

Verify the Privileges:

SELECT acl, principal, privilege, is_grant
FROM   dba_network_acl_privileges
WHERE  acl = '/sys/acls/utl_mail_acl.xml';
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

2. Grant Execute Privilege:

GRANT EXECUTE ON UTL_MAIL TO APP_USER;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
/
Enter fullscreen mode Exit fullscreen mode

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;
/
Enter fullscreen mode Exit fullscreen mode

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/plain for 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)
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode
  • Ensure User Has Privileges: 
  SELECT acl, principal, privilege, is_grant
  FROM   dba_network_acl_privileges
  WHERE  principal = 'APP_USER';
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode

4.2 Function-Based Index Disabled

Error Message:

ORA-30554: function-based index XDB.XDB$ACL_XIDX is disabled
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
  • Verify the Index Status: 
  SELECT index_name, status
  FROM   dba_indexes
  WHERE  owner = 'XDB' AND index_name = 'XDB$ACL_XIDX';
Enter fullscreen mode Exit fullscreen mode

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_HOME and ORACLE_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
Enter fullscreen mode Exit fullscreen mode
  • Modify Procedure to Use Invoker's Rights: 
  CREATE OR REPLACE PROCEDURE your_procedure_name
  AUTHID CURRENT_USER
  AS
    -- Procedure code here
  END;
  /
Enter fullscreen mode Exit fullscreen mode

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:

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:

Buy me a coffee

💖 💪 🙅 🚩
leandro_nnz
Leandro Nuñez

Posted on September 25, 2024

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

Understanding and Configuring Oracle Access Control Lists (ACLs) for Email Sending Using UTL_MAIL
oracle Understanding and Configuring Oracle Access Control Lists (ACLs) for Email Sending Using UTL_MAIL

September 25, 2024