10 Replies Latest reply: May 1, 2013 7:40 AM by Vorlon1 RSS

    LDAP error on new installation

    Vorlon1
      New server 2008R2 64 bit
      New RDBMS installation 11.2.0.3
      New installation of HTTP server 10.2 from legacy Companion disk of 10.2
      New installation of APEX 4.2.1.00.08
      Made dads.conf changes, etc.
      Apex is running fine.
      Installed LDAP package, and granted connect privileges according to APEX installation instructions page 3.44
      I cannot execute an LDAP query from within APEX.
      I get the dreaded ORA-24247: network access denied by control list (ACL)
      I can, however, execute one from the command line as sys.
      Ideas?
        • 1. Re: LDAP error on new installation
          Tom Petrus
          There really is nothing dreaded about it. It only means that you did not provide network access to the apex user (and the parsing schema user of the application you want to run this in).
          This is in the installation documentation aswell (this is 4.0 docs but remains the same as this is 11g stuff): http://docs.oracle.com/cd/E17556_01/doc/install.40/e15513/otn_install.htm#BABBHCID

          Example code for ad:
          --create the ACL, assign the apex user APEX_040200 (this is the apex 4.2 technical user)
          BEGIN
            DBMS_NETWORK_ACL_ADMIN.create_acl (
              acl          => 'ad_ldap.xml', 
              description  => 'User authentication AD',
              principal    => 'APEX_040200',
              is_grant     => TRUE, 
              privilege    => 'connect',
              start_date   => NULL,
              end_date     => NULL);
            COMMIT;
          END;
          /
          
          -- Assign the parsing schema user to the ACL aswell, fe here this is user APX
          BEGIN
             DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
              acl          => 'ad_ldap.xml',                
              principal    => 'APX',
              is_grant     => TRUE, 
              privilege    => 'connect',
              position     => null);
             COMMIT;
          END;
          /
          
          -- Define the access to the network. This will grant network acces to MY_AD_SERVER on port 389
          -- You could replace the name with an IP if you'd rather.
          BEGIN
            DBMS_NETWORK_ACL_ADMIN.assign_acl (
              acl         => 'ad_ldap.xml',
              host        => 'MY_AD_SERVER', 
              lower_port  => 389,
              upper_port  => 389);
            COMMIT;
          END;
          /
          • 2. Re: LDAP error on new installation
            Vorlon1
            Not to put too fine a point on it, but did you see that I had already executed the procedures from the APEX 4.2 installation documentation?
            Please also note that the document that you linked (4.0) contains a different procedure for granting connect procedures than is in the 4.2 document. I am thinking that the procedures listed in the 4.2 document should work, but they don't seem to.
            Also, I called it 'dreaded' because so many people have run into this problem...
            TIA
            • 3. Re: LDAP error on new installation
              Tom Petrus
              You are right, I apologize. I missed it and I stand corrected.

              For anyone looking, these are the 4.2 docs: http://docs.oracle.com/cd/E37097_01/doc/install.42/e35123/otn_install.htm#BABBHCID

              I have had no issues getting this all running, but I didn't really use these scripts too. I created an acl to specifically manage access to the domain controller since I wanted to keep the host-list as restricted as possible and there are plenty of schemas to grant access to. I used the code I put above to do this, and only added statements for the other schemas as required. I did not grant a "connect to everywhere" to the power users. I use different ACLs to keep things like ad access, webservices and mail apart.
              Executing the installation code is only a jumping point though. It only gives access to "power users", ie the technical users, and not schema users. This might be why you are still getting the error.
              Did you try my code by chance?
              If not, do you have any other ACLs defined yourself? If yes, how did you define them: what user, what host or ip? If you defined a host or ip, what did you define in your authentication schema as ad server?
              • 4. Re: LDAP error on new installation
                Vorlon1
                No problem!
                I did try your code with a slight change:
                --create the ACL, assign the apex user APEX_040200 (this is the apex 4.2 technical user)
                BEGIN
                DBMS_NETWORK_ACL_ADMIN.create_acl (
                acl => 'ad_ldap.xml',
                description => 'User authentication AD',
                principal => 'APEX_040200',
                is_grant => TRUE,
                privilege => 'connect',
                start_date => NULL,
                end_date => NULL);
                COMMIT;
                END;
                /

                -- Assign the parsing schema user to the ACL aswell, fe here this is user APX
                BEGIN
                DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
                acl => 'ad_ldap.xml',
                principal => 'APEX_040200',
                is_grant => TRUE,
                privilege => 'connect',
                position => null);
                COMMIT;
                END;
                /

                -- Define the access to the network. This will grant network acces to MY_AD_SERVER on port 389
                -- You could replace the name with an IP if you'd rather.
                BEGIN
                DBMS_NETWORK_ACL_ADMIN.assign_acl (
                acl => 'ad_ldap.xml',
                host => '<my_AD_server>',
                lower_port => 389,
                upper_port => 389);
                COMMIT;
                END;
                /

                No joyl! Still does not work. I am running a little procedure in APEX that connects to the server and executes a query. Same code that works on 10.2. Still get the error ORA-24247.
                Do I have to reboot the server or something crazy like that do I?
                • 5. Re: LDAP error on new installation
                  Vorlon1
                  Current Configuration includes:
                  Select host, lower_port, upper_port, acl from dba_network_acls
                  HOST LOWER_PORT UPPER_PORT ACL
                  ----------------
                  * /sys/acls/power_users.xml
                  <my ad server name> 389 389 /sys/acls/ad_ldap.xml
                  • 6. Re: LDAP error on new installation
                    Tom Petrus
                    Here is where I think you're going wrong:
                    -- Assign the parsing schema user to the ACL aswell, fe here this is user APX
                    BEGIN
                    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
                    acl => 'ad_ldap.xml',
                    principal => 'APEX_040200',
                    is_grant => TRUE,
                    privilege => 'connect',
                    position => null);
                    COMMIT;
                    END;
                    / 
                    You're simply adding APEX_040200 a second time to the ACL. During the creation of the ACL the user is already assigned there (see it as an initial user) through parameter "principal".
                    What I tried to explain is that both that user needs access, but also the user of the schema that your application is running in.

                    For example, your piece of code. This may do a simple bind and and then perform a search on the AD, and should run fine in a sql window in sql developer or from sql plus, or from the SQL Commands tab in the SQL workshop in apex; However, this is not run as the apex user but as the database user of the associated schema. So for example, say I have a user 'TOM', and when i connect to my db I'd use TOM/TOM@mydb. I'm sure you're aware of this but I hope I'm getting my point across. It's similar with the parsing schema of the apex application you'd try to run this code in. Not only does apex_040200 need network access, the schema user of the parsing schema would also need it, and not in the least so that you could test your code!

                    So: execute the "add_privilege" code with the principal set to the user of the schema you try to run your code in.
                    • 7. Re: LDAP error on new installation
                      Vorlon1
                      I'm believe you may be correct in that I don't understand what 'the user of the schema you try to run your code in' means.
                      There are many 'users' in the APEX world: The 'user' that APEX is installed with (in this case): 'APEX_040200', a 'user' (admin) of the APEX environment, 'users' that create work spaces in which to develop applications, and or course 'users' of those applications. Which one do you mean?
                      Again, thanks!
                      • 8. Re: LDAP error on new installation
                        Vorlon1
                        OK, I think I got it. I added access to the 'user'--that is the workspace of the application--and it worked. Just to make sure, is this correct?
                        • 9. Re: LDAP error on new installation
                          Tom Petrus
                          http://stackoverflow.com/questions/880230/difference-between-a-user-and-a-schema-in-oracle

                          Maybe that clears some more doubts. Not sure what you mean by workspace, but probably you refer to the schema. Remember that when you create an application you have to assign a schema, or when you run code you also do this froma ccertain schema. It is that schema (thus, user) that needs those connect privileges.
                          • 10. Re: LDAP error on new installation
                            Vorlon1
                            My root cause of my confusion was in which one to grant privileges. In this case it was the 'workspace' (within APEX) schema. Many thanks for your assistance.