User Management Error out "The user roles need to be from the same group (USER, SUPERUSER, ADMIN)"

Products

• CA Release Automation - Release Operations Center (Nolio)

Issue/Introduction

When accessing the user management in the ROC we are getting the error 
undefined: The user roles need to be  from the same group (USER, SUPERUSER, ADMIN).  

Can you advise how to determine which user/s are the problem and how to fix. 

Environment

Release : 6.x

Component : CA RELEASE AUTOMATION ADMINISTRATION

Cause

After review the result of below query

SELECT * FROM AUTHORITIES;

We identified a corrupt user entry in AUTHORITIES with username [email protected] having two associate roles which can't exist simultaneously.

Output from authorities table 

1216314000	[email protected]	ROLE_USER_ADMIN
2642592000	[email protected]	ROLE_USER

Explanation of Root Cause Analysis:

The above mentioned two roles reflects a data corruption in RA DB and hence causing the error been faced. A user either can be administrator or user and will have depicted roles. The ROLE_*_ADMIN and ROLE_USER can't co-exist simultaneously for a same user.

Resolution

Follow below steps to correct the corrupted user entry in AUTHORITIES table

1: Login to ROC UI as superuser account (any superuser(RA default) or user having superuser access)

2: Navigate to Administration -> User Management and Users 

3: Filter Users list by [email protected] and remove one of the duplicate entry for username=[email protected]

4: To be executed only when there is only one entry for username [email protected] in step 3, we will recommend to remove this user and re-import the same. Please follow below steps

   4.1 : Remove the user

   4.2 Check the authorities table to validate if the entries pertaining to username=[email protected] is removed, using below queries. The result should be 0 rows;

SELECT * from AUTHORITIES where username like '[email protected]'

   4.3 Re-import the user and grant appropriate roles.