7 Replies Latest reply on Jul 21, 2017 6:46 PM by Joe R

    Getting LDAP Data Question

    Joe R

      Version: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

       

      Hello,

       

      I need to get the Username and Email Address (if it exists) from Active Directory, but the only information I have is the First and Last names.

      It looks like the only way I can query the Active Directory is if I have the Username and Password of the user I'm trying to query.

      Is that correct?

       

      Thanks,

      Joe

        • 1. Re: Getting LDAP Data Question
          Sergei Krasnoslobodtsev

          All the parameters/properties  depend on the settings of your domain server.

          Secure transmission of user/password is a separate issue...

          For example and test:

          declare 
            retval       PLS_INTEGER;
            i pls_integer;
            my_session dbms_ldap.SESSION;
            my_attrs     DBMS_LDAP.string_collection;
            my_message   DBMS_LDAP.message;
            my_entry     DBMS_LDAP.message;
            entry_index  PLS_INTEGER;
            my_dn        VARCHAR2(256);
            my_attr_name VARCHAR2(256);
            my_ber_elmt  DBMS_LDAP.ber_element;
            attr_index   PLS_INTEGER;
            my_vals DBMS_LDAP.STRING_COLLECTION ;
            
            
          begin
            my_session := dbms_ldap.init('your ldap server', 389);
            retval := DBMS_LDAP.simple_bind_s(my_session
                                             ,'cn=test,cn=config',
                                             ,'1'
                                             );
          IF retval != DBMS_LDAP_UTL.SUCCESS THEN
              -- Handle Errors
              DBMS_OUTPUT.PUT_LINE('unbind_s returns : ' || to_char(retval));
           ELSE
              DBMS_OUTPUT.PUT_LINE(': Successful.');
           END IF;                                   
            retval := dbms_ldap.search_s(my_session,'DC=corp,DC=test,DC=com', dbms_ldap.SCOPE_SUBTREE,'sAMAccountName='||&your_account,my_attrs,0,my_message);
          -- find
           retval := DBMS_LDAP.count_entries(my_session, my_message);
           DBMS_OUTPUT.PUT_LINE(RPAD('Records ',25,' ') || ': '|| TO_CHAR(retval));
           DBMS_OUTPUT.PUT_LINE('---------------------------------------------------');
           -- begin
           my_entry := DBMS_LDAP.first_entry(my_session, my_message);
           entry_index := 1;
            
          while my_entry IS NOT NULL loop
             --curr items
             my_dn := DBMS_LDAP.get_dn(my_session, my_entry);
             DBMS_OUTPUT.PUT_LINE ('        dn: ' || my_dn); 
             my_attr_name := DBMS_LDAP.first_attribute(my_session,my_entry,my_ber_elmt);
             attr_index := 1;
             while my_attr_name IS NOT NULL loop
               my_vals := DBMS_LDAP.get_values (my_session, my_entry,my_attr_name);
               if my_vals.COUNT > 0 then
                 FOR i in my_vals.FIRST..my_vals.LAST loop
              DBMS_OUTPUT.PUT_LINE('Find      ' || my_attr_name || ' : ' || SUBSTR(my_vals(i),1,200));
                 end loop;    
               end if;
               my_attr_name := DBMS_LDAP.next_attribute(my_session,my_entry,my_ber_elmt);
               attr_index := attr_index+1;
          --
             end loop;
             my_entry := DBMS_LDAP.next_entry(my_session, my_entry);
             DBMS_OUTPUT.PUT_LINE('===================================================');
             entry_index := entry_index+1;
          end loop; 
            
          retval := DBMS_LDAP.unbind_s(my_session);
          exception when others then 
           DBMS_OUTPUT.PUT_LINE(' Error code : ' || TO_CHAR(SQLCODE));
              DBMS_OUTPUT.PUT_LINE(' Error Message : ' || SQLERRM);
              DBMS_OUTPUT.PUT_LINE(' Exception encountered .. exiting');
          retval := DBMS_LDAP.unbind_s(my_session);
            
          end;
          /
          

           

          Additional examples: DBMS_LDAP

          • 2. Re: Getting LDAP Data Question
            Joe R

            Sergey,

             

            Thank you for replying.

             

            I have a question. Is the sAMAccountName (line 30) the username?

            Because I only have the First and Last names. I don't have the username.

             

            Can this still work?

             

            Thanks,

            Joe

            • 3. Re: Getting LDAP Data Question
              handat

              The concept of a 'username' is a bit arbitrary in LDAP. Basically, almost any of a user's attribute can be used as the identifier for authentication provided it is unique and configured to do so. In AD, cn,uid, sAMAccountName (and a few more) are usually configured for that.

              • 4. Re: Getting LDAP Data Question
                Billy~Verreynne

                How are the LDAP attributes used? Not all organisations use these the same.

                 

                Referring to the filter parameter of the DBMS_LDAP.search_st() call below:

                 

                Assuming the name attribute is used for firstname and lastname, you can use:

                filter => '(&(name=Joe R))'
                

                 

                However, name is not likely unique - so your code needs to be able to deal with multiple unique DNs (distinguished names) in response.

                 

                In my company's case, initials are appended to the name, in an attempt to make it unique. So your name attribute would be "Joe R (J)". And a wildcard search filter will be needed as you do not have the full set of initials to append. Thus:

                filter => '(&(name=Joe R*))'
                

                 

                Or the name could include first, second, and last names - which means a wildcard is needed for second (and third..) names:

                filter => '(&(name=Joe * R))'
                

                 

                Or assuming givenName attribute is used (and matches the firstname you have), you can search on givenName and sn (surname) attributes:

                filter => '(&(givenName=Joe)(sn=R))'
                

                 

                Bottom line is that you need to know which LDAP attributes are used, and how they are used, then structure your filter criteria accordingly, potentially using wildcards, and be prepared to get multiple unique result sets (per DN) in response.

                • 5. Re: Getting LDAP Data Question
                  Joe R

                  Billy,

                   

                  Thank you for replying and the information.

                   

                  I information I have to use are:

                  The First Name is the givenName attribute.

                  The Last Name is the sn attribute.

                   

                  I'm trying to get:

                  The Email Address is the mail attribute (if it exists).

                  The Username is the sAMAccountName attribute.

                   

                  Thanks,

                  Joe

                  • 6. Re: Getting LDAP Data Question
                    Billy~Verreynne

                    I use a standard LDAP test function, written as a pipeline, for exploring LDAP attributes and searches - and then use the results to determine how to implement that for production use.

                     

                    Note that I have 2 DNs matching my name (due to business unit moving between companies) - but it does illustrate that your code cannot expect a single hit on given name and surname search.

                     

                    SQL> create or replace type TStrings is table of varchar2(4000);
                      2  /
                    
                    Type created.
                    
                    SQL> 
                    SQL> create or replace type TLDAPattr is object( attribute_name varchar2(50), attribute_value varchar2(4000) );
                      2  /
                    
                    Type created.
                    
                    SQL> create or replace type TLDAPattrList is table of TLDAPattr;
                      2  /
                    
                    Type created.
                    
                    SQL> create or replace function LDAPattr( filter varchar2, attributes TStrings default null ) return TLDAPattrList pipelined authid definer is
                      2  
                      3          LDAP_HOST       constant varchar2(256) := '10.20.30.40';
                      4          LDAP_PORT       constant varchar2(256) := '389';
                      5          -- LDAP credentials
                      6          LDAP_USER       constant varchar2(200) := 'username';
                      7          LDAP_PASSW      constant varchar2(200) := 'password';
                      8          LDAP_ATTR       constant TStrings :=
                      9                          new TStrings(
                     10                                  'sAMAccountName', 'mail', 'mobile', 'telephoneNumber'
                     11                          );
                     12  
                     13          retVal          integer;
                     14          ldapSession     DBMS_LDAP.session;
                     15          attrList        DBMS_LDAP.string_collection;
                     16          valList         DBMS_LDAP.string_collection;
                     17          ldapMessage     DBMS_LDAP.message;
                     18          berElement      DBMS_LDAP.ber_element;
                     19          ldapTimeout     DBMS_LDAP.timeval;
                     20  
                     21          attrName        varchar2(256);
                     22          attrDisplay     varchar2(4000);
                     23          name            varchar2(256);
                     24          attrNum         integer;
                     25          i               integer;
                     26  begin
                     27          pipe row(
                     28                  TLDAPAttr(
                     29                          'PARAM.<filter>',
                     30                          filter
                     31                  )
                     32          );
                     33          DBMS_LDAP.USE_EXCEPTION := true;
                     34  
                     35          -- create LDAP session
                     36          ldapSession := DBMS_LDAP.init(
                     37                  hostname => LDAP_HOST,
                     38                  portnum  => LDAP_PORT
                     39          );
                     40          retval := DBMS_LDAP.simple_bind_s(
                     41                  ld     => ldapSession,
                     42                  dn     => LDAP_USER,
                     43                  passwd => LDAP_PASSW
                     44          );
                     45  
                     46          -- build a distinct ordered list of attributes
                     47          i := 0;
                     48          for c in (select distinct column_value as ATTR from table(nvl(attributes,LDAP_ATTR)) order by 1) loop
                     49                  i := i + 1;
                     50                  attrList(i) := c.attr;
                     51                  attrDisplay := attrDisplay || c.attr || ' ';
                     52          end loop;
                     53          pipe row(
                     54                  TLDAPAttr(
                     55                          'PARAM:<attribute list>',
                     56                          trim(attrDisplay)
                     57                  )
                     58          );
                     59  
                     60          -- use supplied filter to search
                     61          ldapTimeout.seconds := 5;
                     62          ldapTimeout.useconds := 0;
                     63          retval := DBMS_LDAP.search_st(
                     64                  ld => ldapSession,
                     65                  base => ldap_base,
                     66                  scope => DBMS_LDAP.SCOPE_SUBTREE,
                     67                  filter => filter,
                     68                  attrs => attrList,
                     69                  attronly => 0,
                     70                  tv => ldapTimeout,
                     71                  res => ldapMessage
                     72          );
                     73  
                     74          -- is the search successful?
                     75          retval := DBMS_LDAP.count_entries(ld => ldapSession, msg => ldapMessage);
                     76          if retval = 0 then
                     77                  pipe row(
                     78                          TLDAPattr(
                     79                                  'Error',
                     80                                  'LDAP entries count is 0. No matches found for filter ['||filter||'].'
                     81                          )
                     82                  );
                     83                  retVal := DBMS_LDAP.unbind_s(ld => ldapSession);
                     84                  return;
                     85          end if;
                     86  
                     87          -- Get all the entries returned by our search.
                     88          ldapMessage := DBMS_LDAP.first_entry(ld  => ldapSession, msg => ldapMessage);
                     89  
                     90          while ldapMessage is not null
                     91          loop
                     92                  -- Get all the attributes for this entry.
                     93                  attrName := DBMS_LDAP.first_attribute(
                     94                          ld        => ldapSession,
                     95                          ldapentry => ldapMessage,
                     96                          ber_elem  => berElement
                     97                  );
                     98  
                     99                  -- output a null row for formatting an empty line between records
                    100                  pipe row( null );
                    101  
                    102                  -- output DN as unique record identifier
                    103                  pipe row(
                    104                          TLDAPattr(
                    105                                  'distinguishedName',
                    106                                  DBMS_LDAP.get_dn(ldapSession, ldapMessage)
                    107                          )
                    108                  );
                    109  
                    110                  -- now output attribute name-values found for the DN
                    111                  attrNum := 1;
                    112                  loop
                    113                          exit when attrName is null;
                    114                          exit when attrNum > attrList.Count;
                    115  
                    116                          -- Get all the values for this attribute.
                    117                          valList := DBMS_LDAP.get_values (
                    118                                  ld        => ldapSession,
                    119                                  ldapentry => ldapMessage,
                    120                                  attr      => attrName
                    121                          );
                    122  
                    123                          name := valList.First;
                    124                          while name is not null loop
                    125                                  pipe row(
                    126                                          TLDAPattr(
                    127                                                  attrName,
                    128                                                  valList(name)
                    129                                          )
                    130                                  );
                    131                                  name := valList.Next(name);
                    132                          end loop;
                    133  
                    134  
                    135                          attrName := DBMS_LDAP.next_attribute(
                    136                                  ld        => ldapSession,
                    137                                  ldapentry => ldapMessage,
                    138                                  ber_elem  => berElement
                    139                          );
                    140
                    141                          attrNum := attrNum + 1;
                    142                  end loop;
                    143
                    144                  ldapMessage := DBMS_LDAP.next_entry(ld  => ldapSession, msg => ldapMessage);
                    145          end loop;
                    146
                    147
                    148          -- Disconnect from the LDAP server.
                    149          retVal := DBMS_LDAP.unbind_s(ld => ldapSession);
                    150
                    151          return;
                    152  end;
                    153  /
                    
                    Function created.
                    
                    SQL>
                    SQL> select
                      2          *
                      3  from       table(
                      4                  LDAPattr(
                      5                          filter => '(&(givenName=Billy)(sn=Verreynne))',
                      6                          attributes => TStrings('sAMAccountName','mail')
                      7                  )
                      8  );
                    
                    ATTRIBUTE_NAME                 ATTRIBUTE_VALUE
                    ------------------------------ ---------------------------------------------
                    PARAM.<filter>                 (&(givenName=Billy)(sn=Verreynne))
                    PARAM:<attribute list>         mail sAMAccountName
                    
                    distinguishedName              CN=Billy Verreynne (B),OU=Developer,...
                    sAMAccountName                 VerreynneB
                    mail                           VerreynneB@domain1.com
                    
                    distinguishedName              CN=Billy Verreynne,OU=Contacts,....
                    mail                           Billy.Verreynne@domain2.com
                    
                    9 rows selected.
                    
                    SQL>
                    
                    • 7. Re: Getting LDAP Data Question
                      Joe R

                      Billy,

                       

                      Once again thank you very much!!

                      I did get it to work and I was able to obtain the information needed.

                       

                      Thanks,

                      Joe