Find Groups and Group Membership And Global Rights Associated

Products

• Clarity PPM SaaS

Issue/Introduction

Are there any queries to determine the rights associated with a group to use as a comparison between groups/environments?

Resolution

To build on the previous article Find Groups and Group Membership,
here are the 3 queries to be used to review groups, group membership, and group rights.

Replace the 5XXXXX with the actual group id, obtained from the UI, e.g.
http://server/niku/nu#action:nmc.group&id=5XXXXXX

--1 Review Groups

SELECT g.id, g.group_name, g.group_code, g.group_role_type, g.description, g.is_active, g.right_type, g.is_sharable

FROM cmn_sec_groups_v g

WHERE g.id = 5XXXXXX

AND g.language_code = 'en'

 

--2 Review Group user/resource membership

SELECT u.id, u.user_name user_name, r.last_name last_name, u.first_name first_name, l.lookup_code user_status_code,

l.name user_status, r.unique_name unique_name, r.is_external

FROM cmn_sec_users u, cmn_sec_user_groups ug, srm_resources r, cmn_lookups_v l

WHERE u.id = r.user_id

AND u.id = ug.user_id

AND l.id = u.user_status_id

AND l.lookup_type = 'SEC_USER_STATUS'

AND l.language_code = 'en'

AND ug.group_id = 5XXXXXX

AND u.id != -99

AND U.IS_HIDDEN = 0

ORDER BY last_name desc

 

--3 Review group global rights

SELECT

g.id,

g.group_code,

g.group_name group_name,

g.right_type,

g.description description

FROM

cmn_sec_groups_v g,

cmn_sec_group_hierarchies h

WHERE

h.parent_group_id = 5XXXXXX

AND g.language_code = 'en'

AND h.group_id = g.id

AND (g.is_automatic IS NULL

OR g.is_automatic != 1)

AND g.right_type IS NOT NULL

and g.group_code not in (select cfa.code from cmn_feature_artifacts cfa join cmn_features cf on cf.id = cfa.feature_id and cf.enabled = 0 and cfa.type = 'RIGHT' ) AND g.is_active = 1 ORDER BY group_name