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

 

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;

Now try to send mail:

begin
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.