ORA-24247: Network Access Denied By Access Control List (ACL)

ERROR :
While sending mail using utl_mail or utl_stmp in oracle 11g, you may get access denied error:

begin
utl_mail.send(sender => ‘[email protected]’,
recipients => ‘[email protected]’,
subject => ‘MAIL from ADMIn of dbaclass’,
message => ‘Do visit dbaclass’);
end;
/
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.UTL_MAIL”, line 662
ORA-06512: at “SYS.UTL_MAIL”, line 679
ORA-06512: at line 2

Solution:

From 11g onward, to send mail using UTL_MAIL or UTL_SMTP , ACL need to be created in the database.

SQL> alter system set smtp_out_server='mail.abuzer.local' scope=both;
 
System altered.
--- creating ACL as below 
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('send_mail.xml','Allow mail to be send', 'USR_ABUZER', TRUE, 'connect');
commit;
----Grant the connect and resource privilege as below
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('send_mail.xml','USR_ABUZER',TRUE, 'connect');
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('send_mail.xml','USR_ABUZER',TRUE, 'resolve');
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('send_mail.xml','*',587);
commit;
 
conn usr_abuzer/password
Connected.
SQL> begin
  utl_mail.send(sender     => '[email protected]',
                recipients => '[email protected]',
                subject    => 'TEST MAIL',
                message    => 'TEST MESSAGE');
end;
/
PL/SQL procedure successfully completed.

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir