Privilege hierarchy

[Former Member]

Hi experts,

I am working in idm 7.2 SP7 and there is no plan to upgrade to SP08 or SP09 at the moment.

Here is my problem:

After making some search I find the internal table MXI_STRUCTURE_ROOT can give us the parent and the root of the privileges and assigned roles but they are not correct as each time I am getting the top root of roles and not the intermediate.

I am looking for a workaround how to avoid to use this table and get my privilege/role hierarchy ?

I find and and no workaround was exposed.

Thank you,

Nina

[Former Member · Accepted solution]

For those not able to upgrade to a version that includes intermediary roles in structure root, this creates a temporary table #STRUCT that does contain all the levels:

DECLARE @MSKEY int, @MSKEYV VARCHAR(255),@ROOTMSKEY int,@level int,@MAXLEVEL int,@Mylevel int, @Lnum int
SET @MAXLEVEL = 20
-- Not setting below values returns all users.
SET @MSKEY = NULL -- or add user mskey, such as 58
SET @MSKEYV = NULL -- or add user mskeyvlaue, such as 'User.Test.A'


-- Create temporary table
CREATE TABLE #STRUCT (mcChildMskey INT, mcRootMskey INT, mcParentMskey INT, mcEntryType INT, mcLevel INT)


-- Insert first level links
insert into #STRUCT (mcChildMskey, mcRootMskey, mcParentMskey, mcEntryType, mcLevel)
    select A.mcThisMskey, A.mcOtherMskey, A.mcOtherMskey, A.mcthisentrytype, 1 from mxi_link A with (nolock)
    where not exists (select 1 from mxi_link B with (nolock) where  A.mcOtherMskey = B.mcThisMskey and B.mcLinkType = 1 AND B.mcLinkState = 0)
    and   A.mcLinkType = 1 AND A.mcLinkState = 0
    and   A.mcAttrId in (select attr_id from mxi_attributes with (nolock) where AttrName = 'MXREF_MX_ROLE')
    and   A.mcotherentrytype in (select ocid from mxi_objectclasses with (nolock) where ocName = 'MX_ROLE')
    and   A.mcthisentrytype in (select ocid from mxi_objectclasses with (nolock) where ocName in ('MX_ROLE', 'MX_PRIVILEGE'))


  -- Other level links
  set @Mylevel = 2
  while @Mylevel < @MAXLEVEL
  begin
  insert into #STRUCT (mcChildMskey, mcRootMskey, mcParentMskey, mcEntryType, mcLevel)
  select A.mcThisMskey, A.mcOtherMskey, A.mcOtherMskey, A.mcthisentrytype, @Mylevel from mxi_link A  with (nolock)
  where A.mcLinkType = 1 and A.mcLinkState = 0
  and   A.mcattrid in (select attr_id from mxi_attributes with (nolock) where AttrName = 'MXREF_MX_ROLE')
  and   A.mcotherentrytype in (select ocid from mxi_objectclasses with (nolock) where ocName = 'MX_ROLE')
  and   A.mcthisentrytype in (select ocid from mxi_objectclasses with (nolock) where ocName in ('MX_ROLE', 'MX_PRIVILEGE'))
  and   A.mcOtherMskey in (select B.mcChildMskey from #STRUCT B with (nolock) where B.mcLevel = @Mylevel - 1)
  and   not exists (select 1 from #STRUCT C with (nolock) where A.mcThisMskey = C.mcChildMskey and A.mcOtherMskey = C.mcRootMskey and A.mcOtherMskey = C.mcParentMskey)
  set @Lnum = @@Rowcount


  -- Build inherit link structure
  insert into #STRUCT (mcChildMskey, mcRootMskey, mcParentMskey, mcEntryType, mcLevel)
  select distinct A.mcChildMskey, B.mcRootMskey, A.mcParentMskey, A.mcEntryType, 0 from #STRUCT A with (nolock)
  inner join #STRUCT B with (nolock) on A.mcParentMskey = B.mcChildMskey
  where A.mcLevel > 1
  and   not exists (select 1 from #STRUCT C with (nolock) where A.mcChildMskey = C.mcChildMskey and B.mcRootMskey = C.mcRootMskey and A.mcParentMskey = C.mcParentMskey)


  if @Lnum = 0
  begin
  break
  end
  set @Mylevel = @Mylevel + 1
  end
  -- Update sub tree nodes to level 0.
  update #STRUCT set mcLevel = 0 where mcLevel > 1


  select U.mcDisplayName, A.mcDisplayName assignment, L.mcAssignedDirect isDirect,SR.mcParentMskey assignmentParentMskey,AP.mcDisplayName assignmentParentName, SR.mcRootMskey assignmentRootMskey, AR.mcDisplayName assignmentRootName
  from idmv_entry_simple U
  inner join mxi_link L on L.mcThisMSkey = U.mcMSkey and L.mcOtherEntryType in (select ocId from mxi_objectclasses where ocName='MX_PRIVILEGE')
  left outer join idmv_entry_simple A ON A.mcMskey = L.mcOtherMSKEY
  left outer join #STRUCT SR ON SR.mcChildMskey = A.mcMSKEY
  left outer join idmv_entry_simple AP ON AP.mcMSKEY = SR.mcParentMskey
  left outer join idmv_entry_simple AR ON AR.mcMSKEY = SR.mcRootMskey
  where U.mcEntryType='MX_PERSON'
  AND ((@MSKEYV IS NOT NULL AND U.mcMskeyValue = @MSKEYV) OR (@MSKEY IS NOT NULL AND U.mcMskey = @MSKEY) OR (@MSKEY IS NULL AND @MSKEYV IS NULL))
  order by U.mcMskey,A.mcMskey,ap.mcMSKEY,ar.mcMSKEY desc


-- Clean up
DROP TABLE #STRUCT

-

Chris

Message was edited by: Per Krabsetsve

[Former Member · Accepted solution]

Hi Chris,

You are the best! Excellent,

Nina

[Former Member]

Any comment or help on this issue please?

Nina

[peterwass]

If you want the complete hierarchy, you'll need to write some javascript to do a recursive walk up the tree for each privilege.  MXI_STRUCTURE_ROOT is only useful if you want where it ultimately comes from

Peter