Bending SAP IDM Stored Procedures to your Will
Everything you can imagine is real.” ― Pablo Picasso
One of the things I’ve always been hesitant to do in my IDM career is play with the actual database objects such as the Stored Procedures. There really should not be a problem with using them as long as you are careful and use common sense. For example, making changes to the actual Stored Procedure code is a bad idea. At the very least your changes will be overwritten in the next update, and at worst, they could potentially break the update process (or IDM itself!) But I think as long as the basic precautions are taken, the stored procedures can be used with some effectiveness to enhance how we use IDM. The main thing is to go slowly, test what you are working with thoroughly and use examples from existing working jobs and the Stored Procedure definitions themselves so you know what is expected.
If you want to see how this is done in general, take a look at an initial load job, when IDM goes to create the account attribute in the very first pass (at least in the AS Java (Database) – Initial Load job.)
From this screen shot, we see a To Database pass and that it is possible to execute several operations in the same pass. Also the SQL updating option has been selected. This option allows the IDM engine to act as a direct gateway to the back end database where you can run almost any valid SQL command. I don’t know that there are any real exceptions to this save that whatever command is to be executed needs to have permissions for the [mxmc]_rt account.
Looking a little deeper, we see there’s a script being executed to prepare the statement called sap_care_callStoredProcedure and that it takes two arguments separated by the standard IDM delimiter of ‘!!’ Let’s take a quick look to see what it does:
// Main function: sap_core_callStoredProcedure
//
// Call/execute a stored procedure.
// Parameters (separated by “!!”):
// – Name of the stored procedure
// – List of procedure arguments (separated by “,” and string arguments enclosed in ” – actually this is the syntax accepted by MSSQL and Oracle at least)
// Note: The <prefix>_rt user/role must be allowed to execute the respective procedure!
function sap_core_callStoredProcedure(Par){
var ParComponents = Par.split(“!!”);
var procedure = ParComponents[0];
var arguments = ParComponents[1];
var dbType = “%$ddm.databasetype%”;
var result = “”;
if (dbType == 1) { // MSSQL
result = “execute ” + procedure + ” ” + arguments;
} else { // Oracle
result = “call ” + procedure + ” (” + arguments + “)”;
}
return result;
}
So basically, all this script does is break apart the pieces and then add the appropriate database command based on database type (be careful if you’re using DB2, I have not tested it, but if issues arise, please refer to this article. I’m pretty sure that the proper database command is ‘call’ since DB2 is being used in Oracle emulation mode.) When troubleshooting you might want to add in a uInfo (result) or uWarning (result) in just before the return statement, it really helps sometimes.
The nice thing about this using this script is that it makes it much easier to call the Stored Procedures and work with the various databases supported by IDM. You might also notice that when the Oracle Database is used that the stored procedure arguments are also encased in parentheses ()
For a practical example of how to use this functionality, I’m going to work with the stored procedure mxi_xcreate_objectclass. This is the stored procedure that is used by IDM to create a new EntryType. (The original architecture of IDM was heavily influenced by LDAP directories) Personally, I’ve wanted to be able to do this particular operation on some past projects where there’s been a need to create EntryTypes in multiple environments and systems, so putting this into a job helps to automate it. Also as a consultant, it helps me to “productize” some of the enhancements I develop, which makes them easier to distribute.
So calling the Stored Procedure seems pretty easy as it’s called as follows:
$FUNCTION.sap_core_callStoredProcedure(mxi_xcreate_objectclass!!1,’ZMY_ENTRYTYPE,’An EntryType Example’,’My Entry Type’,0,1,NULL)$$
However I was a little confused when I first tried to call it as I could not get the last parameter, Pocid parameter to populate correctly, so I needed to look at the code to figure things out (including the names of the parameters) This is probably also a good time to mention that there is no documentation for working with the stored procedures, so you probably should take some time to review any code before you use it. It’s a good way to learn about how IDM’s innermost workings are built and also gives you an idea of what is expected when using any of these Stored Procedures.
Pocid correlates to the MSKEY and is generated during the execution of the stored procedure. When I looked through the code and saw it was checking if that parameter was NULL, I knew how to populate it and things worked just fine.
So there’s a couple of things to consider when working with these stored procedures.
- There’s not too much documentation here, so be careful.
- Don’t’ make changes to the Stored Procedure code, comments might be OK, but remember that they will potentially be removed with the next update, so be careful.
- Working directly with the Stored Procedures has direct impact on the Identity Store with fewer built in safeguards, so make sure things are backed up if they are important, and be careful.
Are you sensing the overriding theme here? 🙂
If you’d like to take this example a step further, go ahead and use the Stored Procedures mxi_schema_create_attr_ns and mx_schema_add_attr_to_oc_ns to create a new attribute and then add it to the EntryType. Examples can be found in the SAP NetWeaver Templates (start with the Initial Load job for a repository) If you have questions, post them in comments or start a discussion thread if they are more general.
So go ahead and try and use some of the Stored Procedures to advance your IDM needs, just be careful. I’ll be working on a somewhat more complicated example next and as soon as my testing and verification is complete, I’ll be sharing again with the community.
I have not been able to test this with Version 8 yet. If anyone does, please let me know how it works and what you needed to do.
Nice write-up, Matt!
I'm always a bit nervous when using stored procedures (or any database update pass), because of my fear of totally wrecking IDM, when I use them wrong (because maybe it does not, when I think it does).
The only stored procedure I have used relatively often by now is the one for deleting jobs. And even for that I have created a documentation that I can fall back on, when I need to do it some months later. And always double-check everything again and again, before I actually execute the procedure.
Reading your blog I began to become comfortable again with the idea of using them to auto-create repository-related attributes. Buuut then I read your "be careful"s and now I'm not so sure again. 😀
I'm looking forward to your next blog on this "How the heck can I use stored procedures and not wreck my IDM?" series. 😉
Regards,
Steffi.
Well the only way to not wreck the database for sure with SP's is not to use them. 🙂 Or just start with test systems and get to know the DBAs who will do your backup and restores! I understand they can be bribed with coffee!
Creating Repositories along with related constants and variables can be done with uFunctions as I recall. That's what I'll be looking into next, but there seem to be a couple of things I'm not comfortable with until I work on them a little more and maybe have some discussions with the developers. I'll keep you posted.
Matt
Creating the repositories and their constants is no biggie for me manually, because I don't have so many systems to maintain through IDM. But creating attributes specific for the new repositories is pretty tedious, because you can't copy&paste existing attributes.
Somebody wrote here on SCN, that he had created a job to auto-create attributes. I hope to construct one, where I can point to certain existing attributes and the job creates copies of them with a different name and description etc, all pre-defined. Change one specific part of the texts to be precise. That's pretty much all I need it to do. 🙂
Sounds easy enough... in theory. ^^
Whenever I wanted to screw around with the stored procedures, I would just make copies of them, i.e. call the new script sap_core_<whatever>v2, then proceed to do whatever I wanted in DEV. If it eventually worked right, that's the script I would import into PROD. In short, while I'm not against modifying the stored procedures, I was terrified of what job or task might be using it.
Good to see you back Brandon!
I'm always hesitant to modify the SAP scripts, but if I do, I do it the way you are referring. Let my custom code use the custom scripts.
The stored procedures in the database are a different animal, and as I said above, I'd be very hesitant to change them.
Matt
Hello Matt,
in our company we are currently working with IdM 8 and I can confirm that the sap_call_storedProcedure is still the same.
We had to customize some core procedure of IdM 8 for our needings and the best practise is to customize the installer scripts:
All stored procedures are stored in the /5D-create-procs.sql file. There you can modify them to your needs or even add new ones.
On new updates you can simply add a line to the file /5D-create-procs.sql with a sql-reference (@5F-custom-procs.sql for example) and maintain these changes on version updates 🙂
Hi Aydin.
I don't know that I can support the idea of modifying any of the installer DBScripts.
Best practice is to implement IDM per the original installation media and then make changes. As I've said before, I'm a big fan of using the stored procedures, and not modifying them. If you must make changes I would make then on a copy of any database object rather than on the original.
Matt
Modifying the original SAP internal stored procedures is maybe a bad idea (if you dont know what you are doing), you are right.
Like you said: Sometimes you have use cases where you need additional stored procedures. If you reference on these stored procedures in your IdM config, on updates the procedures might get deleted and the system wont work.
To keep your systems up to date AND work with custom stored procedures, modifying the installer-scripts simplifies the "post-update-work" imho (if you have many systems to maintain)
Looks cool , but have in mind that if you compromise your DB you won't be getting any support from us (IDM Dev Team). So either have someone expirienced like Mike to do it and inform the devs (if you open a ticket) that your DB stored procedures have been changed 😉
Hi Matt,
Really good blog.
According to my experience, IdM is very flexible and open to customization.There were a couple of times, when I had to call the internal procedures, but as you mentioned you have to be very careful(and I called them directly from the SQL developer).
In addition, your blog can be very helpful, in case you want to implement some custom logic and automate the use of initial procedures.
Thanks,
Simona