Tuesday, March 8, 2011

ORA-24247: network access denied by access control list (ACL)

One of our users reported the following issue in one of the concurrent programs:

Cause: FDPSTP failed due to ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "XAUDI.XAUDI_UTILS_PKG", line 144
ORA-06512: at "XAUDI.XAUDI_CSC_ODS_PKG", line 1993
ORA-06512: at line 1

Reason: migrated from 11.5.9 to R12.1.1 on 11gR2 db. The fix is to grant connect and resolve privileges to the schema which is trying to use these objects.

SELECT DISTINCT owner FROM DBA_DEPENDENCIES WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR');


This will give you the list of schemas which have object that have dependency on the utl objects which are protected by ACLs.

This will help you identify not only the user who is having the issues but also potential issues.

Next, see if there is an xml file which has the ACLs for the database defined or not:

SELECT * FROM DBA_NETWORK_ACLS WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

If you get the xml file name as an output, then move to the next steps 2. If not, perform step 1.

Step 1:
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(‘','ACL used for utl packages','APPS', TRUE, 'connect');
This will create the xml if one such is not available.

Step 2:
Begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('[XML NAME]','XAUDI', TRUE, 'connect');

END
/
Commit;

Step 3:
Begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('[XML NAME]','XAUDI', TRUE, 'resolve');
END
/
Commit;



This should resolve the issue.



- Aravind Kamath Posral

No comments: