- 3,722,531 Users
- 2,244,331 Discussions
- 7,849,906 Comments
Forum Stats
Discussions
Categories
- 16 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 2K Databases
- 592 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 32 Multilingual Engine
- 495 MySQL Community Space
- 7 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 419 SQLcl
- 57 SQL Developer Data Modeler
- 185K SQL & PL/SQL
- 21.1K SQL Developer
- 2.3K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.5K Development Tools
- 12 DevOps
- 3K QA/Testing
- 323 Java
- 10 Java Learning Subscription
- 12 Database Connectivity
- 70 Java Community Process
- 2 Java 25
- 11 Java APIs
- 141.2K Java Development Tools
- 8 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 14 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 145 LiveLabs
- 34 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 4 Deutsche Oracle Community
- 15 Español
- 1.9K Japanese
- 3 Portuguese
Getting LDAP Data Question

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
Best 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 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>
Answers
-
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
-
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
-
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.
-
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.
-
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
-
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>
-
Billy,
Once again thank you very much!!
I did get it to work and I was able to obtain the information needed.
Thanks,
Joe