ORA-24247: Network Access Denied By Access Control List (ACL)
While sending mail using utl_mail or utl_stmp , you may get access denied error:or
If you are trying to use oracle to invoke a web service or to connect to a smtp service and you get the following error:
begin
utl_mail.send(sender => 'admin@testuser.com’,
recipients => ‘admin@testuser.com’,
subject => ‘Test mail from user’,
message => ‘testing’);
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
Since Oracle 11, oracle introduced a fine grained access to network services using access control lists (ACL).
This new feature gave the DBA a better control on which user can connect to which computer
In order to solve ORA-24247 you will need to:
1) Create an acl (if it is not already created)
2) Add privileges to the user using the network resources
3) Assign the acl to a specific address
SELECT * FROM dba_network_acls;
If the computer you are trying to connect to is not listed under host, you will need to create an acl:
Solution:
From 11g onward, to send mail using UTL_MAIL or UTL_SMTP , ACL need to be created in the database.
Set the SMTP_OUT_SERVER parameter
SQL> alter system set smtp_out_server='mailhost' scope=both;
System altered.
Now create ACLS
Suppose the user chitrap want to send mail from procedure.
--- creating ACL as below
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('test_mail.xml','Allow mail to be send', 'chitrap', TRUE, 'connect');
commit;
----Grant the connect and resource privilege as below
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('test_mail.xml','chitrap',TRUE, 'connect');
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('test_mail.xml','chitrap',TRUE, 'resolve');
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('test_mail.xml','*',25);
commit;
utl_mail.send(sender => 'admin@testuser.com’,
recipients => ‘admin@testuser.com’,
subject => ‘Test mail from user’,
message => ‘testing’);
end;
/
PL/SQL procedure successfully completed
select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;
Drop ACL :
BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl (
acl => 'test_mail.xml');
COMMIT;
END;
/
It is important to note that only one ACL can be assigned to any host computer. If you assign a new acl to a target the old acl gets unassigned.
