SONAR-9105 add GroupPermissionDao#selectGroupIdsWithPermissionOnProjectBut

4 files changed, 98 insertions, 0 deletions

diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/GroupPermissionDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/GroupPermissionDao.java
index cccf5a4d717..4b18d0b9e2a 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/GroupPermissionDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/GroupPermissionDao.java
@@ -22,6 +22,7 @@ package org.sonar.db.permission;
 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
+import java.util.Set;
 import javax.annotation.Nullable;
 import org.apache.ibatis.session.ResultHandler;
 import org.apache.ibatis.session.RowBounds;
@@ -103,6 +104,15 @@ public class GroupPermissionDao implements Dao {
     return mapper(session).selectProjectPermissionsOfGroup(organizationUuid, groupId, projectId);
   }
 
+  /**
+   * Lists id of groups with at least one permission on the specified root component but which do not have the specified
+   * permission, <strong>excluding group "AnyOne"</strong> (which implies the returned {@code Sett} can't contain
+   * {@code null}).
+   */
+  public Set<Integer> selectGroupIdsWithPermissionOnProjectBut(DbSession session, long projectId, String permission) {
+    return mapper(session).selectGroupIdsWithPermissionOnProjectBut(projectId, permission);
+  }
+
   public void insert(DbSession dbSession, GroupPermissionDto dto) {
     ensureComponentPermissionConsistency(dbSession, dto);
     ensureGroupPermissionConsistency(dbSession, dto);
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/GroupPermissionMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/GroupPermissionMapper.java
index 05439bd61e8..19153f7f845 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/GroupPermissionMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/GroupPermissionMapper.java
@@ -21,6 +21,7 @@ package org.sonar.db.permission;
 
 import java.util.List;
 import java.util.Map;
+import java.util.Set;
 import javax.annotation.Nullable;
 import org.apache.ibatis.annotations.Param;
 import org.apache.ibatis.session.ResultHandler;
@@ -51,6 +52,13 @@ public interface GroupPermissionMapper {
   void selectAllPermissionsByGroupId(@Param("organizationUuid") String organizationUuid,
     @Param("groupId") Integer groupId, ResultHandler resultHandler);
 
+  /**
+   * Lists id of groups with at least one permission on the specified root component but which do not have the specified
+   * permission, <strong>excluding group "AnyOne"</strong> (which implies the returned {@code Set} can't contain
+   * {@code null}).
+   */
+  Set<Integer> selectGroupIdsWithPermissionOnProjectBut(@Param("projectId") long projectId, @Param("role") String permission);
+
   void deleteByOrganization(@Param("organizationUuid") String organizationUuid);
 
   void deleteByRootComponentId(@Param("rootComponentId") long componentId);
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/permission/GroupPermissionMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/permission/GroupPermissionMapper.xml
index 735a7fb5617..74e031b602b 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/permission/GroupPermissionMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/permission/GroupPermissionMapper.xml
@@ -162,6 +162,26 @@
     and gr.group_id = #{groupId,jdbcType=INTEGER}
   </select>
 
+  <select id="selectGroupIdsWithPermissionOnProjectBut" resultType="Integer">
+    select
+      distinct gr1.group_id
+    from
+      group_roles gr1
+    where
+      gr1.resource_id = #{projectId,jdbcType=BIGINT}
+      and gr1.group_id is not null
+      and not exists (
+        select
+          1
+        from
+          group_roles gr2
+        where
+          gr2.resource_id = gr1.resource_id
+          and gr2.group_id = gr1.group_id
+          and gr2.role = #{role,jdbcType=VARCHAR}
+      )
+  </select>
+
   <insert id="insert" parameterType="GroupPermission" keyColumn="id" useGeneratedKeys="true" keyProperty="id">
     insert into group_roles (
     organization_uuid,
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/permission/GroupPermissionDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/permission/GroupPermissionDaoTest.java
index 1097e8190c5..d857b5dea5d 100644
--- a/server/sonar-db-dao/src/test/java/org/sonar/db/permission/GroupPermissionDaoTest.java
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/permission/GroupPermissionDaoTest.java
@@ -486,6 +486,66 @@ public class GroupPermissionDaoTest {
   }
 
   @Test
+  public void selectGroupIdsWithPermissionOnProjectBut_returns_empty_if_project_does_not_exist() {
+    OrganizationDto organization = db.organizations().insert();
+    ComponentDto project = randomPublicOrPrivateProject(organization);
+    GroupDto group = db.users().insertGroup(organization);
+    db.users().insertProjectPermissionOnGroup(group, "foo", project);
+
+    assertThat(underTest.selectGroupIdsWithPermissionOnProjectBut(dbSession, 1234, UserRole.USER))
+      .isEmpty();
+  }
+
+  @Test
+  public void selectGroupIdsWithPermissionOnProjectBut_returns_only_groups_of_project_which_do_not_have_permission() {
+    OrganizationDto organization = db.organizations().insert();
+    ComponentDto project = randomPublicOrPrivateProject(organization);
+    GroupDto group1 = db.users().insertGroup(organization);
+    GroupDto group2 = db.users().insertGroup(organization);
+    db.users().insertProjectPermissionOnGroup(group1, "p1", project);
+    db.users().insertProjectPermissionOnGroup(group2, "p2", project);
+
+    assertThat(underTest.selectGroupIdsWithPermissionOnProjectBut(dbSession, project.getId(), "p2"))
+      .containsOnly(group1.getId());
+    assertThat(underTest.selectGroupIdsWithPermissionOnProjectBut(dbSession, project.getId(), "p1"))
+      .containsOnly(group2.getId());
+    assertThat(underTest.selectGroupIdsWithPermissionOnProjectBut(dbSession, project.getId(), "p3"))
+      .containsOnly(group1.getId(), group2.getId());
+  }
+
+  @Test
+  public void selectGroupIdsWithPermissionOnProjectBut_does_not_returns_group_AnyOne_of_project_when_it_does_not_have_permission() {
+    OrganizationDto organization = db.organizations().insert();
+    ComponentDto project = db.components().insertPublicProject(organization);
+    GroupDto group1 = db.users().insertGroup(organization);
+    GroupDto group2 = db.users().insertGroup(organization);
+    db.users().insertProjectPermissionOnGroup(group1, "p1", project);
+    db.users().insertProjectPermissionOnGroup(group2, "p2", project);
+    db.users().insertProjectPermissionOnAnyone("p2", project);
+
+    assertThat(underTest.selectGroupIdsWithPermissionOnProjectBut(dbSession, project.getId(), "p2"))
+      .containsOnly(group1.getId());
+    assertThat(underTest.selectGroupIdsWithPermissionOnProjectBut(dbSession, project.getId(), "p1"))
+      .containsOnly(group2.getId());
+  }
+
+  @Test
+  public void selectGroupIdsWithPermissionOnProjectBut_does_not_return_groups_which_have_no_permission_at_all_on_specified_project() {
+    OrganizationDto organization = db.organizations().insert();
+    ComponentDto project = randomPublicOrPrivateProject(organization);
+    GroupDto group1 = db.users().insertGroup(organization);
+    GroupDto group2 = db.users().insertGroup(organization);
+    GroupDto group3 = db.users().insertGroup(organization);
+    db.users().insertProjectPermissionOnGroup(group1, "p1", project);
+    db.users().insertProjectPermissionOnGroup(group2, "p2", project);
+
+    assertThat(underTest.selectGroupIdsWithPermissionOnProjectBut(dbSession, project.getId(), "p2"))
+      .containsOnly(group1.getId());
+    assertThat(underTest.selectGroupIdsWithPermissionOnProjectBut(dbSession, project.getId(), "p1"))
+      .containsOnly(group2.getId());
+  }
+
+  @Test
   public void deleteByRootComponentId_on_private_project() {
     OrganizationDto org = db.organizations().insert();
     GroupDto group1 = db.users().insertGroup(org);