on 02-17-2014 5:47 PM
Hi Experts,
Is it possible to get the origin of an assigned privilege ? Like recovering from which Business Role or Rule the user gets the privileges?
Any advice or SQL requests will be appreciated?
Victoria,
The mxi_structure_root table should contain this information as Peter mentioned. Unfortunately there is a bit of a difference in its contents depending on which version you're using. Since its an internal tabel we don't guarantee that its contents or schema remain the same between versions or patches.
On SP9 this will list privileges and their parents and roots and intermediarys
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 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 = 'some.user'
order by U.mcMskey,A.mcMskey,ap.mcMSKEY,ar.mcMSKEY desc
One of my testusers get this result, showing the role hierarchy on the right (assignmentRootName) leading to the privilege (PRIV.A.A.A.A) and others being assigned
I can't guarantee the output on earlier versions, or that it would be the same in SP10 when that happens. I believe we have a feature request for a view into the mxi_structure_root table, and if that happens then the content would have to be nailed down and stable between releases.
Br,
Chris
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Ho Per,
Could you please update your help:
How to check if a privilege is assigned to a user? and how to check if this privilege is assigned to BR ?
Assignment Status ¦ Priv assigned to user? ¦ Priv assigned to BR?
I need to add these 3 columns to my output
User ¦ Priv ¦ ParentBR ¦ Status ¦ Priv assigned to user? ¦ Priv assigned to BR?
I appreciate your help,
Victoria
My first guess would be that the contents of mxi_structure_root table is different in your version. As I mentioned, it's an internal table and the content can vary between releases. My SP8 schema has a structure root table that is not equal to the SP9 version I'm usually working on. On SP9 the query returns all link levels, on Sp8 it gives a result that looks like yours where a role-role link level is missing.
Br,
Chris
I appreciate your help Per 🙂
Victoria
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
There are several ways to do that. Here's one example using the link from the child (this) to the parent (Other) in the link table :
select mcOtherMskeyvalue RoleName,mcThisMskeyValue Childrole from idmv_link_ext where mcOtherMskeyValue = 'ROLE.A.A.A' and mcThisOcName ='MX_ROLE'
Change mcThisOcName to MX_PRIVILEGE to see what privileges are assigned by role membership.
You can also use the attribute references, but these are just a view of the above and not as efficient and a bit more clunky:
select mcMskeyValue ChildRoles from idmv_entry_simple where mcEntryType = 'MX_ROLE' and
mcmskey in
(select mcmskey from idmv_vallink_basic where mcAttrname = 'MXREF_MX_ROLE'
and mcSearchvalue = (select convert(varchar,mcmskey) from idmv_entry_simple where mcmskeyvalue = 'ROLE.A.A.A')
)
Not sure if you consider any of these straight forward though.
-
Per Christian
There a probably a large number of ways.
You can do a recursive script which traces up the hierarchy from the role or privilege looking for anything that is in the users assigned roles/privs list.
I'm not sure if you can use the mxi_structure_root table which contains the users and where they get all their privs from (I think - don't have it in front of me).
Just make sure that you account from the fact that the person can have multiple paths to a particular assignment.
Peter
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
9 | |
7 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.