Mail from Application Express with Access Control Enabled

One of the post installation task after installing Oracle 11 is the creation of Access Control List (ACL). Oracle provides a few scripts in order to allow flows_030000 to connect any host. What if you do not allow the database to connect any host but only one host. For instance, you want to send mail from Application Express (APEX) ?

First, set up the email settings in the administrative interface (Home>Manage Service>Instance Settings). Secondly, run the following statements as
system.

-- Create an access control list
BEGIN
  dbms_network_acl_admin.create_acl(acl => 'apexmail.xml',   
    description => 'Mail from Apex',   principal => 'FLOWS_030000',
    is_grant => TRUE,   privilege => 'connect',   start_date => NULL,
    end_date => NULL);
END;
/

-- Assign the list to your SMTP host, i.e. smtp.yyy.xx
BEGIN
  dbms_network_acl_admin.assign_acl(acl => 'apexmail.xml',
    host => 'smtp.yyy.xx',   lower_port => 25,   upper_port => 25);
END;
/

-- Uncomment to remove access control list
--BEGIN
--  dbms_network_acl_admin.drop_acl(acl => 'apexmail.xml');
--END;
--/

-- Please, do commit work.
COMMIT;

You can find more information about the usage of dbms_network_acl_admin in the Database Security Guide. For all the remaining network services you can apply the same technique.