User is not part any group but still has the roles

[Community Alums]

Hello There,

Greetings!!!

I have come across scenario where user is not part of any of the groups but still have the roles and they show inherited as true:

I checked sys_user_has_role > role inheritance map it does not show any mapping

How to delete all such entries from sys_user_has_role table for all the users like above user?

What causes this? 

Thanks,

Tejas

[ProCloudC]

Steps to Delete Inherited Roles for Users

1. Identify Inherited Roles Without Explicit Group Mapping

   • First, let's identify the records in the sys_user_has_role table that have the inheritance flag set to true, but no explicit group-to-role mapping exists.

2. Query the sys_user_has_role Table
   You can run a query to check for all records where the inheritance is true but there's no group-role inheritance mapping.

   sql

   SELECT * FROM sys_user_has_role WHERE inheritance = TRUE AND NOT EXISTS ( SELECT 1 FROM sys_user_group WHERE sys_user_group.user = sys_user_has_role.user AND sys_user_group.role = sys_user_has_role.role );

   This query checks for all user-role assignments where inheritance is marked as true but there is no corresponding mapping between users' groups and their roles.

3. Delete These Roles
   If you want to remove these entries, you can use a DELETE statement based on the above condition:

   sql

   DELETE FROM sys_user_has_role WHERE inheritance = TRUE AND NOT EXISTS ( SELECT 1 FROM sys_user_group WHERE sys_user_group.user = sys_user_has_role.user AND sys_user_group.role = sys_user_has_role.role );

   This will delete any roles where the inheritance is true and there’s no valid mapping between the user’s groups and roles.

4. Verify the Changes
   After deleting the records, it's good practice to verify that the roles are removed as expected. You can re-run the SELECT query to ensure no unwanted records are left behind.

Additional Considerations:

• Check for Role Assignment via Other Mechanisms: Sometimes, roles are assigned through other indirect means (e.g., via scripts, workflows, or business rules). Make sure there isn’t another process assigning roles in a way that's not easily traceable by the usual group-role mapping.

• Test on a Subset: If this is in a production environment, consider running this on a small subset of data first (or in a dev/test environment) to ensure it behaves as expected.

Let me know if you need help with any additional queries or troubleshooting steps!