News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

11g Access Control Lists (ACL) and Sending Mail From APEX

  • Posted by Gavin Soorma
  • On July 2, 2009
  • 3 Comments

In Oracle 11g, security has been enhanced by restricting access to packages that were used in earlier releases like UTL_SMTP and UTL_HTTP to send emails and connect over the network to mail servers etc. By default, attempt to use these packages will result in an ORA-24247 (network access denied by access control list).

Using Access Control Lists or ACL’s, administrators can have control over which ports are opened for ‘public’ access.

This example below will show how we can use the DBMS_NETWORK_ACL_ADMIN package to enable us to send emails from an APEX 3.1 application which connects to an Oracle 11g database.

Create the mailserver_acl procedure which calls the DBMS_NETWORK_ACL_ADMIN package

set serveroutput on

show user;

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;
/
show errors

Now we need to grant the database user ‘FLOWS_030100’ and the application owner ‘MONITOR’ the required privileges to interact with network services – in this case to access the SMTP server FRMWEB02 using port 25.

begin
mailserver_acl(
‘mailserver_acl.xml’,
‘ACL for used Email Server to connect’,
MONITOR‘,
TRUE,
‘connect’,
‘FRMWEB02.BANKWEST.COM’,
25);
end;
/

begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(‘mailserver_acl.xml’,‘FLOWS_030100’,TRUE,’connect’);
commit;
end;
/

 

3 Comments

cristian
  • Aug 14 2009
ORA-24244: asignación no válida de host o puerto a lista de control de acceso (ACL) error
Gavin Soorma
  • Aug 18 2009
Hi Cristian - I do not understand Spanish - can you ask your question in English please - regards Gavin
Michael Seberg
  • Apr 6 2010
I like the example. It goes nowhere on oracle 11g R2 Same error as Cristian, no matter what.

Leave Reply

Your email address will not be published. Required fields are marked *