An Oracle blog about Consulting Security Corner

  • March 29, 2011

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):

Sort by : null
numColumns is 4
cn:Directory Administrators
cn:Accounting Managers
cn:HR Managers
cn:QA Managers
cn:PD Managers
BUILD SUCCESSFUL (total time: 1 second)

Posted via email from Virtual Identity Dialogue

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Integrated Cloud Applications & Platform Services