privilege's hierarchy

[Former Member]

Hello,

Find this discussion, to to build the privilege's hierachy.

After testing the query, I am getting a list a roles in the list of roles which have NO relation to the user but only because this privilige is assigned these roles. in reality the user has only role1, which include priv1.

Example: Priv1 is assigned to role1 and role2. your query will show:

user     priv1      role1

user     priv1      role2

But here the problem, the query displays that the user has       priv       role2  also which is wrong ?

Any comment please?

Niad

[Steffi_Warnecke]

Hello Niad,

could you please share the SQL statement that you use to get this list?

Per's first query in the mentioned thread looks for roles that are assigned to roles. I guess you changed that part to show privileges, that are part of a role and this is working correctly. So there "just" seems to be a disconnect between that part of the statement and the user. Right?

Regards,

Steffi.

[Former Member]

Hi Steffi,

When I am using the query bellow to get MYUSER's information (priv, parent role and root role assignement ) it's correct:

select U.mcDisplayName, A.mcDisplayName assignment,L.mcAssignedDirect isDirect,SR.mcParentMskeyassignmentParentMskey,AP.mcDisplayName assignmentParentName,SR.mcRootMskey assignmentRootMskey, AR.mcDisplayNameassignmentRootName

from idmv_entry_simple U

inner join mxi_link L on L.mcThisMSkey = U.mcMSkey andL.mcOtherEntryType in (select ocId from mxi_objectclasses whereocName='MX_PRIVILEGE')

left outer join idmv_entry_simple A ON A.mcMskey = L.mcOtherMSKEY

left outer join mxi_structure_root 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 U.mcMskeyValue='MYUSER'

order by U.mcMskey,A.mcMskey,ap.mcMSKEY,ar.mcMSKEY desc

-- RESULT IS CORRECT

As I am looking for PRIVILEGE's information by user and role, at the end I just removed : .....and U.mcMskeyValue='MYUSER' and added: ...and A.mcMSKEYVALUE='priv1' . So the new query is:

select U.mcDisplayName, A.mcDisplayName assignment,L.mcAssignedDirect isDirect,SR.mcParentMskeyassignmentParentMskey,AP.mcDisplayName assignmentParentName,SR.mcRootMskey assignmentRootMskey, AR.mcDisplayNameassignmentRootName

from idmv_entry_simple U

inner join mxi_link L on L.mcThisMSkey = U.mcMSkey andL.mcOtherEntryType in (select ocId from mxi_objectclasses whereocName='MX_PRIVILEGE')

left outer join idmv_entry_simple A ON A.mcMskey = L.mcOtherMSKEY

left outer join mxi_structure_root 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 A.mcMSKEYVALUE='priv1'

order by U.mcMskey,A.mcMskey,ap.mcMSKEY,ar.mcMSKEY desc

-- RESULT IS INCORRECT

So any comment?

Thanks,

Niad

[Former Member]

Hi experts,

Any comments please why adding:

......where U.mcEntryType='MX_PERSON' and A.mcMSKEYVALUE='priv1'

is not giving the correct anwsers,

Regards,

Niad