Archive for August 2010
Our “Self Service Password Change” does not allow the end user to select which targets they want to change their password on. It was considered an additonal burden to the end user, so we have set the workflow to automatically select all your targets… HOWEVER.. Consider the following scenario…
You have all your passwords nicely synchronized, with something you can remember. You have just been given access to a target system. For instance, one of the corporate SAP systems because of a new job function. So, you need to set your password on that system, but you don’t want to reset all your passwords. But, I have already told you that we set the workflows to automatically select all your targets.
Well, what I did was make a copy of the Self Service Password Change workflow, and I called it “New Target Password Change”. By setting the IdentityMap selection to whatever you already have, but adding “AND LastPwdChange IS NULL” only your NEW targets are select. This allows you to set your new target’s password, not touching your other systems, and fulfills managements choice of automating the target selection.
Minor updates –
I now display a list of these new targets for the end user by including this custom macro in the “Password Synchronization Field Selection Form”, “Form Instructions”:
%Custom Macro.WebContent_Form_Instructions%
<br>
%Custom Macro.Get_Users_New_Target_List%
The Get_Users_New_Target_List is defined :
select CommonName , ‘<br>’ as CRLF from dbo.TargetIDMapping
where UBTName IN
(select targetid from dbo.IdentityMap where profileuid =’%Provisionee Community.User%’ and LastPwdChange is null)
Of course, you could also go under Actions / Password Reset / Action Settings / Properties and Check or Uncheck “Automate Selection of IdentityMap” to suite your needs.
In addition, there seems to be some changes in the way that Courion copies workflows, so we lost our customizations. I have a call in.
- You can add a Last Password Change column to the identity map and populate only for successful password changes. Yes, the same information could be gotten by querying the ticketing (or, in my case, since we split it off – password_ticketing) table, but this is more convenient. WARNING, if you do a COPY AND PASTE, the quotation marks will be ‘the wrong type’ please replace with the single quote that is straight up and down…
It’s just a matter of
- adding the column to the identitymap table in the database
- adding the stored procedure to populate it
- adding the trigger to the workflows in courion
- In Microsoft SQL Server Management Studio Express, connect to your database, go under Tables, and Right Mouse click on IdentityMap. Click on Modify, and in the bottom of the list of columns, add LastPwdChange and set it as SmallDateTime. Leave all other values at the default. When you close this tab, it will prompt if you want to save it or not. Obviously, you do.
- Here is the code for the Stored Procedure (for any new courion admins, Using Microsoft SQL Server Management Studio Express, connect to your database, go under Programmability, Stored Procedures, and right click on Stored Procedures. Click on “New Stored Procedure”. Paste the code below, but be sure to change the name of the database to match yours)
/****** Object: Procedure [dbo].[Update_IDMap_LastPWDChange] Script Date: 8/18/2010 1:15:12 PM ******/
USE [Name of your courion database goes here];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[Update_IDMap_LastPWDChange]
@ProfileUID varchar(25), @TargetID varchar(50)
WITH EXECUTE AS CALLER AS
BEGIN
UPDATE [IdentityMap]
SET LastPwdChange = getDate()
WHERE ProfileUID = @ProfileUID and TargetID = @TargetID
END
GO
- Now go into each workflo, Actions, Password Reset, Trigger Configuration. Click ADD, select the Microsoft ADO connector, the appropriate target (probably Courion), and the object should be ADO Trigger Object.
- Name: Update_IDMap_LastPWDChange
- Set Available to ON
- Event to Resource Success
- Leave the control type as Text Area
- Check the Active box
- and set the default value to this->
EXEC Update_IDMap_LastPWDChange ‘%Provisionee Community.User%’, ‘%TargetId%’
now TEST TEST TEST, and you should be all set.
Some of this is based on views I haven’t uploaded yet, so don’t be too confused. Also struggled with giving them useful names…
Querying the role mining master based on a few user ID’s… to determine how a role should be defined:
SELECT GroupName,TargetID, COUNT(GroupName) as ‘UserCount’
FROM Role_Mining_Master
where profileuid IN
(‘1234xyza’,’bobdogcat’)
GROUP BY GroupName, Targetid
order by UserCount desc
Shows what the role says you should have (maybe you do, and maybe you
don’t)
select * from dbo.Role_Attributes_Assigned_To_User_via_Role where profileuid=’catdog’
Shows what the role says you should have, and you have it:
select * from dbo.Roles_Stuffthatuserhasthatdidcomefromrole where profileuid =’catdog’
Shows what the role says you should have, but you don’t have it:
SELECT * FROM Role_Attributes_Assigned_To_User_via_Role
WHERE ( profileuid=’catdog’ AND (NOT EXISTS
(SELECT 1 AS Expr1
FROM Role_Mining_Master
WHERE (ProfileUID = Role_Attributes_Assigned_To_User_via_Role.ProfileUID)
AND (GroupName =Role_Attributes_Assigned_To_User_via_Role.Role_Attribute))))
Shows what you have “Of Unknown Origin” – you have it, but it isn’t because
of the role:
SELECT * FROM Role_Mining_Master
WHERE ( profileuid=’catdog’ AND (NOT EXISTS
(SELECT 1 AS Expr1 FROM
Role_Attributes_Assigned_To_User_via_Role
WHERE (ProfileUID = Role_Mining_Master.ProfileUID)
AND (Role_Attribute = Role_Mining_Master.GroupName))))
Shows everything the user has from the role mining master (EVERYTHING)
select * from role_mining_master where ProfileUID =’catdog’
I had originally thought, now that we have generated a role, to simply shove all that into the “shopping cart”. Any account or membership creations that a user ALREADY had would simply fail.
Well, it occurred to me this morning that we can query the IdentityMap and/or the “role mining master” and we will know what accounts that user already has – avoiding unnecessary processing overhead and/or confusing the end user.
More later…
Thought about it some more. Basically, I think I need:
- A view of what the user has – I have this
- A view of what the role says the user should have – again, I have this
- A view of what the user’s role says he should have AND he does have
- A view of what the users’ role says he should have and he DOESN’T have
- A view of what the user has above and beyond what the role says he should have – more for auditing and possibly refining the definition of the role. Could also be a list of latent permissions – stuff they had access to in a previous job..
As far as Active Directory goes… Well, for you to be a member of an AD group, you have to have an account in AD. This isn’t a problem for us, everyone gets an AD account when on-boarded. If this were an issue, however, it should be possible do to a cross join of some sort between the identity map and group membership defined in the role and determine that the user ALSO needs to have an AD account. Anyway, not a problem for us, but I did think of it.
We use a “shopping cart” analogy to allow the users to request access to systems. We also have a “model after” process, where they can pull up a user’s ID and select whatever accounts that other user has, and then request them. (OF COURSE, that request must then get approved before the accounts are created).
We already have these processes in place. So, what is the difference between a role and a shopping cart that has already been filled up for you? OR, between modelling your account after a ROLE definition instead of someone else? We are struggling with that very question, and haven’t been able to come up with a logical objection to using the processes we already have in place. We could “stamp” the request pre-approved, or we could simply send it thru the same approvals process that all requests are currently sent thru (both are viable options). This would allow us minimal development time….
Anyway, we are not there yet, still struggling with it.
Using the “Role Mining Master” view <proposed in one of my other posts>, it’s pretty easy to query it for what Group Memberships / Roles a set of users has on which targets, and to sort that by how many of those users have that attribute:
SELECT GroupName,TargetID, COUNT(GroupName) as 'UserCount'
FROM Role_Mining_Master where profileuid IN
('ProfileUID_1','ProfileUID_2','ProfileUID_3') <-UserID's to query
GROUP BY GroupName, Targetid order by UserCount desc
(And, yes, Role Courier does present a nice graphical interface of similar information) The “order by UserCount desc” puts the attributes that all (or almost all) users have at the top, and the attributes that the fewest number of users have at the bottom. In other words, the stuff that you would most likely want to use to define a role based on those people will be at the top, and the stuff you probably don’t want will be at the bottom.
You can, of course, put an “INSERT INTO <tablename>” at the front of this, and it will save it into your (previously prepared) SQL Server table. Then you can simply delete the rows that you have decieded should not be part of that role. Or export it to an Excel spreadsheet and email it around for approval…
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
UNION ALL
((SELECT TOP (100) PERCENT 'OracleInternet' AS targetid, GroupName, UserName AS 'ProfileUID'
FROM dbo.Staging_OracleInternetGroups
UNION ALL
SELECT TOP (100) PERCENT TargetID, GroupName, ProfileUID
FROM dbo.Staging_Oracle_Roles)
UNION ALL
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…
Dan's Tips 