Hi, I agreed dave and martti statement. This is a final query, to fecth the user access rights for all scenarios as mentioned dave and also included the instance rights which directly assigned to system (e.g. resource manager). Please look at the below final query.-- Records fetch which instance access assigned thro resource Access rightsSELECT DISTINCT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",G.GROUP_NAME "Access Right Name",G.DESCRIPTION DESCRIPTION, G.RIGHT_TYPE GRANTED_THROUGH_TYPE, O.NAME GRANTED_THROUGH , '' GROUP_NAME,(SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =A.CREATED_BY) "Granted By",TO_CHAR(A.CREATED_DATE,'DD-Mon-YYYY') "Granted Date", 'Resource Access Rights: Instance' "Access Right Type"FROM CMN_SEC_ASSGND_OBJ_PERM A, CMN_SEC_GROUPS_V G, ODF_OBJECTS_V O, SRM_RESOURCES SRM WHERE SRM.USER_ID = A.PRINCIPAL_ID AND A.PRINCIPAL_ID = :CMN_ID AND A.PRINCIPAL_TYPE = 'USER' AND A.RIGHT_ID = G.IDAND G.RIGHT_TYPE = O.RIGHT_CODE AND G.LANGUAGE_CODE = 'en' AND O.LANGUAGE_CODE = 'en' AND G.IS_ACTIVE=1UNION-- Records fetch which OBS Unit access assigned thro resource Access rightsSELECT DISTINCT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name", G.GROUP_NAME "Access Right Name", G.DESCRIPTION DESCRIPTION, T.NAME || ':' || U.NAME GRANTED_THROUGH_TYPE, A.INSTANCE_TYPE GRANTED_THROUGH , ' ' GROUP_NAME,(SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =A.CREATED_BY) "Granted By",TO_CHAR(A.CREATED_DATE,'DD-Mon-YYYY') "Granted Date", 'Resource Access Rights: OBS Unit' "Access Right Type" FROM CMN_SEC_GROUPS_V G, PRJ_OBS_UNITS U, PRJ_OBS_TYPES T, CMN_SEC_ASSGND_RIGHT A, SRM_RESOURCES SRM WHERE SRM.USER_ID = A.PRINCIPAL_ID AND G.LANGUAGE_CODE='en' AND T.ID = U.TYPE_ID AND G.ID = A.RIGHT_ID AND U.ID = A.INSTANCE_IDAND A.PRINCIPAL_TYPE = 'USER' AND A.PRINCIPAL_ID = :CMN_ID AND A.RIGHT_ID = G.ID AND G.LANGUAGE_CODE = 'en'UNION---- Records fetch which Global access assigned thro resource Access rights.SELECT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",G.GROUP_NAME "Access Right Name", G.DESCRIPTION DESCRIPTION, G.RIGHT_TYPE GRANTED_THROUGH_TYPE,'USER' GRANTED_THROUGH, '' GROUP_NAME, (SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =AR.CREATED_BY) "Granted By",TO_CHAR(AR.CREATED_DATE,'DD-Mon-YYYY') "Granted Date", 'Resource Access Rights: Global' "Access Right Type"FROM CMN_SEC_GROUPS_V G, CMN_SEC_ASSGND_RIGHT AR , SRM_RESOURCES SRM WHERE SRM.USER_ID = AR.PRINCIPAL_ID AND AR.PRINCIPAL_ID = :CMN_ID AND AR.PRINCIPAL_TYPE = 'USER'AND AR.INSTANCE_TYPE = 'SYSTEM' AND G.LANGUAGE_CODE= 'en' AND AR.RIGHT_ID = G.ID AND (G.IS_AUTOMATIC != 1 OR G.IS_AUTOMATIC IS NULL) AND G.RIGHT_TYPE IS NOT NULL AND G.IS_ACTIVE = 1UNIONSELECT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",RI.GROUP_NAME "Access Right Name",RI.DESCRIPTION DESCRIPTION,RI.RIGHT_TYPE GRANTED_THROUGH_TYPE,'GROUP' GRANTED_THROUGH,G.GROUP_NAME GROUP_NAME, (SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =UG.CREATED_BY) "Granted By",TO_CHAR(UG.CREATED_DATE,'DD-Mon-YYYY') "Granted Date",'Resource Access Rights: Global' "Access Right Type" FROM CMN_SEC_GROUPS_V RI,CMN_SEC_GROUPS_V G, CMN_SEC_GROUP_FLAT_HIERS H, CMN_SEC_USER_GROUPS UG , SRM_RESOURCES SRM WHERE SRM.USER_ID = UG.USER_ID AND UG.GROUP_ID = H.PARENT_GROUP_ID AND UG.GROUP_ID = G.ID AND G.GROUP_ROLE_TYPE = 'GROUP'AND G.IS_ACTIVE = 1 AND UG.USER_ID = :CMN_ID AND G.LANGUAGE_CODE= 'en' AND RI.LANGUAGE_CODE= 'en' AND RI.ID = H.GROUP_ID AND (RI.IS_AUTOMATIC != 1 OR RI.IS_AUTOMATIC IS NULL) AND RI.RIGHT_TYPE IS NOT NULLUNIONSELECT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",G.GROUP_NAME "Access Right Name", G.DESCRIPTION DESCRIPTION, G.RIGHT_TYPE GRANTED_THROUGH_TYPE, 'OBS' GRANTED_THROUGH, U2.NAME || ':' || L.NAME GROUP_NAME, (SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =AR.CREATED_BY) "Granted By",TO_CHAR(AR.CREATED_DATE,'DD-Mon-YYYY') "Granted Date", 'Resource Access Rights: Global' "Access Right Type" FROM CMN_SEC_GROUPS_V G,CMN_SEC_ASSGND_RIGHT AR, OBS_UNIT_USERS_V U, PRJ_OBS_UNITS U2, CMN_LOOKUPS_V L , SRM_RESOURCES SRM WHERE SRM.USER_ID = AR.PRINCIPAL_ID AND AR.PRINCIPAL_ID = U.UNIT_ID AND AR.PRINCIPAL_TYPE = U.UNIT_MODE AND AR.PRINCIPAL_ID = U2.IDAND U.USER_ID = :CMN_ID AND AR.INSTANCE_TYPE = 'SYSTEM' AND G.LANGUAGE_CODE= 'en'AND AR.RIGHT_ID = G.ID AND (G.IS_AUTOMATIC != 1 OR G.IS_AUTOMATIC IS NULL) AND G.RIGHT_TYPE IS NOT NULL AND G.IS_ACTIVE = 1 AND L.LOOKUP_TYPE = 'RIGHT_PRINCIPAL_ASSOC_TYPE' AND L.LANGUAGE_CODE = 'en' AND L.LOOKUP_CODE = U.UNIT_MODEUNION-- Records fetch for Access to this Resource : ResourceSELECT DISTINCT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",G.GROUP_NAME "Access Right Name",G.DESCRIPTION DESCRIPTION, G.RIGHT_TYPE GRANTED_THROUGH_TYPE,'Resource' GRANTED_THROUGH,'' GROUP_CODE,(SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =A.CREATED_BY) "Granted By",TO_CHAR(A.CREATED_DATE,'DD-Mon-YYYY') "Granted Date", 'Access to this Resource: Resource' "Access Right Type"FROM CMN_SEC_ASSGND_OBJ_PERM A,SRM_RESOURCES R,SRM_RESOURCES R1,CMN_SEC_GROUPS_V G , SRM_RESOURCES SRM WHERE SRM.USER_ID =R1.USER_ID AND A.OBJECT_INSTANCE_ID = R1.ID ANDR1.USER_ID = :CMN_IDAND A.PRINCIPAL_TYPE = 'USER'AND A.PRINCIPAL_ID = R.USER_IDAND A.RIGHT_ID = G.IDAND G.RIGHT_TYPE = 'SRM_RESOURCES' AND A.RIGHT_ID =3710AND G.LANGUAGE_CODE = 'en'UNION-- Records fetch for Access to this Resource : GroupSELECT DISTINCT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",G.GROUP_NAME "Access Right Name", G.DESCRIPTION DESCRIPTION,G.RIGHT_TYPE GRANTED_THROUGH_TYPE,'Resource' GRANTED_THROUGH,'' GROUP_CODE,(SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =A.CREATED_BY) "Granted By",TO_CHAR(A.CREATED_DATE,'DD-Mon-YYYY') "Granted Date", 'Access to this Resource: Group' "Access Right Type" FROM CMN_SEC_ASSGND_OBJ_PERM A, CMN_SEC_GROUPS_V R, CMN_SEC_GROUPS_V G, SRM_RESOURCES SRM WHERE SRM.USER_ID = A.OBJECT_INSTANCE_ID AND A.OBJECT_INSTANCE_ID = :CMN_ID AND A.PRINCIPAL_TYPE = 'GROUP'AND A.PRINCIPAL_ID = G.ID AND A.RIGHT_ID = R.ID AND R.RIGHT_TYPE = 'SRM_RESOURCES' AND R.LANGUAGE_CODE = 'en' AND G.PRINCIPAL_TYPE IS NULLAND G.LANGUAGE_CODE = 'en'UNION-- Records fetch for Access to this Resource : OBS UnitSELECT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",G.GROUP_NAME "Access Right Name", G.DESCRIPTION DESCRIPTION,T.NAME || ':' || U.NAME GRANTED_THROUGH_TYPE,A.INSTANCE_TYPE GRANTED_THROUGH,' ' GROUP_CODE,(SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =A.CREATED_BY) "Granted By",TO_CHAR(A.CREATED_DATE,'DD-Mon-YYYY') "Granted Date", 'Access to this Resource: OBS Unit' "Access Right Type" FROM CMN_SEC_GROUPS_V G, CMN_SEC_ASSGND_RIGHT A, PRJ_OBS_UNITS U, PRJ_OBS_TYPES T, CMN_LOOKUPS L , SRM_RESOURCES SRM WHERE SRM.USER_ID = A.INSTANCE_ID AND G.LANGUAGE_CODE='en' AND G.ID = A.RIGHT_ID AND L.LOOKUP_TYPE = 'OBS_UNIT_ASSOCIATION' AND A.PRINCIPAL_TYPE = L.LOOKUP_CODEAND A.PRINCIPAL_ID = U.ID AND U.TYPE_ID = T.ID AND A.INSTANCE_ID = :CMN_ID AND A.INSTANCE_TYPE = 'INSTANCE' UNION -- INSTNCE ACCESS RIGHTS - GROUP SELECT DISTINCT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",G.GROUP_NAME "Access Right Name", G.DESCRIPTION DESCRIPTION, G.RIGHT_TYPE GRANTED_THROUGH_TYPE,O.NAME GRANTED_THROUGH,RI.GROUP_NAME GROUP_NAME, (SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =UG.CREATED_BY) "Granted By",TO_CHAR(UG.CREATED_DATE,'DD-Mon-YYYY') "Granted Date",'Group Access Rights: Instance' "Access Right Type"FROM CMN_SEC_ASSGND_OBJ_PERM A, CMN_SEC_GROUPS_V G, ODF_OBJECTS_V O, CMN_SEC_USER_GROUPS UG, CMN_SEC_GROUPS_V RI, SRM_RESOURCES SRM WHERE SRM.USER_ID = UG.USER_ID AND A.PRINCIPAL_ID =UG.GROUP_IDAND A.PRINCIPAL_TYPE = 'GROUP'AND A.RIGHT_ID = G.IDAND G.RIGHT_TYPE = O.RIGHT_CODE AND G.ID 3810 AND RI.ID = UG.GROUP_IDAND G.LANGUAGE_CODE = 'en'AND O.LANGUAGE_CODE = 'en' AND UG.USER_ID = :CMN_IDAND G.IS_ACTIVE=1 UNION-- OBS UNIT ACCESS RIGHTS - GROUPSELECT DISTINCT SRM.UNIQUE_NAME "Resource ID", SRM.FULL_NAME "Resource Name",g.group_name "Access Right Name", G.DESCRIPTION,t.name || ':' || u.name GRANTED_THROUGH_TYPE,a.instance_type GRANTED_THROUGH,RI.GROUP_NAME GROUP_NAME, (SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID =A.CREATED_BY) "Granted By",TO_CHAR(A.CREATED_DATE,'DD-Mon-YYYY') "Granted Date",'Group Access Rights: OBS Unit' "Access Right Type"FROM cmn_sec_groups_v g,prj_obs_units u,prj_obs_types t,cmn_sec_assgnd_right a, CMN_SEC_USER_GROUPS UG, CMN_SEC_GROUPS_V RI, SRM_RESOURCES SRM WHERE SRM.USER_ID = UG.USER_ID AND g.language_code='en'AND t.id = u.type_idAND g.id = a.right_idAND u.id = a.instance_id AND RI.ID = UG.GROUP_IDAND a.principal_type = 'GROUP'AND a.principal_id = ug.GROUP_ID and UG.USER_ID = :CMN_IdAND a.right_id = g.idAND g.language_code = 'en' ORDER BY "Access Right Name","Access Right Type" Note : Please pass internal id for cmn_sec_users table (cmn.id = cmn_sec_users.id) Thanks
Senthil Message Edited by senthil on 09-09-2009 04:49 AM [left]