With a little side of applesauce...

Tuesday, February 17, 2009

Coldfusion - comparing SQL and LDAP queries

I have a current project which maintains user information in disparate systems. In an effort to flatten my directory searching, I am creating LDAP groups to mirror the information which is currently held in multiple relative databases. Harnessing Ben Nadel's blog posting below, I have found the synchronization to be much faster!

(EDIT: Now here is the code)

    <cffunction name="sync"
returnType="void"
access="public"
output="yes"
hint="Sync LDAP group with relational database.
@param serverAddr string required ldap server
@param port string required ldap server port
@param username string required bind user
@param password string required bind password
@param peopleBaseDn string required baseDn for the people container (ie ou=people,dc=example,dc=com)
@param groupDn string required target group dn (ie cn=students,ou=groups,dc=example,dc=com)
@param dbComponentPath string required component path to CFC with our DB methods (ie cfc.edu.stolaf.sql.MySQL)
@param dbMethod string required function name which executes our SQL and returns a resultset containing dbColumn (ie getStudentsFromMySQL)
@param dbColumn string required resultset column which contains our usernames
@param filter string ldap search filter (default objectClass=*)
@param attribute string attribute which will be synchronized (default member)
@param ldapSeparator string attribute list separator (default semi-colon)
@param userObjectClass string user objectClass (default cn)

@return void">

<!--- LDAP arguments --->
<cfargument name="serverAddr" type="string" required="yes">
<cfargument name="port" type="string" required="yes">
<cfargument name="username" type="string" required="yes">
<cfargument name="password" type="string" required="yes">
<cfargument name="peopleBaseDn" type="string" required="yes">
<cfargument name="groupDn" type="string" required="yes">
<!--- DB arguments --->
<cfargument name="dbComponentPath" type="string" required="yes">
<cfargument name="dbMethod" type="string" required="yes">
<cfargument name="dbColumn" type="string" required="yes">
<!--- log handler --->
<cfargument name="logHandler" type="Any" required="yes">
<!--- optional arguments --->
<cfargument name="filter" type="string" default="objectClass=*">
<cfargument name="attribute" type="string" default="member">
<cfargument name="ldapSeparator" type="string" default=";">
<cfargument name="userObjectClass" type="string" default="cn">

<!--- dynamically instantiate the database library (db) and query the dbmethod which was passed --->
<cfobject name="cfcDb" component="#dbComponentPath#">
<cfset dbMethodStr = "cfcDb.#dbmethod#()" />
<cfset userQuery = Evaluate( dbMethodStr )>

<!--- grab the LDAP query for the group passed to the function --->
<cfobject name="cfcLdap" component="cfc.edu.stolaf.naming.Ldap.Ldap">
<cfset ldapQuery = cfcLdap.search( serverAddr, port, username, password, groupDn, attribute, filter, "base" )>

<!--- first, we check and see if LDAP has any extra users. If it does, the users don't exist anymore, and we delete them --->
<cfset m = -1 />
<cfset listName = "ldapQuery.#attribute#" />
<cfloop list="#Evaluate( listName )#" index="user" delimiters="#ldapSeparator#">
<!--- check to see if the value exists in userQuery --->
<cfset ldapUser = ListGetAt( ListGetAt( user, 1, "," ), 2, "=" )>
<cfset m = userQuery[ dbcolumn ].IndexOf( JavaCast( "string", ldapUser )) />

<!--- The user exists in LDAP, but not stobase, so we delete them from LDAP --->
<cfif m EQ -1>
<cfset cfcLdap.modify( serverAddr, port, username, password, "DELETE", groupDn, "#attribute#=#user#" )>
</cfif>
<cfset m = -1 />
</cfloop>

<cfset match = -1 />
<cfset dbQueryColumn = "userQuery.#dbColumn#" />
<cfloop query = "userQuery">
<!--- check to see if the value exists in ldapQuery --->
<cfset match = ldapQuery[ attribute ].IndexOf( JavaCast( "string", "#userObjectClass#=#Evaluate( dbQueryColumn )#,#peopleBaseDn#" )) />

