Why Doesn't My Directory Speak SQL

A regular question I get is "does OVD support SQL". And I'm sure that most of the other directory vendors get this too.

This is because either their own development experience and/or their staff developer experience has been heavily tied to SQL. This does not mean though that their developers know SQL - most often their tools/frameworks know SQL.

Here is why directory services don't support SQL directly. The reason is that SQL is not a protocol - it's a query language designed to be used by relational databases. Every relational database must provide its own client-server protocol to enable database clients to connect to the database. This is why abstraction API (JDBC, ODBC, Perl-DBI) were created. They allowed developers to do standard database operations (connect to database, do some SQL, process results, close connection) without needing to learn every single database API out there.

And since most applications use objects and not SQL itself there are
even higher level frameworks like Toplink and Hibernate that abstract
this even further.

LDAP on the other hand - specified both a client/server protocol as well as its own data access language (plus even standard object/record types - aka standard LDAP schema).

To be candid - of course an LDAP server could offer a database protocol and SQL interface to its sources. However, most LDAP implementations did not use relational databases as their storage, thus it wasn't practical. While OID does use Oracle database for storage - it's an optimized schema for LDAP not designed for general purpose relational access.

This doesn't mean there have not been attempts to help solve this quandary. One option for Java developers is to use something like JDBC-LDAP. JDBC-LDAP is a JDBC driver that converts SQL calls into LDAP. We created it at OctetString and then donated it OpenLDAP a few years ago.

While it sounds like a good idea (querying LDAP via JDBC-LDAP) - the fact is that it doesn't usually work well in real-life.

There are a few common reasons for this:

  • Clients still must be rather directory centric and thus benefit more from object abstraction using native APIs like JNDI or in certain situations a custom web service (yes, OVD can speak SOAP (and DSML and even JSON) as well as LDAP)
  • Most applications that want to use LDAP want it for authentication & authorization - which frameworks like JAAS, Oracle Access Manager (or CA Siteminder or Tivoli ), Apache modules externalize from applications
  • The database may provide a native mechanism to retrieve data from an LDAP server (Oracle Database supports DBMS_LDAP which can be used to query data from LDAP). Thus you can use your standard database tools to connect to Oracle and show LDAP in your database (useful for Virtual Private Databases or building a white pages service in APEX)
  • Future of identity access lies in identity services and thus will be commonly accessed by developers similar to the way they interact with databases through abstraction frameworks like Java Persistence Architecture (TopLink, Hibernate).
Also it could be that the application connected to the database is doing reports and sometimes, it's just easier to leverage that data in a relational format. Thus putting it in database makes sense. But that's usually more the exception than the rule when you look at most applications.

So in summary:
  • OVD supports multiple client-access protocols currently those are LDAP, HTTP, DSML (1&2) and SOAP.
  • SQL is just a query language and you need much more for a database application to use data than just a query language
  • There are options like JDBC-LDAP or DBMS_LDAP to investigate to see if they can meet tactical problems
  • While LDAP is the core identity protocol (and likely to remain that way) - the future is through Identity Services and following standards like Identity Governance Framework will prepare developers for tomorrow.

*One of our competitors likes to claim they support SQL but they don't. Because they don't actually virtualize data, they copy it to another database first. While that is fine to do if that is what you want - that is not directory virtualization, that's copying your identity data to another repository. OVD supports that as well (e.g. you could connect OVD to that database) but unlike our competition it is not a required to do so in order for OVD to function. Optionally if you are deploying Oracle Identity Manager or Unified Customer Management you can connect OVD to those repositories to expose their data as LDAP.*


Right on! Of course, OpenLDAP (www.openldap.org) offers an SQL back-end but it's hardly fast or terribly easy to use. We've been working with MySQL to support their MySQL Cluster (NDB) product but that's not just picking up your SQL data and serving it happily eaither. They both require some design etc. However, one or the other might be interesting (as they are to those who wanted them in the first place).

Posted by Marty Heyman on May 19, 2008 at 03:24 PM PDT #

Post a Comment:
Comments are closed for this entry.

This is the blog for Oracle Consulting Security North America team. Edited by Mark Wilcox - Chief Technology Officer for Oracle Consulting Security - North America.


« February 2016