This discussion is archived
10 Replies Latest reply: May 1, 2013 5:40 AM by Vorlon1 RSS

LDAP error on new installation

Vorlon1 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points