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;
/
****************************************************