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

Advertisements

Posted August 10, 2010 by mmdmurphy in Courion Tip

Tagged with

%d bloggers like this: