Q&A: Using Database Stored Procedures

JV writes:

In my company there is a Stored Procedure I should call from IDM. This
SP do the provision in SQL Server resource (they want to take
advantage from this automation).

Researching I’ve found out some ideas but I think it could be a
simpler way to do it.

In Sun Identity Manager, the default database resource adapters or connectors do not really offer you much ability to control the way the data is retrieved or added to a table in the database.  In general, the standard insert, select, update and delete SQL commands are used.  (And in many cases without the use of bind varibles!  Bad Sun developer!  Bad!)  If you want to use custom stored procedures in a database then the solution is to use the Scripted JDBC Adapter.  (At the time of this post there was no  Scripted JDBC resource connector for IDM 8.1, only the old 7.1 resource adapter format.  IDM 8.1 is backwards compatible with the old resource adapter format so it should not be an issue.)

The Scripted JDBC Adapter is a fairly customizable resource adapter.  I tend to use it for all my database resources regardless of the type simply because of the power and flexibility of the adapter.  I’ve found that being able to update the adapter to support new business requirements very handy in minimizing the development effort.

With the scripted JDBC adapter, it is up to the IDM developer to implement all aspects of the database interaction.  The actions that IDM exposes to you are:

• create
• getUser
• delete
• update
• enable
• disable
• listAll
• getAccountIterator
• getActiveSyncIterator
• authenticate
• test

Strictly speaking none of the actions are required to be implemented.  If an action isn’t implemented then IDM will not be able to perform the function for that specific resource.  (For example, if the delete action was not implemented the IDM could update a user but never delete them.)

In order to implement an action one must develop either a JavaScript or a BeanShell script that executes the stored procedure using regular JDBC calls to the database.  Personally, I prefer BeanShell since it’s more Java-like but it’s up to you.  What that means is when you call an IDM function, such as enable, IDM will in turn call your script to perform the enable on the resource.  The same is true for the other actions.

One thing to remember is the Scripted JDBC adapter is very useful for collapsing complex database structures into a flat attribute map.  Using the Scripted JDBC adapter you can perform the complex joins you need and create a single attribute map.  This reduces the need to create multiple database table resource adapters and have some crazy logic within an IDM form or workflow.

Conveniently, there is a sample BeanShell script using stored procedures for the Scripted JDBC Adapter in the samples/ScriptedJDBC/StoredProc/beanshell directory of your IDM 8.1 installation.  The examples there are very good and pretty clear how to implement a BeanShell script with stored procedures.

So, to answer JV’s question: use the Scripted JDBC adapter and implement your stored procedure calls using a BeanShell or JavaScript script.  As you can see there is a huge amount of flexibility offered by using the scripted JDBC adapter and I recommend anyone doing any database resource interaction to use it rather than the default database resource adapters.  There’s simply more control offered.