Query to get a list of Security Groups containing specific access rights

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

• It is possible to go through one Security Group at a time, in the UI, to determine the access rights contained within it (Administration -> Groups)
• This can be tedious if a list of all groups that have one or more access rights of interest, has to be determined
• So, is there a query that can be used to get this information?

Resolution

The following query can be used:

SELECT
    DISTINCT(g.group_code)
FROM
    CMN_SEC_GROUPS_V gv
    JOIN cmn_sec_group_hierarchies gh ON gh.group_id = gv.id
    JOIN cmn_sec_groups g ON g.id = gh.parent_group_id
WHERE
    gv.language_code = 'en'
    AND lower(gv.group_code) LIKE '%idea%'

In this example, a list of all groups with 'idea' related global access rights in them is being determined.