Tables holding security rights for groups

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Question:
Could you please tell me which tables in the database hold the information on security rights?
1) who are the members of a defined security group?
2) what are the rights (instance and global) for a given security group?

Answer:
Execute the following queries through a database query tool

/* query to determine the group ID*/
select id, group_name, description from CMN_SEC_GROUPS_V
where group_name like '%Portfolio%' and language_code='en'

/* query to determine the users for group ID 51052 - implicit join*/
select UG.user_id, usr.user_name, UG.GROUP_ID, grp.group_name, grp.description
from cmn_sec_user_groups UG, cmn_sec_groups_v grp, CMN_SEC_USERS usr
where UG.group_ID in (51052) and
UG.GROUP_ID = grp.ID and
ug.user_id=usr.id and
grp.LANGUAGE_CODE = 'en'
order by UG.group_id

/* query to determine the users for group ID 51052 - explicit join*/
select UG.user_id, usr.user_name, UG.GROUP_ID, grp.group_name, grp.description
from cmn_sec_user_groups UG
inner join cmn_sec_groups_v grp on UG.GROUP_ID = grp.ID
inner join CMN_SEC_USERS usr on ug.user_id=usr.id
where UG.group_ID in (51052) and
grp.LANGUAGE_CODE = 'en'
order by ug.group_id

/* here is the global rights for group ID 51052 */
select gh.group_id, rv.group_name, rv.description, gh.parent_group_id, gv.group_name, gv.description
from cmn_sec_group_hierarchies gh
inner join cmn_sec_groups_v gv on gh.parent_group_id=gv.id
inner join cmn_sec_groups_v rv on gh.group_id=rv.id
where gh.parent_group_id in (51052) and
rv.language_code='en' and
gv.language_code='en'

/* here is the instance rights for group ID 51052 */
select distinct ort.object_id, ov.name, ov.description, ov.object_type, ort.principal_id, gv.group_name, ort.right_id, rv.group_name, rv.description
from CMN_SEC_ASSGND_OBJ_PERM ort
inner join CMN_sec_objects_v ov on ort.object_id = ov.id
inner join cmn_sec_groups_v gv on ort.principal_id=gv.id
inner join cmn_sec_groups_v rv on ort.right_id=rv.id
where ort.principal_id in (51052) and
ov.language_code='en' and
gv.language_code='en' and
rv.language_code='en'

More Information:
These queries were tested on MS SQL Server 2000
Reference the Clarity Technical Reference Guide for more information on the Clarity Database schema.

Environment

Release: ESPCLA99000-13.2-Clarity-Extended Support Plus
Component: