Find Groups and Group Membership

Products

• Clarity PPM SaaS

Issue/Introduction

Are there any queries to determine which users are part of a group?

Resolution

The following queries can be used to discover more about users/groups

--Find out groups listed in Classic > Administration > Organization and Access > Groups

SELECT
    g.id,
    g.group_name || ' , id=' ||
    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 1=1
--AND g.id = 5xxxxxxxx
--AND g.GROUP_NAME IN ('%fill_this_in%')
AND g.language_code = 'en'

--2 users and groups associated 
select
 c.user_id, c.unique_name, c.first_name, c.last_name,
 g.id group_id,
 g.group_name group_name,
 g.description description,
 g.is_active
from
 cmn_sec_groups_v g,
 cmn_sec_user_groups ug,
 srm_resources c
where ug.user_id = c.user_id
 and g.language_code = 'en'
 and g.group_role_type = 'GROUP'
 and ug.group_id = g.id
 and ug.user_id in (5aaaaaa,5bbbbbb) 
 and g.principal_type is null
order by
 ug.user_id