The Clarity Administration > License Information Portlet titled 'Rights by License Type' (ID=rightsbylicensetype) shows a list of access rights by name and code, however it does not provide the 'permission code' associated with the access right. Sometimes you need to know the 'permission code' when looking at queries from reports and portlets and you want to know what access rights are associated with the specific 'permission code' to ensure the end-user has the appropriate access rights.
One example of this was when I had to locate all the possible access rights associated with the 'CostPlanView' permission code.
I developed the following queries that can be used as a compliment to the License Information Portlets.
/* all access rights, including automatic rights */
SELECT r.right_id
, g.group_name right_name
, g.group_code right_code
, r.permission_code
, g.description right_description
, g.lic_right_type
, g.is_automatic
, g.language_code
FROM cmn_sec_groups_v g
, cmn_sec_right r
WHERE
g.ID = r.right_id
AND g.right_type IS NOT NULL
AND g.is_active = 1
AND g.language_code = 'en'
ORDER BY permission_code, right_name
;
/* without automatic rights */
SELECT r.right_id
, g.group_name right_name
, g.group_code right_code
, r.permission_code
, g.description right_description
, g.lic_right_type
, g.is_automatic
, g.language_code
FROM cmn_sec_groups_v g
, cmn_sec_right r
WHERE
g.ID = r.right_id
AND (g.is_automatic != 1 OR g.is_automatic IS NULL)
AND g.right_type IS NOT NULL
AND g.is_active = 1
AND g.language_code = 'en'
ORDER BY permission_code, right_name
;