ACL for Sending e-Mail with APEX Oracle 11.2

Question

I'm trying to send e-Mail as a simple Send e-Mail process with Oracle APEX 11.2, and I can't figure out how the correct ACL has to be set. I tried this, but no success, still getting: ORA-24247: network access denied by access control list (ACL). What am I doing wrong? SELECT * FROM dba_network_acl_privileges shows that all rules are granted and also select * from dba_network_acls shows both rules. Thanks in Advance!

BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL (acl => 'send_mail.xml' );
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('send_mail.xml','Allow mail to be send', 'APEX_050100', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('send_mail.xml','APEX_050100',TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('send_mail.xml','APEX_050100',TRUE, 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (acl => 'send_mail.xml',host => '*',lower_port => null, upper_port => null);
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (acl => 'send_mail.xml',host => 'smtp.google.com',lower_port => null, upper_port => null);
commit;
END;


BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL (acl => 'utl_smtp.xml' );
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('utl_smtp.xml','Allow mail to be send', 'APEX_050100', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('utl_smtp.xml','APEX_050100',TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('utl_smtp.xml','APEX_050100',TRUE, 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (acl => 'utl_smtp.xml',host => '*',lower_port => null, upper_port => null);
END;

Answer 1

This is how I do it.

-- Drop ACL ====================================================================

BEGIN
   DBMS_NETWORK_ACL_ADMIN.drop_acl (acl => 'mydba.xml');
END;
/

-- Create ACL ==================================================================

BEGIN
   DBMS_NETWORK_ACL_ADMIN.create_acl (
      acl          => 'mydba.xml',
      description  => 'SMTP, MAIL, HTTP Access',
      principal    => 'LITTLEFOOT',
      is_grant     => TRUE,
      privilege    => 'connect',
      start_date   => NULL,
      end_date     => NULL);
END;
/

-- Assign ACL ==================================================================

BEGIN
   DBMS_NETWORK_ACL_ADMIN.assign_acl (acl         => 'mydba.xml',
                                      HOST        => '*',
                                      lower_port  => NULL,
                                      upper_port  => NULL);
END;
/

-- Add privilege ===============================================================

BEGIN
   -- LITTLEFOOT
   DBMS_NETWORK_ACL_ADMIN.add_privilege (acl         => 'mydba.xml',
                                         principal   => 'LITTLEFOOT',
                                         is_grant    => TRUE,
                                         privilege   => 'connect',
                                         start_date  => NULL,
                                         end_date    => NULL);

   DBMS_NETWORK_ACL_ADMIN.add_privilege (acl         => 'mydba.xml',
                                         principal   => 'LITTLEFOOT',
                                         is_grant    => TRUE,
                                         privilege   => 'resolve',
                                         start_date  => NULL,
                                         end_date    => NULL);
END;
/

COMMIT;

When new users require privileges, I just copy/paste LITTLEFOOT's data from the "Add privilege" procedure and change principal's name (currently, there are dozen of users in my script).