Skip to Content

A quick guide to using SQL Queries for populating Legal Attribute Values

One of the most dynamic and popular ways to load data into attributes is to use the SQL Query method of providing attribute values. I’ve seen these hold State Names, Organizational Data, even mail server names! Attributes can even be linked together as I have written here.

The most import thing to remember here is that if the data is loaded by an IDM job, then you will need to add additional access to the mxmc_prov account.  This account handles the interface between the run-time and the presentation UI (PHP in version 7.0 and WebDynPro in 7.1 and beyond). It used to be that I would go and ask the DBAs to add this access as I thought something special needed to happen behind the scenes.  However, after talking to a knowledgeable person, I was informed that this can be done programatically.

It’s actually quite easy to do in a To Database pass type, which I illustrate below for updating a table in a Microsoft SQL Server 2008 R2 database:


Note that by selecting the “SQL Updating” option actual SQL Code can be passed from IDM to the back end database. Interestingly enough this is even somewhat simpler in an Oracle Database since the user reference is mxmc_prov.

Two things I’ve noticed along the way with this functionality:

1. In some Oracle Database scenarios, I had to “re-grant” the SELECT privilege every time I updated the table. I suspect that this is because I am using the “Delete table before loading” Option.  This is not a big deal. I’ve displayed the job outline below:


I’m not sure if this can happen in SQL Server or if it will always happen in Oracle, but it’s a good idea to keep this in mind.  Until I realized what was happening I thought I was loosing my mind and I’m pretty sure the BASIS person who was granting the access again and again thought so too! 🙂

2. Even though you can make these changes via IDM automatically, it’s still a good idea to let the DBAs and other System Administrators aware of what you have done. These changes are made to systems outside of IDM, so it’s best that they know these are “legal” and not to view these changes as a potential security breach.

You must be Logged on to comment or reply to a post.
  • Just worked with this some and realized something. When you reference the custom table from the attribute it should be in this form (MSSQL):

    SELECT Attr_Data from mxmc_rt_u.Z_Select


  • Good stuff Matt. The note about schema prefix is good point, especially if you would add more values to the standard value help table dbo.mxi_attrValueHelp with an IdM job.

    If you omit the prefix the IdM run time running the toDatabase-pass will create a new table to the run time user’s schema (on SQL Server haven’t “tested” it on Oracle). I once ended up with mxmc_rt.mxi_attrValueHelp and dbo.mxi_attrValueHelp tables and spent a while figuring out why aren’t my newly added Value Help rows visible in the UI. One of those mistakes if you do it once, you’ll remember it forever 🙂

    • Thanks, Tero, it’s something I always had trouble remembering and figuring out how to do this via a job was a life (and sanity) saver.

      One other thing I saw today was that I needed to re-grant the SELECT privilege on a MSSQL table after updating it, so maybe that’s the way it needs to be.  I’m going to consider it a best practice of mine from here on out.

      This also makes it more important than ever to keep the DBAs in the loop since they might be concerned about a large number of changes in the DB.