DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, July 22, 2014

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

Getting the above error after apex upgrade from 4.0.2 to 4.2.5 version. The PDF printing from EBS concurrent requests was failing. So followed the below steps to rectify the issue.

On executing the below verification script got the following output:

SQL> DECLARE
  ACL_PATH VARCHAR2(4000);
  ACL_ID RAW(16);
  BEGIN
  -- Look for the ACL currently assigned to '*' and give APEX_040200
  -- the "connect" privilege if APEX_040200 does not have the privilege yet.
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
  WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
    -- Before checking the privilege, make sure that the ACL is valid
  -- (for example, does not contain stale references to dropped users).
  -- If it does, the following exception will be raised:
  --
  -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040200'
  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
  --
  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
  FROM XDB.XDB$ACL A, PATH_VIEW P
  WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
  EQUALS_PATH(P.RES, ACL_PATH) = 1;
    DBMS_XDBZ.ValidateACL(ACL_ID);
    IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE (ACL_PATH, 'APEX_040200', 'connect')IS NULL THEN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
  'APEX_040200', TRUE, 'connect');
  END IF;
    EXCEPTION
  -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
  'ACL that lets power users to connect to everywhere',
  'APEX_040200', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
  END;
/

Output
=====



DECLARE
*
ERROR at line 1:
ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'
ORA-06512: at "XDB.DBMS_XDBZ", line 130
ORA-06512: at line 22


Script to check the ACL:

SQL> SELECT ACL, PRINCIPAL
FROM DBA_NETWORK_ACLS NACL, XDS_ACE ACE
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL AND
NACL.ACLID = ACE.ACLID AND
NOT EXISTS (SELECT NULL FROM ALL_USERS WHERE USERNAME = PRINCIPAL);



Output:
=====

ACL
--------------------------------------------------------------------------------
PRINCIPAL
--------------------------------------------------------------------------------
/sys/acls/OracleEBS.xml
APEX_040000

Solution:
======


SQL> create user APEX_040000 identified by apex;

User created.

SQL> grant connect,resource to APEX_040000;

Grant succeeded.

SQL> begin
2 dbms_network_acl_admin.add_privilege('/sys/acls/OracleEBS.xml',
'APEX_040200', TRUE, 'connect');
3 4 end;
5 /

PL/SQL procedure successfully completed.



Now the issue was fixed and was able to print PDF from EBS concurrent request.