SONAR-8134 refactor SQL to check organization permission

1 files changed, 24 insertions, 2 deletions

diff --git a/sonar-db/src/main/resources/org/sonar/db/permission/AuthorizationMapper.xml b/sonar-db/src/main/resources/org/sonar/db/permission/AuthorizationMapper.xml
index 0dbb5162cbf..d819a2b2294 100644
--- a/sonar-db/src/main/resources/org/sonar/db/permission/AuthorizationMapper.xml
+++ b/sonar-db/src/main/resources/org/sonar/db/permission/AuthorizationMapper.xml
@@ -6,11 +6,23 @@
   <select id="selectOrganizationPermissions" parameterType="map" resultType="string">
     select gr.role
     from group_roles gr
+    inner join groups_users gu on gr.group_id=gu.group_id
     where
     gr.organization_uuid=#{organizationUuid,jdbcType=VARCHAR} and
     gr.resource_id is null and
-    (gr.group_id is null or gr.group_id in (select gu.group_id from groups_users gu where gu.user_id=#{userId,jdbcType=BIGINT}))
+    gu.user_id=#{userId,jdbcType=BIGINT}
+
     union
+
+    select gr.role
+    from group_roles gr
+    where
+    gr.organization_uuid=#{organizationUuid,jdbcType=VARCHAR} and
+    gr.group_id is null and
+    gr.resource_id is null
+
+    union
+
     select ur.role
     from user_roles ur
     where
@@ -30,10 +42,20 @@
   <select id="selectRootComponentPermissions" parameterType="map" resultType="string">
     select gr.role
     from group_roles gr
+    inner join groups_users gu on gr.group_id=gu.group_id
     where
     gr.resource_id=#{rootComponentId,jdbcType=BIGINT} and
-    (gr.group_id is null or gr.group_id in (select gu.group_id from groups_users gu where gu.user_id=#{userId,jdbcType=BIGINT}))
+    gu.user_id=#{userId,jdbcType=BIGINT}
+
     union
+    select gr.role
+    from group_roles gr
+    where
+    gr.resource_id=#{rootComponentId,jdbcType=BIGINT} and
+    gr.group_id is null
+
+    union
+
     select ur.role
     from user_roles ur
     where