Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Getting LDAP Data Question

Joe RJul 19 2017 — edited Jul 21 2017

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
This post has been answered by Billy Verreynne on Jul 21 2017
Jump to Answer

Comments

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

1 person found this helpful
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.

1 person found this helpful
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
Answer

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>
Marked as Answer by Joe R · Sep 27 2020
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

1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 18 2017
Added on Jul 19 2017
7 comments
2,283 views