Database Software

Hi,

I am new on this forum and I thank you for your welcome. I am DBA and SQL developper, running a Database 11.2.0.3 under Windows Server 2003.

I have created a stored procedure using util.smtp package.

CREATE OR REPLACE PROCEDURE VERIF_EV_48H is
last_date date ;
ecart NUMBER ;
max_ecart NUMBER default 0.0417 ;
v_From VARCHAR2(80) := 'ne_pas_repondre@syndice.fr';
v_Recipient VARCHAR2(120) := 'xxx@xx.com';
v_Subject VARCHAR2(80) := 'Délai dépassé pour EV_48H';
v_Mail_Host VARCHAR2(30) := '10.0.10.5';-- IP of email server
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);
v_dt VARCHAR2(30):=to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss');
begin
execute immediate 'select max(ev_dateauto) from ev_48h' into last_date ;
ecart := (SYSDATE -last_date) ;
IF ecart > max_ecart then
  v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
  utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
  utl_smtp.Mail(v_Mail_Conn, v_From);
  utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
  utl_smtp.Data(v_Mail_Conn,
  'Date: ' || v_dt || crlf ||
  'Expéditeur : ' || v_From || crlf ||
  'Subject: '|| v_Subject || crlf ||
  'To: ' || v_Recipient || crlf ||
  crlf ||
  'Attention : le délai d''inactivité de EV_48H est dépassé.'|| crlf ||     -- Message body
  'ecart : ' || ecart || crlf || crlf ||
  'Le robot Oracle'
  );
  utl_smtp.Quit(v_mail_conn);
end if ;
EXCEPTION
  WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
  raise_application_error(-20000, 'Unable to send mail: '||sqlerrm) ;
END VERIF_EV_48H ;

I have created the acl to run it as user SYSMAN ::

begin
dbms_network_acl_admin.create_acl (
acl => 'http_permissions.xml', -- or any other name
description => 'HTTP Access',
principal => 'SYSMAN',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
end;

begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',
principal => 'SYSMAN',
is_grant => true,
privilege => 'connect');
end;

begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',
principal => 'SYSMAN',
is_grant => true,
privilege => 'resolve');
end;

BEGIN
dbms_network_acl_admin.assign_acl (
acl => 'http_permissions.xml',
host => '*',
lower_port => 80,
upper_port => 80
);
END;

I have grant SYSMAN execution privilege on the procedure VERIF_EV_48H :

SQL> grant execute on VERIF_EV_48H to SYSMAN;

But when I try to run it under SQL Plus, I get the fORA-24247 error

SQL> conn sysman/xxxxx@something

SQL> exec sivoa.VERIF_EV_48H;

BEGIN sivoa.VERIF_EV_48H; END;

ORA-24247: network access denied by access control list (ACL)

ORA-06512: Ó "SYS.UTL_TCP", ligne 17

ORA-06512: Ó "SYS.UTL_TCP", ligne 267

ORA-06512: Ó "SYS.UTL_SMTP", ligne 161

ORA-06512: Ó "SYS.UTL_SMTP", ligne 197

Could you please help me to solve this problem ?

Thank you.