Hi,
I need to display the Group, Users corresponding to Group, Cube the user has access to(Plan Type), Object Type, Object Name and the Access Level. By going through different blogs i am able to display Group, User, Object Type, Object Name and Access Level with the following query.
SELECT G.OBJECT_NAME "Group"
,U.OBJECT_NAME "User"
,OT.TYPE_NAME "Object Type"
,O.OBJECT_NAME "Object Name"
,CASE AC.ACCESS_MODE WHEN 3 THEN 'Read/Write'
WHEN 2 THEN 'Write'
WHEN 1 THEN 'Read'
WHEN -1 THEN 'None'
ELSE 'Unknown'
END AS "Access Level"
FROM HSP_ACCESS_CONTROL AC
,HSP_OBJECT U
,HSP_OBJECT O
,HSP_OBJECT_TYPE OT
,HSP_OBJECT G
,HSP_USERSINGROUP UG
WHERE AC.USER_ID = UG.GROUP_ID
AND UG.USER_ID = U.OBJECT_ID
AND AC.OBJECT_ID = O.OBJECT_ID
AND UG.GROUP_ID = G.OBJECT_ID
AND O.OBJECT_TYPE = OT.OBJECT_TYPE
ORDER BY 1,2,3,4
To get the Plan Type information i believe the above query has to be linked to the HSP_PLAN_TYPE table. But i am not sure what is the proper link. I also observed that there is link between HSP_PLAN_TYPE table to HSP_VERSION (HSP_PLAN_TYPE.PLAN_TYPE = HSP_VERSION.IN_USE) in turn HSP_VERSION can be linked to HSP_OBJECT table.similarly
HSP_PLAN_TYPE table to HSP_ACCOUNT(HSP_PLAN_TYPE.PLAN_TYPE = HSP_ACCOUNT.USED_IN) in turn HSP_ACCOUNT can be linked to HSP_OBJECT table,
HSP_PLAN_TYPE table to HSP_ENTITY(HSP_PLAN_TYPE.PLAN_TYPE = HSP_ENTITY.USED_IN) in turn HSP_ENTITY can be linked to HSP_OBJECT table but i don't see the link for HSP_SCENARIO table and dimension tables. I also tried to see if i can go in the route of HSP_CUBES but i am not sure what are the correct links/joins i need to use.
Appreciate your suggestions on this.
Thank you
Ashok