Archive for August 2010

Courion Tip – New Target Password Change

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%
%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.

Posted August 22, 2010 by mmdmurphy in Courion Tip

Tagged with

Courion Tip – Add a “Last Password Changed” Date/Time stamp to Identity Map

  • 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

  1. adding the column to the identitymap table in the database
  2. adding the stored procedure to populate it
  3. 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];
CREATE PROCEDURE [dbo].[Update_IDMap_LastPWDChange]
@ProfileUID varchar(25), @TargetID varchar(50)
UPDATE [IdentityMap]
SET LastPwdChange = getDate()
WHERE ProfileUID = @ProfileUID and TargetID = @TargetID


  • 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.

Posted August 18, 2010 by mmdmurphy in Courion Tip

Tagged with

Courion Tip – Querying the Role Mining Master

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

GROUP BY GroupName, Targetid
order by UserCount desc

Shows what the role says you should have (maybe you do, and maybe you

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
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’

Posted August 10, 2010 by mmdmurphy in Courion Tip

Tagged with

Courion Tip – Roles and existing accounts

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.

Posted August 8, 2010 by mmdmurphy in Courion Tip

Tagged with

Safe Browsing from a Cafe

I don’t want to pick on Starbucks, it’s not just them. Anyway, I don’t have the answer, but I think here’s one piece:

Posted August 6, 2010 by mmdmurphy in Internet, security, tips

Tagged with

Courion Tip – Role Mining and Shopping Carts

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.

Posted August 6, 2010 by mmdmurphy in Courion Tip

Tagged with

Courion Tip – Querying the Role Mining Master

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…

Posted August 6, 2010 by mmdmurphy in Courion Tip

Tagged with