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.