Hi,

I recently installed a new server (mainly by using the installation guide you can find in the menu).

I think this needs an extra step, since I wasn’t able to send out emails from that system using Apex.
In the email-queue it kept saying: ORA-24247: network access denied by access control list (ACL)

A quick “Google”, I ended up here : http://blogs.oracle.com/pankaj/entry/how_to_get_mail_working_with_a

So I needed to execute something (read the blog), I did that as SYS:

<EDIT 12 sep 2011>
Please adjust the name of your apex schema in the code below, by adding / removing the remarks for the t_apex_user variable within the declare section of the code.
</EDIT>

<EDIT 29 mar 2013>
I altered the script into a more generic thing. It now takes a few variables:
t_apex_user contains the user for which you need to grant.
t_hostname is the host invloved (this can be ‘*’)
t_new_acl_name holds the name of the ACL in case a new ACL must be created
t_new_acl_descr is the description for this ACL
This makes this into a more generic ACL script, not only for opening up email for the apex user
</EDIT>

declare
  t_acl_path varchar2(4000);
  t_acl_id raw(16);
--  t_apex_user varchar2(30) := 'APEX_030200';
--  t_apex_user varchar2(30) := 'APEX_040000';
  t_apex_user varchar2(30)       := 'SPELEN';
  t_hostname  varchar2(2000)     := 'accounts.google.com';
  t_new_acl_name varchar2(2000);
  t_new_acl_descr varchar2(2000) := 'ACL that lets power users to connect to maps.google.com';
begin
  -- Look for the ACL currently assigned to '*' and give t_apex_user
  -- the "connect" privilege if t_apex_user does not have the privilege yet.
  
  if t_hostname = '*' then
    t_new_acl_name := 'power_users.xml';
  else
    t_new_acl_name := t_hostname || '.xml';
  end if;

  select acl
  into   t_acl_path
  from   dba_network_acls
  where  host = t_hostname
    and  lower_port is null
    and  upper_port is null;

  -- Before checking the privilege, make sure that the ACL is valid
  -- (for example, does not contain stale references to dropped users).
  -- If it does, the following exception will be raised:
  --
  -- ORA-44416: Invalid ACL: Unresolved principal t_apex_user
  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
  --
  select sys_op_r2o(extractvalue(p.res, '/Resource/XMLRef'))
  into   t_acl_id
  from   xdb.xdb$acl a
  ,      path_view p
  where  extractvalue(p.res, '/Resource/XMLRef') = ref(a)
    and  equals_path(p.res, t_acl_path) = 1;

  dbms_xdbz.validateacl(t_acl_id);
  if dbms_network_acl_admin.check_privilege(t_acl_path, t_apex_user, 'connect') is null
    then
      dbms_network_acl_admin.add_privilege(t_acl_path, t_apex_user, true, 'connect');
  end if;

  commit;

exception
  -- when no acl has been assigned to '*'.
  when no_data_found
    then
      dbms_network_acl_admin.create_acl(t_new_acl_name, t_new_acl_descr, t_apex_user, TRUE, 'connect');
      dbms_network_acl_admin.assign_acl(t_new_acl_name, t_hostname);
end;

I had to replace the “FLOWS_030000” by “APEX_040000” in Pankaj’s script. After this everything worked like a charm.

Leave a Reply