Cannot administer users without roles

Document ID : KB000009093
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

The following problem has seen in the portal version 3.5.

When we are managing users (modifying or creating new users) in the section /admin (CMS (Content Management System)) of the portal, if we do not select a role for the user (at least one) the user disappears from all the list in this section. 

 

We suppose all of this is happening because we are not selecting at least one role for the user, and the web validations are not working properly because they are allowing us to submit the changes without a user selected. 
Meanwhile if we access through the portal we correctly log in to the portal and in my profile we have all the data from the user. 

Environment:
Portal 3.5
Cause:

User was created without a role so it could not be seen from the Manage Users page. 

Resolution:

If I create a user without assigning a role to it, the user is created in the database, but when I try to view the users in the "Manage Users page" I can only see the users that have a role assigned to them. 

I believe this happens because the Portal application is executing a query, specifically, a query that does an INNER JOIN between the cmsuser, cmsuserroles and cmsroles tables. Because the operation is an INNER JOIN a user that has no role assigned to it (no entries in the cmsuserroles table for the specific user id) will not be returned by the query and it will not show up in the webpage. 

So can you try the following commands in the portals privileged shell : 

>> mysql -e "SELECT * FROM lrs.cmsusers AS u LEFT JOIN (lrs.cmsuserroles AS ur INNER JOIN 
lrs.cmsroles AS r ON ur.roleid=r.id ) ON u.id=ur.uid\G" > /home/ssgconfig/allUsersWithRoles.txt 

This is a query that should return all users and if they have any information on the cmsuserroles table attach it to the output. 

Also, just to make sure can you also execute this command: 

>> mysql -e "SELECT * FROM lrs.cmsusers\G" > /home/ssgconfig/allUsers.txt 

Both query should return the missing user. 

You can use the following query to add a connection between the missing user (user id=) and the cmsuser role (role id=): 

>> INSERT INTO lrs.cmsuserroles(uid,roleid) VALUES (<value>,<value>); 

I would suggest you take a backup of the database before you do any direct changes, especially if this is a live production system.