We were often seeing following network ACL error.
ORA-24247: network access denied by access control list (ACL)
Query to check for existing ACLs.
SELECT NACL.ACLID,ACL, PRINCIPAL
FROM DBA_NETWORK_ACLS NACL, XDS_ACE ACE
WHERE NACL.ACLID = ACE.ACLID ;
We had to run following steps to resolve the issue.
EXEC DBMS_NETWORK_ACL_ADMIN.DROP_ACL(acl => 'mails.xml' );
EXEC DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'mails.xml', description => 'Mail ACL', principal => 'SCOTT', is_grant => TRUE, PRIVILEGE => 'connect');
Principal ==> says which schema will primarily own the ACL
is_grant => TRUE will allow other schema in DB to access this ACL (this line needs verification)
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege (acl => 'mails.xml',
principal => 'SCOTT', --Schema name
is_grant => TRUE, --same as above
PRIVILEGE => 'connect',
position => NULL,
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/
EXEC DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'mails.xml', HOST => 'mailhost', lower_port => 21, upper_port => 30);
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege (acl => 'mails.xml',
principal => 'SCHEMA2',
is_grant => TRUE,
PRIVILEGE => 'connect',
position => NULL,
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/
For more details: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm