6 Replies Latest reply: Feb 12, 2013 10:29 AM by snmdla RSS

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

    683199
      Hi,

      I try to send email with APEX.

      I have enter the parameters of my mail server and activate the email on my application.
      I have follow the APEX installation guide and apply the script given in the "Granting Connect Privileges" section.

      When I try to send email or make a subscription, I don't receive any email and can see this error in the table "WWV_FLOW_MAIL_LOG"
      +"MAIL_TO","MAIL_FROM","MAIL_REPLYTO","MAIL_SUBJ","MAIL_CC","MAIL_BCC","MAIL_SEND_ERROR","LAST_UPDATED_BY","LAST_UPDATED_ON","SECURITY_GROUP_ID"+
      +"olivier.villegente@sic.nc","olivier.villegente@sic.nc","olivier.villegente@sic.nc","Suivi de Besoins","","","ORA-24247: network access denied by access control list (ACL)","SYS",05/03/12,3210210578052219+
      +"olivier.villegente@sic.nc","olivier.villegente@sic.nc","olivier.villegente@sic.nc","Suivi de Besoins","","","ORA-24247: network access denied by access control list (ACL)","SYS",05/03/12,3210210578052219+
      +"olivier.villegente@sic.nc","olivier.villegente@sic.nc","olivier.villegente@sic.nc","Suivi de Besoins","","","ORA-24247: network access denied by access control list (ACL)","SYS",05/03/12,3210210578052219+
      +"olivier.villegente@sic.nc","olivier.villegente@sic.nc","olivier.villegente@sic.nc","Suivi de Besoins","","","ORA-24247: network access denied by access control list (ACL)","SYS",05/03/12,3210210578052219+
      +"jean-michel.plancade@sic.nc","jean-michel.plancade@sic.nc","jean-michel.plancade@sic.nc","Suivi de Besoins","","","ORA-24247: network access denied by access control list (ACL)","SYS",24/02/12,3210210578052219+
      +"olivier.villegente@sic.nc","olivier.villegente@sic.nc","olivier.villegente@sic.nc","Suivi de Besoins","","","ORA-24247: network access denied by access control list (ACL)","SYS",05/03/12,3210210578052219+

      Do you see what is wrong in my configuration ?

      I use APEX 4.1, Oracle 11g.
      The script that I have apply is :

      DECLARE
      ACL_PATH  VARCHAR2(4000);
      ACL_ID    RAW(16);
      BEGIN
      -- Look for the ACL currently assigned to '*' and give APEX_040100
      -- the "connect" privilege if APEX_040100 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_040100'
      -- 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_040100',
      +'connect') IS NULL THEN+
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
      +'APEX_040100', 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_040100', TRUE, 'connect');+
      DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
      END;
      +/+
      COMMIT;


      Thanks for your help,
        • 1. Re: APEX and ORA-24247: network access denied by access control list (ACL)
          683199
          This script work on my test server (same server as the production one). Do you have an idea ?
          Do you know if it is possible to get a more detailled log than what I can see in the table "WWV_FLOW_MAIL_LOG".

          Edited by: villegente on 5 mars 2012 20:38
          • 2. Re: APEX and ORA-24247: network access denied by access control list (ACL)
            916806
            FYI:
            ORA-24247: network access denied by access control list (ACL)

            Cause: No access control list (ACL) has been assigned to the target host or the privilege necessary to access the target host has not been granted to the user in the access control list.

            Action: Ensure that an access control list (ACL) has been assigned to the target host and the privilege necessary to access the target host has been granted to the user.
            • 3. Re: APEX and ORA-24247: network access denied by access control list (ACL)
              683199
              Hi,

              I don't see what is wrong in configuration.

              If i look in DBA_NETWORK_ACL_PRIVILEGES I have :
              -----
              /sys/acls/all-network-PUBLIC.xml     BEA2335B75D203AEE04010AC671E4CED     PUBLIC     connect     true     false          
              /sys/acls/all-network-PUBLIC.xml     BEA2335B75D203AEE04010AC671E4CED     PUBLIC     resolve     true     false          
              /sys/acls/open_acl_file.xml     B017B8EDDA9A3BD6E04010AC671E0DD3     SIC_PRODUCTION     connect     true     false     25/10/11 13:59:05,086949000 +11:00     
              -----

              In DBA_NETWORK_ACLS I have :

              -----
              *               /sys/acls/all-network-PUBLIC.xml     BEA2335B75D203AEE04010AC671E4CED
              *     1     9999     /sys/acls/open_acl_file.xml     B017B8EDDA9A3BD6E04010AC671E0DD3
              -----

              In DBA_NETWORK_ACL_PRIVILEGES I have :

              -----
              /sys/acls/all-network-PUBLIC.xml     BEA2335B75D203AEE04010AC671E4CED     PUBLIC     connect     true     false          
              /sys/acls/all-network-PUBLIC.xml     BEA2335B75D203AEE04010AC671E4CED     PUBLIC     resolve     true     false          
              /sys/acls/open_acl_file.xml     B017B8EDDA9A3BD6E04010AC671E0DD3     SIC_PRODUCTION     connect     true     false     25/10/11 13:59:05,086949000 +11:00     
              -----

              Do you see what is wrong ?

              Regards,

              Edited by: villegente on 27 avr. 2012 16:25
              • 4. Re: APEX and ORA-24247: network access denied by access control list (ACL)
                Jaydipsinh Raulji
                Hi,

                You need to grant privilege to the user.

                i.e add principal

                You can use script :

                DECLARE
                ACL_ID   RAW(16);
                CNT      NUMBER;
                BEGIN
                -- Look for the object ID of the ACL currently assigned to ''*
                SELECT ACLID INTO ACL_ID FROM DBA_NETWORK_ACLS
                WHERE HOST = '' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;*

                -- If just some users referenced in the ACL are invalid, remove just those
                -- users in the ACL. Otherwise, drop the ACL completely.
                SELECT COUNT(PRINCIPAL) INTO CNT FROM XDS_ACE
                WHERE ACLID = ACL_ID AND
                EXISTS (SELECT NULL FROM ALL_USERS WHERE USERNAME = PRINCIPAL);

                IF (CNT > 0) THEN

                FOR R IN (SELECT PRINCIPAL FROM XDS_ACE
                WHERE ACLID = ACL_ID AND
                NOT EXISTS (SELECT NULL FROM ALL_USERS
                WHERE USERNAME = PRINCIPAL)) LOOP
                UPDATE XDB.XDB$ACL
                SET OBJECT_VALUE =
                DELETEXML(OBJECT_VALUE,
                *'/ACL/ACE[PRINCIPAL="'||R.PRINCIPAL||'"]')*
                WHERE OBJECT_ID = ACL_ID;
                END LOOP;

                ELSE
                DELETE FROM XDB.XDB$ACL WHERE OBJECT_ID = ACL_ID;
                END IF;

                END;
                */*

                REM commit the changes.

                COMMIT;


                Or you need to add privilege to specific user/schema using following script:


                BEGIN
                DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
                acl          => 'aclfilename.xml',
                principal    => 'databaseuser',
                is_grant     => TRUE,
                privilege    => 'connect',
                position     => null);
                COMMIT;
                END;

                Please execute this code after connect as sysdba user.

                Thanks & Regards,
                Jaydipsinh Raulji

                Web: [www.oracleapexconsultant.com|www.oracleapexconsultant.com]
                • 5. Re: APEX and ORA-24247: network access denied by access control list (ACL)
                  935219
                  I faced similar issue. It got solved by giving required privilege to the Apex Application Owner. See this page: http://gavinsoorma.com/2009/07/sending-mail-from-apex-installed-on-an-oracle-11g-database/comment-page-1/#comment-5761
                  • 6. Re: APEX and ORA-24247: network access denied by access control list (ACL)
                    snmdla
                    I was somewhat surprised to find out this, but thinking twice, it's
                    clear that we need to go that way:

                    For mechanisms like the built-in LDAP authorization to work correctly,
                    it is sufficient to have principal APEX_040100 (the schema matching
                    the APEX version) specified in the ACL.

                    Now I see, that for each application schema that implements network
                    operations (in our case, e.g. invoking DBMS_LDAP.search_s), this
                    setting has do be done again, because the Apex Application Owner
                    alone is not sufficient.

                    this will be something like this:

                    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => <ACLSPEC>,
                    principal => <PARSING SCHEMA>,
                    is_grant => true,
                    privilege => 'connect');

                    Regards, Tom