Courion Tip – Role Mining Master

I know all Courion implementations are different, so this may not apply to you.   However, in our implementation we have a number of “staging” tables which are now permanent.  These are intermediates to populating the IdentityMap, but there are other tables for other purposes…. We have been collecting information about roles/ group memberships from our targets, and are ready to take a long look at role based provisioning. Using the current implementation and projecting to our eventual goal of 600+ targets, that means that we will eventually have 600 tables, one connector for each in our Role Management workflow. As targets come and go, each of these will have to be maintained as well.  There should be a better solution… and I believe I have come up with it…

So, how can we combine these different tables into ONE view? (and actually, that’s the tip. Use a VIEW). Here’s how mine is set up:

{Our active directory domain targets end in “PROD” (or test, etc.) and the native query just returns the domain name, so I appended it this way}
SELECT Domain + 'PROD' AS TargetID, GroupName, ProfileUID 
FROM dbo.Staging_ADGroup_Membership
((SELECT TOP (100) PERCENT 'OracleInternet' AS targetid, GroupName, UserName AS 'ProfileUID'
FROM dbo.Staging_OracleInternetGroups
SELECT TOP (100) PERCENT TargetID, GroupName, ProfileUID
FROM dbo.Staging_Oracle_Roles)
SELECT TOP (100) PERCENT 'OracleInternet' as TargetID, 'Base OID Login' as 'GroupName', UserName as 'ProfileUID'
FROM dbo.Staging_OracleInternet)

You might ask why Oracle Internet is in there twice – we have 2 tables, one for the base account, and one for group memberships in there. Also, Oracle_Roles refers to Oracle Databases (a completely different beast).
I saved this view as “Role_Mining_Master” since that’s what I intend it for – mining it for our role definitions. Unfortunately, I can’t show you the resulting table, but it “appears” to be one large table, and contains just about all the information about a user – what targets they have accounts on, and what group membership or roles they have on that system. No, we haven’t tackled SAP yet, but we plan to. This one view allows us to see where you have accounts, what group memberships and roles you have, on 96 targets… We have processes for maintaining the staging tables this is based on, and since this is a view, there is no maintenance to this ‘table’ – it gets updated as the underlying tables get updated…

P.S., note that I “solved” the inconsistent naming of the different columns in SQL server by using the “AS” statement, for example UserName as ‘ProfileUID’  – this ensures the data goes into the correct columns…


Posted August 6, 2010 by mmdmurphy in Courion Tip

Tagged with

%d bloggers like this: