SONAR-7839 Change WS api/permissions/template_groups to return all permissions

7 files changed, 295 insertions, 41 deletions

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 3addc4babe0..8ac55f7cd50 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
@@ -71,6 +71,8 @@ public class PermissionQuery {
     return withPermissionOnly;
   }
 
+  // TODO remove it, it should not be in the query, but set as a separate parameter
+  @Deprecated
   public String template() {
     return template;
   }
@@ -155,7 +157,7 @@ public class PermissionQuery {
     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(searchQuery == null || searchQuery.length() >= SEARCH_QUERY_MIN_LENGTH, "Search query should contains at least %s characters", SEARCH_QUERY_MIN_LENGTH);
       return new PermissionQuery(this);
     }
   }
diff --git a/sonar-db/src/main/java/org/sonar/db/permission/PermissionRepository.java b/sonar-db/src/main/java/org/sonar/db/permission/PermissionRepository.java
index bbf20b323ce..a0cb5b868a9 100644
--- a/sonar-db/src/main/java/org/sonar/db/permission/PermissionRepository.java
+++ b/sonar-db/src/main/java/org/sonar/db/permission/PermissionRepository.java
@@ -28,7 +28,6 @@ import java.util.stream.Collectors;
 import javax.annotation.Nullable;
 import org.apache.commons.lang.StringUtils;
 import org.sonar.api.config.Settings;
-import org.sonar.api.security.DefaultGroups;
 import org.sonar.db.DbClient;
 import org.sonar.db.DbSession;
 import org.sonar.db.component.ComponentDto;
@@ -41,6 +40,8 @@ import org.sonar.db.user.GroupDto;
 import org.sonar.db.user.GroupRoleDto;
 import org.sonar.db.user.UserPermissionDto;
 
+import static org.sonar.api.security.DefaultGroups.isAnyone;
+
 /**
  * This facade wraps db operations related to permissions
  * <p/>
@@ -104,7 +105,7 @@ public class PermissionRepository {
   }
 
   public void insertGroupPermission(@Nullable Long resourceId, String groupName, String permission, DbSession session) {
-    if (DefaultGroups.isAnyone(groupName)) {
+    if (isAnyone(groupName)) {
       insertGroupPermission(resourceId, (Long) null, permission, session);
     } else {
       GroupDto group = dbClient.groupDao().selectByName(session, groupName);
@@ -124,7 +125,7 @@ public class PermissionRepository {
   }
 
   public void deleteGroupPermission(@Nullable Long resourceId, String groupName, String permission, DbSession session) {
-    if (DefaultGroups.isAnyone(groupName)) {
+    if (isAnyone(groupName)) {
       deleteGroupPermission(resourceId, (Long) null, permission, session);
     } else {
       GroupDto group = dbClient.groupDao().selectByName(session, groupName);
@@ -156,7 +157,8 @@ public class PermissionRepository {
     usersPermissions.forEach(userPermission -> insertUserPermission(componentId, userPermission.getUserId(), userPermission.getPermission(), false, session));
 
     List<PermissionTemplateGroupDto> groupsPermissions = permissionTemplate.getGroupPermissions();
-    groupsPermissions.forEach(groupPermission -> insertGroupPermission(componentId, groupPermission.getGroupId(), groupPermission.getPermission(), false, session));
+    groupsPermissions.forEach(groupPermission -> insertGroupPermission(componentId, isAnyone(groupPermission.getGroupName()) ? null : groupPermission.getGroupId(),
+      groupPermission.getPermission(), false, session));
 
     List<PermissionTemplateCharacteristicDto> characteristics = permissionTemplate.getCharacteristics();
     if (currentUserId != null) {
diff --git a/sonar-db/src/main/java/org/sonar/db/permission/template/PermissionTemplateDao.java b/sonar-db/src/main/java/org/sonar/db/permission/template/PermissionTemplateDao.java
index f62df9e463f..1870f82c06a 100644
--- a/sonar-db/src/main/java/org/sonar/db/permission/template/PermissionTemplateDao.java
+++ b/sonar-db/src/main/java/org/sonar/db/permission/template/PermissionTemplateDao.java
@@ -82,19 +82,38 @@ public class PermissionTemplateDao implements Dao {
     return mapper(dbSession).selectUserPermissionsByTemplateIdAndUserLogins(templateId, Collections.emptyList());
   }
 
+  public List<String> selectGroupNamesByQueryAndTemplate(DbSession session, PermissionQuery query, long templateId) {
+    return mapper(session).selectGroupNamesByQueryAndTemplate(query, templateId, new RowBounds(query.getPageOffset(), query.getPageSize()));
+  }
+
+  public int countGroupNamesByQueryAndTemplate(DbSession session, PermissionQuery query, long templateId) {
+    return mapper(session).countGroupNamesByQueryAndTemplate(query, templateId);
+  }
+
+  public List<PermissionTemplateGroupDto> selectGroupPermissionsByTemplateIdAndGroupNames(DbSession dbSession, long templateId, List<String> groups) {
+    return executeLargeInputs(groups, g -> mapper(dbSession).selectGroupPermissionsByTemplateIdAndGroupNames(templateId, g));
+  }
+
+  public List<PermissionTemplateGroupDto> selectGroupPermissionsByTemplateId(DbSession dbSession, long templateId) {
+    return mapper(dbSession).selectGroupPermissionsByTemplateIdAndGroupNames(templateId, Collections.emptyList());
+  }
+
   /**
    * 'Anyone' group is not returned when it has not the asked permission.
    * Membership parameter from query is not taking into account in order to deal more easily with the 'Anyone' group.
    * @return a non paginated list of groups.
    */
+  @Deprecated
   public List<GroupWithPermissionDto> selectGroups(DbSession session, OldPermissionQuery query, Long templateId) {
     return selectGroups(session, query, templateId, 0, Integer.MAX_VALUE);
   }
 
+  @Deprecated
   public List<GroupWithPermissionDto> selectGroups(DbSession session, OldPermissionQuery query, Long templateId, int offset, int limit) {
     return mapper(session).selectGroups(query, templateId, ANYONE, ADMIN, new RowBounds(offset, limit));
   }
 
+  @Deprecated
   public List<GroupWithPermissionDto> selectGroups(OldPermissionQuery query, Long templateId) {
     DbSession session = myBatis.openSession(false);
     try {
@@ -104,6 +123,7 @@ public class PermissionTemplateDao implements Dao {
     }
   }
 
+  @Deprecated
   public int countGroups(DbSession session, OldPermissionQuery query, long templateId) {
     return countGroups(session, query, templateId, null);
   }
@@ -141,23 +161,13 @@ public class PermissionTemplateDao implements Dao {
     }
 
     List<PermissionTemplateUserDto> userPermissions = selectUserPermissionsByTemplateId(session, template.getId());
-    List<PermissionTemplateGroupDto> groupPermissions = mapper.selectGroupPermissionsByTemplateId(template.getId());
+    List<PermissionTemplateGroupDto> groupPermissions = selectGroupPermissionsByTemplateId(session, template.getId());
     PermissionTemplateCharacteristicMapper characteristicMapper = session.getMapper(PermissionTemplateCharacteristicMapper.class);
     List<PermissionTemplateCharacteristicDto> characteristics = characteristicMapper.selectByTemplateId(template.getId());
 
     return new PermissionTemplate(template, userPermissions, groupPermissions, characteristics);
   }
 
-  @CheckForNull
-  public PermissionTemplate selectByUuidWithUserAndGroupPermissions(String templateUuid) {
-    DbSession session = myBatis.openSession(false);
-    try {
-      return selectByUuidWithUserAndGroupPermissions(session, templateUuid);
-    } finally {
-      MyBatis.closeQuietly(session);
-    }
-  }
-
   public List<PermissionTemplateDto> selectAll(DbSession session, String nameMatch) {
     String uppercaseNameMatch = toUppercaseSqlQuery(nameMatch);
     return mapper(session).selectAll(uppercaseNameMatch);
diff --git a/sonar-db/src/main/java/org/sonar/db/permission/template/PermissionTemplateMapper.java b/sonar-db/src/main/java/org/sonar/db/permission/template/PermissionTemplateMapper.java
index 7b86fc0dc60..c955d70fcd2 100644
--- a/sonar-db/src/main/java/org/sonar/db/permission/template/PermissionTemplateMapper.java
+++ b/sonar-db/src/main/java/org/sonar/db/permission/template/PermissionTemplateMapper.java
@@ -53,7 +53,7 @@ public interface PermissionTemplateMapper {
 
   List<PermissionTemplateUserDto> selectUserPermissionsByTemplateIdAndUserLogins(@Param("templateId") long templateId, @Param("logins") List<String> logins);
 
-  List<PermissionTemplateGroupDto> selectGroupPermissionsByTemplateId(long templateId);
+  List<PermissionTemplateGroupDto> selectGroupPermissionsByTemplateIdAndGroupNames(@Param("templateId") long templateId, @Param("groups") List<String> groups);
 
   void insertUserPermission(PermissionTemplateUserDto permissionTemplateUser);
 
@@ -61,9 +61,6 @@ public interface PermissionTemplateMapper {
 
   void deleteByGroupId(long groupId);
 
-  List<GroupWithPermissionDto> selectGroups(@Param("query") OldPermissionQuery query, @Param("templateId") long templateId, @Param("anyoneGroup") String anyoneGroup,
-    @Param("projectAdminPermission") String projectAdminPermission, RowBounds rowBounds);
-
   List<UserWithPermissionDto> selectUsers(@Param("query") OldPermissionQuery query, @Param("templateId") long templateId, RowBounds rowBounds);
 
   PermissionTemplateDto selectByName(String name);
@@ -72,9 +69,16 @@ public interface PermissionTemplateMapper {
 
   int countUserLoginsByQueryAndTemplate(@Param("query") PermissionQuery query, @Param("templateId") long templateId);
 
+  List<GroupWithPermissionDto> selectGroups(@Param("query") OldPermissionQuery query, @Param("templateId") long templateId, @Param("anyoneGroup") String anyoneGroup,
+                                            @Param("projectAdminPermission") String projectAdminPermission, RowBounds rowBounds);
+
   int countGroups(@Param("query") OldPermissionQuery query, @Param("templateId") long templateId, @Param("anyoneGroup") String anyoneGroup,
     @Param("projectAdminPermission") String projectAdminPermission, @Nullable @Param("groupName") String groupName);
 
+  List<String> selectGroupNamesByQueryAndTemplate(@Param("query") PermissionQuery query, @Param("templateId") long templateId, RowBounds rowBounds);
+
+  int countGroupNamesByQueryAndTemplate(@Param("query") PermissionQuery query, @Param("templateId") long templateId);
+
   List<PermissionTemplateDto> selectAll(@Param("nameMatch") String nameMatch);
 
   int countAll(@Param("nameMatch") String nameMatch);
diff --git a/sonar-db/src/main/resources/org/sonar/db/permission/template/PermissionTemplateMapper.xml b/sonar-db/src/main/resources/org/sonar/db/permission/template/PermissionTemplateMapper.xml
index d2f93bac737..93adf4031ef 100644
--- a/sonar-db/src/main/resources/org/sonar/db/permission/template/PermissionTemplateMapper.xml
+++ b/sonar-db/src/main/resources/org/sonar/db/permission/template/PermissionTemplateMapper.xml
@@ -205,6 +205,47 @@
     </where>
   </select>
 
+  <select id="selectGroupNamesByQueryAndTemplate" parameterType="map" resultType="string">
+    SELECT DISTINCT groups.name, LOWER(groups.name), groups.group_id
+    <include refid="groupNamesByQueryAndTemplate" />
+    ORDER BY LOWER(groups.name), groups.name, groups.group_id
+  </select>
+
+  <select id="countGroupNamesByQueryAndTemplate" parameterType="map" resultType="int">
+    SELECT COUNT(1)
+    FROM (
+      SELECT DISTINCT group_id
+      <include refid="groupNamesByQueryAndTemplate" />) g
+  </select>
+
+  <sql id="groupNamesByQueryAndTemplate">
+    FROM
+    (SELECT g.id AS group_id, g.name AS name, ptg.permission_reference AS permission
+    FROM groups g
+    LEFT JOIN perm_templates_groups ptg ON ptg.group_id=g.id AND ptg.template_id=#{templateId}
+    UNION ALL
+    SELECT 0 AS group_id, 'Anyone' AS name, ptg.permission_reference AS permission
+    FROM perm_templates_groups ptg
+    <where>
+      AND ptg.template_id=#{templateId}
+      <if test="query.withPermissionOnly()">
+        AND ptg.group_id IS NULL
+      </if>
+    </where>
+    ) groups
+    <where>
+      <if test="query.searchQueryToSql != null">
+        AND LOWER(groups.name) LIKE #{query.searchQueryToSql} ESCAPE '/'
+      </if>
+      <if test="query.withPermissionOnly()">
+        AND groups.permission IS NOT NULL
+        <if test="query.permission != null">
+          AND groups.permission=#{query.permission}
+        </if>
+      </if>
+    </where>
+  </sql>
+
   <sql id="templateColumns">
     id, name, kee, description, key_pattern AS keyPattern, created_at AS createdAt, updated_at AS updatedAt
   </sql>
@@ -271,19 +312,46 @@
     </where>
   </select>
 
-  <select id="selectGroupPermissionsByTemplateId" parameterType="Long" resultType="PermissionTemplateGroup">
+  <select id="selectGroupPermissionsByTemplateIdAndGroupNames" parameterType="Long" resultType="PermissionTemplateGroup">
     SELECT
-    ptg.id,
-    ptg.template_id as templateId,
-    ptg.permission_reference AS permission,
-    ptg.group_id AS groupId,
-    g.name AS groupName,
-    ptg.created_at as createdAt,
-    ptg.updated_at as updatedAt
-    FROM perm_templates_groups ptg
-    LEFT OUTER JOIN groups g ON g.id=ptg.group_id
-    WHERE ptg.template_id=#{templateId}
-    AND (g.name IS NOT NULL OR ptg.group_id IS NULL)
+      sub.id,
+      sub.templateId,
+      sub.permission,
+      sub.groupId,
+      sub.groupName,
+      sub.createdAt,
+      sub.updatedAt
+    FROM  (
+      SELECT
+        ptg.id,
+        ptg.template_id as templateId,
+        ptg.permission_reference AS permission,
+        ptg.group_id AS groupId,
+        g.name AS groupName,
+        ptg.created_at as createdAt,
+        ptg.updated_at as updatedAt
+        FROM perm_templates_groups ptg
+      INNER JOIN groups g ON g.id=ptg.group_id
+      UNION ALL
+        SELECT
+          ptg.id,
+          ptg.template_id as templateId,
+          ptg.permission_reference AS permission,
+          0 AS groupId,
+          'Anyone' AS groupName,
+          ptg.created_at as createdAt,
+          ptg.updated_at as updatedAt
+        FROM perm_templates_groups ptg
+        WHERE ptg.group_id IS NULL
+    ) sub
+    <where>
+      sub.templateId=#{templateId}
+      <if test="!groups.isEmpty()">
+        AND sub.groupName IN <foreach collection="groups" open="(" close=")" item="group" separator=",">
+        #{group}
+        </foreach>
+      </if>
+    </where>
   </select>
 
   <select id="selectPotentialPermissionsByUserIdAndTemplateId" parameterType="map" resultType="String">
diff --git a/sonar-db/src/test/java/org/sonar/db/permission/template/GroupWithPermissionTemplateDaoTest.java b/sonar-db/src/test/java/org/sonar/db/permission/template/GroupWithPermissionTemplateDaoTest.java
index a924229b49d..ac11218540b 100644
--- a/sonar-db/src/test/java/org/sonar/db/permission/template/GroupWithPermissionTemplateDaoTest.java
+++ b/sonar-db/src/test/java/org/sonar/db/permission/template/GroupWithPermissionTemplateDaoTest.java
@@ -19,19 +19,28 @@
  */
 package org.sonar.db.permission.template;
 
+import java.util.Collections;
 import java.util.List;
+import java.util.stream.IntStream;
 import org.junit.Rule;
 import org.junit.Test;
 import org.sonar.api.utils.System2;
-import org.sonar.api.web.UserRole;
 import org.sonar.db.DbSession;
 import org.sonar.db.DbTester;
 import org.sonar.db.permission.GroupWithPermissionDto;
 import org.sonar.db.permission.OldPermissionQuery;
-import org.sonar.db.permission.template.PermissionTemplateDao;
+import org.sonar.db.permission.PermissionQuery;
+import org.sonar.db.user.GroupDbTester;
+import org.sonar.db.user.GroupDto;
 
+import static java.util.Arrays.asList;
 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.USER;
+import static org.sonar.core.permission.GlobalPermissions.PROVISIONING;
+import static org.sonar.db.permission.PermissionQuery.builder;
+import static org.sonar.db.user.GroupTesting.newGroupDto;
 
 public class GroupWithPermissionTemplateDaoTest {
 
@@ -39,8 +48,12 @@ public class GroupWithPermissionTemplateDaoTest {
 
   @Rule
   public DbTester dbTester = DbTester.create(System2.INSTANCE);
+
   DbSession session = dbTester.getSession();
 
+  GroupDbTester groupDb = new GroupDbTester(dbTester);
+  PermissionTemplateDbTester permissionTemplateDbTester = new PermissionTemplateDbTester(dbTester);
+
   PermissionTemplateDao underTest = dbTester.getDbClient().permissionTemplateDao();
 
   @Test
@@ -80,7 +93,7 @@ public class GroupWithPermissionTemplateDaoTest {
     dbTester.prepareDbUnit(getClass(), "groups_with_permissions.xml");
 
     // Anyone group is returned even if it doesn't have the permission
-    OldPermissionQuery query = OldPermissionQuery.builder().permission(UserRole.USER).build();
+    OldPermissionQuery query = OldPermissionQuery.builder().permission(USER).build();
     List<GroupWithPermissionDto> result = underTest.selectGroups(session, query, TEMPLATE_ID);
     assertThat(result).hasSize(4);
 
@@ -121,4 +134,154 @@ public class GroupWithPermissionTemplateDaoTest {
     assertThat(result.get(3).getName()).isEqualTo("sonar-users");
   }
 
+  @Test
+  public void select_group_names_by_query_and_template() {
+    GroupDto group1 = groupDb.insertGroup(newGroupDto().setName("Group-1"));
+    GroupDto group2 = groupDb.insertGroup(newGroupDto().setName("Group-2"));
+    GroupDto group3 = groupDb.insertGroup(newGroupDto().setName("Group-3"));
+
+    PermissionTemplateDto template = permissionTemplateDbTester.insertTemplate();
+    permissionTemplateDbTester.addGroupToTemplate(template.getId(), group1.getId(), USER);
+    permissionTemplateDbTester.addGroupToTemplate(template.getId(), group1.getId(), ADMIN);
+    permissionTemplateDbTester.addGroupToTemplate(template.getId(), group2.getId(), PROVISIONING);
+
+    PermissionTemplateDto anotherTemplate = permissionTemplateDbTester.insertTemplate();
+    permissionTemplateDbTester.addGroupToTemplate(anotherTemplate.getId(), null, USER);
+    permissionTemplateDbTester.addGroupToTemplate(anotherTemplate.getId(), group1.getId(), PROVISIONING);
+
+    assertThat(selectGroupNamesByQueryAndTemplate(builder().build(), template.getId())).containsOnly("Group-1", "Group-2", "Group-3", "Anyone");
+    assertThat(selectGroupNamesByQueryAndTemplate(builder().withPermissionOnly().build(), template.getId())).containsOnly("Group-1", "Group-2");
+    assertThat(selectGroupNamesByQueryAndTemplate(builder().setPermission(USER).build(), template.getId())).containsOnly("Group-1");
+    assertThat(selectGroupNamesByQueryAndTemplate(builder().setPermission(USER).build(), anotherTemplate.getId())).containsOnly("Anyone");
+    assertThat(selectGroupNamesByQueryAndTemplate(builder().setSearchQuery("groU").build(), template.getId())).containsOnly("Group-1", "Group-2", "Group-3");
+    assertThat(selectGroupNamesByQueryAndTemplate(builder().setSearchQuery("nYo").build(), template.getId())).containsOnly("Anyone");
+    assertThat(selectGroupNamesByQueryAndTemplate(builder().setSearchQuery("p-2").build(), template.getId())).containsOnly("Group-2");
+
+    assertThat(selectGroupNamesByQueryAndTemplate(builder().withPermissionOnly().build(), 123L)).isEmpty();
+    assertThat(selectGroupNamesByQueryAndTemplate(builder().setSearchQuery("unknown").build(), template.getId())).isEmpty();
+  }
+
+  @Test
+  public void select_group_names_by_query_and_template_is_ordered_by_group_names() {
+    GroupDto group2 = groupDb.insertGroup(newGroupDto().setName("Group-2"));
+    groupDb.insertGroup(newGroupDto().setName("Group-3"));
+    groupDb.insertGroup(newGroupDto().setName("Group-1"));
+
+    PermissionTemplateDto template = permissionTemplateDbTester.insertTemplate();
+    permissionTemplateDbTester.addGroupToTemplate(template.getId(), group2.getId(), USER);
+
+    assertThat(selectGroupNamesByQueryAndTemplate(builder().build(), template.getId())).containsExactly("Anyone", "Group-1", "Group-2", "Group-3");
+  }
+
+  @Test
+  public void select_group_names_by_query_and_template_is_paginated() {
+    IntStream.rangeClosed(0, 9).forEach(i -> groupDb.insertGroup(newGroupDto().setName(i + "-name")));
+
+    PermissionTemplateDto template = permissionTemplateDbTester.insertTemplate();
+
+    assertThat(selectGroupNamesByQueryAndTemplate(builder().setPageIndex(1).setPageSize(1).build(), template.getId())).containsExactly("0-name");
+    assertThat(selectGroupNamesByQueryAndTemplate(builder().setPageIndex(2).setPageSize(3).build(), template.getId())).containsExactly("3-name", "4-name", "5-name");
+  }
+
+  @Test
+  public void count_group_names_by_query_and_template() {
+    GroupDto group1 = groupDb.insertGroup(newGroupDto().setName("Group-1"));
+    GroupDto group2 = groupDb.insertGroup(newGroupDto().setName("Group-2"));
+    GroupDto group3 = groupDb.insertGroup(newGroupDto().setName("Group-3"));
+
+    PermissionTemplateDto template = permissionTemplateDbTester.insertTemplate();
+    permissionTemplateDbTester.addGroupToTemplate(template.getId(), group1.getId(), USER);
+    permissionTemplateDbTester.addGroupToTemplate(template.getId(), group1.getId(), ADMIN);
+    permissionTemplateDbTester.addGroupToTemplate(template.getId(), group2.getId(), PROVISIONING);
+
+    PermissionTemplateDto anotherTemplate = permissionTemplateDbTester.insertTemplate();
+    permissionTemplateDbTester.addGroupToTemplate(anotherTemplate.getId(), null, USER);
+    permissionTemplateDbTester.addGroupToTemplate(anotherTemplate.getId(), group1.getId(), PROVISIONING);
+
+    assertThat(countGroupNamesByQueryAndTemplate(builder().build(), template.getId())).isEqualTo(4);
+    assertThat(countGroupNamesByQueryAndTemplate(builder().withPermissionOnly().build(), template.getId())).isEqualTo(2);
+    assertThat(countGroupNamesByQueryAndTemplate(builder().setPermission(USER).build(), template.getId())).isEqualTo(1);
+    assertThat(countGroupNamesByQueryAndTemplate(builder().setPermission(USER).build(), anotherTemplate.getId())).isEqualTo(1);
+    assertThat(countGroupNamesByQueryAndTemplate(builder().setSearchQuery("groU").build(), template.getId())).isEqualTo(3);
+    assertThat(countGroupNamesByQueryAndTemplate(builder().setSearchQuery("nYo").build(), template.getId())).isEqualTo(1);
+    assertThat(countGroupNamesByQueryAndTemplate(builder().setSearchQuery("p-2").build(), template.getId())).isEqualTo(1);
+
+    assertThat(countGroupNamesByQueryAndTemplate(builder().withPermissionOnly().build(), 123L)).isZero();
+    assertThat(countGroupNamesByQueryAndTemplate(builder().setSearchQuery("unknown").build(), template.getId())).isZero();
+  }
+
+  @Test
+  public void select_group_permissions_by_template_id_and_group_names() {
+    GroupDto group1 = groupDb.insertGroup(newGroupDto().setName("Group-1"));
+    GroupDto group2 = groupDb.insertGroup(newGroupDto().setName("Group-2"));
+    GroupDto group3 = groupDb.insertGroup(newGroupDto().setName("Group-3"));
+
+    PermissionTemplateDto template = permissionTemplateDbTester.insertTemplate();
+    permissionTemplateDbTester.addGroupToTemplate(template.getId(), group1.getId(), USER);
+    permissionTemplateDbTester.addGroupToTemplate(template.getId(), group1.getId(), ADMIN);
+    permissionTemplateDbTester.addGroupToTemplate(template.getId(), group2.getId(), PROVISIONING);
+
+    PermissionTemplateDto anotherTemplate = permissionTemplateDbTester.insertTemplate();
+    permissionTemplateDbTester.addGroupToTemplate(anotherTemplate.getId(), null, USER);
+    permissionTemplateDbTester.addGroupToTemplate(anotherTemplate.getId(), group1.getId(), PROVISIONING);
+
+    assertThat(underTest.selectGroupPermissionsByTemplateIdAndGroupNames(session, template.getId(), asList("Group-1")))
+      .extracting(PermissionTemplateGroupDto::getGroupId, PermissionTemplateGroupDto::getGroupName, PermissionTemplateGroupDto::getPermission)
+      .containsOnly(
+        tuple(group1.getId(), "Group-1", USER),
+        tuple(group1.getId(), "Group-1", ADMIN));
+
+    assertThat(underTest.selectGroupPermissionsByTemplateIdAndGroupNames(session, anotherTemplate.getId(), asList("Group-1")))
+      .extracting(PermissionTemplateGroupDto::getGroupId, PermissionTemplateGroupDto::getGroupName, PermissionTemplateGroupDto::getPermission)
+      .containsOnly(
+        tuple(group1.getId(), "Group-1", PROVISIONING));
+
+    assertThat(underTest.selectGroupPermissionsByTemplateIdAndGroupNames(session, anotherTemplate.getId(), asList("Anyone")))
+      .extracting(PermissionTemplateGroupDto::getGroupId, PermissionTemplateGroupDto::getGroupName, PermissionTemplateGroupDto::getPermission)
+      .containsOnly(
+        tuple(0L, "Anyone", USER));
+
+    assertThat(underTest.selectGroupPermissionsByTemplateIdAndGroupNames(session, template.getId(), asList("Group-1", "Group-2", "Anyone"))).hasSize(3);
+    assertThat(underTest.selectGroupPermissionsByTemplateIdAndGroupNames(session, template.getId(), asList("Unknown"))).isEmpty();
+    assertThat(underTest.selectGroupPermissionsByTemplateIdAndGroupNames(session, template.getId(), Collections.emptyList())).isEmpty();
+  }
+
+  @Test
+  public void select_group_permissions_by_template_id() {
+    GroupDto group1 = groupDb.insertGroup(newGroupDto().setName("Group-1"));
+    GroupDto group2 = groupDb.insertGroup(newGroupDto().setName("Group-2"));
+    GroupDto group3 = groupDb.insertGroup(newGroupDto().setName("Group-3"));
+
+    PermissionTemplateDto template = permissionTemplateDbTester.insertTemplate();
+    permissionTemplateDbTester.addGroupToTemplate(template.getId(), group1.getId(), USER);
+    permissionTemplateDbTester.addGroupToTemplate(template.getId(), group1.getId(), ADMIN);
+    permissionTemplateDbTester.addGroupToTemplate(template.getId(), group2.getId(), PROVISIONING);
+
+    PermissionTemplateDto anotherTemplate = permissionTemplateDbTester.insertTemplate();
+    permissionTemplateDbTester.addGroupToTemplate(anotherTemplate.getId(), null, USER);
+    permissionTemplateDbTester.addGroupToTemplate(anotherTemplate.getId(), group1.getId(), PROVISIONING);
+
+    assertThat(underTest.selectGroupPermissionsByTemplateId(session, template.getId()))
+      .extracting(PermissionTemplateGroupDto::getGroupId, PermissionTemplateGroupDto::getGroupName, PermissionTemplateGroupDto::getPermission)
+      .containsOnly(
+        tuple(group1.getId(), "Group-1", USER),
+        tuple(group1.getId(), "Group-1", ADMIN),
+        tuple(group2.getId(), "Group-2", PROVISIONING));
+    assertThat(underTest.selectGroupPermissionsByTemplateId(session, anotherTemplate.getId()))
+      .extracting(PermissionTemplateGroupDto::getGroupId, PermissionTemplateGroupDto::getGroupName, PermissionTemplateGroupDto::getPermission)
+      .containsOnly(
+        tuple(group1.getId(), "Group-1", PROVISIONING),
+        tuple(0L, "Anyone", USER));
+
+    assertThat(underTest.selectGroupPermissionsByTemplateId(session, 321L)).isEmpty();
+  }
+
+  private List<String> selectGroupNamesByQueryAndTemplate(PermissionQuery query, long templateId) {
+    return underTest.selectGroupNamesByQueryAndTemplate(session, query, templateId);
+  }
+
+  private int countGroupNamesByQueryAndTemplate(PermissionQuery query, long templateId) {
+    return underTest.countGroupNamesByQueryAndTemplate(session, query, templateId);
+  }
+
 }
diff --git a/sonar-db/src/test/java/org/sonar/db/permission/template/PermissionTemplateDaoTest.java b/sonar-db/src/test/java/org/sonar/db/permission/template/PermissionTemplateDaoTest.java
index e957c331950..ce77af5a465 100644
--- a/sonar-db/src/test/java/org/sonar/db/permission/template/PermissionTemplateDaoTest.java
+++ b/sonar-db/src/test/java/org/sonar/db/permission/template/PermissionTemplateDaoTest.java
@@ -44,6 +44,7 @@ import static org.assertj.core.api.Assertions.assertThat;
 import static org.assertj.core.api.Assertions.tuple;
 import static org.mockito.Mockito.mock;
 import static org.mockito.Mockito.when;
+import static org.sonar.api.security.DefaultGroups.ANYONE;
 import static org.sonar.api.web.UserRole.ADMIN;
 import static org.sonar.api.web.UserRole.CODEVIEWER;
 import static org.sonar.api.web.UserRole.ISSUE_ADMIN;
@@ -90,7 +91,7 @@ public class PermissionTemplateDaoTest {
   public void should_select_permission_template() {
     db.prepareDbUnit(getClass(), "selectPermissionTemplate.xml");
 
-    PermissionTemplate result = underTest.selectByUuidWithUserAndGroupPermissions("my_template_20130102_030405");
+    PermissionTemplate result = underTest.selectByUuidWithUserAndGroupPermissions(dbSession, "my_template_20130102_030405");
 
     assertThat(result).isNotNull();
     PermissionTemplateDto template = result.getTemplate();
@@ -105,8 +106,8 @@ public class PermissionTemplateDaoTest {
     assertThat(usersPermissions).extracting("permission").containsOnly("user_permission1", "user_permission1", "user_permission2");
     List<PermissionTemplateGroupDto> groupsPermissions = result.getGroupPermissions();
     assertThat(groupsPermissions).hasSize(3);
-    assertThat(groupsPermissions).extracting("groupId").containsOnly(1L, 2L, null);
-    assertThat(groupsPermissions).extracting("groupName").containsOnly("group1", "group2", null);
+    assertThat(groupsPermissions).extracting("groupId").containsOnly(1L, 2L, 0L);
+    assertThat(groupsPermissions).extracting("groupName").containsOnly("group1", "group2", "Anyone");
     assertThat(groupsPermissions).extracting("permission").containsOnly("group_permission1", "group_permission1", "group_permission2");
   }
 
@@ -249,6 +250,7 @@ public class PermissionTemplateDaoTest {
     GroupDto group = groupDb.insertGroup(newGroupDto());
     UserDto user = userDb.insertUser(newUserDto());
     templateDb.addGroupToTemplate(template.getId(), group.getId(), UserRole.ADMIN);
+    templateDb.addGroupToTemplate(template.getId(), null, UserRole.USER);
     templateDb.addUserToTemplate(template.getId(), user.getId(), UserRole.CODEVIEWER);
     templateDb.addProjectCreatorToTemplate(template.getId(), UserRole.USER);
 
@@ -259,9 +261,12 @@ public class PermissionTemplateDaoTest {
     assertThat(result.getCharacteristics()).hasSize(1)
       .extracting(PermissionTemplateCharacteristicDto::getPermission, PermissionTemplateCharacteristicDto::getWithProjectCreator)
       .containsExactly(tuple(UserRole.USER, true));
-    assertThat(result.getGroupPermissions()).hasSize(1)
+    assertThat(result.getGroupPermissions())
       .extracting(PermissionTemplateGroupDto::getGroupId, PermissionTemplateGroupDto::getGroupName, PermissionTemplateGroupDto::getPermission)
-      .containsExactly(tuple(group.getId(), group.getName(), UserRole.ADMIN));
+      .containsOnly(
+        tuple(group.getId(), group.getName(), UserRole.ADMIN),
+        tuple(0L, ANYONE, UserRole.USER)
+      );
     assertThat(result.getUserPermissions()).hasSize(1)
       .extracting(PermissionTemplateUserDto::getUserId, PermissionTemplateUserDto::getUserLogin, PermissionTemplateUserDto::getPermission)
       .containsExactly(tuple(user.getId(), user.getLogin(), UserRole.CODEVIEWER));