SONAR-7838 DAO to select groups and group permissions

6 files changed, 299 insertions, 36 deletions

diff --git a/sonar-db/src/main/java/org/sonar/db/permission/PermissionDao.java b/sonar-db/src/main/java/org/sonar/db/permission/PermissionDao.java
index 7f5eafaffd4..a1d4b753793 100644
--- a/sonar-db/src/main/java/org/sonar/db/permission/PermissionDao.java
+++ b/sonar-db/src/main/java/org/sonar/db/permission/PermissionDao.java
@@ -30,6 +30,7 @@ import org.sonar.api.security.DefaultGroups;
 import org.sonar.db.Dao;
 import org.sonar.db.DbSession;
 import org.sonar.db.MyBatis;
+import org.sonar.db.user.GroupRoleDto;
 import org.sonar.db.user.UserPermissionDto;
 
 import static com.google.common.collect.Maps.newHashMap;
@@ -117,6 +118,21 @@ public class PermissionDao implements Dao {
   }
 
   /**
+   * ordered by group names
+   */
+  public List<String> selectGroupNamesByPermissionQuery(DbSession dbSession, PermissionQuery query) {
+    return mapper(dbSession).selectGroupNamesByPermissionQuery(query, new RowBounds(query.getPageOffset(), query.getPageSize()));
+  }
+
+  public int countGroupsByPermissionQuery(DbSession dbSession, PermissionQuery query) {
+    return mapper(dbSession).countGroupsByPermissionQuery(query);
+  }
+
+  public List<GroupRoleDto> selectGroupPermissionsByQuery(DbSession dbSession, PermissionQuery query) {
+    return mapper(dbSession).selectGroupPermissionByQuery(query);
+  }
+
+  /**
    * Each row returns a CountByProjectAndPermissionDto
    */
   public void usersCountByComponentIdAndPermission(DbSession dbSession, List<Long> componentIds, ResultHandler resultHandler) {
diff --git a/sonar-db/src/main/java/org/sonar/db/permission/PermissionMapper.java b/sonar-db/src/main/java/org/sonar/db/permission/PermissionMapper.java
index 02b2415b9a1..2a1fc5cc11a 100644
--- a/sonar-db/src/main/java/org/sonar/db/permission/PermissionMapper.java
+++ b/sonar-db/src/main/java/org/sonar/db/permission/PermissionMapper.java
@@ -24,6 +24,7 @@ import java.util.Map;
 import org.apache.ibatis.annotations.Param;
 import org.apache.ibatis.session.ResultHandler;
 import org.apache.ibatis.session.RowBounds;
+import org.sonar.db.user.GroupRoleDto;
 import org.sonar.db.user.UserPermissionDto;
 
 public interface PermissionMapper {
@@ -42,6 +43,12 @@ public interface PermissionMapper {
 
   int countGroups(Map<String, Object> parameters);
 
+  List<String> selectGroupNamesByPermissionQuery(@Param("query") PermissionQuery query, RowBounds rowBounds);
+
+  int countGroupsByPermissionQuery(@Param("query") PermissionQuery query);
+
+  List<GroupRoleDto> selectGroupPermissionByQuery(@Param("query") PermissionQuery query);
+
   void usersCountByProjectIdAndPermission(Map<String, Object> parameters, ResultHandler resultHandler);
 
   void groupsCountByProjectIdAndPermission(Map<String, Object> parameters, ResultHandler resultHandler);
diff --git a/sonar-db/src/main/java/org/sonar/db/permission/PermissionQuery.java b/sonar-db/src/main/java/org/sonar/db/permission/PermissionQuery.java
index cff95938144..a7f6a042376 100644
--- a/sonar-db/src/main/java/org/sonar/db/permission/PermissionQuery.java
+++ b/sonar-db/src/main/java/org/sonar/db/permission/PermissionQuery.java
@@ -19,10 +19,13 @@
  */
 package org.sonar.db.permission;
 
+import com.google.common.collect.ImmutableList;
+import java.util.Collections;
 import java.util.List;
 import java.util.Locale;
 import javax.annotation.CheckForNull;
 import javax.annotation.Nullable;
+import javax.annotation.concurrent.Immutable;
 import org.sonar.db.WildcardPosition;
 
 import static com.google.common.base.MoreObjects.firstNonNull;
@@ -34,6 +37,7 @@ import static org.sonar.db.DatabaseUtils.buildLikeValue;
 /**
  * Query used to get users and groups permissions
  */
+@Immutable
 public class PermissionQuery {
   public static final int RESULTS_MAX_SIZE = 100;
   public static final int SEARCH_QUERY_MIN_LENGTH = 3;
@@ -47,6 +51,7 @@ public class PermissionQuery {
   private final String searchQueryToSql;
   private final boolean withPermissionOnly;
   private final List<String> logins;
+  private final List<String> groupNames;
 
   private final int pageSize;
   private final int pageOffset;
@@ -60,7 +65,8 @@ public class PermissionQuery {
     this.searchQueryToSql = builder.searchQuery == null ? null : buildLikeValue(builder.searchQuery, WildcardPosition.BEFORE_AND_AFTER).toLowerCase(Locale.ENGLISH);
     this.pageSize = builder.pageSize;
     this.pageOffset = offset(builder.pageIndex, builder.pageSize);
-    this.logins = builder.logins;
+    this.logins = builder.logins == null ? Collections.emptyList() : ImmutableList.copyOf(builder.logins);
+    this.groupNames = builder.groupNames == null ? Collections.emptyList() : ImmutableList.copyOf(builder.groupNames);
   }
 
   @CheckForNull
@@ -99,11 +105,14 @@ public class PermissionQuery {
     return pageOffset;
   }
 
-  @CheckForNull
   public List<String> getLogins() {
     return logins;
   }
 
+  public List<String> getGroupNames() {
+    return groupNames;
+  }
+
   public static Builder builder() {
     return new Builder();
   }
@@ -115,6 +124,7 @@ public class PermissionQuery {
     private String searchQuery;
     private boolean withPermissionOnly;
     private List<String> logins;
+    private List<String> groupNames;
 
     private Integer pageIndex = DEFAULT_PAGE_INDEX;
     private Integer pageSize = DEFAULT_PAGE_SIZE;
@@ -163,11 +173,17 @@ public class PermissionQuery {
       return this;
     }
 
+    public Builder setGroupNames(@Nullable List<String> groupNames) {
+      this.groupNames = groupNames;
+      return this;
+    }
+
     public PermissionQuery build() {
       this.pageIndex = firstNonNull(pageIndex, DEFAULT_PAGE_INDEX);
       this.pageSize = firstNonNull(pageSize, DEFAULT_PAGE_SIZE);
       checkArgument(searchQuery == null || searchQuery.length() >= SEARCH_QUERY_MIN_LENGTH);
       checkArgument(logins == null || !logins.isEmpty());
+      checkArgument(groupNames == null || !groupNames.isEmpty());
       return new PermissionQuery(this);
     }
   }
diff --git a/sonar-db/src/main/resources/org/sonar/db/permission/PermissionMapper.xml b/sonar-db/src/main/resources/org/sonar/db/permission/PermissionMapper.xml
index e9e37c92933..ffe5974e41a 100644
--- a/sonar-db/src/main/resources/org/sonar/db/permission/PermissionMapper.xml
+++ b/sonar-db/src/main/resources/org/sonar/db/permission/PermissionMapper.xml
@@ -41,7 +41,7 @@
   </sql>
 
   <sql id="userColumns">
-    <!-- lower(u.name) and u.id are present to order by with select distinct -->
+    <!-- u.name and u.id are present to order by with select distinct -->
     u.login as login, u.name as name, u.id
   </sql>
 
@@ -201,4 +201,63 @@
     </where>
     ORDER BY groups.name
   </select>
+
+  <select id="selectGroupNamesByPermissionQuery" parameterType="map" resultType="string">
+    select distinct gr.name, lower(gr.name), gr.group_id
+    <include refid="groupsByQuery" />
+    order by lower(gr.name), gr.name, gr.group_id
+  </select>
+
+  <select id="countGroupsByPermissionQuery" parameterType="map" resultType="int">
+    select count(1)
+    from (
+      select distinct gr.group_id
+      <include refid="groupsByQuery" />) g
+  </select>
+
+  <select id="selectGroupPermissionByQuery" parameterType="map" resultType="GroupRole">
+    select distinct gr.id as id, gr.group_id as groupId, gr.resource_id as resourceId, gr.permission as role
+    <include refid="groupsByQuery" />
+  </select>
+
+  <sql id="groupsByQuery">
+    from (
+    select g.id as group_id, g.name as name, gr.role as permission, gr.resource_id as resource_id, gr.id as id
+    from groups g
+      left join group_roles gr on g.id = gr.group_id
+    UNION ALL
+    select 0 as group_id, 'Anyone' as name, gr.role as permission, gr.resource_id as resource_id, gr.id as id
+    from group_roles gr
+    <where>
+      <if test="query.withPermissionOnly()">
+        and gr.group_id is null
+      </if>
+    </where>
+    ) gr
+    left join projects p on gr.resource_id = p.id
+    <where>
+      <if test="!query.groupNames.isEmpty()">
+        and gr.name in
+        <foreach collection="query.groupNames" open="(" close=")" item="name" separator=",">
+          #{name}
+        </foreach>
+      </if>
+      <if test="query.searchQueryToSql != null">
+        and lower(gr.name) like #{query.searchQueryToSql} ESCAPE '/'
+      </if>
+      <!-- filter rows with group permissions -->
+      <if test="query.withPermissionOnly()">
+        and gr.permission is not null
+        <if test="query.componentUuid==null">
+          and gr.resource_id is null
+        </if>
+        <if test="query.componentUuid!=null">
+          and p.uuid=#{query.componentUuid}
+        </if>
+        <if test="query.permission!=null">
+          and gr.permission=#{query.permission}
+        </if>
+      </if>
+    </where>
+  </sql>
 </mapper>
diff --git a/sonar-db/src/test/java/org/sonar/db/permission/GroupWithPermissionDaoTest.java b/sonar-db/src/test/java/org/sonar/db/permission/GroupWithPermissionDaoTest.java
index e171fbe2bf9..9e0b104b7e7 100644
--- a/sonar-db/src/test/java/org/sonar/db/permission/GroupWithPermissionDaoTest.java
+++ b/sonar-db/src/test/java/org/sonar/db/permission/GroupWithPermissionDaoTest.java
@@ -22,29 +22,43 @@ package org.sonar.db.permission;
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.List;
-import javax.annotation.Nullable;
+import java.util.stream.IntStream;
 import org.junit.Rule;
 import org.junit.Test;
+import org.sonar.api.security.DefaultGroups;
 import org.sonar.api.utils.System2;
+import org.sonar.api.web.UserRole;
+import org.sonar.core.permission.GlobalPermissions;
 import org.sonar.db.DbSession;
 import org.sonar.db.DbTester;
+import org.sonar.db.component.ComponentDbTester;
+import org.sonar.db.component.ComponentDto;
+import org.sonar.db.user.GroupDbTester;
 import org.sonar.db.user.GroupDto;
 import org.sonar.db.user.GroupRoleDto;
 
+import static com.google.common.collect.Lists.newArrayList;
 import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.api.Assertions.tuple;
 import static org.sonar.api.web.UserRole.ADMIN;
 import static org.sonar.api.web.UserRole.ISSUE_ADMIN;
 import static org.sonar.api.web.UserRole.USER;
+import static org.sonar.core.permission.GlobalPermissions.PROVISIONING;
+import static org.sonar.core.permission.GlobalPermissions.SCAN_EXECUTION;
+import static org.sonar.core.permission.GlobalPermissions.SYSTEM_ADMIN;
+import static org.sonar.db.component.ComponentTesting.newProjectDto;
 import static org.sonar.db.user.GroupTesting.newGroupDto;
 
-
 public class GroupWithPermissionDaoTest {
 
   private static final long COMPONENT_ID = 100L;
 
   @Rule
   public DbTester db = DbTester.create(System2.INSTANCE);
-  final DbSession session = db.getSession();
+  GroupDbTester groupDb = new GroupDbTester(db);
+  PermissionDbTester permissionDb = new PermissionDbTester(db);
+  ComponentDbTester componentDb = new ComponentDbTester(db);
+  DbSession dbSession = db.getSession();
 
   PermissionDao underTest = new PermissionDao(db.myBatis());
 
@@ -53,7 +67,7 @@ public class GroupWithPermissionDaoTest {
     db.prepareDbUnit(getClass(), "groups_with_permissions.xml");
 
     OldPermissionQuery query = OldPermissionQuery.builder().permission("user").build();
-    List<GroupWithPermissionDto> result = underTest.selectGroups(session, query, COMPONENT_ID);
+    List<GroupWithPermissionDto> result = underTest.selectGroups(dbSession, query, COMPONENT_ID);
     assertThat(result).hasSize(4);
 
     GroupWithPermissionDto anyone = result.get(0);
@@ -83,7 +97,7 @@ public class GroupWithPermissionDaoTest {
 
     // Anyone group has not the permission 'admin', so it's not returned
     OldPermissionQuery query = OldPermissionQuery.builder().permission("admin").build();
-    List<GroupWithPermissionDto> result = underTest.selectGroups(session, query, COMPONENT_ID);
+    List<GroupWithPermissionDto> result = underTest.selectGroups(dbSession, query, COMPONENT_ID);
     assertThat(result).hasSize(3);
 
     GroupWithPermissionDto group1 = result.get(0);
@@ -104,7 +118,7 @@ public class GroupWithPermissionDaoTest {
     db.prepareDbUnit(getClass(), "groups_with_permissions.xml");
 
     OldPermissionQuery query = OldPermissionQuery.builder().permission("admin").build();
-    List<GroupWithPermissionDto> result = underTest.selectGroups(session, query, null);
+    List<GroupWithPermissionDto> result = underTest.selectGroups(dbSession, query, null);
     assertThat(result).hasSize(3);
 
     GroupWithPermissionDto group1 = result.get(0);
@@ -124,11 +138,11 @@ public class GroupWithPermissionDaoTest {
   public void search_by_groups_name() {
     db.prepareDbUnit(getClass(), "groups_with_permissions.xml");
 
-    List<GroupWithPermissionDto> result = underTest.selectGroups(session, OldPermissionQuery.builder().permission("user").search("aDMini").build(), COMPONENT_ID);
+    List<GroupWithPermissionDto> result = underTest.selectGroups(dbSession, OldPermissionQuery.builder().permission("user").search("aDMini").build(), COMPONENT_ID);
     assertThat(result).hasSize(1);
     assertThat(result.get(0).getName()).isEqualTo("sonar-administrators");
 
-    result = underTest.selectGroups(session, OldPermissionQuery.builder().permission("user").search("sonar").build(), COMPONENT_ID);
+    result = underTest.selectGroups(dbSession, OldPermissionQuery.builder().permission("user").search("sonar").build(), COMPONENT_ID);
     assertThat(result).hasSize(3);
   }
 
@@ -136,8 +150,8 @@ public class GroupWithPermissionDaoTest {
   public void search_groups_should_be_sorted_by_group_name() {
     db.prepareDbUnit(getClass(), "groups_with_permissions_should_be_sorted_by_group_name.xml");
 
-    List<GroupWithPermissionDto> result = underTest.selectGroups(session, OldPermissionQuery.builder().permission("user").build(), COMPONENT_ID);
-    int count = underTest.countGroups(session, "user", COMPONENT_ID);
+    List<GroupWithPermissionDto> result = underTest.selectGroups(dbSession, OldPermissionQuery.builder().permission("user").build(), COMPONENT_ID);
+    int count = underTest.countGroups(dbSession, "user", COMPONENT_ID);
 
     assertThat(result).hasSize(4);
     assertThat(count).isEqualTo(2);
@@ -149,23 +163,21 @@ public class GroupWithPermissionDaoTest {
 
   @Test
   public void group_count_by_permission_and_component_id() {
-    GroupDto group1 = insertGroup(newGroupDto());
-    GroupDto group2 = insertGroup(newGroupDto());
-    GroupDto group3 = insertGroup(newGroupDto());
-
-    insertGroupRole(ISSUE_ADMIN, group1.getId(), 42L);
-    insertGroupRole(ADMIN, group1.getId(), 123L);
-    insertGroupRole(ADMIN, group2.getId(), 123L);
-    insertGroupRole(ADMIN, group3.getId(), 123L);
+    GroupDto group1 = groupDb.insertGroup();
+    GroupDto group2 = groupDb.insertGroup();
+    GroupDto group3 = groupDb.insertGroup();
+
+    permissionDb.addProjectPermissionToGroup(ISSUE_ADMIN, group1.getId(), 42L);
+    permissionDb.addProjectPermissionToGroup(ADMIN, group1.getId(), 123L);
+    permissionDb.addProjectPermissionToGroup(ADMIN, group2.getId(), 123L);
+    permissionDb.addProjectPermissionToGroup(ADMIN, group3.getId(), 123L);
     // anyone group
-    insertGroupRole(ADMIN, null, 123L);
-    insertGroupRole(USER, group1.getId(), 123L);
-    insertGroupRole(USER, group1.getId(), 456L);
-
-    commit();
+    permissionDb.addProjectPermissionToGroup(ADMIN, null, 123L);
+    permissionDb.addProjectPermissionToGroup(USER, group1.getId(), 123L);
+    permissionDb.addProjectPermissionToGroup(USER, group1.getId(), 456L);
 
     final List<CountByProjectAndPermissionDto> result = new ArrayList<>();
-    underTest.groupsCountByComponentIdAndPermission(session, Arrays.asList(123L, 456L, 789L), context -> result.add((CountByProjectAndPermissionDto) context.getResultObject()));
+    underTest.groupsCountByComponentIdAndPermission(dbSession, Arrays.asList(123L, 456L, 789L), context -> result.add((CountByProjectAndPermissionDto) context.getResultObject()));
 
     assertThat(result).hasSize(3);
     assertThat(result).extracting("permission").containsOnly(ADMIN, USER);
@@ -173,18 +185,154 @@ public class GroupWithPermissionDaoTest {
     assertThat(result).extracting("count").containsOnly(4, 1);
   }
 
-  private GroupDto insertGroup(GroupDto groupDto) {
-    return db.getDbClient().groupDao().insert(session, groupDto);
+  @Test
+  public void select_groups_by_query_ordered_by_group_names() {
+    GroupDto group2 = groupDb.insertGroup(newGroupDto().setName("Group-2"));
+    GroupDto group3 = groupDb.insertGroup(newGroupDto().setName("Group-3"));
+    GroupDto group1 = groupDb.insertGroup(newGroupDto().setName("Group-1"));
+
+    permissionDb.addGlobalPermissionToGroup(SCAN_EXECUTION, null);
+
+    PermissionQuery.Builder dbQuery = PermissionQuery.builder();
+    List<String> groupNames = selectGroupNames(dbQuery);
+    int countNames = countGroupNames(dbQuery);
+    List<GroupRoleDto> permissions = selectGroupPermissions(dbQuery);
+
+    assertThat(groupNames).containsExactly("Anyone", "Group-1", "Group-2", "Group-3");
+    assertThat(countNames).isEqualTo(4);
+    assertThat(permissions).hasSize(4)
+      .extracting(GroupRoleDto::getGroupId)
+      .containsOnlyOnce(0L, group1.getId(), group2.getId(), group3.getId());
+  }
+
+  @Test
+  public void select_groups_by_query_with_global_permission() {
+    GroupDto group1 = groupDb.insertGroup(newGroupDto().setName("Group-1"));
+    GroupDto group2 = groupDb.insertGroup(newGroupDto().setName("Group-2"));
+    GroupDto group3 = groupDb.insertGroup(newGroupDto().setName("Group-3"));
+
+    ComponentDto project = componentDb.insertComponent(newProjectDto());
+
+    permissionDb.addGlobalPermissionToGroup(SCAN_EXECUTION, group1.getId());
+    permissionDb.addGlobalPermissionToGroup(SCAN_EXECUTION, null);
+    permissionDb.addGlobalPermissionToGroup(PROVISIONING, null);
+    permissionDb.addProjectPermissionToGroup(UserRole.ADMIN, group2.getId(), project.getId());
+    permissionDb.addGlobalPermissionToGroup(GlobalPermissions.SYSTEM_ADMIN, group3.getId());
+
+    PermissionQuery.Builder dbQuery = PermissionQuery.builder()
+      .withPermissionOnly()
+      .setPermission(SCAN_EXECUTION);
+    List<String> groupNames = selectGroupNames(dbQuery);
+    int countNames = countGroupNames(dbQuery);
+    List<GroupRoleDto> permissions = selectGroupPermissions(dbQuery);
+
+    assertThat(groupNames).containsExactly(DefaultGroups.ANYONE, "Group-1");
+    assertThat(countNames).isEqualTo(2);
+    assertThat(permissions)
+      .extracting(GroupRoleDto::getRole, GroupRoleDto::getGroupId, GroupRoleDto::getResourceId)
+      .containsOnlyOnce(
+        tuple(SCAN_EXECUTION, 0L, null),
+        tuple(SCAN_EXECUTION, group1.getId(), null));
+  }
+
+  @Test
+  public void select_groups_by_query_with_project_permissions() {
+    GroupDto group1 = groupDb.insertGroup();
+    GroupDto group2 = groupDb.insertGroup();
+    GroupDto group3 = groupDb.insertGroup();
+
+    ComponentDto project = componentDb.insertComponent(newProjectDto());
+    ComponentDto anotherProject = componentDb.insertComponent(newProjectDto());
+
+    permissionDb.addProjectPermissionToGroup(SCAN_EXECUTION, group1.getId(), project.getId());
+    permissionDb.addProjectPermissionToGroup(PROVISIONING, group1.getId(), project.getId());
+    permissionDb.addProjectPermissionToGroup(SYSTEM_ADMIN, group1.getId(), anotherProject.getId());
+    permissionDb.addProjectPermissionToGroup(SYSTEM_ADMIN, null, anotherProject.getId());
+    permissionDb.addGlobalPermissionToGroup(SCAN_EXECUTION, group2.getId());
+    permissionDb.addProjectPermissionToGroup(SCAN_EXECUTION, group3.getId(), anotherProject.getId());
+
+    PermissionQuery.Builder dbQuery = PermissionQuery.builder()
+      .setComponentUuid(project.uuid())
+      .withPermissionOnly();
+    List<String> groupNames = selectGroupNames(dbQuery);
+    int countNames = countGroupNames(dbQuery);
+    List<GroupRoleDto> permissions = selectGroupPermissions(dbQuery);
+
+    assertThat(groupNames).containsOnlyOnce(group1.getName());
+    assertThat(countNames).isEqualTo(1);
+    assertThat(permissions).hasSize(2)
+      .extracting(GroupRoleDto::getRole, GroupRoleDto::getGroupId, GroupRoleDto::getResourceId)
+      .containsOnlyOnce(
+        tuple(SCAN_EXECUTION, group1.getId(), project.getId()),
+        tuple(PROVISIONING, group1.getId(), project.getId()));
+  }
+
+  @Test
+  public void select_groups_by_query_in_group_names() {
+    groupDb.insertGroup(newGroupDto().setName("group-name-1"));
+    groupDb.insertGroup(newGroupDto().setName("group-name-2"));
+    groupDb.insertGroup(newGroupDto().setName("another-group-name"));
+
+    PermissionQuery.Builder dbQuery = PermissionQuery.builder().setGroupNames(newArrayList("group-name-1", "group-name-2"));
+    List<String> groupNames = selectGroupNames(dbQuery);
+    int countNames = countGroupNames(dbQuery);
+    List<GroupRoleDto> permissions = selectGroupPermissions(dbQuery);
+
+    assertThat(groupNames).containsOnlyOnce("group-name-1", "group-name-2");
+    assertThat(countNames).isEqualTo(2);
+    assertThat(permissions).hasSize(2);
+  }
+
+  @Test
+  public void select_groups_by_query_paginated() {
+    IntStream.rangeClosed(0, 9).forEach(i -> groupDb.insertGroup(newGroupDto().setName(i + "-name")));
+
+    PermissionQuery.Builder dbQuery = PermissionQuery.builder().setPageIndex(2).setPageSize(3);
+    List<String> groupNames = selectGroupNames(dbQuery);
+    int countNames = countGroupNames(dbQuery);
+
+    assertThat(groupNames).containsExactly("3-name", "4-name", "5-name");
+    assertThat(countNames).isEqualTo(10);
+  }
+
+  @Test
+  public void select_groups_by_query_with_search_query() {
+    GroupDto group = groupDb.insertGroup(newGroupDto().setName("group-anyone"));
+    groupDb.insertGroup(newGroupDto().setName("unknown"));
+
+    permissionDb.addGlobalPermissionToGroup(SCAN_EXECUTION, group.getId());
+
+    PermissionQuery.Builder dbQuery = PermissionQuery.builder().setSearchQuery("any");
+    List<String> groupNames = selectGroupNames(dbQuery);
+    int countNames = countGroupNames(dbQuery);
+
+    assertThat(groupNames).containsOnlyOnce(DefaultGroups.ANYONE, "group-anyone");
+    assertThat(countNames).isEqualTo(2);
+  }
+
+  @Test
+  public void select_groups_by_query_does_not_return_anyone_when_group_roles_is_empty() {
+    GroupDto group = groupDb.insertGroup();
+
+    PermissionQuery.Builder dbQuery = PermissionQuery.builder();
+    List<String> groupNames = selectGroupNames(dbQuery);
+    int countNames = countGroupNames(dbQuery);
+
+    assertThat(groupNames)
+      .doesNotContain(DefaultGroups.ANYONE)
+      .containsExactly(group.getName());
+    assertThat(countNames).isEqualTo(1);
+  }
+
+  private List<String> selectGroupNames(PermissionQuery.Builder dbQuery) {
+    return underTest.selectGroupNamesByPermissionQuery(dbSession, dbQuery.build());
   }
 
-  private void insertGroupRole(String permission, @Nullable Long groupId, long componentId) {
-    db.getDbClient().roleDao().insertGroupRole(session, new GroupRoleDto()
-      .setRole(permission)
-      .setGroupId(groupId)
-      .setResourceId(componentId));
+  private int countGroupNames(PermissionQuery.Builder dbQuery) {
+    return underTest.countGroupsByPermissionQuery(dbSession, dbQuery.build());
   }
 
-  private void commit() {
-    session.commit();
+  private List<GroupRoleDto> selectGroupPermissions(PermissionQuery.Builder dbQuery) {
+    return underTest.selectGroupPermissionsByQuery(dbSession, dbQuery.build());
   }
 }
diff --git a/sonar-db/src/test/java/org/sonar/db/permission/PermissionDbTester.java b/sonar-db/src/test/java/org/sonar/db/permission/PermissionDbTester.java
index e2c390cb7a8..a75a2b3ef3d 100644
--- a/sonar-db/src/test/java/org/sonar/db/permission/PermissionDbTester.java
+++ b/sonar-db/src/test/java/org/sonar/db/permission/PermissionDbTester.java
@@ -20,9 +20,11 @@
 
 package org.sonar.db.permission;
 
+import javax.annotation.Nullable;
 import org.sonar.db.DbClient;
 import org.sonar.db.DbSession;
 import org.sonar.db.DbTester;
+import org.sonar.db.user.GroupRoleDto;
 import org.sonar.db.user.UserPermissionDto;
 
 public class PermissionDbTester {
@@ -50,4 +52,19 @@ public class PermissionDbTester {
       .setUserId(userId));
     db.commit();
   }
+
+  public void addProjectPermissionToGroup(String permission, @Nullable Long groupId, long componentId) {
+    dbClient.roleDao().insertGroupRole(dbSession, new GroupRoleDto()
+      .setRole(permission)
+      .setGroupId(groupId)
+      .setResourceId(componentId));
+    db.commit();
+  }
+
+  public void addGlobalPermissionToGroup(String permission, @Nullable Long groupId) {
+    dbClient.roleDao().insertGroupRole(dbSession, new GroupRoleDto()
+      .setRole(permission)
+      .setGroupId(groupId));
+    db.commit();
+  }
 }