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

Problem:

-- As Scott User

CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,

p_from IN VARCHAR2,

p_subject IN VARCHAR2,

p_message IN VARCHAR2,

p_smtp_host IN VARCHAR2,

p_smtp_port IN NUMBER DEFAULT 25)

AS

l_mail_conn UTL_SMTP.connection;

BEGIN

l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);

UTL_SMTP.helo(l_mail_conn, p_smtp_host);

UTL_SMTP.mail(l_mail_conn, p_from);

UTL_SMTP.rcpt(l_mail_conn, p_to);

UTL_SMTP.open_data(l_mail_conn);

UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);

UTL_SMTP.close_data(l_mail_conn);

UTL_SMTP.quit(l_mail_conn);

END;

/

begin

send_mail(p_to=> 'mdnazmulhuda@test.com',p_from=> 'oracle@test.com',p_subject => 'TEST SEND MAIL',p_message =>'test',p_smtp_host => '127.0.0.1');

end;

/

*

ERROR at line 1:

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

ORA-06512: at "SYS.UTL_TCP", line 17

ORA-06512: at "SYS.UTL_TCP", line 246

ORA-06512: at "SYS.UTL_SMTP", line 127

ORA-06512: at "SYS.UTL_SMTP", line 150

ORA-06512: at "SCOTT.SEND_MAIL", line 10

ORA-06512: at line 2

Solution:

This is because the SCOTT does not have the privilege to access the mail/smtp server. So it must be added to the ACL.

-- AS sys User

SELECT host, lower_port, upper_port, acl FROM dba_network_acls ;

no rows selected.

This is because the SCOTT does not have the privilege to access the mail/smtp server. So it must be added to the ACL.

Create and assign ACL.

BEGIN

DBMS_NETWORK_ACL_ADMIN.create_acl (

acl => 'send_mail.xml',

description => 'Purpose of the acl is to send mail',

principal => 'SCOTT',

is_grant => TRUE,

privilege => 'connect',

start_date => SYSTIMESTAMP,

end_date => NULL);

DBMS_NETWORK_ACL_ADMIN.assign_acl (

acl => 'send_mail.xml',

host => '127.0.0.1',

lower_port => 1,

upper_port => 9999);

COMMIT;

END;

/

SELECT host, lower_port, upper_port, acl FROM dba_network_acls ;

-- Now try Again

-- As scott User

begin

send_mail(p_to=> 'mdnazmulhuda@test.com',p_from=> 'oracle@test.com',p_subject => 'TEST SEND MAIL',p_message =>'test',p_smtp_host => '127.0.0.1');

end;

/

PL/SQL procedure successfully completed.