Clarity

Hello All,

Can anyone tell me the name of table in which instance rights information of a user can be retrieved?

These instance rights should be provided to user through groups.

Thanks,

Pragya Singh

Check this -

https://communities.ca.com/message/2290353#2290353

https://communities.ca.com/message/241727670#241727670

https://communities.ca.com/message/120451438#120451438

NJ

https://communities.ca.com/message/120451438#120451438

NJ

And this -

https://communities.ca.com/message/17458630#17458630 - Thanks to Kathy_Fisher

--Resource Instance Rights

SELECT DISTINCT a.right_id, g.right_type, g.description description, o.name right_type_name,

g.group_name right_name, g.group_code, g.is_automatic

FROM cmn_sec_assgnd_obj_perm a, cmn_sec_groups_v g, odf_objects_v o

WHERE a.principal_id = 5000108 --resource internal ID

AND a.principal_type = 'USER'

AND a.right_id = g.id

AND g.right_type = o.right_code

AND g.language_code = 'en'

AND o.language_code = 'en'

AND g.is_active=1

ORDER BY right_name

NJ

Hello navzjoshi00,

Thank you for the links.

I checked all the links and found below query in the thread https://communities.ca.com/message/120451438#120451438 which gave the instance rights list through groups:

SELECT DISTINCT     G.ID,g.group_name   group_name,

                              g.description   description,

                              g.right_type,

                              'INSTANCE' granted_through_type,

                              g.group_name   granted_through,

                              A.PRINCIPAL_ID user_id

                              FROM CMN_SEC_ASSGND_OBJ_PERM A, CMN_SEC_GROUPS_V G

                              WHERE

                              A.PRINCIPAL_ID=5483953

                              and A.PRINCIPAL_TYPE = 'USER'

                              AND A.RIGHT_ID = G.ID

                              AND G.LANGUAGE_CODE = 'en'

                             -- AND G.ID=  pass the Rights id     

          union-- Global Access Rights Provided Via System and access provided at OBS

select 

                              g.id,g.group_name   group_name,

                              g.description   description,

                              g.right_type,

                              case when instance_type like '%OBS%' then 'OBS' else 'USER' end granted_through_type,

                              '' granted_through,

                              AR.PRINCIPAL_ID user_id

              from       cmn_sec_groups_v g,

                              cmn_sec_assgnd_right ar

              where     ar.right_id = g.id

              and AR.PRINCIPAL_ID=5483953

              and         g.language_code= 'en'

              and         (g.is_automatic != 1 OR g.is_automatic is null)

              and         g.right_type is not null

              and         g.is_active = 1

            --  and         g.id=pass the Rights id     

Thanks,

Pragya Singh

Good to know

Please mark the thread as Answered if your query has been resolved

NJ

Hi Pragya,

This is one another query I had but I havent tested this on Oracle, in case you are still interested.

select usr.id User_id,usr.user_name User_name,(case when usr.is_ldap=1 then 'LDAP' else 'NOT LDAP' END) as is_ldap,grp.group_name Group_Name,

vw.global_right Right_name,obs.BU_Group Dept,dim.res_obs as RES_OBS

from cmn_sec_user_groups usrgrp join cmn_sec_users usr on usr.id=usrgrp.user_id

join niku.CMN_SEC_GROUPS_V grp on grp.id =usrgrp.group_id

join (select a.group_name group_name,b.group_name Global_Right

from (select * from niku.CMN_SEC_GROUPS_V where language_code='en' and group_role_type='GROUP' and is_active=1 and right_type is null )a,

(select * from niku.CMN_SEC_GROUPS_V where language_code='en' and group_role_type='ROLE' and is_active=1 and  right_type='System')b,

niku.CMN_SEC_GROUP_HIERARCHIES h where h.parent_group_id=a.id and h.group_id=b.id ) vw on vw.group_name=grp.group_name

left outer join (select USR.id user_id,srm.unique_name,OBS.LEVEL2_NAME as BU_Group,OBS.PATH BU_PATH from

SRM_RESOURCES SRM

JOIN CMN_SEC_USERS USR ON USR.ID = SRM.USER_ID

LEFT OUTER JOIN

PRJ_OBS_ASSOCIATIONS *** ON ***.RECORD_ID = SRM.ID AND TABLE_NAME='SRM_RESOURCES'

LEFT OUTER JOIN

PRJ_OBS_UNITS UNI ON UNI.ID = ***.UNIT_ID and type_id = (SELECT id from prj_obs_types where unique_name='amd_depts')

JOIN

NBI_DIM_OBS OBS ON  OBS.OBS_UNIT_ID = UNI.ID) obs on obs.user_id=usr.id and obs.user_id=usrgrp.user_id

left outer join (select srm.user_id usrid,***.record_id res_id,dim.path res_obs from nbi_dim_obs dim,prj_obs_units uni,prj_obs_associations ***,srm_resources srm

where dim.obs_type_name='AMD HR Reporting Structure' and dim.obs_unit_id=uni.id and ***.unit_id=uni.id and srm.id = ***.record_id) dim on dim.usrid=usr.id

where  grp.language_code='en'

and grp.group_role_type='GROUP' and grp.is_active=1 and grp.right_type is null

UNION

SELECT AR.PRINCIPAL_ID User_id,

(SELECT USER_NAME FROM CMN_SEC_USERS WHERE ID=AR.PRINCIPAL_ID) User_name,

(SELECT (case when is_ldap=1 then 'LDAP' else 'NOT LDAP' END) FROM CMN_SEC_USERS WHERE ID=AR.PRINCIPAL_ID)

as is_ldap,

'' Group_Name, G.GROUP_NAME Right_name ,

obs.BU_Group Dept,dim.res_obs as RES_OBS

FROM CMN_SEC_GROUPS_V G,

CMN_SEC_ASSGND_RIGHT AR,

CMN_SEC_USERS CMN ,

(select USR.id user_id,srm.unique_name,OBS.LEVEL2_NAME as BU_Group,OBS.PATH BU_PATH from

SRM_RESOURCES SRM

JOIN CMN_SEC_USERS USR ON USR.ID = SRM.USER_ID

LEFT OUTER JOIN

PRJ_OBS_ASSOCIATIONS *** ON ***.RECORD_ID = SRM.ID AND TABLE_NAME='SRM_RESOURCES'

LEFT OUTER JOIN

PRJ_OBS_UNITS UNI ON UNI.ID = ***.UNIT_ID and type_id = (SELECT id from prj_obs_types where unique_name='amd_depts')

JOIN

NBI_DIM_OBS OBS ON  OBS.OBS_UNIT_ID = UNI.ID) obs ,

(select srm.user_id usrid,***.record_id res_id,dim.path res_obs from nbi_dim_obs dim,prj_obs_units uni,prj_obs_associations ***,srm_resources srm

where dim.obs_type_name='AMD HR Reporting Structure' and dim.obs_unit_id=uni.id and ***.unit_id=uni.id and srm.id = ***.record_id) dim

WHERE AR.PRINCIPAL_ID = CMN.ID

and obs.user_id=cmn.id

AND AR.PRINCIPAL_TYPE = 'USER' AND AR.INSTANCE_TYPE = 'SYSTEM'

and  dim.usrid=cmn.id

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

order by usr.user_name,grp.group_name

Thanks,

Jerin