Fix user having custom role in multiple objects in a hierarchy

What does this MR do and why?

The MR is fixing the following issue:

When there is a data structure such as:

• Group A -> Project 1

• Custom role "Code owner" with custom ability admin_merge_request allowed

• Custom role "Vulnerability admin" with custom ability read_vulnerability allowed

• User has "Code owner" membership in Group A, and "Vulnerability admin" membership in Project 1

• So they should be able to admin_merge_request (coming from group membership) and read_vulnerability (coming from project membership)

But the user has only one of these permissions.

This MR fixes that.

SQL queries

UserMemberRolesInGroupsPreloader

• old query
• new query

UserMemberRolesInProjectsPreloader

• old query
• new query

Examples for testing

Namespace structure

• Group A -> Project 1
• Group A -> Subgroup B -> Project 2

Member Roles (all based on guest)

• "Code owner" with admin_merge_request, this is project-only ability
• "Vulnerability admin" with read_vulnerability
• "Group manager" with admin_group_member, this is group-only ability
• "Superstar" with admin_merge_request, read_vulnerability, archive_project, remove_project

Sample assignments and available features

Precondition: user is a guest member of Group A

Role in Group A	Project 1	Subgroup B	Project 2	Abilities in Group A	Project 1	Subgroup B	Project 2
Approver	-	-	-	-	admin_merge_request	-	admin_merge_request
Vulnerability admin	-	-	-	read_vulnerability	read_vulnerability	read_vulnerability	read_vulnerability
Vulnerability admin	Approver	-	-	read_vulnerability	read_vulnerability, admin_merge_request	read_vulnerability	read_vulnerability
Vulnerability admin	Approver	Approver	-	read_vulnerability	read_vulnerability, admin_merge_request	read_vulnerability	read_vulnerability, admin_merge_request
Vulnerability admin	-	-	Approver	read_vulnerability	read_vulnerability	read_vulnerability	read_vulnerability, admin_merge_request
Superstar	-	-	Approver	admin_group_member, read_vulnerability	admin_merge_request, read_vulnerability, archive_project, remove_project	admin_group_member, read_vulnerability`	admin_merge_request, read_vulnerability, archive_project, remove_project
Approver	-	-	Superstart	-	admin_merge_request	-	admin_merge_request, read_vulnerability, archive_project, remove_project
Group manager	-	Vulnerability admin	Project superstar	admin_group_member	-	admin_group_member, read_vulnerability	admin_merge_request, read_vulnerability, archive_project, remove_project

Creation and testing

• Create project strcuture: Group A (group) -> Project 1 (project_1), Group A -> Subgroup (subgroup) -> Project 2 (project_2)
• Pick a user you'll be assigning combination of guest based custom roles based on the table above (or think up other combinations)

In consoles

# create custom roles
approver = MemberRole.create(name: 'Approver', base_access_level: 10, admin_merge_request: true)
vuln_admin = MemberRole.create(name: 'Vulnerability Admin', base_access_level: 10, read_vulnerability: true)
group_man = MemberRole.create(name: 'Group Manager', base_access_level: 10, admin_group_member: true)
superstar = MemberRole.create(name: 'Superstar', base_access_level: 10, archive_project: true, remove_project: true, admin_merge_request: true, read_vulnerability: true, admin_group_member: true)

# find objects
user = User.find_by(username: 'your-test-user-username')
group = Group.find_by(path: 'group-a')
subgroup = Group.find_by(path: 'subgroup-b')
project_1 = Project.find_by(path: 'project-1')
project_2 = Project.find_by(path: 'project-2')

# to test allowed abilities in console
abilities_group = ::Preloaders::UserMemberRolesInGroupsPreloader.new(groups: [group], user: user).execute
abilities_subgroup = ::Preloaders::UserMemberRolesInGroupsPreloader.new(groups: [subgroup], user: user).execute

abilities_project_1 = ::Preloaders::UserMemberRolesInProjectsPreloader.new(projects: [project_1], user: user).execute
abilities_project_2 = ::Preloaders::UserMemberRolesInProjectsPreloader.new(projects: [project_2], user: user).execute

# you can specifically test for an ability and user/object
user.can?(:admin_merge_request, project_1)

Additionally, you can test the abilities in the UI:

• admin_merge_request - can approve opened MRs
• read_vulnerability - can see vulnerabilities in project / group -> Secure -> Vulnerability report
• admin_group_member - can invite group members in group -> members
• archive_project, remove_project - can perform these actions in project -> settings -> general -> Advanced

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before	After

How to set up and validate locally

1. Create 2 custom roles, one with admin_merge_request ability (Code owner), second with read_vulnerability ability (Vulnerability admin). You can simply do it in the rails console:

MemberRole.create(name: 'Code owner', base_access_level: 10, admin_merge_request: true)
 MemberRole.create(name: 'Vulnerability administrator', base_access_level: 10, read_vulnerability: true)

2. Go to a group page and assign Code owner role to a user.
3. Go to the same group project and assign Vulnerability admin to the same user.
4. Sign in as the user
5. Go to the project. You should be able to approve merge requests and also read vulnerabilities.

Related to #440530 (closed)