<!--- The user exists in stobase, but not in LDAP, so we need to add the user to ldap --->
<cfif match EQ -1>
<cftry>
<cfset cfcLdap.modify( serverAddr, port, username, password, "ADD", groupDn, "#attribute#=#userObjectClass#=#Evaluate( dbQueryColumn )#,#peopleBaseDn#" )>
<cfcatch type="any">
<cfif cfcatch.message NEQ 'An error has occured while trying to execute modify :[LDAP: error code 20 - modify/add: member: value ##0 already exists].'>
<cfset l = "#logHandler#( cfcatch.message )">
<cfset #Evaluate( l )#>
</cfif>
</cfcatch>
</cftry>
</cfif>
<cfset match = -1 />
</cfloop>
</cffunction>


A couple of notes:

1. It relies on an LDAP CFC that I have created to simplify, (in my mind), add/delete/modify/search calls. (I'll post the LDAP CFC as well).
2. It assumes that the comparison is a Query/Query comparison, so you could pass it any method which returns a Query object.
3. It needs a log handler passed to it, (in my case, I initialize a log handler in the Application scope, so "Application.cfcLog.logger").


Here is the Ldap.cfc which syncGroup uses:

<cfcomponent name="Ldap"
displayname="Ldap"
hint="Base Ldap CFC for all basic Ldap functionality
@author Shannon Eric Peevey speeves@stolaf.edu '09">

<cffunction name="add"
returnType="void"
access="public"
output="no"
hint="create an LDAP object
@param serverAddr string required ldap server
@param port string required ldap server port
@param username string required bind user
@param password string required bind password
@param dn string required target object dn (ie uid=speeves,ou=people,dc=example,dc=com)
@param objectLdif string required Ldif defining the object
@param delimiter string delimiter LDIF separator (default semi-colon)

@return void docObject is created">

<cfargument name="serverAddr" type="string" required="yes">
<cfargument name="port" type="string" required="yes">
<cfargument name="username" type="string" required="yes">
<cfargument name="password" type="string" required="yes">
<cfargument name="dn" type="string" required="yes">
<cfargument name="objectLdif" type="string" required="yes">
<cfargument name="delimiter" type="string" default=";">

<CFLDAP
ACTION="ADD"
SERVER="#serverAddr#"
PORT="#port#"
USERNAME="#username#"
PASSWORD="#password#"
DN="#dn#"
ATTRIBUTES="#objectLdif#"
DELIMITER="#delimiter#"
>
</cffunction>

<cffunction name="delete"
returnType="void"
access="public"
output="no"
hint="del an LDAP object
@param serverAddr string required ldap server
@param port string required ldap server port
@param username string required bind user
@param password string required bind password
@param dn string required target object dn (ie uid=speeves,ou=people,dc=example,dc=com)

@return void docObject is deleted">

<cfargument name="serverAddr" type="string" required="yes">
<cfargument name="port" type="string" required="yes">
<cfargument name="username" type="string" required="yes">
<cfargument name="password" type="string" required="yes">
<cfargument name="dn" type="string" required="yes">

<CFLDAP
ACTION="DELETE"
SERVER="#serverAddr#"
PORT="#port#"
USERNAME="#username#"
PASSWORD="#password#"
DN="#dn#"
>
</cffunction>

<cffunction name="modify"
returnType="void"
access="public"
output="no"
hint="add attributes to an object, (default separator is semi-colon).
@param serverAddr string required ldap server
@param port string required ldap server port
@param username string required bind user
@param password string required bind password
@param modifyType string required replace/add/delete
@param dn string required target object dn (ie uid=speeves,ou=people,dc=example,dc=com)
@param attributes string required attribute list (ie mail=speeves@example.com;mail=speeves@cc.admin.example.com)
@param separator string attribute list separator (default semi-colon)

@return void attribute is added or removed">

<cfargument name="serverAddr" type="string" required="yes">
<cfargument name="port" type="string" required="yes">
<cfargument name="username" type="string" required="yes">
<cfargument name="password" type="string" required="yes">
<cfargument name="modifyType" type="string" required="yes">
<cfargument name="dn" type="string" required="yes">
<cfargument name="attributes" type="string" required="yes">
<cfargument name="separator" type="string" default=";">

<cfldap
SERVER="#serverAddr#"
PORT="#port#"
USERNAME="#username#"
PASSWORD="#password#"
ACTION="MODIFY"
MODIFYTYPE="#modifyType#"
DN="#dn#"
ATTRIBUTES="#attributes#"
SEPARATOR="#separator#"
>
</cffunction>

<cffunction name="search"
returnType="query"
access="public"
output="no"
hint="perform LDAP searches
@param serverAddr string required ldap server
@param port string required ldap server port
@param username string required bind user
@param password string required bind password
@param basedn string required basedn from which we search
@param attributes string attributes to return (default *)
@param filter string filter for search (default *)
@param scope string type of search subtree/onelevel/base (default SUBTREE)
@param separator string delimiter for multiple attribute returns (default |)

@return query LDAP search results">

<cfargument name="serverAddr" type="string" required="yes">
<cfargument name="port" type="string" required="yes">
<cfargument name="username" type="string" required="yes">
<cfargument name="password" type="string" required="yes">
<cfargument name="basedn" type="string" required="yes">
<cfargument name="attributes" type="string" default="*">
<cfargument name="filter" type="string" default="*">
<cfargument name="scope" type="string" default="SUBTREE">
<cfargument name="separator" type="string" default="|">

<CFLDAP
ACTION="QUERY"
NAME="searchLdap"
SERVER="#serverAddr#"
PORT="#port#"
USERNAME="#username#"
PASSWORD="#password#"
START="#basedn#"
ATTRIBUTES="#attributes#"
FILTER="#filter#"
SCOPE="#scope#"
SEPARATOR="#separator#">

<cfreturn searchLdap>
</cffunction>
</cfcomponent>


Both of these are supposed to be "global", so the easiest way to use them is to create a local CFC which handles serverAddr, port, username, password, and any other variables which may be standard for your application. On the other hand, you can just replace the cfcLdap.search() and cfcLdap.modify() calls with their equivalent CFLDAP call.

http://www.bennadel.com/blog/260-Using-ColdFusion-Query-Column-s-Java-Methods-To-Find-Values-And-Test-Existence.htm

2 comments:

Anonymous said...

Good day !.
You re, I guess , probably very interested to know how one can make real money .
There is no initial capital needed You may begin to get income with as small sum of money as 20-100 dollars.

AimTrust is what you need
AimTrust represents an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

It is based in Panama with offices everywhere: In USA, Canada, Cyprus.
Do you want to become a happy investor?
That`s your chance That`s what you desire!

I`m happy and lucky, I started to get income with the help of this company,
and I invite you to do the same. It`s all about how to choose a correct partner who uses your funds in a right way - that`s it!.
I make 2G daily, and my first investment was 500 dollars only!
It`s easy to join , just click this link http://cafadoqemo.lookseekpages.com/ytezesy.html
and lucky you`re! Let`s take our chance together to get rid of nastiness of the life

Anonymous said...

Hi !.
You may , probably curious to know how one can make real money .
There is no need to invest much at first. You may commense earning with as small sum of money as 20-100 dollars.

AimTrust is what you thought of all the time
AimTrust represents an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

Its head office is in Panama with structures everywhere: In USA, Canada, Cyprus.
Do you want to become a happy investor?
That`s your choice That`s what you wish in the long run!

I feel good, I started to take up income with the help of this company,
and I invite you to do the same. It`s all about how to choose a proper companion utilizes your savings in a right way - that`s the AimTrust!.
I make 2G daily, and what I started with was a funny sum of 500 bucks!
It`s easy to start , just click this link http://tosylogowi.bigheadhosting.net/ohujys.html
and lucky you`re! Let`s take our chance together to get rid of nastiness of the life