5 Replies Latest reply on Jun 28, 2011 11:30 AM by patfmnd

    Searching LDAP attributes after Authentication

    patfmnd
      All,
      Thanks to Tyler Muth's blog posting on Secure LDAP, I have been able to get authentication working with our SunOne LDAP using a custom authentication scheme. Apex does not natively support SSL authentication where you have to use a secure bind to LDAP with a service DN (which has its own service userID and password) before passing on username, password of the actual user.

      Now I am trying to retrieve attributes on that authenticated user -- attributes that are in the LDAP directory like, department, title, etc. Does anyone have some suggestions -- code examples would be great? I looked at the example in Pro Application Express book but what confuses me is how do you pass the username that you already have from the authentication back to a function call to do the 'dbms_ldap.search'. The authentication function closes the LDAP session once the user is authenticated. So now I am in the application with that 'APP_USER'. I believe I now have to do the following steps but not sure how to accomplish them:

      1.) I need to re-open an LDAP session ( I assume I still will need to re-bind with LDAP using my service Dn and service password as in the custom Auth that was just done).
      2.) I need to pass to the LDAP search the current 'APP_USER and start retrieving the other attributes-- our LDAP admin says that I can perform the search on that 'UID" as it is in our LDAP store.
      3.) There is code on a custom LDAPQuery function (built on dbms_ldap.search) in "Pro Oracle App Express" but it sends the data to a table and then does a query on the table to retrieve attributes.

      The code in "Pro App Express" does not require that initial Service DN (service username, password) secure bind (assigned to me by our LDAP admin). It looks like it is assuming the binding user is the user whose attributes are sent in the LDAPQuery function call (user and password parameters), but that is not the case in my situation. I want to query based on the currently logged in 'APP_USER' and retrieve attribute data into Form input items on the page the user has just authenticated into.

      Any help would be appreciated -- especially if you have done this already and have some sample code!

      Thanks,
      Pat
        • 1. Re: Searching LDAP attributes after Authentication
          828941
          Hi,

          You need to include the logic u mentioned above in Post Login Process...



          1.) I need to re-open an LDAP session ( I assume I still will need to re-bind with LDAP using my service Dn and service password as in the custom Auth that was just done).



          Yes
            l_session := dbms_ldap.init( ldap_host , ldap_port);
            retval := dbms_ldap.simple_bind_s(l_session , ldap_admin , ldap_admin_password);
          2.) I need to pass to the LDAP search the current 'APP_USER and start retrieving the other attributes-- our LDAP admin says that I can perform the search on that 'UID" as it is in our LDAP store.

          Yes.
          retval := dbms_ldap.search_s( l_session ,
                                          ldap_base ,
                                          dbms_ldap.scope_subtree ,
                                          '(&(objectClass=user)(samAccountName=' || :APP_USER || '))',
                                          l_attrs ,
                                          0 ,
                                          l_message);
          
            l_entry := dbms_ldap.first_entry(  l_session , l_message);
            l_dn := dbms_ldap.get_dn( l_session , l_entry);
            l_attr_name := dbms_ldap.first_attribute(l_session , l_entry ,  l_ber_elmt);
            l_vals := dbms_ldap.get_values( l_session , l_entry , l_attr_name);
            :F106_disp_name :=  l_vals(0) ;
            l_attr_name := dbms_ldap.next_attribute(l_session , l_entry ,  l_ber_elmt);  
            l_vals := dbms_ldap.get_values( l_session , l_entry , l_attr_name);
            retval := dbms_ldap.unbind_s( l_session );
            :f106_email := l_vals(0);
          Regards,
          Shijesh
          • 2. Re: Searching LDAP attributes after Authentication
            patfmnd
            Shijesh,
            I will build on this and let you know how it works out.

            Appreciate your quick response!

            Pat
            • 3. Re: Searching LDAP attributes after Authentication
              patfmnd
              Hi, Shijesh,
              I first wanted to test out the LDAP search outside of Apex and tried the following anonymous block:

              Declare
              retval PLS_INTEGER;
              l_dn VARCHAR2(1000);
              l_session DBMS_LDAP.session;
              l_attrs DBMS_LDAP.string_collection;
              l_message DBMS_LDAP.message;
              l_entry DBMS_LDAP.message;
              l_attr_name VARCHAR2(256);
              l_vals DBMS_LDAP.string_collection;
              l_ber_elmt DBMS_LDAP.ber_element;
              begin
              ---next 3 lines were set to be exactly same as used in my LDAP authentication which works fine with pre-defined global for host and port
              ---I put my own UID in for 'user_id'
              l_session := dbms_ldap.init(ldap_globals.g_host, ldap_globals.g_port);
              retval := DBMS_LDAP.open_ssl(l_session,'file:/var/opt/ORACLE/wallet_location','wallet_pswd',2);
              retval := dbms_ldap.simple_bind_s( l_session, 'ldab_admin_user', 'admin_pswd')
              l_attrs := 'ndtitle, title,nddepartment';
              retval := dbms_ldap.search_s( l_session ,
              ldap_globals.g_search_base,
              dbms_ldap.scope_subtree ,
              '(uid=user_id)',
              l_attrs ,
              0 ,
              l_message);
              l_entry := dbms_ldap.first_entry( l_session , l_message);
              l_dn := dbms_ldap.get_dn( l_session , l_entry);
              l_attr_name := dbms_ldap.first_attribute(l_session , l_entry , l_ber_elmt);
              dbms_output.put_line ('Attribute:' || l_attr_name) ;
              l_vals := dbms_ldap.get_values( l_session , l_entry , l_attr_name);
              dbms_output.put_line('Value:' || l_vals(0));
              l_attr_name := dbms_ldap.next_attribute(l_session , l_entry , l_ber_elmt);
              dbms_output.put_line ('Attribute:' || l_attr_name) ;
              l_vals := dbms_ldap.get_values( l_session , l_entry , l_attr_name);
              dbms_output.put_line('Value:' || l_vals(0));
              l_attr_name := dbms_ldap.next_attribute(l_session , l_entry , l_ber_elmt);
              dbms_output.put_line ('Attribute:' || l_attr_name) ;
              l_vals := dbms_ldap.get_values( l_session , l_entry , l_attr_name);
              dbms_output.put_line('Value:' || l_vals(0));
              retval := dbms_ldap.unbind_s( l_session );
              end;

              I get the following error which is pointing to the line with my 'dbms_ldap.simple_bind_s' which was copied exactly from my authentication function which works.

              ORA-06550: line 15, column 14:
              PLS-00382: expression is of wrong type
              ORA-06550: line 15, column 3:
              PL/SQL: Statement ignored

              Do you see what might be causing the error?

              Thanks,
              Pat
              • 4. Re: Searching LDAP attributes after Authentication
                828941
                Hi,

                try changing this line

                l_attrs := 'ndtitle, title,nddepartment';

                to

                l_attrs(1) := 'ndtitle';
                l_attr(2) := 'title' ;
                l_attr(3) := nddepartment';


                Regards,
                Shijesh
                • 5. Re: Searching LDAP attributes after Authentication
                  patfmnd
                  Shijesh,
                  That worked! Thank you so much. Now I will work to integrate with Apex.

                  Pat