SONAR-9105 add selectUserIdsWithPermissionOnProjectBut

4 files changed, 75 insertions, 0 deletions

diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionDao.java
index 31f276e1595..8ffeb772d29 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionDao.java
@@ -21,6 +21,7 @@ package org.sonar.db.permission;
 
 import java.util.Collection;
 import java.util.List;
+import java.util.Set;
 import javax.annotation.Nullable;
 import org.apache.ibatis.session.RowBounds;
 import org.sonar.core.util.stream.MoreCollectors;
@@ -99,6 +100,10 @@ public class UserPermissionDao implements Dao {
     return mapper(dbSession).selectProjectPermissionsOfUser(userId, projectId);
   }
 
+  public Set<Integer> selectUserIdsWithPermissionOnProjectBut(DbSession session, long projectId, String permission) {
+    return mapper(session).selectUserIdsWithPermissionOnProjectBut(projectId, permission);
+  }
+
   public void insert(DbSession dbSession, UserPermissionDto dto) {
     ensureComponentPermissionConsistency(dbSession, dto);
     mapper(dbSession).insert(dto);
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionMapper.java
index 4007e221b0c..5c78b79d38a 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionMapper.java
@@ -21,6 +21,7 @@ package org.sonar.db.permission;
 
 import java.util.Collection;
 import java.util.List;
+import java.util.Set;
 import javax.annotation.Nullable;
 import org.apache.ibatis.annotations.Param;
 import org.apache.ibatis.session.RowBounds;
@@ -45,6 +46,11 @@ public interface UserPermissionMapper {
    */
   List<CountPerProjectPermission> countUsersByProjectPermission(@Param("projectIds") List<Long> projectIds);
 
+  /**
+   * select id of users with at least one permission on the specified project but which do not have the specified permission.
+   */
+  Set<Integer> selectUserIdsWithPermissionOnProjectBut(@Param("projectId") long projectId, @Param("permission") String permission);
+
   void insert(UserPermissionDto dto);
 
   void deleteGlobalPermission(@Param("userId") int userId, @Param("permission") String permission,
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/permission/UserPermissionMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/permission/UserPermissionMapper.xml
index 7e9e3d4b68b..bfe02d6267d 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/permission/UserPermissionMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/permission/UserPermissionMapper.xml
@@ -81,6 +81,26 @@
     group by ur.resource_id, ur.role
   </select>
 
+  <select id="selectUserIdsWithPermissionOnProjectBut" resultType="Integer">
+    select
+      distinct ur1.user_id
+    from
+      user_roles ur1
+    where
+      ur1.resource_id = #{projectId,jdbcType=BIGINT}
+      and role &lt;&gt; #{permission,jdbcType=VARCHAR}
+      and not exists (
+        select
+          1
+        from
+          user_roles ur2
+        where
+          ur2.resource_id = ur1.resource_id
+          and ur2.user_id = ur1.user_id
+          and role = #{permission,jdbcType=VARCHAR}
+      )
+  </select>
+
   <insert id="insert" parameterType="org.sonar.db.permission.UserPermissionDto" useGeneratedKeys="false">
     insert into user_roles (
     organization_uuid,
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/permission/UserPermissionDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/permission/UserPermissionDaoTest.java
index 64762e5eed4..fdffb764833 100644
--- a/server/sonar-db-dao/src/test/java/org/sonar/db/permission/UserPermissionDaoTest.java
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/permission/UserPermissionDaoTest.java
@@ -368,6 +368,50 @@ public class UserPermissionDaoTest {
   }
 
   @Test
+  public void selectGroupIdsWithPermissionOnProjectBut_returns_empty_if_project_does_not_exist() {
+    OrganizationDto organization = dbTester.organizations().insert();
+    ComponentDto project = randomPublicOrPrivateProject(organization);
+    UserDto user = dbTester.users().insertUser();
+    dbTester.users().insertProjectPermissionOnUser(user, "foo", project);
+
+    assertThat(underTest.selectUserIdsWithPermissionOnProjectBut(dbSession, 1234, UserRole.USER))
+      .isEmpty();
+  }
+
+  @Test
+  public void selectGroupIdsWithPermissionOnProjectBut_returns_only_users_of_projects_which_do_not_have_permission() {
+    OrganizationDto organization = dbTester.organizations().insert();
+    ComponentDto project = randomPublicOrPrivateProject(organization);
+    UserDto user1 = dbTester.users().insertUser();
+    UserDto user2 = dbTester.users().insertUser();
+    dbTester.users().insertProjectPermissionOnUser(user1, "p1", project);
+    dbTester.users().insertProjectPermissionOnUser(user2, "p2", project);
+
+    assertThat(underTest.selectUserIdsWithPermissionOnProjectBut(dbSession, project.getId(), "p2"))
+      .containsOnly(user1.getId());
+    assertThat(underTest.selectUserIdsWithPermissionOnProjectBut(dbSession, project.getId(), "p1"))
+      .containsOnly(user2.getId());
+    assertThat(underTest.selectUserIdsWithPermissionOnProjectBut(dbSession, project.getId(), "p3"))
+      .containsOnly(user1.getId(), user2.getId());
+  }
+
+  @Test
+  public void selectGroupIdsWithPermissionOnProjectBut_does_not_return_groups_which_have_no_permission_at_all_on_specified_project() {
+    OrganizationDto organization = dbTester.organizations().insert();
+    ComponentDto project = randomPublicOrPrivateProject(organization);
+    UserDto user1 = dbTester.users().insertUser();
+    UserDto user2 = dbTester.users().insertUser();
+    UserDto user3 = dbTester.users().insertUser();
+    dbTester.users().insertProjectPermissionOnUser(user1, "p1", project);
+    dbTester.users().insertProjectPermissionOnUser(user2, "p2", project);
+
+    assertThat(underTest.selectUserIdsWithPermissionOnProjectBut(dbSession, project.getId(), "p2"))
+      .containsOnly(user1.getId());
+    assertThat(underTest.selectUserIdsWithPermissionOnProjectBut(dbSession, project.getId(), "p1"))
+      .containsOnly(user2.getId());
+  }
+
+  @Test
   public void deleteByOrganization_does_not_fail_if_table_is_empty() {
     underTest.deleteByOrganization(dbSession, "some uuid");
     dbSession.commit();