Issue:
ORA-24247: network access denied by access control list (ACL) error accorded after db upgrade to 11gr2 from 9.2.0.8 in EBS Environment.
Error:
ORA-24247: network access denied by access control list (ACL)
Impact:
Unable to sent mail through database.
Reason:
I had ignored pre-upgrade tool report warning:-
WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER APPS has dependent objects.
Solution:
1. Please check whether the below files exist:
2. If the above files exist , then run 'Autoconfig' on the DB Tier and check if the issue resolves..
3. If the issue does not resolve, then you can check the below steps.
Create a ACL if one does not exist by referring the bellow command. You can use the scripts mentioned below to check the available ACLs and the related privileges..
SQL> select * from DBA_NETWORK_ACLS;
SQL> select * from DBA_NETWORK_ACL_PRIVILEGES;
Assign the specific Users or Roles to the ACL list.
BEGIN
-- Only uncomment the following line if ACL "network_services.xml" has already been created
--DBMS_NETWORK_ACL_ADMIN.DROP_ACL('network_services.xml');
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => 'network_services.xml',
description => 'FTP ACL',
principal => 'APPS',
is_grant => true,
privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'network_services.xml',
principal => 'APPS',
is_grant => true,
privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'network_services.xml',
host => '*');
COMMIT;
END;
Assign the ACL to the required Hosts including the Mail Server
connect apps/apps;
DECLARE
conn utl_smtp.connection;
begin
conn := utl_smtp.open_connection('mail1.indiandba.com', 25);
end;
/
Check the configuration:
select utl_inaddr.get_host_address('mail1.indiandba.com') from dual;
Now retest the issue.