Create and manage ACL

Oracle allows access to external network services using several PL/SQL APIs (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP andUTL_INADDR), all of which are implemented using the TCP protocol. Sometime when configuring jobs inside database to send emails from within the database, we get the following error.

ORA-24247: network access denied by access control list (ACL)

The reason being the user is not authorised by Oracle to send emails. In this case one should create ACL (Access Control List) and grant permission to access external network or send emails. Oracle 11g introduces fine grained access to network services using access control lists (ACL) in the XML DB repository, allowing control over which users access which network resources, regardless of package grants. So as a prerequisite XML DB component has to be installed on the database. 

The following code creates an ACL and grants permission to a user.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'email_access.xml', -- ACL file name
    description  => 'Email access to users', -- Description
    principal    => 'USER', -- username
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);
  COMMIT;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'email_access.xml',
    host        => '*', -- all hosts, you can specify which hosts can be accessed
    lower_port  => NULL,
    upper_port  => NULL); 

 COMMIT;
END;
/

Now after creating ACL and granting it to the user and assigning the hosts, the user can send emails without issue from inside the database.

To set the email server, the following code can be issued.

-- replace localhost with mail server name
execute dbms_scheduler.set_scheduler_attribute('email_server','localhost');

We can now check to confirm whether the user is able to send emails from the database by the following block. Connect as granted user and run this

DECLARE
   v_mailsever_host VARCHAR2(30) := 'localhost'; -- replace with mail server name
   v_mailsever_port PLS_INTEGER  := 25; -- 25 is default port
   l_mail_conn  UTL_SMTP.CONNECTION;
BEGIN
   l_mail_conn := UTL_SMTP.OPEN_CONNECTION( v_mailsever_host, v_mailsever_port);
END;
/

Now we see the emails are sent without any errors or issues.

If we need to add another user 'USER2' to ACL, the following can be issued in the database.

  exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('email_access.xml' ,'USER2', TRUE, 'connect');
  exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('email_access.xml' ,'USER2', TRUE, 'resolve');

Now users USER and USER2 will be able to send emails from inside the database using PL/SQL APIs listed above.

Happy working!