SONAR-9090 make projects privates based on permissions

5 files changed, 799 insertions, 2 deletions

diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java
index 580be3b13bc..e596a7024af 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java
@@ -68,6 +68,7 @@ public class DbVersion64 implements DbVersion {
       .add(1637, "Make column PROJECTS.PRIVATE not nullable", MakeColumnProjectsPrivateNotNullable.class)
       .add(1638, "Add column ORGANIZATIONS.NEW_PROJECT_PRIVATE", AddColumnNewProjectPrivate.class)
       .add(1639, "Set ORGANIZATIONS.NEW_PROJECT_PRIVATE to false", SetNewProjectPrivateToFalse.class)
-      .add(1640, "Make column ORGANIZATIONS.NEW_PROJECT_PRIVATE not nullable", MakeColumnNewProjectPrivateNotNullable.class);
+      .add(1640, "Make column ORGANIZATIONS.NEW_PROJECT_PRIVATE not nullable", MakeColumnNewProjectPrivateNotNullable.class)
+      .add(1641, "Make components private based on permissions", MakeComponentsPrivateBasedOnPermissions.class);
   }
 }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/MakeComponentsPrivateBasedOnPermissions.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/MakeComponentsPrivateBasedOnPermissions.java
new file mode 100644
index 00000000000..32aa0a5dac1
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/MakeComponentsPrivateBasedOnPermissions.java
@@ -0,0 +1,271 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2017 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v64;
+
+import java.sql.SQLException;
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.step.DataChange;
+import org.sonar.server.platform.db.migration.step.MassUpdate;
+import org.sonar.server.platform.db.migration.step.Select;
+import org.sonar.server.platform.db.migration.step.SqlStatement;
+
+/**
+ * This DB migration assumes the whole PROJECTS table contains only rows with private=false
+ * (set by {@link PopulateColumnProjectsPrivate}) and performs the following:
+ * <ul>
+ *   <li>set private=true for any tree of component which root has neither user nor codeviewer permission for group AnyOne
+ *       but defines at least one permission directly to a group or user</li>
+ *   <li>removes any permission to group AnyOne for root components which are made private</li>
+ *   <li>ensures any group or user with direct permission to a private root component also has both permissions
+ *       user and codeviewer</li>
+ *   <li>deletes any permission user or codeviewer for root components which stays public</li>
+ * </ul>
+ * This DB migration of course works if PROJECTS table contains rows with private=true, but it will assume they have
+ * been set by a previous run of itself (ie. the DB migration is reentrant).
+ */
+public class MakeComponentsPrivateBasedOnPermissions extends DataChange {
+
+  private static final String SCOPE_PROJECT = "PRJ";
+  private static final String QUALIFIER_PROJECT = "TRK";
+  private static final String QUALIFIER_VIEW = "VW";
+  private static final String PERMISSION_USER = "user";
+  private static final String PERMISSION_CODEVIEWER = "codeviewer";
+
+  public MakeComponentsPrivateBasedOnPermissions(Database db) {
+    super(db);
+  }
+
+  @Override
+  protected void execute(Context context) throws SQLException {
+    makePrivateComponent(context);
+    cleanPermissionsOfPublicComponents(context);
+    insertUserPermissionOfPrivateRootComponent(context, PERMISSION_USER);
+    insertUserPermissionOfPrivateRootComponent(context, PERMISSION_CODEVIEWER);
+    insertGroupPermissionOfPrivateRootComponent(context, PERMISSION_USER);
+    insertGroupPermissionOfPrivateRootComponent(context, PERMISSION_CODEVIEWER);
+  }
+
+  private static void makePrivateComponent(Context context) throws SQLException {
+    MassUpdate massUpdate = context.prepareMassUpdate();
+    massUpdate.select("select uuid, id from projects p where " +
+      " p.scope = ?" +
+      " and p.qualifier in (?, ?)" +
+      " and p.private = ?" +
+      " and not exists (" +
+      "   select" +
+      "     1" +
+      "   from group_roles gr" +
+      "   where " +
+      "     gr.resource_id = p.id" +
+      "     and gr.group_id is null" +
+      "     and gr.role in (?, ?)" +
+      " )" +
+      // trees with only permissions to group must not be made private
+      " and (" +
+      "   exists (" +
+      "     select" +
+      "       1" +
+      "     from " +
+      "       group_roles gr2" +
+      "     where" +
+      "       gr2.resource_id = p.id" +
+      "       and gr2.group_id is not null" +
+      "    )" +
+      "    or exists (" +
+      "      select" +
+      "        1" +
+      "      from " +
+      "        user_roles ur" +
+      "      where" +
+      "        ur.resource_id = p.id" +
+      "    )" +
+      ")")
+      .setString(1, SCOPE_PROJECT)
+      .setString(2, QUALIFIER_PROJECT)
+      .setString(3, QUALIFIER_VIEW)
+      .setBoolean(4, false)
+      .setString(5, PERMISSION_USER)
+      .setString(6, PERMISSION_CODEVIEWER);
+    massUpdate.rowPluralName("component trees to be made private");
+    // make project private
+    massUpdate.update("update projects set private = ? where project_uuid = ?");
+    // delete any permission given to group "Anyone"
+    massUpdate.update("delete from group_roles where resource_id = ? and group_id is null");
+    massUpdate.execute(MakeComponentsPrivateBasedOnPermissions::handleMakePrivateComponent);
+  }
+
+  private static boolean handleMakePrivateComponent(Select.Row row, SqlStatement update, int updateIndex) throws SQLException {
+    String rootUuid = row.getString(1);
+    long id = row.getLong(2);
+    switch (updateIndex) {
+      case 0:
+        update.setBoolean(1, true);
+        update.setString(2, rootUuid);
+        return true;
+      case 1:
+        update.setLong(1, id);
+        return true;
+      default:
+        throw new IllegalArgumentException("Unsupported update index " + updateIndex);
+    }
+  }
+
+  private static void cleanPermissionsOfPublicComponents(Context context) throws SQLException {
+    MassUpdate massUpdate = context.prepareMassUpdate();
+    massUpdate.select("select id from projects p where " +
+      " p.scope = ?" +
+      " and p.qualifier in (?, ?)" +
+      " and p.private = ?" +
+      " and exists (" +
+      "   select" +
+      "     1" +
+      "   from group_roles gr" +
+      "   where " +
+      "     gr.resource_id = p.id" +
+      "     and gr.role in (?, ?)" +
+      "   union" +
+      "   select" +
+      "     1" +
+      "   from user_roles gr" +
+      "   where " +
+      "     gr.resource_id = p.id" +
+      "     and gr.role in (?, ?)" +
+      ")")
+      .setString(1, SCOPE_PROJECT)
+      .setString(2, QUALIFIER_PROJECT)
+      .setString(3, QUALIFIER_VIEW)
+      .setBoolean(4, false)
+      .setString(5, PERMISSION_USER)
+      .setString(6, PERMISSION_CODEVIEWER)
+      .setString(7, PERMISSION_USER)
+      .setString(8, PERMISSION_CODEVIEWER);
+    massUpdate.rowPluralName("public component trees to clean permissions of");
+    massUpdate.update("delete from group_roles where resource_id = ? and role in ('user', 'codeviewer')");
+    massUpdate.update("delete from user_roles where resource_id = ? and role in ('user', 'codeviewer')");
+    massUpdate.execute(MakeComponentsPrivateBasedOnPermissions::handleCleanPermissionsOfPublicComponents);
+  }
+
+  private static boolean handleCleanPermissionsOfPublicComponents(Select.Row row, SqlStatement update, int updateIndex) throws SQLException {
+    long id = row.getLong(1);
+    switch (updateIndex) {
+      case 0:
+      case 1:
+        update.setLong(1, id);
+        return true;
+      default:
+        throw new IllegalArgumentException("Unsupported update index " + updateIndex);
+    }
+  }
+
+  private static void insertUserPermissionOfPrivateRootComponent(Context context, String permission) throws SQLException {
+    MassUpdate massUpdate = context.prepareMassUpdate();
+    massUpdate.select("select" +
+      " distinct r1.user_id, p.organization_uuid, p.id" +
+      " from" +
+      "   user_roles r1" +
+      " inner join projects p on" +
+      "   p.id = r1.resource_id" +
+      "   and p.scope = ?" +
+      "   and p.qualifier in (?, ?)" +
+      "   and p.private = ?" +
+      " where" +
+      "   not exists (" +
+      "     select" +
+      "       1" +
+      "     from" +
+      "       user_roles r2" +
+      "     where " +
+      "       r2.user_id = r1.user_id" +
+      "       and r2.resource_id = r1.resource_id" +
+      "       and r2.role = ?" +
+      ")")
+      .setString(1, SCOPE_PROJECT)
+      .setString(2, QUALIFIER_PROJECT)
+      .setString(3, QUALIFIER_VIEW)
+      .setBoolean(4, true)
+      .setString(5, permission);
+    massUpdate.rowPluralName("users of private component tree without " + permission + " permission");
+    massUpdate.update("insert into user_roles" +
+      " (organization_uuid, user_id, resource_id, role)" +
+      " values" +
+      " (?, ?, ?, ?)");
+    massUpdate.execute((row, update) -> insertUserPermission(row, update, permission));
+  }
+
+  private static boolean insertUserPermission(Select.Row row, SqlStatement update, String permission) throws SQLException {
+    int userId = row.getInt(1);
+    String organizationUuid = row.getString(2);
+    int resourceId = row.getInt(3);
+
+    update.setString(1, organizationUuid);
+    update.setInt(2, userId);
+    update.setInt(3, resourceId);
+    update.setString(4, permission);
+    return true;
+  }
+
+  private static void insertGroupPermissionOfPrivateRootComponent(Context context, String permission) throws SQLException {
+    MassUpdate massUpdate = context.prepareMassUpdate();
+    massUpdate.select("select" +
+      " distinct g1.group_id, p.organization_uuid, p.id" +
+      " from" +
+      "   group_roles g1" +
+      " inner join projects p on" +
+      "   p.id = g1.resource_id" +
+      "   and p.scope = ?" +
+      "   and p.qualifier in (?, ?)" +
+      "   and p.private = ?" +
+      " where" +
+      "   g1.group_id is not null" +
+      "   and not exists (" +
+      "     select" +
+      "       1" +
+      "     from" +
+      "       group_roles g2" +
+      "     where " +
+      "       g2.group_id = g1.group_id" +
+      "       and g2.resource_id = g1.resource_id" +
+      "       and g2.role = ?" +
+      ")")
+      .setString(1, SCOPE_PROJECT)
+      .setString(2, QUALIFIER_PROJECT)
+      .setString(3, QUALIFIER_VIEW)
+      .setBoolean(4, true)
+      .setString(5, permission);
+    massUpdate.rowPluralName("groups of private component tree without " + permission + " permission");
+    massUpdate.update("insert into group_roles" +
+      " (organization_uuid, group_id, resource_id, role)" +
+      " values" +
+      " (?, ?, ?, ?)");
+    massUpdate.execute((row, update) -> insertGroupPermission(row, update, permission));
+  }
+
+  private static boolean insertGroupPermission(Select.Row row, SqlStatement update, String permission) throws SQLException {
+    int groupId = row.getInt(1);
+    String organizationUuid = row.getString(2);
+    int resourceId = row.getInt(3);
+
+    update.setString(1, organizationUuid);
+    update.setInt(2, groupId);
+    update.setInt(3, resourceId);
+    update.setString(4, permission);
+    return true;
+  }
+}
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java
index 36c5c49d8bb..336f8b89dbd 100644
--- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java
@@ -35,6 +35,6 @@ public class DbVersion64Test {
 
   @Test
   public void verify_migration_count() {
-    verifyMigrationCount(underTest, 41);
+    verifyMigrationCount(underTest, 42);
   }
 }
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/MakeComponentsPrivateBasedOnPermissionsTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/MakeComponentsPrivateBasedOnPermissionsTest.java
new file mode 100644
index 00000000000..6c8d3189fa9
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/MakeComponentsPrivateBasedOnPermissionsTest.java
@@ -0,0 +1,432 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2017 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v64;
+
+import java.sql.SQLException;
+import java.util.Map;
+import java.util.Random;
+import java.util.Set;
+import java.util.stream.Stream;
+import javax.annotation.Nullable;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.core.util.stream.MoreCollectors;
+import org.sonar.db.CoreDbTester;
+
+import static java.lang.String.valueOf;
+import static org.assertj.core.api.Assertions.assertThat;
+
+public class MakeComponentsPrivateBasedOnPermissionsTest {
+  private static final String ROLE_USER = "user";
+  private static final String ROLE_CODEVIEWER = "codeviewer";
+  private static final String PROJECT_QUALIFIER = "TRK";
+  private static final String VIEW_QUALIFIER = "VW";
+
+  @Rule
+  public CoreDbTester db = CoreDbTester.createForSchema(MakeComponentsPrivateBasedOnPermissionsTest.class, "projects_and_group_roles_and_user_roles.sql");
+
+  private final Random random = new Random();
+  private final String randomPublicConditionRole = random.nextBoolean() ? ROLE_CODEVIEWER : ROLE_USER;
+  private final String randomQualifier = random.nextBoolean() ? PROJECT_QUALIFIER : VIEW_QUALIFIER;
+  private final String randomRole = "role_" + random.nextInt(12);
+  private final int randomUserId = random.nextInt(500);
+  private final int randomGroupId = random.nextInt(500);
+  private MakeComponentsPrivateBasedOnPermissions underTest = new MakeComponentsPrivateBasedOnPermissions(db.database());
+
+  @Test
+  public void execute_does_nothing_on_empty_tables() throws SQLException {
+    underTest.execute();
+  }
+
+  @Test
+  public void execute_makes_project_private_if_group_AnyOne_has_global_permission_USER() throws SQLException {
+    long pId = insertRootComponent("p1", false);
+    insertGroupPermission(ROLE_USER, null, null);
+    insertGroupPermission(randomRole, pId, randomGroupId);
+
+    underTest.execute();
+
+    assertThat(isPrivate("p1")).isTrue();
+  }
+
+  @Test
+  public void execute_makes_project_private_if_group_AnyOne_has_global_permission_BROWSE() throws SQLException {
+    long pId = insertRootComponent("p1", false);
+    insertGroupPermission(ROLE_CODEVIEWER, null, null);
+    insertUserPermission(randomRole, pId, randomUserId);
+
+    underTest.execute();
+
+    assertThat(isPrivate("p1")).isTrue();
+  }
+
+  @Test
+  public void execute_makes_project_private_if_group_other_than_AnyOne_has_permission_BROWSE_on_other_project() throws SQLException {
+    long pId1 = insertRootComponent("p1", false);
+    insertGroupPermission(ROLE_CODEVIEWER, pId1, random.nextInt(30));
+
+    underTest.execute();
+
+    assertThat(isPrivate("p1")).isTrue();
+  }
+
+  @Test
+  public void execute_makes_project_private_if_group_other_than_AnyOne_has_permission_USER_on_other_project() throws SQLException {
+    long pId1 = insertRootComponent("p1", false);
+    insertGroupPermission(ROLE_USER, pId1, random.nextInt(30));
+
+    underTest.execute();
+
+    assertThat(isPrivate("p1")).isTrue();
+  }
+
+  @Test
+  public void execute_keeps_project_public_if_group_AnyOne_has_permission_USER_on_it() throws SQLException {
+    long pId1 = insertRootComponent("p1", false);
+    insertGroupPermission(ROLE_USER, pId1, null);
+
+    underTest.execute();
+
+    assertThat(isPrivate("p1")).isFalse();
+  }
+
+  @Test
+  public void execute_keeps_project_public_if_group_AnyOne_has_permission_BROWSE_on_it() throws SQLException {
+    long pId1 = insertRootComponent("p1", false);
+    insertGroupPermission(ROLE_CODEVIEWER, pId1, null);
+
+    underTest.execute();
+
+    assertThat(isPrivate("p1")).isFalse();
+  }
+
+  @Test
+  public void execute_keeps_project_public_if_only_group_AnyOne_has_permission_on_it() throws SQLException {
+    long pId1 = insertRootComponent("p1", false);
+    insertGroupPermission(randomRole, pId1, null);
+
+    underTest.execute();
+
+    assertThat(isPrivate("p1")).isFalse();
+  }
+
+  @Test
+  public void execute_keeps_project_public_if_project_has_no_permission() throws SQLException {
+    insertRootComponent("p1", false);
+
+    underTest.execute();
+
+    assertThat(isPrivate("p1")).isFalse();
+  }
+
+  @Test
+  public void execute_does_not_change_private_projects_to_public_when_they_actually_should_be_because_they_have_USER_or_BROWSE_on_group_Anyone() throws SQLException {
+    long p1Id = insertRootComponent("p1", true);
+    long p2Id = insertRootComponent("p2", true);
+    long p3Id = insertRootComponent("p3", true);
+    insertGroupPermission(ROLE_CODEVIEWER, p1Id, null);
+    insertGroupPermission(ROLE_USER, p1Id, null);
+    insertGroupPermission(ROLE_CODEVIEWER, p2Id, null);
+    insertGroupPermission(ROLE_USER, p3Id, null);
+
+    underTest.execute();
+
+    assertThat(isPrivate("p1")).isTrue();
+    assertThat(isPrivate("p2")).isTrue();
+    assertThat(isPrivate("p3")).isTrue();
+  }
+
+  @Test
+  public void execute_changes_non_root_rows_to_private_based_on_permissions_of_their_root_row() throws SQLException {
+    // root stays public, children are unchanged
+    long pId1 = insertRootComponent("root1", false);
+    insertGroupPermission(randomPublicConditionRole, pId1, null);
+    insertComponent("u1", "root1", false);
+    // root becomes privates, children are changed accordingly
+    long pId2 = insertRootComponent("root2", false);
+    int someUserId = random.nextInt(50);
+    insertGroupPermission(randomRole, pId2, someUserId);
+    insertComponent("u2", "root2", false);
+    insertComponent("u3", "root2", true);
+
+    underTest.execute();
+
+    assertThat(isPrivate("root1")).isFalse();
+    assertThat(isPrivate("u1")).isFalse();
+    assertThat(isPrivate("root2")).isTrue();
+    assertThat(isPrivate("u2")).isTrue();
+    assertThat(isPrivate("u3")).isTrue();
+  }
+
+  @Test
+  public void execute_does_not_fix_inconsistencies_of_non_root_rows_if_root_stays_public_or_is_already_private() throws SQLException {
+    // root stays public, children are unchanged
+    long pId1 = insertRootComponent("root1", false);
+    insertGroupPermission(randomPublicConditionRole, pId1, null);
+    insertComponent("u1", "root1", false);
+    insertComponent("u2", "root1", true); // inconsistent information is not fixed
+    // root is already private but children are inconsistent => not fixed
+    insertRootComponent("root2", true);
+    insertGroupPermission(randomPublicConditionRole, pId1, null);
+    insertComponent("u3", "root2", false);
+    insertComponent("u4", "root2", true);
+
+    underTest.execute();
+
+    assertThat(isPrivate("root1")).isFalse();
+    assertThat(isPrivate("u1")).isFalse();
+    assertThat(isPrivate("u2")).isTrue();
+    assertThat(isPrivate("root2")).isTrue();
+    assertThat(isPrivate("u3")).isFalse();
+    assertThat(isPrivate("u4")).isTrue();
+  }
+
+  @Test
+  public void execute_does_change_non_root_rows_which_root_does_not_exist() throws SQLException {
+    // non existent root, won't be changed
+    long pId1 = insertComponent("u1", "non existent root", false);
+    insertGroupPermission(randomPublicConditionRole, pId1, null);
+    insertComponent("u2", "non existent root", true);
+
+    underTest.execute();
+
+    assertThat(isPrivate("u1")).isFalse();
+    assertThat(isPrivate("u2")).isTrue();
+  }
+
+  @Test
+  public void execute_deletes_any_permission_to_group_Anyone_for_root_components_which_are_made_private() throws SQLException {
+    long idRoot1 = insertRootComponent("root1", false);
+    int someGroupId = random.nextInt(50);
+    int someUserId = random.nextInt(50);
+    insertGroupPermission(randomRole, idRoot1, null);
+    insertGroupPermission(randomRole, idRoot1, someGroupId);
+    insertUserPermission(randomRole, idRoot1, someUserId);
+
+    underTest.execute();
+
+    assertThat(isPrivate("root1")).isTrue();
+    assertThat(permissionsOfGroupAnyone(idRoot1)).isEmpty();
+    assertThat(permissionsOfGroup(idRoot1, someGroupId)).containsOnly(randomRole, ROLE_USER, ROLE_CODEVIEWER);
+    assertThat(permissionsOfUser(idRoot1, someUserId)).containsOnly(randomRole, ROLE_USER, ROLE_CODEVIEWER);
+  }
+
+  @Test
+  public void execute_ensures_any_user_of_with_at_least_one_permission_on_root_component_which_is_made_private_also_has_permissions_USER_and_CODEVIEWER() throws SQLException {
+    long idRoot = insertRootComponent("root1", false);
+    String someRole = "role_" + random.nextInt(12);
+    int user1 = insertUser();
+    int user2 = insertUser();
+    insertUserPermission(someRole, idRoot, user1);
+
+    underTest.execute();
+
+    assertThat(isPrivate("root1")).isTrue();
+    assertThat(permissionsOfGroupAnyone(idRoot)).isEmpty();
+    assertThat(permissionsOfUser(idRoot, user1)).containsOnly(someRole, ROLE_USER, ROLE_CODEVIEWER);
+    assertThat(permissionsOfUser(idRoot, user2)).isEmpty();
+  }
+
+  @Test
+  public void execute_ensures_any_group_of_with_at_least_one_permission_on_root_component_which_is_made_private_also_has_permissions_USER_and_CODEVIEWER() throws SQLException {
+    long idRoot = insertRootComponent("root1", false);
+    String someRole = "role_" + random.nextInt(12);
+    int group1 = insertGroup();
+    int group2 = insertGroup();
+    insertGroupPermission(someRole, idRoot, group1);
+
+    underTest.execute();
+
+    assertThat(isPrivate("root1")).isTrue();
+    assertThat(permissionsOfGroup(idRoot, group1)).containsOnly(someRole, ROLE_USER, ROLE_CODEVIEWER);
+    assertThat(permissionsOfGroup(idRoot, group2)).isEmpty();
+  }
+
+  @Test
+  public void execute_does_not_delete_permissions_to_group_Anyone_for_root_components_which_are_already_private() throws SQLException {
+    long idRoot = insertRootComponent("root1", true);
+    String someRole = "role_" + random.nextInt(12);
+    int someGroupId = random.nextInt(50);
+    int someUserId = random.nextInt(50);
+    insertGroupPermission(someRole, idRoot, null);
+    insertGroupPermission(someRole, idRoot, someGroupId);
+    insertGroupPermission(randomPublicConditionRole, idRoot, someGroupId);
+    insertUserPermission(someRole, idRoot, someUserId);
+    insertUserPermission(randomPublicConditionRole, idRoot, someUserId);
+
+    underTest.execute();
+
+    assertThat(isPrivate("root1")).isTrue();
+    assertThat(permissionsOfGroupAnyone(idRoot)).containsOnly(someRole);
+    assertThat(permissionsOfGroup(idRoot, someGroupId)).containsOnly(someRole, ROLE_USER, ROLE_CODEVIEWER);
+    assertThat(permissionsOfUser(idRoot, someUserId)).containsOnly(someRole, ROLE_USER, ROLE_CODEVIEWER);
+  }
+
+  @Test
+  public void execute_ensures_any_user_of_with_at_least_one_permission_on_root_component_which_is_already_private_also_has_permissions_USER_and_CODEVIEWER() throws SQLException {
+    long idRoot = insertRootComponent("root1", true);
+    String someRole = "role_" + random.nextInt(12);
+    int user1 = insertUser();
+    int user2 = insertUser();
+    insertUserPermission(someRole, idRoot, user1);
+
+    underTest.execute();
+
+    assertThat(isPrivate("root1")).isTrue();
+    assertThat(permissionsOfGroupAnyone(idRoot)).isEmpty();
+    assertThat(permissionsOfUser(idRoot, user1)).containsOnly(someRole, ROLE_USER, ROLE_CODEVIEWER);
+    assertThat(permissionsOfUser(idRoot, user2)).isEmpty();
+  }
+
+  @Test
+  public void execute_ensures_any_group_of_with_at_least_one_permission_on_root_component_which_is_already_private_also_has_permissions_USER_and_CODEVIEWER() throws SQLException {
+    long idRoot = insertRootComponent("root1", true);
+    String someRole = "role_" + random.nextInt(12);
+    int group1 = insertGroup();
+    int group2 = insertGroup();
+    insertGroupPermission(someRole, idRoot, group1);
+
+    underTest.execute();
+
+    assertThat(isPrivate("root1")).isTrue();
+    assertThat(permissionsOfGroup(idRoot, group1)).containsOnly(someRole, ROLE_USER, ROLE_CODEVIEWER);
+    assertThat(permissionsOfGroup(idRoot, group2)).isEmpty();
+  }
+
+  @Test
+  public void execute_deletes_any_USER_or_BROWSE_permission_of_public_project() throws SQLException {
+    long idRoot = insertRootComponent("root1", false);
+    int someGroupId = random.nextInt(55);
+    int someUserId = random.nextInt(55);
+    String someRole = "role_" + random.nextInt(12);
+    Stream.of(ROLE_USER, ROLE_CODEVIEWER, someRole)
+      .forEach(role -> {
+        insertGroupPermission(role, idRoot, null);
+        insertGroupPermission(role, idRoot, someGroupId);
+        insertUserPermission(role, idRoot, someUserId);
+      });
+    assertThat(isPrivate("root1")).isFalse();
+    assertThat(permissionsOfGroupAnyone(idRoot)).containsOnly(ROLE_USER, ROLE_CODEVIEWER, someRole);
+    assertThat(permissionsOfGroup(idRoot, someGroupId)).containsOnly(ROLE_USER, ROLE_CODEVIEWER, someRole);
+    assertThat(permissionsOfUser(idRoot, someUserId)).containsOnly(ROLE_USER, ROLE_CODEVIEWER, someRole);
+
+    underTest.execute();
+
+    assertThat(isPrivate("root1")).isFalse();
+    assertThat(permissionsOfGroupAnyone(idRoot)).containsOnly(someRole);
+    assertThat(permissionsOfGroup(idRoot, someGroupId)).containsOnly(someRole);
+    assertThat(permissionsOfUser(idRoot, someUserId)).containsOnly(someRole);
+  }
+
+  private long insertRootComponent(String uuid, boolean isPrivate) {
+    db.executeInsert(
+      "PROJECTS",
+      "ORGANIZATION_UUID", "org_" + uuid,
+      "SCOPE", "PRJ",
+      "QUALIFIER", randomQualifier,
+      "UUID", uuid,
+      "UUID_PATH", "path_" + uuid,
+      "ROOT_UUID", "root_" + uuid,
+      "PROJECT_UUID", uuid,
+      "PRIVATE", valueOf(isPrivate));
+    return (long) db.selectFirst("select id as \"ID\" from projects where uuid='" + uuid + "'").get("ID");
+  }
+
+  private long insertComponent(String uuid, String projectUuid, boolean isPrivate) {
+    db.executeInsert(
+      "PROJECTS",
+      "ORGANIZATION_UUID", "org_" + uuid,
+      "UUID", uuid,
+      "UUID_PATH", "path_" + uuid,
+      "ROOT_UUID", "root_" + uuid,
+      "PROJECT_UUID", projectUuid,
+      "PRIVATE", valueOf(isPrivate));
+    return (long) db.selectFirst("select id as \"ID\" from projects where uuid='" + uuid + "'").get("ID");
+  }
+
+  private void insertGroupPermission(String role, @Nullable Long resourceId, @Nullable Integer groupId) {
+    db.executeInsert(
+      "GROUP_ROLES",
+      "ORGANIZATION_UUID", "org" + random.nextInt(50),
+      "GROUP_ID", groupId == null ? null : valueOf(groupId),
+      "RESOURCE_ID", resourceId == null ? null : valueOf(resourceId),
+      "ROLE", role);
+  }
+
+  private int groupCount = Math.abs(random.nextInt(22));
+
+  private int insertGroup() {
+    String name = "group" + groupCount++;
+    db.executeInsert(
+      "GROUPS",
+      "ORGANIZATION_UUID", "org" + random.nextInt(12),
+      "NAME", name);
+    return ((Long) db.selectFirst("select id as \"ID\" from groups where name='" + name + "'").get("ID")).intValue();
+  }
+
+  private void insertUserPermission(String role, @Nullable Long resourceId, int userId) {
+    db.executeInsert(
+      "USER_ROLES",
+      "ORGANIZATION_UUID", "org_" + random.nextInt(66),
+      "USER_ID", valueOf(userId),
+      "RESOURCE_ID", resourceId == null ? null : valueOf(resourceId),
+      "ROLE", role);
+  }
+
+  private int userCount = Math.abs(random.nextInt(22));
+
+  private int insertUser() {
+    String login = "user" + userCount++;
+    db.executeInsert(
+      "USERS",
+      "LOGIN", login,
+      "IS_ROOT", String.valueOf(false));
+    return ((Long) db.selectFirst("select id as \"ID\" from users where login='" + login + "'").get("ID")).intValue();
+  }
+
+  private boolean isPrivate(String uuid) {
+    Map<String, Object> row = db.selectFirst("select private as \"PRIVATE\" from projects where uuid = '" + uuid + "'");
+    return (boolean) row.get("PRIVATE");
+  }
+
+  private Set<String> permissionsOfGroupAnyone(long resourceId) {
+    return db.select("select role from group_roles where group_id is null and resource_id = " + resourceId)
+      .stream()
+      .flatMap(map -> map.entrySet().stream())
+      .map(entry -> (String) entry.getValue())
+      .collect(MoreCollectors.toSet());
+  }
+
+  private Set<String> permissionsOfGroup(long resourceId, int groupId) {
+    return db.select("select role from group_roles where group_id = " + groupId + " and resource_id = " + resourceId)
+      .stream()
+      .flatMap(map -> map.entrySet().stream())
+      .map(entry -> (String) entry.getValue())
+      .collect(MoreCollectors.toSet());
+  }
+
+  private Set<String> permissionsOfUser(long resourceId, int userId) {
+    return db.select("select role from user_roles where resource_id = " + resourceId + " and user_id = " + userId)
+      .stream()
+      .flatMap(map -> map.entrySet().stream())
+      .map(entry -> (String) entry.getValue())
+      .collect(MoreCollectors.toSet());
+  }
+}
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/MakeComponentsPrivateBasedOnPermissionsTest/projects_and_group_roles_and_user_roles.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/MakeComponentsPrivateBasedOnPermissionsTest/projects_and_group_roles_and_user_roles.sql
new file mode 100644
index 00000000000..30977a18097
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/MakeComponentsPrivateBasedOnPermissionsTest/projects_and_group_roles_and_user_roles.sql
@@ -0,0 +1,93 @@
+CREATE TABLE "PROJECTS" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "ORGANIZATION_UUID" VARCHAR(40) NOT NULL,
+  "KEE" VARCHAR(400),
+  "UUID" VARCHAR(50) NOT NULL,
+  "UUID_PATH" VARCHAR(1500) NOT NULL,
+  "ROOT_UUID" VARCHAR(50) NOT NULL,
+  "PROJECT_UUID" VARCHAR(50) NOT NULL,
+  "MODULE_UUID" VARCHAR(50),
+  "MODULE_UUID_PATH" VARCHAR(1500),
+  "NAME" VARCHAR(2000),
+  "DESCRIPTION" VARCHAR(2000),
+  "PRIVATE" BOOLEAN NOT NULL,
+  "TAGS" VARCHAR(500),
+  "ENABLED" BOOLEAN NOT NULL DEFAULT TRUE,
+  "SCOPE" VARCHAR(3),
+  "QUALIFIER" VARCHAR(10),
+  "DEPRECATED_KEE" VARCHAR(400),
+  "PATH" VARCHAR(2000),
+  "LANGUAGE" VARCHAR(20),
+  "COPY_COMPONENT_UUID" VARCHAR(50),
+  "LONG_NAME" VARCHAR(2000),
+  "DEVELOPER_UUID" VARCHAR(50),
+  "CREATED_AT" TIMESTAMP,
+  "AUTHORIZATION_UPDATED_AT" BIGINT,
+  "B_CHANGED" BOOLEAN,
+  "B_COPY_COMPONENT_UUID" VARCHAR(50),
+  "B_DESCRIPTION" VARCHAR(2000),
+  "B_ENABLED" BOOLEAN,
+  "B_UUID_PATH" VARCHAR(1500),
+  "B_LANGUAGE" VARCHAR(20),
+  "B_LONG_NAME" VARCHAR(500),
+  "B_MODULE_UUID" VARCHAR(50),
+  "B_MODULE_UUID_PATH" VARCHAR(1500),
+  "B_NAME" VARCHAR(500),
+  "B_PATH" VARCHAR(2000),
+  "B_QUALIFIER" VARCHAR(10)
+);
+CREATE INDEX "PROJECTS_ORGANIZATION" ON "PROJECTS" ("ORGANIZATION_UUID");
+CREATE UNIQUE INDEX "PROJECTS_KEE" ON "PROJECTS" ("KEE");
+CREATE INDEX "PROJECTS_ROOT_UUID" ON "PROJECTS" ("ROOT_UUID");
+CREATE UNIQUE INDEX "PROJECTS_UUID" ON "PROJECTS" ("UUID");
+CREATE INDEX "PROJECTS_PROJECT_UUID" ON "PROJECTS" ("PROJECT_UUID");
+CREATE INDEX "PROJECTS_MODULE_UUID" ON "PROJECTS" ("MODULE_UUID");
+CREATE INDEX "PROJECTS_QUALIFIER" ON "PROJECTS" ("QUALIFIER");
+
+CREATE TABLE "GROUPS" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "ORGANIZATION_UUID" VARCHAR(40) NOT NULL,
+  "NAME" VARCHAR(500),
+  "DESCRIPTION" VARCHAR(200),
+  "CREATED_AT" TIMESTAMP,
+  "UPDATED_AT" TIMESTAMP
+);
+
+CREATE TABLE "GROUP_ROLES" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "ORGANIZATION_UUID" VARCHAR(40) NOT NULL,
+  "GROUP_ID" INTEGER,
+  "RESOURCE_ID" INTEGER,
+  "ROLE" VARCHAR(64) NOT NULL
+);
+CREATE INDEX "GROUP_ROLES_RESOURCE" ON "GROUP_ROLES" ("RESOURCE_ID");
+CREATE UNIQUE INDEX "UNIQ_GROUP_ROLES" ON "GROUP_ROLES" ("ORGANIZATION_UUID", "GROUP_ID", "RESOURCE_ID", "ROLE");
+
+CREATE TABLE "USERS" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "LOGIN" VARCHAR(255),
+  "NAME" VARCHAR(200),
+  "EMAIL" VARCHAR(100),
+  "CRYPTED_PASSWORD" VARCHAR(40),
+  "SALT" VARCHAR(40),
+  "ACTIVE" BOOLEAN DEFAULT TRUE,
+  "SCM_ACCOUNTS" VARCHAR(4000),
+  "EXTERNAL_IDENTITY" VARCHAR(255),
+  "EXTERNAL_IDENTITY_PROVIDER" VARCHAR(100),
+  "IS_ROOT" BOOLEAN NOT NULL,
+  "USER_LOCAL" BOOLEAN,
+  "CREATED_AT" BIGINT,
+  "UPDATED_AT" BIGINT
+);
+CREATE UNIQUE INDEX "USERS_LOGIN" ON "USERS" ("LOGIN");
+CREATE INDEX "USERS_UPDATED_AT" ON "USERS" ("UPDATED_AT");
+
+CREATE TABLE "USER_ROLES" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "ORGANIZATION_UUID" VARCHAR(40) NOT NULL,
+  "USER_ID" INTEGER,
+  "RESOURCE_ID" INTEGER,
+  "ROLE" VARCHAR(64) NOT NULL
+);
+CREATE INDEX "USER_ROLES_RESOURCE" ON "USER_ROLES" ("RESOURCE_ID");
+CREATE INDEX "USER_ROLES_USER" ON "USER_ROLES" ("USER_ID");