SONAR-11271 Add migration to create new permissions

5 files changed, 367 insertions, 1 deletions

diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v74/CreateApplicationsAndPortfoliosCreatorPermissions.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v74/CreateApplicationsAndPortfoliosCreatorPermissions.java
new file mode 100644
index 00000000000..c72291a09ba
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v74/CreateApplicationsAndPortfoliosCreatorPermissions.java
@@ -0,0 +1,103 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2018 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.v74;
+
+import org.sonar.api.security.DefaultGroups;
+import org.sonar.api.utils.System2;
+import org.sonar.api.utils.log.Logger;
+import org.sonar.api.utils.log.Loggers;
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.SupportsBlueGreen;
+import org.sonar.server.platform.db.migration.step.DataChange;
+
+import java.sql.SQLException;
+import java.util.Date;
+
+@SupportsBlueGreen
+public class CreateApplicationsAndPortfoliosCreatorPermissions extends DataChange {
+
+  private static final Logger LOG = Loggers.get(CreateApplicationsAndPortfoliosCreatorPermissions.class);
+  private static final String DEFAULT_ORGANIZATION_KEY = "default-organization";
+
+  private final System2 system2;
+
+  public CreateApplicationsAndPortfoliosCreatorPermissions(Database db, System2 system2) {
+    super(db);
+    this.system2 = system2;
+  }
+
+  @Override
+  protected void execute(Context context) throws SQLException {
+    Date now = new Date(system2.now());
+    Long adminGroupId = context.prepareSelect("SELECT id FROM groups WHERE name=?")
+      .setString(1, DefaultGroups.ADMINISTRATORS)
+      .get(row -> row.getLong(1));
+    String templateKey = context.prepareSelect("SELECT default_perm_template_view FROM organizations WHERE kee=?")
+      .setString(1, DEFAULT_ORGANIZATION_KEY)
+      .get(row -> row.getString(1));
+
+    if (adminGroupId == null) {
+      LOG.info("Unable to find {} group. Skipping adding applications and portfolios creator permissions.", DefaultGroups.ADMINISTRATORS);
+      return;
+    }
+
+    if (templateKey == null) {
+      LOG.info("There is no default template for views. Skipping adding applications and portfolios creator permissions.");
+    }
+
+    Long templateId = context.prepareSelect("SELECT id FROM permission_templates WHERE kee=?")
+      .setString(1, templateKey)
+      .get(row -> row.getLong(1));
+
+    if (templateId == null) {
+      LOG.info("Unable to find the default template [{}] for views. Skipping adding applications and portfolios creator permissions.", templateKey);
+      return;
+    }
+
+    if (isPermissionAbsent(context, adminGroupId, "applicationcreator")) {
+      insertPermission(context, adminGroupId, templateId, "applicationcreator", now);
+    }
+
+    if (isPermissionAbsent(context, adminGroupId, "portfoliocreator")) {
+      insertPermission(context, adminGroupId, templateId, "portfoliocreator", now);
+    }
+  }
+
+  private static boolean isPermissionAbsent(Context context, Long groupId, String permission) throws SQLException {
+    Long count = context.prepareSelect("SELECT count(*) FROM perm_templates_groups WHERE group_id=? AND permission_reference=?")
+      .setLong(1, groupId)
+      .setString(2, permission)
+      .get(row -> (row.getLong(1)));
+
+    return (count == null) || count == 0;
+  }
+
+  private static void insertPermission(Context context, Long groupId, Long templateId, String permission, Date now) throws SQLException {
+    context.prepareUpsert("INSERT INTO perm_templates_groups (group_id, template_id, permission_reference, created_at, updated_at) values (?,?,?,?,?)")
+      .setLong(1, groupId)
+      .setLong(2, templateId)
+      .setString(3, permission)
+      .setDate(4, now)
+      .setDate(5, now)
+      .execute()
+      .commit();
+  }
+}
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v74/DbVersion74.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v74/DbVersion74.java
index 45186436a14..da1f3af2268 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v74/DbVersion74.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v74/DbVersion74.java
@@ -44,6 +44,7 @@ public class DbVersion74 implements DbVersion {
       .add(2321, "Increase organization key and name length", IncreaseOrganizationsKeeAndNameLength.class)
       .add(2322, "Create table CE_TASK_MESSAGE", CreateCeTaskMessage.class)
       .add(2323, "Clean orphans from deleted branches and PRs in CE_* tables", CleanOrphanRowsInCeTables.class)
+      .add(2324, "Create new creator permissions for applications and portfolios", CreateApplicationsAndPortfoliosCreatorPermissions.class)
     ;
   }
 }
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v74/CreateApplicationsAndPortfoliosCreatorPermissionsTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v74/CreateApplicationsAndPortfoliosCreatorPermissionsTest.java
new file mode 100644
index 00000000000..b9fe947e5ad
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v74/CreateApplicationsAndPortfoliosCreatorPermissionsTest.java
@@ -0,0 +1,213 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2018 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.v74;
+
+import java.sql.SQLException;
+import java.util.Date;
+import java.util.stream.Collectors;
+import org.assertj.core.groups.Tuple;
+import org.junit.Before;
+import org.junit.Rule;
+import org.junit.Test;
+import org.junit.rules.ExpectedException;
+import org.sonar.api.utils.System2;
+import org.sonar.api.web.UserRole;
+import org.sonar.core.util.UuidFactoryFast;
+import org.sonar.db.CoreDbTester;
+
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.groups.Tuple.tuple;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.when;
+
+public class CreateApplicationsAndPortfoliosCreatorPermissionsTest {
+  @Rule
+  public ExpectedException expectedException = ExpectedException.none();
+
+  @Rule
+  public CoreDbTester db = CoreDbTester.createForSchema(CreateApplicationsAndPortfoliosCreatorPermissionsTest.class, "perm_templates_groups.sql");
+
+  private static final Date PAST = new Date(100_000_000_000L);
+  private static final Date NOW = new Date(500_000_000_000L);
+  private static final String DEFAULT_ORGANIZATION_UUID = UuidFactoryFast.getInstance().create();
+  private static final String DEFAULT_PERM_TEMPLATE_VIEW = "default_view_template";
+  private static final String ANOTHER_PERM_TEMPLATE_VIEW = "another_template";
+
+  private System2 system2 = mock(System2.class);
+  private CreateApplicationsAndPortfoliosCreatorPermissions underTest = new CreateApplicationsAndPortfoliosCreatorPermissions(db.database(), system2);
+
+  @Before
+  public void setupDatabase() {
+    insertDefaultOrganization();
+    insertDefaultGroups();
+    insertPermissionTemplate();
+  }
+
+
+  @Test
+  public void migration_is_reentrant() throws SQLException {
+    when(system2.now()).thenReturn(NOW.getTime());
+
+    underTest.execute();
+    underTest.execute();
+
+    Long idOfDefaultPermissionTemplate = getIdOfPermissionTemplate(DEFAULT_PERM_TEMPLATE_VIEW);
+    Long idOfAdministratorGroup = getIdOfGroup("sonar-administrators");
+
+    assertPermTemplateGroupRoles(
+      tuple(idOfDefaultPermissionTemplate, idOfAdministratorGroup, "applicationcreator", NOW, NOW),
+      tuple(idOfDefaultPermissionTemplate, idOfAdministratorGroup, "portfoliocreator", NOW, NOW));
+  }
+
+  @Test
+  public void insert_missing_permissions() throws SQLException {
+    when(system2.now()).thenReturn(NOW.getTime());
+
+    underTest.execute();
+
+    Long idOfDefaultPermissionTemplate = getIdOfPermissionTemplate(DEFAULT_PERM_TEMPLATE_VIEW);
+    Long idOfAdministratorGroup = getIdOfGroup("sonar-administrators");
+
+    assertPermTemplateGroupRoles(
+      tuple(idOfDefaultPermissionTemplate, idOfAdministratorGroup, "applicationcreator", NOW, NOW),
+      tuple(idOfDefaultPermissionTemplate, idOfAdministratorGroup, "portfoliocreator", NOW, NOW));
+  }
+
+  @Test
+  public void does_nothing_if_template_group_has_the_permissions_already() throws SQLException {
+    Long idOfDefaultPermissionTemplate = getIdOfPermissionTemplate(DEFAULT_PERM_TEMPLATE_VIEW);
+    Long idOfAdministratorGroup = getIdOfGroup("sonar-administrators");
+
+    insertPermTemplateGroupRole(1, 2, "noissueadmin");
+    insertPermTemplateGroupRole(3, 4, "issueadmin");
+    insertPermTemplateGroupRole(3, 4, "another");
+    insertPermTemplateGroupRole(5, 6, "securityhotspotadmin");
+    insertPermTemplateGroupRole(idOfDefaultPermissionTemplate.intValue(), idOfAdministratorGroup.intValue(), "applicationcreator");
+    insertPermTemplateGroupRole(idOfDefaultPermissionTemplate.intValue(), idOfAdministratorGroup.intValue(), "portfoliocreator");
+
+    when(system2.now()).thenReturn(NOW.getTime());
+    underTest.execute();
+
+    assertPermTemplateGroupRoles(
+      tuple(1L, 2L, "noissueadmin", PAST, PAST),
+      tuple(3L, 4L, "issueadmin", PAST, PAST),
+      tuple(3L, 4L, "another", PAST, PAST),
+      tuple(5L, 6L, "securityhotspotadmin", PAST, PAST),
+      tuple(idOfDefaultPermissionTemplate, idOfAdministratorGroup, "applicationcreator", PAST, PAST),
+      tuple(idOfDefaultPermissionTemplate, idOfAdministratorGroup, "portfoliocreator", PAST, PAST));
+  }
+
+  @Test
+  public void insert_missing_permission_keeping_other_template_group_permissions() throws SQLException {
+    when(system2.now()).thenReturn(NOW.getTime());
+    insertPermTemplateGroupRole(1, 2, "noissueadmin");
+    insertPermTemplateGroupRole(3, 4, "issueadmin");
+    insertPermTemplateGroupRole(3, 4, "another");
+    insertPermTemplateGroupRole(5, 6, "securityhotspotadmin");
+
+    underTest.execute();
+
+    Long idOfDefaultPermissionTemplate = getIdOfPermissionTemplate(DEFAULT_PERM_TEMPLATE_VIEW);
+    Long idOfAdministratorGroup = getIdOfGroup("sonar-administrators");
+
+    assertPermTemplateGroupRoles(
+      tuple(1L, 2L, "noissueadmin", PAST, PAST),
+      tuple(3L, 4L, "issueadmin", PAST, PAST),
+      tuple(3L, 4L, "another", PAST, PAST),
+      tuple(5L, 6L, "securityhotspotadmin", PAST, PAST),
+      tuple(idOfDefaultPermissionTemplate, idOfAdministratorGroup, "applicationcreator", NOW, NOW),
+      tuple(idOfDefaultPermissionTemplate, idOfAdministratorGroup, "portfoliocreator", NOW, NOW));
+  }
+
+  private void insertPermTemplateGroupRole(int templateId, int groupId, String role) {
+    db.executeInsert(
+      "PERM_TEMPLATES_GROUPS",
+      "TEMPLATE_ID", templateId,
+      "GROUP_ID", groupId,
+      "PERMISSION_REFERENCE", role,
+      "CREATED_AT", PAST,
+      "UPDATED_AT", PAST);
+  }
+
+  private void insertDefaultGroups() {
+    db.executeInsert(
+      "GROUPS",
+      "NAME", "sonar-administrators",
+      "CREATED_AT", PAST,
+      "UPDATED_AT", PAST,
+      "ORGANIZATION_UUID", DEFAULT_ORGANIZATION_UUID);
+    db.executeInsert(
+      "GROUPS",
+      "NAME", "sonar-users",
+      "CREATED_AT", PAST,
+      "UPDATED_AT", PAST,
+      "ORGANIZATION_UUID", DEFAULT_ORGANIZATION_UUID);
+  }
+
+  private void insertDefaultOrganization() {
+    db.executeInsert(
+      "ORGANIZATIONS",
+      "UUID", DEFAULT_ORGANIZATION_UUID,
+      "KEE", "default-organization",
+      "NAME", "Default Organization",
+      "GUARDED", true,
+      "DEFAULT_PERM_TEMPLATE_VIEW", DEFAULT_PERM_TEMPLATE_VIEW,
+      "DEFAULT_QUALITY_GATE_UUID", UuidFactoryFast.getInstance().create(),
+      "NEW_PROJECT_PRIVATE", false,
+      "SUBSCRIPTION", "SONARQUBE",
+      "CREATED_AT", PAST.getTime(),
+      "UPDATED_AT", PAST.getTime());
+  }
+
+  private void insertPermissionTemplate() {
+    db.executeInsert(
+      "PERMISSION_TEMPLATES",
+      "ORGANIZATION_UUID", DEFAULT_ORGANIZATION_UUID,
+      "NAME", "Default template for views",
+      "KEE", DEFAULT_PERM_TEMPLATE_VIEW,
+      "CREATED_AT", PAST,
+      "UPDATED_AT", PAST);
+    db.executeInsert(
+      "PERMISSION_TEMPLATES",
+      "ORGANIZATION_UUID", DEFAULT_ORGANIZATION_UUID,
+      "NAME", ANOTHER_PERM_TEMPLATE_VIEW,
+      "KEE", ANOTHER_PERM_TEMPLATE_VIEW,
+      "CREATED_AT", PAST,
+      "UPDATED_AT", PAST);
+  }
+
+  private Long getIdOfPermissionTemplate(String key) {
+    return (Long) db.selectFirst("SELECT id FROM permission_templates WHERE kee='" + key + "'")
+      .get("ID");
+  }
+
+  private Long getIdOfGroup(String key) {
+    return (Long) db.selectFirst("SELECT id FROM groups WHERE name='" + key + "'")
+      .get("ID");
+  }
+
+  private void assertPermTemplateGroupRoles(Tuple... expectedTuples) {
+    assertThat(db.select("SELECT TEMPLATE_ID, GROUP_ID, PERMISSION_REFERENCE, CREATED_AT, UPDATED_AT FROM PERM_TEMPLATES_GROUPS")
+      .stream()
+      .map(map -> new Tuple(map.get("TEMPLATE_ID"), map.get("GROUP_ID"), map.get("PERMISSION_REFERENCE"), map.get("CREATED_AT"), map.get("UPDATED_AT")))
+      .collect(Collectors.toList()))
+      .containsExactlyInAnyOrder(expectedTuples);
+  }
+}
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v74/DbVersion74Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v74/DbVersion74Test.java
index b67fedaffea..5064842d84d 100644
--- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v74/DbVersion74Test.java
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v74/DbVersion74Test.java
@@ -35,6 +35,6 @@ public class DbVersion74Test {
 
   @Test
   public void verify_migration_count() {
-    verifyMigrationCount(underTest, 17);
+    verifyMigrationCount(underTest, 18);
   }
 }
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v74/CreateApplicationsAndPortfoliosCreatorPermissionsTest/perm_templates_groups.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v74/CreateApplicationsAndPortfoliosCreatorPermissionsTest/perm_templates_groups.sql
new file mode 100644
index 00000000000..0a8c29211f0
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v74/CreateApplicationsAndPortfoliosCreatorPermissionsTest/perm_templates_groups.sql
@@ -0,0 +1,49 @@
+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 "PERMISSION_TEMPLATES" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "ORGANIZATION_UUID" VARCHAR(40) NOT NULL,
+  "NAME" VARCHAR(100) NOT NULL,
+  "KEE" VARCHAR(100) NOT NULL,
+  "DESCRIPTION" VARCHAR(4000),
+  "KEY_PATTERN" VARCHAR(500),
+  "CREATED_AT" TIMESTAMP,
+  "UPDATED_AT" TIMESTAMP
+);
+
+CREATE TABLE "PERM_TEMPLATES_GROUPS" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "GROUP_ID" INTEGER,
+  "TEMPLATE_ID" INTEGER NOT NULL,
+  "PERMISSION_REFERENCE" VARCHAR(64) NOT NULL,
+  "CREATED_AT" TIMESTAMP,
+  "UPDATED_AT" TIMESTAMP
+);
+
+CREATE TABLE "ORGANIZATIONS" (
+  "UUID" VARCHAR(40) NOT NULL,
+  "KEE" VARCHAR(32) NOT NULL,
+  "NAME" VARCHAR(64) NOT NULL,
+  "DESCRIPTION" VARCHAR(256),
+  "URL" VARCHAR(256),
+  "AVATAR_URL" VARCHAR(256),
+  "GUARDED" BOOLEAN NOT NULL,
+  "DEFAULT_PERM_TEMPLATE_PROJECT" VARCHAR(40),
+  "DEFAULT_PERM_TEMPLATE_VIEW" VARCHAR(40),
+  "DEFAULT_GROUP_ID" INTEGER,
+  "DEFAULT_QUALITY_GATE_UUID" VARCHAR(40) NOT NULL,
+  "NEW_PROJECT_PRIVATE" BOOLEAN NOT NULL,
+  "SUBSCRIPTION" VARCHAR(40) NOT NULL,
+  "CREATED_AT" BIGINT NOT NULL,
+  "UPDATED_AT" BIGINT NOT NULL,
+
+  CONSTRAINT "PK_ORGANIZATIONS" PRIMARY KEY ("UUID")
+);
+CREATE UNIQUE INDEX "ORGANIZATION_KEY" ON "ORGANIZATIONS" ("KEE");