This discussion is archived
7 Replies Latest reply: Oct 11, 2012 5:40 AM by Christian Neumueller RSS

APEX_LDAP.GET_ATTRIBUTES does not work when authenticated against the BASE

sect55 Journeyer
Currently Being Moderated
I use the DBMS_LDAP package to authenticate the user. But I use the APEX_LDAP wrapper to get the mail attribute (i.e., email address) via get_user_attributes procedure. The AD Administrator wants authenticate against the base DN. So I removed the OU=xxxxxx from the search base, which is now 'dc=pacorp,dc=panynj,dc=gov'. I use the following call to the procedure:
   L_ATTRIBUTES(1) := 'mail';
    apex_ldap.get_user_attributes(p_username         => p_username,
                                  p_pass             => p_password,
                                  P_AUTH_BASE        =>'dc=pacorp,dc=panynj,dc=gov',
                                  P_HOST             => 'host_name',
                                  P_PORT             => 'port',
                                  p_attributes       => l_attributes,
                                  p_attribute_values => l_attributes_values);
I get the error message ORA-31202: DBMS_LDAP: LDAP client/server error: Invali.

Can I use the apex_ldap wrapper when searching from the base? If so, what do I need to change for it to work. If not, what is the equivalent DBMS_LDAP procedures to use?

version APEX 4.1
DBMS Oracle Server - Enterprise Edition - 10.2.0.3

Robert
http://apexjscss.blogger.com

