Security Users view in the UI queries the database for all user role mappings in the entire database

Create logger named org.sonatype.nexus.internal.security.model.CUserRoleMappingDAO at DEBUG level.

Open Security -> Users in the UI as admin user

Observe that this single action will make the following database query per user record :

SELECT * FROM user_role_mapping WHERE user_lo = ? AND source = ?

Then at the end of all these queries, a final query is performed to select EVERY user role mapping record:

SELECT * FROM user_role_mapping;

Given that absolutely no user role mappings are displayed in this UI view, and they are only needed for a specific user record when that user is selected from the list, these database queries are needless. In the case where a customer has thousands of user role mappings, loading the UI can become unusable.

Expected

Do not perform these needless queries when loading the Users UI for any selected User Source.

Technical Notes

We either need to query the role mappings once at the beginning instead of for each user, or we need to query just the roles against the role mappings table to avoid a full table scan for everything.