Hi All,
We generally face the scenario where we need to retrieve the instance level access rights for a group but usually do not have the appropriate query to get it . As I got the opportunity to work with these rights, I have written a query for same. I am sharing it here so that it can help others as well.
SELECT
G.GROUP_NAME "ROLE NAME",
G1.ID,
G1.GROUP_CODE "GROUP ID",
G1.GROUP_NAME "GROUP NAME",
G1.DESCRIPTION DESCRIPTION,
CASE WHEN G1.IS_ACTIVE= 1
THEN 'ACTIVE'
ELSE 'INACTIVE'
END STATUS,
A.RIGHT_ID
FROM CMN_SEC_ASSGND_OBJ_PERM A
INNER JOIN CMN_SEC_GROUPS_V G ON A.RIGHT_ID = G.ID
INNER JOIN CMN_SEC_GROUPS_V G1 ON G1.ID =A.PRINCIPAL_ID
WHERE G.LANGUAGE_CODE = 'EN'
AND G1.LANGUAGE_CODE='EN'
AND A.PRINCIPAL_TYPE = 'GROUP'
AND G1.IS_ACTIVE=1
AND G1.GROUP_CODE='<YOUR GROUP CODE>'
Hopefully, this fulfils the purpose.