Access Control List for External Network Services (11g)

Starting with Oracle 11gR1 (11.1.0.6) so called "fine-grained access" was implemented to limit usage of packages like UTL_SMTP, UTL_HTTP connecting over the network to other services like mail server etc.

You need to configure the ACL (Access Control List) get the benefits from these packages in addition to have the execute privs on them.

Create ACL

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'test_acl_file.xml',
    description  => 'ACL functionality',
    principal    => 'SCOTT',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  COMMIT;
END;

acl=> Name of the Access Control List. This is a XML file which will be created in /sys/acls directory by default.

Description=> Description of the ACL.

Principal=> Name of the user or role (case sensitive) to whom the permissions are being granted or denied. You can use the ROLE (already created) fro principal

is_grant=> TRUE or FALSE, whether to grant access or deny access.

privilege=> connect or resolve (lowercase always). Will the user be able to connect to the network resource or just could resolve the network address.

start_date=> Start date (optional) of the access to the user.

end_date=> End date (optional) of the access to the user.

Assign a network host to Access Control List

BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'test_acl_file.xml',
    host        => 'integrationserverstg.domain.com',
    lower_port  => 80,
    upper_port  => NULL);
  COMMIT;
END;

you can use IP address or the patter ('*.oracle.com') for the host.

Remove a host from Access Control List

BEGIN
  DBMS_NETWORK_ACL_ADMIN.unassign_acl (
    acl         => 'test_acl_file.xml',
    host        => 'integrationserverstg.domain.com'); 
  COMMIT;
END;

Add a privilege to Access Control List

If any other user or role needs permission on the ACL you may user the procedure

ADD_PRIVILEGE. 

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl          => 'test_acl_file.xml',                
    principal    => 'SCOTT',
    is_grant     => TRUE, 
    privilege    => 'connect',
    position     => null);
   COMMIT;
END;

Delete a privilege from Access Control List

BEGIN
  DBMS_NETWORK_ACL_ADMIN.delete_privilege ( 
    acl         => 'test_acl_file.xml', 
    principal   => 'SCOTT',
    is_grant    => TRUE, 
    privilege   => 'connect');
  COMMIT;
END;

Drop an Access Control List

BEGIN
  DBMS_NETWORK_ACL_ADMIN.DROP_ACL ( 
    acl         => 'test_acl_file.xml');
  COMMIT;
END;

Views related to ACL

DBA_NETWORK_ACLS
DBA_NETWORK_ACL_PRIVILEGES
USER_NETWORK_ACL_PRIVILEGES 

ACL for mail Server

You can use the below to create the ACL for mail server.

create or replace procedure mailserver_acl(
       aacl       varchar2,
      acomment   varchar2,
      aprincipal varchar2,
      aisgrant   boolean,
      aprivilege varchar2,
      aserver    varchar2,
      aport      number)
    is
   begin  
     begin
       DBMS_NETWORK_ACL_ADMIN.DROP_ACL(aacl);
        dbms_output.put_line('ACL dropped.....'); 
     exception
       when others then
         dbms_output.put_line('Error dropping ACL: '||aacl);
         dbms_output.put_line(sqlerrm);
     end;
     begin
       DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,aprivilege);
       dbms_output.put_line('ACL created.....'); 
     exception
       when others then
         dbms_output.put_line('Error creating ACL: '||aacl);
         dbms_output.put_line(sqlerrm);
     end;  
     begin
       DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(aacl,aserver,aport);
       dbms_output.put_line('ACL assigned.....');         
     exception
       when others then
         dbms_output.put_line('Error assigning ACL: '||aacl);
         dbms_output.put_line(sqlerrm);
     end;   
     commit;
     dbms_output.put_line('ACL commited.....'); 
  end;
 

begin
      mailserver_acl(
        'mailserver_acl.xml',
        'ACL for used Email Server to connect',
        'HOME',
        TRUE,
        'connect',
        'mail.domain.com',
        25);
  end ;

MAIL SENDING TESTING
You can use the below to test the ACL.
DECLARE
  v_From      VARCHAR2(80) := 'inam@domain.com';
  v_Recipient VARCHAR2(80) := 'inam@domain.com';
  v_Subject   VARCHAR2(80) := 'test subject';
  v_Mail_Host VARCHAR2(30) := 'mail.domain.com';
  v_Mail_Conn utl_smtp.Connection;
  crlf        VARCHAR2(2)  := chr(13)||chr(10);
BEGIN
 v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
 utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
 utl_smtp.Mail(v_Mail_Conn, v_From);
 utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
 utl_smtp.Data(v_Mail_Conn,
   'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
   'From: '   || v_From || crlf ||
   'Subject: '|| v_Subject || crlf ||
   'To: '     || v_Recipient || crlf ||
   crlf ||
   'some message text'|| crlf || -- Message body
   'more message text'|| crlf
 );
 utl_smtp.Quit(v_mail_conn);
EXCEPTION
 WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
   raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;