The licensing portlet shows licenses associated to an user that can not be found on the user profile. How to deal with orphan license entries

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

I Go to Administration, Organization and Access: License Information portlets.

When looking to the User Count by License Type portlet and accessing the Full type of license I can see a user listed that contains an access right that can not be found at user level:

Administration, Organization and Access: Resources, Resource's Access Rights tab. Checking all pages where rights can show up.

There is a discrepancy between the license shown in the License Information portlet and Resource's Access Rights tab.

Somehow there is an orphan record on License Information portlet. How do I deal with this orphan access right entry?

Environment

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

Resolution

Run the following select query against the affected userID:

SELECT q.*, s.group_name, s.description Description 

FROM (SELECT '1' query, r.principal_id user_id, l.lic_right_type, l.licence_priority, r.right_id right_id
      FROM cmn_sec_assgnd_right r, cmn_lic_right_v l
      WHERE r.right_id = l.id AND principal_type = 'USER' AND instance_type = 'SYSTEM'
      UNION
      SELECT '2' query, ug.user_id, l.lic_right_type, l.licence_priority, gh.group_id right_id
      FROM cmn_sec_user_groups ug, cmn_lic_right_v l, cmn_sec_group_hierarchies gh, cmn_sec_groups g
      WHERE gh.group_id = l.id AND ug.group_id = gh.parent_group_id AND g.id = ug.group_id AND g.is_active = 1
      UNION
      SELECT '3' query, r.user_id user_id, l.lic_right_type, l.licence_priority, os.right_id right_id
      FROM cmn_lic_right_v l, cmn_sec_assgnd_right os, obs_unit_entities_v ou, srm_resources r
      WHERE os.principal_type = ou.unit_mode AND os.principal_id = ou.unit_id AND os.instance_type = 'SYSTEM' AND os.right_id = l.id AND ou.instance_id = r.id AND ou.table_name = 'SRM_RESOURCES'
      UNION
      SELECT '4' query, r.principal_id user_id, l.lic_right_type, l.licence_priority, r.right_id right_id
      FROM cmn_sec_assgnd_right r, cmn_lic_right_v l
      WHERE r.right_id = l.id AND principal_type = 'USER' AND instance_type LIKE 'OBS%'
      UNION
      SELECT '5' query, ug.user_id, l.lic_right_type, l.licence_priority, r.right_id right_id
      FROM cmn_sec_assgnd_right r, cmn_lic_right_v l, cmn_sec_user_groups ug, cmn_sec_groups g
      WHERE r.right_id = l.id AND r.principal_type = 'GROUP' AND r.principal_id = ug.group_id AND r.instance_type != 'SYSTEM' AND g.id = ug.group_id AND g.is_active = 1
      UNION
      SELECT '6' query, r.user_id user_id, l.lic_right_type, l.licence_priority, os.right_id right_id
      FROM cmn_sec_assgnd_right os, obs_unit_entities_v ou, srm_resources r, cmn_lic_right_v l
      WHERE os.principal_type = ou.unit_mode AND os.principal_id = ou.unit_id AND os.instance_type LIKE 'OBS%' AND os.right_id = l.id AND ou.instance_id = r.id AND ou.table_name = 'SRM_RESOURCES'
      UNION
      SELECT '7' query, principal_id user_id, l.lic_right_type, l.licence_priority, l.id right_id
      FROM cmn_sec_assgnd_obj_perm aop, cmn_lic_right_v l
      WHERE aop.principal_type = 'USER' AND l.id = right_id
      UNION
      SELECT '8' query, ug.user_id, l.lic_right_type, l.licence_priority, l.id right_id
      FROM cmn_sec_assgnd_obj_perm aop, cmn_lic_right_v l, cmn_sec_user_groups ug, cmn_sec_groups g
      WHERE aop.principal_type = 'GROUP' AND l.id = right_id AND aop.principal_id = ug.group_id AND g.id = ug.group_id AND g.is_active = 1
      UNION
      SELECT '9' query, r.user_id user_id, l.lic_right_type, l.licence_priority, os.right_id right_id
      FROM cmn_sec_assgnd_right os, obs_unit_entities_v ou, srm_resources r, cmn_lic_right_v l
      WHERE os.principal_type = ou.unit_mode AND os.principal_id = ou.unit_id AND os.instance_type = 'INSTANCE' AND os.right_id = l.id AND ou.instance_id = r.id AND ou.table_name = 'SRM_RESOURCES') q
INNER JOIN cmn_sec_groups_v s
ON ( s.id = q.right_id AND s.language_code = 'en' )
WHERE user_id = 5007518  

The query above will return every access right associated to that specific user. Make sure the right userID is used in the WHERE clause criteria.

Note the 'right_ID' of the affected access right that you find on the License Information and is missing from the user profile.

Once we have identified the orphan record, please run the following delete statement (where Principal_ID is the affected user_ID) :

NOTE: This will permanently delete the access right for the user.  Be sure this this the one you want to remove. To reassign the right, use the UI.

delete from CMN_SEC_ASSGND_OBJ_PERM
where RIGHT_ID=51300
and PRINCIPAL_ID= 5007518