How To Query OVD, OID, DSEE Using SQL

One of the perpetual questions in LDAP is "how to query via SQL". I even wrote a post on this 3 years ago.

And while it doesn't occur very often anymore - it popped up again this week. So I suspect there might be others.


First - to be clear - SQL is very different than LDAP. SQL is simply a standardized query language for querying a relational database. Each database has a different protocol - that's why each database must provide its own database driver even for a standard connection API like JDBC (or ODBC or .NET ADO).

Second - if you have access to an Oracle database (even Oracle XE) you can use the DBMS_LDAP PL/SQL API to query an LDAP server. And a very useful trick to perform with that is to create a database view that maps to a DBMS_LDAP call. When you go this route - you can have your PL/SQL expert write one package and then anything that can connect to the view - can use the data without needing to use PL/SQL or LDAP.

Third - If you are using Java - you can use the JDBC-LDAP library. JDBC-LDAP is a JDBC driver we wrote almost a decade ago at OctetString. Because there was so little demand for it - we actually released it as open-source and donated to OpenLDAP. And you can get pre-built binaries here.

Once you have JDBC-LDAP then you can use it similar to any other JDBC driver.

And even do a SQL query - though it has a strong LDAP flavor:

ResultSet rs = stmt.executeQuery("SELECT cn, uniquemember FROM subTreeScope;dc=example,dc=com WHERE objectclass=groupofuniquenames");

This says "retrieve the cn and uniquemember attributes from any groupofuniquenames objects under the dc=example,dc=com branch"

In LDAP terms - the start of the statement lists which attributes you want (this could be * for all attributes), Scope & searchbase is set on FROM and WHERE clause is the LDAP filter.

And here is an example of what the results look like (captured from my output in Netbeans):

run:
Sort by : null
numColumns is 4
uniquemember_0:uid=kvaughan,ou=People,dc=example,dc=com
uniquemember_1:uid=rdaugherty,ou=People,dc=example,dc=com
uniquemember_2:uid=hmiller,ou=People,dc=example,dc=com
cn:Directory Administrators
uniquemember_0:uid=scarter,ou=People,dc=example,dc=com
uniquemember_1:uid=tmorris,ou=People,dc=example,dc=com
uniquemember_2:
cn:Accounting Managers
uniquemember_0:uid=kvaughan,ou=People,dc=example,dc=com
uniquemember_1:uid=cschmith,ou=People,dc=example,dc=com
uniquemember_2:
cn:HR Managers
uniquemember_0:uid=abergin,ou=People,dc=example,dc=com
uniquemember_1:uid=jwalker,ou=People,dc=example,dc=com
uniquemember_2:
cn:QA Managers
uniquemember_0:uid=kwinters,ou=People,dc=example,dc=com
uniquemember_1:uid=trigden,ou=People,dc=example,dc=com
uniquemember_2:
cn:PD Managers
BUILD SUCCESSFUL (total time: 1 second)

Posted via email from Virtual Identity Dialogue

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

bocadmin_ww

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today