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

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

book

Article ID: 389917

calendar_today

Updated On: 03-05-2025

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.