ACL Error Solution in Oracle 11g

Oracle 11g have been used ACL procedure to send smtp e-mail. Default configuration can not send e-mail. You have to create below procedures for complete smtp configuration.

I used CONNUSER as ACL connection user and 100.10.10.11 IP address as mail server IP and NOTIFYUSER as user for notification for configuration. You can change this users or IP address.

Create ACL MailServer

SQL> 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;

Procedure created.
/

Create ACL Connection

SQL> begin
mailserver_acl(
‘mailserver_acl.xml’,
‘ACL for used Email Server to connect’,
‘CONNUSER’,
TRUE,
‘connect’,
‘100.10.10.11’,
25);
end;

PL/SQL procedure successfully completed.
/

Add User to ACL Privilage

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

PL/SQL procedure successfully completed.