Edited by: sect55 on Oct 3, 2012 3:13 PM
  • 1. Re: APEX_LDAP.GET_ATTRIBUTES does not work when authenticated against the BASE
    sect55 Journeyer
    Currently Being Moderated
    Gurus,

    Any Guru or Anyone from the APEX team that knows LDAP pretty well? I received no answered as of yet. Created an SR on My Oracle Support.

    Please assist.

    Robert
    http://apexjscss.blogspot.com
  • 2. Re: APEX_LDAP.GET_ATTRIBUTES does not work when authenticated against the BASE
    Christian Neumueller Expert
    Currently Being Moderated
    Hi Robert,

    we from the Apex team have been busy with OOW. I answered to the support engineer, who will update the SR.

    Regards,
    Christian
  • 3. Re: APEX_LDAP.GET_ATTRIBUTES does not work when authenticated against the BASE
    sect55 Journeyer
    Currently Being Moderated
    Christian,

    I informed the support engineer that the attribute is for the same user.

    In summary,
    I performed the dbms_ldap.simple_bid_s already to verify that the user is on the AD with the proper password and it works fine.
    Then, I am trying to get the mail attribute of the user that is logged on

    I hope that clears it up.

    Regards,
    Robert
    http://apexjscss.blogspot.com
  • 4. Re: APEX_LDAP.GET_ATTRIBUTES does not work when authenticated against the BASE
    sect55 Journeyer
    Currently Being Moderated
    Christian,

    I fixed my issue. I used DBMS_LDAP package instead of the APEX_LDAP package since the APEX_LDAP package seems to have a problem if searching from the Base (i.e., dc=companyname,dc=corpname, dc=company_suffix) and not the OU. The following is the code I used:
    function get_ldap_mail_attribute (p_username IN VARCHAR2, p_password IN VARCHAR2) return varchar2 is
     L_VALS              DBMS_LDAP.STRING_COLLECTION;
     L_SESSION         DBMS_LDAP.SESSION;
     L_ATTRS             DBMS_LDAP.STRING_COLLECTION;
     L_ATTR_NAME     VARCHAR2(256);
     L_ENTRY            DBMS_LDAP.MESSAGE;
     L_MESSAGE        DBMS_LDAP.MESSAGE;
     L_LDAP_USER     VARCHAR2(256);
     L_RETVAL           PLS_INTEGER;
     L_LDAP_BASE      VARCHAR2(256) := 'dc=corpname,dc=compoanyname,dc=suffix';
     L_BER_ELEMENT  DBMS_LDAP.BER_ELEMENT;
     L_EMAIL             VARCHAR2(255);
     I                       NUMBER;
     L_ERR_NUM        NUMBER;
     l_err_msg           VARCHAR2(200);
     begin
     --Declare the ID attribute
     
        L_LDAP_USER := P_USERNAME ||G_DOMAIN;
      
        BEGIN
          L_SESSION := DBMS_LDAP.INIT (HOSTNAME=>G_HOST,PORTNUM => G_PORT);
          EXCEPTION
             WHEN DBMS_LDAP.INVALID_SESSION THEN 
                  CREATIVE_PKG.CSRSR_DEBUGGING ('LDAP.get_ldap_mail_attribute',
                       '0200-Session handle ld is invalid-'|| 'User:'||L_LDAP_USER||
                       ' Host:'||G_HOST||' Port:'||G_PORT);
        END;
        L_RETVAL := DBMS_LDAP.SIMPLE_BIND_S(LD => L_SESSION, DN => L_LDAP_USER, PASSWD =>  P_PASSWORD );
        L_ATTRS(1) := 'mail'; -- mail attribute
        L_EMAIL := LOWER(P_USERNAME) || '@panynj.gov';
        L_RETVAL := DBMS_LDAP.SEARCH_S(LD => L_SESSION, BASE => L_LDAP_BASE, SCOPE => DBMS_LDAP.SCOPE_SUBTREE, 
                       FILTER => 'cn='||lower(P_USERNAME), --objectclass=*
                       ATTRS => L_ATTRS, ATTRONLY => 0, RES => L_MESSAGE);
                      
        IF L_RETVAL <> DBMS_LDAP.SUCCESS THEN
          CREATIVE_PKG.CSRSR_DEBUGGING('LDAP.get_ldap_mail_attribute','0400-SEARCH_S WAS UNSUCCESSFUL: L_RETVAL='||TO_CHAR(L_RETVAL));
        END IF;
    
    L_ENTRY := DBMS_LDAP.FIRST_ENTRY(LD  => L_SESSION, MSG => L_MESSAGE);
    --
    -- Loop through the entries
    --
    << entry_loop >>
    WHILE L_ENTRY IS NOT NULL LOOP
      L_ATTR_NAME := DBMS_LDAP.FIRST_ATTRIBUTE(L_SESSION,L_ENTRY, L_BER_ELEMENT);
      L_VALS := DBMS_LDAP.GET_VALUES(LD => L_SESSION, LDAPENTRY => L_ENTRY, ATTR => L_ATTR_NAME);
      << values_loop >>
      FOR I IN L_VALS.FIRST .. L_VALS.LAST LOOP
          L_EMAIL := SUBSTR(L_VALS(I),1,200);
          EXIT values_loop;
      END LOOP values_loop;
      RETURN L_EMAIL;
      l_entry := DBMS_LDAP.next_entry(ld => l_session, msg => l_entry);  
    END LOOP ENTRY_LOOP;
    return l_email;
    EXCEPTION
        WHEN OTHERS THEN
          --No parameter found
             L_ERR_NUM := SQLCODE;
              L_ERR_MSG := SUBSTR(SQLERRM, 1, 200);
              CREATIVE_PKG.CSRSR_DEBUGGING ('LDAP.get_ldap_mail_attribute','0010-apex_ldap.get_user_attributes ERROR ' ||TO_CHAR(L_ERR_NUM)||':' ||L_ERR_MSG);
    
     END GET_LDAP_MAIL_ATTRIBUTE;
    I don't know if it is the most effective or efficient way to do it, but it works. If you know a better way to accomplish the same results, let me know.

    Robert
    http://apexjscss.blogspot.com

    Robert
  • 5. Re: APEX_LDAP.GET_ATTRIBUTES does not work when authenticated against the BASE
    Christian Neumueller Expert
    Currently Being Moderated
    Hi Robert,

    thanks for sharing your code. I think I understand now. You are binding with 'joeuser\mydomain', which can be used as distinguished name in AD. Then you search the subtree under l_ldap_base for 'cn=joeuser' and return the mail attribute of the 1st entry.

    Currently, apex_ldap.get_user_attributes always prepends "cn=" or "uid=" to the username and appends the search base for the bind, so this will not work. Your implementation seems fine to me, although you should call sys.dbms_ldap.unbind_s to free resources and there is a slight possibility that the search returns more than 1 row. You could use sys.dbms_ldap.count_entries to check the result count.

    Regards,
    Christian
  • 6. Re: APEX_LDAP.GET_ATTRIBUTES does not work when authenticated against the BASE
    sect55 Journeyer
    Currently Being Moderated
    Christian,

    Thanks for looking at my code and providing your suggestions. I have implemented your suggestions to free resources (i.e., unbind_s) and test for number of enteries (I will take the 1st entry but I will write a message to an error log if multiple entries occur so we can fix the entries on AD).

    I think I will add my experience to my blog so others can learn from my experience and not have to re-invent the wheel.

    Is apex_ldap package going to be changed for the future. It is very lmiited as you saw in this case. I have had other issues in the past as well (e.g., when the OU contained a blank)? It is lacking in functionality (i.e., since LDAP may contain several OU in the organization, it is prudent that one search from the base, i.e., throughout all the OU's).

    Robert
    http://apexjscss.blogspot.com
  • 7. Re: APEX_LDAP.GET_ATTRIBUTES does not work when authenticated against the BASE
    Christian Neumueller Expert
    Currently Being Moderated
    Robert,

    I fixed minor bugs in apex_ldap for 4.2, but did not have time to add new features to that package. LDAP authentication schemes now support username escaping and we have new API functions apex_escape.ldap_dn and apex_escape.ldap_search_filter to escape reserved LDAP characters. While I do have some ideas to improve LDAP support, there was too little time for that in 4.2.

    We are always looking for good ideas from the field. Actually, there is a site where you can add feature requests and others can vote for it:

    https://apex.oracle.com/pls/apex/f?p=55447

    Please use this app, e.g. by requesting better AD support and mentioning scenarios we do not yet cover. The more demand we see in an area, the easier it is to reserve development time, so you would be helping us help you ;-)

    Regards,
    Christian

Legend

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