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

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 records100                  pipe row( null );101  102                  -- output DN as unique record identifier103                  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 DN111                  attrNum := 1;112                  loop113                          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      => attrName121                          );122  123                          name := valList.First;124                          while name is not null loop125                                  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  => berElement139                          );140141                          attrNum := attrNum + 1;142                  end loop;143144                  ldapMessage := DBMS_LDAP.next_entry(ld  => ldapSession, msg => ldapMessage);145          end loop;146147148          -- Disconnect from the LDAP server.149          retVal := DBMS_LDAP.unbind_s(ld => ldapSession);150151          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 sAMAccountNamedistinguishedName              CN=Billy Verreynne (B),OU=Developer,...sAMAccountName                 VerreynneBmail                           [email protected]distinguishedName              CN=Billy Verreynne,OU=Contacts,....mail                           [email protected]9 rows selected.SQL>

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

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

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.

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.

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

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 records100                  pipe row( null );101  102                  -- output DN as unique record identifier103                  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 DN111                  attrNum := 1;112                  loop113                          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      => attrName121                          );122  123                          name := valList.First;124                          while name is not null loop125                                  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  => berElement139                          );140141                          attrNum := attrNum + 1;142                  end loop;143144                  ldapMessage := DBMS_LDAP.next_entry(ld  => ldapSession, msg => ldapMessage);145          end loop;146147148          -- Disconnect from the LDAP server.149          retVal := DBMS_LDAP.unbind_s(ld => ldapSession);150151          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 sAMAccountNamedistinguishedName              CN=Billy Verreynne (B),OU=Developer,...sAMAccountName                 VerreynneBmail                           [email protected]distinguishedName              CN=Billy Verreynne,OU=Contacts,....mail                           [email protected]9 rows selected.SQL>

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