SONAR-9019 Create "members" groups on existing organizations

6 files changed, 487 insertions, 2 deletions

diff --git a/server/sonar-db-core/src/main/resources/org/sonar/db/version/rows-h2.sql b/server/sonar-db-core/src/main/resources/org/sonar/db/version/rows-h2.sql
index f932a18a544..0b5fbc28757 100644
--- a/server/sonar-db-core/src/main/resources/org/sonar/db/version/rows-h2.sql
+++ b/server/sonar-db-core/src/main/resources/org/sonar/db/version/rows-h2.sql
@@ -555,6 +555,7 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1618');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1619');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1620');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1621');
+INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1622');
 
 INSERT INTO USERS(ID, LOGIN, NAME, EMAIL, EXTERNAL_IDENTITY, EXTERNAL_IDENTITY_PROVIDER, USER_LOCAL, CRYPTED_PASSWORD, SALT, IS_ROOT, CREATED_AT, UPDATED_AT) VALUES (1, 'admin', 'Administrator', '', 'admin', 'sonarqube', true, 'a373a0e667abb2604c1fd571eb4ad47fe8cc0878', '48bc4b0d93179b5103fd3885ea9119498e9d161b', false, '1418215735482', '1418215735482');
 ALTER TABLE USERS ALTER COLUMN ID RESTART WITH 2;
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/CreateMembersGroupsInEachOrganization.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/CreateMembersGroupsInEachOrganization.java
new file mode 100644
index 00000000000..3b368430aaf
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/CreateMembersGroupsInEachOrganization.java
@@ -0,0 +1,92 @@
+/*
+ * 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.Date;
+import org.sonar.api.utils.System2;
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.step.DataChange;
+import org.sonar.server.platform.db.migration.step.MassUpdate;
+
+public class CreateMembersGroupsInEachOrganization extends DataChange {
+
+  private static final String GROUP_MEMBERS = "Members";
+
+  private final System2 system2;
+
+  public CreateMembersGroupsInEachOrganization(Database db, System2 system2) {
+    super(db);
+    this.system2 = system2;
+  }
+
+  @Override
+  protected void execute(Context context) throws SQLException {
+    Date now = new Date(system2.now());
+    if (isOrganizationEnabled(context)) {
+      createMembersGroup(context, now);
+      createPermissionTemplateGroups(context, now);
+    }
+  }
+
+  private static void createMembersGroup(Context context, Date now) throws SQLException {
+    MassUpdate massUpdate = context.prepareMassUpdate().rowPluralName("groups");
+    massUpdate.select("SELECT o.uuid FROM organizations o " +
+      "WHERE NOT EXISTS (SELECT 1 FROM groups g WHERE g.organization_uuid=o.uuid AND g.name=?)").setString(1, GROUP_MEMBERS);
+    massUpdate.update("INSERT INTO groups (organization_uuid, name, description, created_at, updated_at) values (?, ?, ?, ?, ?)");
+    massUpdate.execute((row, update) -> {
+      update
+        .setString(1, row.getString(1))
+        .setString(2, GROUP_MEMBERS)
+        .setString(3, "All members of the organization")
+        .setDate(4, now)
+        .setDate(5, now);
+      return true;
+    });
+  }
+
+  private static void createPermissionTemplateGroups(Context context, Date now) throws SQLException {
+    MassUpdate massUpdate = context.prepareMassUpdate().rowPluralName("permission templates on groups");
+    massUpdate.select("SELECT g.id, pt.id FROM organizations o " +
+      "INNER JOIN permission_templates pt ON pt.kee=o.default_perm_template_project " +
+      "INNER JOiN groups g ON g.organization_uuid=o.uuid AND g.name=? " +
+      "WHERE NOT EXISTS (SELECT 1 FROM perm_templates_groups ptg WHERE ptg.group_id=g.id AND ptg.template_id=pt.id)")
+      .setString(1, GROUP_MEMBERS);
+    massUpdate.update("INSERT INTO perm_templates_groups (group_id, template_id, permission_reference, created_at, updated_at) values (?, ?, ?, ?, ?)");
+    massUpdate.update("INSERT INTO perm_templates_groups (group_id, template_id, permission_reference, created_at, updated_at) values (?, ?, ?, ?, ?)");
+    massUpdate.execute((row, update, updateIndex) -> {
+      update
+        .setLong(1, row.getLong(1))
+        .setLong(2, row.getLong(2))
+        .setString(3, updateIndex == 0 ? "user" : "codeviewer")
+        .setDate(4, now)
+        .setDate(5, now);
+      return true;
+    });
+  }
+
+  private static boolean isOrganizationEnabled(Context context) throws SQLException {
+    Boolean result = context.prepareSelect("SELECT text_value FROM internal_properties WHERE kee=?")
+      .setString(1, "organization.enabled")
+      .get(row -> "true".equals(row.getString(1)));
+    return result != null ? result : false;
+  }
+
+}
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 ac52df0a136..c4625c245ac 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
@@ -49,6 +49,7 @@ public class DbVersion64 implements DbVersion {
       .add(1618, "Make index on ORGANIZATIONS.KEE unique", org.sonar.server.platform.db.migration.version.v63.MakeIndexOnOrganizationsKeeUnique.class)
       .add(1619, "Restore 'sonar-users' group", RestoreSonarUsersGroups.class)
       .add(1620, "Delete 'sonar.defaultGroup' setting", DeleteDefaultGroupSetting.class)
-      .add(1621, "Set all users into 'sonar-users' group", SetAllUsersIntoSonarUsersGroup.class);
+      .add(1621, "Set all users into 'sonar-users' group", SetAllUsersIntoSonarUsersGroup.class)
+      .add(1622, "Create 'Members' group in each organization", CreateMembersGroupsInEachOrganization.class);
   }
 }
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/CreateMembersGroupsInEachOrganizationTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/CreateMembersGroupsInEachOrganizationTest.java
new file mode 100644
index 00000000000..c1b1adfcf05
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/CreateMembersGroupsInEachOrganizationTest.java
@@ -0,0 +1,325 @@
+/*
+ * 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.Date;
+import java.util.List;
+import java.util.stream.Collectors;
+import javax.annotation.Nullable;
+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.utils.log.LogTester;
+import org.sonar.db.CoreDbTester;
+
+import static java.lang.String.format;
+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;
+
+public class CreateMembersGroupsInEachOrganizationTest {
+
+  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 = "def-org";
+  private static final String ORGANIZATION_1 = "ORGANIZATION_1";
+  private static final String ORGANIZATION_2 = "ORGANIZATION_2";
+  private static final String TEMPLATE_1 = "TEMPLATE_1";
+  private static final String TEMPLATE_2 = "TEMPLATE_2";
+
+  @Rule
+  public ExpectedException expectedException = ExpectedException.none();
+
+  @Rule
+  public LogTester logTester = new LogTester();
+
+  @Rule
+  public CoreDbTester db = CoreDbTester.createForSchema(CreateMembersGroupsInEachOrganizationTest.class, "initial.sql");
+
+  private System2 system2 = mock(System2.class);
+
+  private CreateMembersGroupsInEachOrganization underTest = new CreateMembersGroupsInEachOrganization(db.database(), system2);
+
+  @Before
+  public void setUp() throws Exception {
+    when(system2.now()).thenReturn(NOW.getTime());
+  }
+
+  @Test
+  public void does_nothing_when_organization_disabled() throws Exception {
+    setupDefaultOrganization();
+    insertOrganization(ORGANIZATION_1, TEMPLATE_1);
+    insertOrganization(ORGANIZATION_2, TEMPLATE_2);
+    insertPermissionTemplate(ORGANIZATION_1, TEMPLATE_1, "Default");
+    insertPermissionTemplate(ORGANIZATION_2, TEMPLATE_2, "Default");
+
+    underTest.execute();
+
+    checkNoGroups();
+    checkNoPermTemplateGroups();
+  }
+
+  @Test
+  public void insert_members_groups_when_not_existing() throws SQLException {
+    setupDefaultOrganization();
+    enableOrganization();
+    insertOrganization(ORGANIZATION_1, null);
+    insertOrganization(ORGANIZATION_2, null);
+
+    underTest.execute();
+
+    checkGroups(
+      tuple(ORGANIZATION_1, "Members", "All members of the organization", NOW, NOW),
+      tuple(ORGANIZATION_2, "Members", "All members of the organization", NOW, NOW),
+      tuple(DEFAULT_ORGANIZATION_UUID, "Members", "All members of the organization", NOW, NOW));
+  }
+
+  @Test
+  public void does_not_insert_members_group_when_group_already_exist() throws SQLException {
+    setupDefaultOrganization();
+    enableOrganization();
+    insertMembersGroup(DEFAULT_ORGANIZATION_UUID);
+    insertOrganization(ORGANIZATION_1, null);
+    insertMembersGroup(ORGANIZATION_1);
+    insertOrganization(ORGANIZATION_2, null);
+    insertMembersGroup(ORGANIZATION_2);
+
+    underTest.execute();
+
+    checkGroups(
+      tuple(ORGANIZATION_1, "Members", "All members of the organization", PAST, PAST),
+      tuple(ORGANIZATION_2, "Members", "All members of the organization", PAST, PAST),
+      tuple(DEFAULT_ORGANIZATION_UUID, "Members", "All members of the organization", PAST, PAST));
+  }
+
+  @Test
+  public void insert_only_missing_members_group_when_some_groups_already_exist() throws SQLException {
+    setupDefaultOrganization();
+    enableOrganization();
+    insertMembersGroup(DEFAULT_ORGANIZATION_UUID);
+    insertOrganization(ORGANIZATION_1, null);
+    insertMembersGroup(ORGANIZATION_1);
+    insertOrganization(ORGANIZATION_2, null);
+
+    underTest.execute();
+
+    checkGroups(
+      tuple(ORGANIZATION_1, "Members", "All members of the organization", PAST, PAST),
+      tuple(ORGANIZATION_2, "Members", "All members of the organization", NOW, NOW),
+      tuple(DEFAULT_ORGANIZATION_UUID, "Members", "All members of the organization", PAST, PAST));
+  }
+
+  @Test
+  public void insert_permission_template_groups_when_not_existing() throws Exception {
+    setupDefaultOrganization();
+    enableOrganization();
+    insertOrganization(ORGANIZATION_1, TEMPLATE_1);
+    long template1 = insertPermissionTemplate(ORGANIZATION_1, TEMPLATE_1, "Default");
+    insertOrganization(ORGANIZATION_2, TEMPLATE_2);
+    long template2 = insertPermissionTemplate(ORGANIZATION_2, TEMPLATE_2, "Default");
+
+    underTest.execute();
+
+    long group1 = selectMembersGroupId(ORGANIZATION_1);
+    long group2 = selectMembersGroupId(ORGANIZATION_2);
+    checkPermTemplateGroups(
+      tuple(group1, template1, "user", NOW, NOW),
+      tuple(group1, template1, "codeviewer", NOW, NOW),
+      tuple(group2, template2, "user", NOW, NOW),
+      tuple(group2, template2, "codeviewer", NOW, NOW));
+  }
+
+  @Test
+  public void does_not_insert_permission_template_groups_when_no_default_permission_template() throws Exception {
+    setupDefaultOrganization();
+    enableOrganization();
+    insertOrganization(ORGANIZATION_1, TEMPLATE_1);
+    insertOrganization(ORGANIZATION_2, TEMPLATE_2);
+
+    underTest.execute();
+
+    checkNoPermTemplateGroups();
+  }
+
+  @Test
+  public void does_not_insert_permission_template_groups_when_already_existing() throws Exception {
+    setupDefaultOrganization();
+    enableOrganization();
+    insertOrganization(ORGANIZATION_1, TEMPLATE_1);
+    long template1 = insertPermissionTemplate(ORGANIZATION_1, TEMPLATE_1, "Default");
+    long group1 = insertMembersGroup(ORGANIZATION_1);
+    insertPermissionTemplateGroup(group1, "user", template1);
+    insertPermissionTemplateGroup(group1, "codeviewer", template1);
+
+    insertOrganization(ORGANIZATION_2, TEMPLATE_2);
+    long template2 = insertPermissionTemplate(ORGANIZATION_2, TEMPLATE_2, "Default");
+    long group2 = insertMembersGroup(ORGANIZATION_2);
+    insertPermissionTemplateGroup(group2, "user", template2);
+    insertPermissionTemplateGroup(group2, "codeviewer", template2);
+
+    underTest.execute();
+
+    checkPermTemplateGroups(
+      tuple(group1, template1, "user", PAST, PAST),
+      tuple(group1, template1, "codeviewer", PAST, PAST),
+      tuple(group2, template2, "user", PAST, PAST),
+      tuple(group2, template2, "codeviewer", PAST, PAST));
+  }
+
+  @Test
+  public void insert_only_missing_permission_template_groups() throws Exception {
+    setupDefaultOrganization();
+    enableOrganization();
+    insertOrganization(ORGANIZATION_1, TEMPLATE_1);
+    long template1 = insertPermissionTemplate(ORGANIZATION_1, TEMPLATE_1, "Default");
+    long group1 = insertMembersGroup(ORGANIZATION_1);
+    insertPermissionTemplateGroup(group1, "user", template1);
+    insertPermissionTemplateGroup(group1, "codeviewer", template1);
+
+    insertOrganization(ORGANIZATION_2, TEMPLATE_2);
+    long template2 = insertPermissionTemplate(ORGANIZATION_2, TEMPLATE_2, "Default");
+    long group2 = insertMembersGroup(ORGANIZATION_2);
+
+    underTest.execute();
+
+    checkPermTemplateGroups(
+      tuple(group1, template1, "user", PAST, PAST),
+      tuple(group1, template1, "codeviewer", PAST, PAST),
+      tuple(group2, template2, "user", NOW, NOW),
+      tuple(group2, template2, "codeviewer", NOW, NOW));
+  }
+
+  @Test
+  public void migration_is_reentrant() throws Exception {
+    setupDefaultOrganization();
+    enableOrganization();
+    insertOrganization(ORGANIZATION_1, TEMPLATE_1);
+    long template1 = insertPermissionTemplate(ORGANIZATION_1, TEMPLATE_1, "Default");
+    insertOrganization(ORGANIZATION_2, TEMPLATE_2);
+    long template2 = insertPermissionTemplate(ORGANIZATION_2, TEMPLATE_2, "Default");
+
+    underTest.execute();
+
+    long group1 = selectMembersGroupId(ORGANIZATION_1);
+    long group2 = selectMembersGroupId(ORGANIZATION_2);
+    checkPermTemplateGroups(
+      tuple(group1, template1, "user", NOW, NOW),
+      tuple(group1, template1, "codeviewer", NOW, NOW),
+      tuple(group2, template2, "user", NOW, NOW),
+      tuple(group2, template2, "codeviewer", NOW, NOW));
+  }
+
+  private void checkGroups(Tuple... expectedTuples) {
+    List<Tuple> tuples = db.select("select name, description, organization_uuid, created_at, updated_at from groups").stream()
+      .map(map -> new Tuple(map.get("ORGANIZATION_UUID"), map.get("NAME"), map.get("DESCRIPTION"), map.get("CREATED_AT"), map.get("UPDATED_AT")))
+      .collect(Collectors.toList());
+    assertThat(tuples).containsOnly(expectedTuples);
+  }
+
+  private void checkNoGroups(){
+    checkGroups();
+  }
+
+  private void checkPermTemplateGroups(Tuple... expectedTuples) {
+    List<Tuple> tuples = db.select("select group_id, template_id, permission_reference, created_at, updated_at from perm_templates_groups").stream()
+      .map(map -> new Tuple(map.get("GROUP_ID"), map.get("TEMPLATE_ID"), map.get("PERMISSION_REFERENCE"), map.get("CREATED_AT"), map.get("UPDATED_AT")))
+      .collect(Collectors.toList());
+    assertThat(tuples).containsOnly(expectedTuples);
+  }
+
+  private void checkNoPermTemplateGroups(){
+    checkPermTemplateGroups();
+  }
+
+  private long selectMembersGroupId(String organization) {
+    return (Long) db.selectFirst(format("select id from groups where name='%s' and organization_uuid='%s'", "Members", organization)).get("ID");
+  }
+
+  private void insertOrganization(String uuid, @Nullable String defaultPermissionTemplateProject) {
+    db.executeInsert(
+      "ORGANIZATIONS",
+      "UUID", uuid,
+      "KEE", uuid,
+      "NAME", uuid,
+      "DEFAULT_PERM_TEMPLATE_PROJECT", defaultPermissionTemplateProject,
+      "GUARDED", "false",
+      "CREATED_AT", 1_000L,
+      "UPDATED_AT", 1_000L);
+  }
+
+  private long insertPermissionTemplate(String organizationUuid, String key, String name) {
+    db.executeInsert(
+      "PERMISSION_TEMPLATES",
+      "KEE", key,
+      "ORGANIZATION_UUID", organizationUuid,
+      "NAME", name,
+      "CREATED_AT", PAST,
+      "UPDATED_AT", PAST);
+    return (Long) db.selectFirst(format("select id from permission_templates where kee='%s'", key)).get("ID");
+  }
+
+  private void insertPermissionTemplateGroup(@Nullable Long groupId, String permission, @Nullable Long templateId) {
+    db.executeInsert(
+      "PERM_TEMPLATES_GROUPS",
+      "GROUP_ID", groupId,
+      "TEMPLATE_ID", templateId,
+      "PERMISSION_REFERENCE", permission,
+      "CREATED_AT", PAST,
+      "UPDATED_AT", PAST);
+  }
+
+  private long insertMembersGroup(String organizationUuid) {
+    db.executeInsert(
+      "GROUPS",
+      "NAME", "Members",
+      "DESCRIPTION", "All members of the organization",
+      "ORGANIZATION_UUID", organizationUuid,
+      "CREATED_AT", PAST,
+      "UPDATED_AT", PAST);
+    return (Long) db.selectFirst(format("select id from groups where name='%s' and organization_uuid='%s'", "Members", organizationUuid)).get("ID");
+  }
+
+  private void setupDefaultOrganization() {
+    db.executeInsert("ORGANIZATIONS",
+      "UUID", DEFAULT_ORGANIZATION_UUID,
+      "KEE", DEFAULT_ORGANIZATION_UUID, "NAME",
+      DEFAULT_ORGANIZATION_UUID, "GUARDED", false,
+      "CREATED_AT", 1_000L,
+      "UPDATED_AT", 1_000L);
+    db.executeInsert("INTERNAL_PROPERTIES",
+      "KEE", "organization.default",
+      "IS_EMPTY", "false",
+      "TEXT_VALUE", DEFAULT_ORGANIZATION_UUID);
+  }
+
+  private void enableOrganization(){
+    db.executeInsert("INTERNAL_PROPERTIES",
+      "KEE", "organization.enabled",
+      "IS_EMPTY", "false",
+      "TEXT_VALUE", "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 588ea5939ca..8a813b71ba5 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,7 +35,7 @@ public class DbVersion64Test {
 
   @Test
   public void verify_migration_count() {
-    verifyMigrationCount(underTest, 22);
+    verifyMigrationCount(underTest, 23);
   }
 
 }
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/CreateMembersGroupsInEachOrganizationTest/initial.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/CreateMembersGroupsInEachOrganizationTest/initial.sql
new file mode 100644
index 00000000000..4db5fdb0192
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/CreateMembersGroupsInEachOrganizationTest/initial.sql
@@ -0,0 +1,66 @@
+CREATE TABLE "ORGANIZATIONS" (
+  "UUID" VARCHAR(40) NOT NULL PRIMARY KEY,
+  "KEE" VARCHAR(32) NOT NULL,
+  "NAME" VARCHAR(64) NOT NULL,
+  "DESCRIPTION" VARCHAR(256),
+  "URL" VARCHAR(256),
+  "AVATAR_URL" VARCHAR(256),
+  "GUARDED" BOOLEAN NOT NULL,
+  "USER_ID" INTEGER,
+  "DEFAULT_PERM_TEMPLATE_PROJECT" VARCHAR(40),
+  "DEFAULT_PERM_TEMPLATE_VIEW" VARCHAR(40),
+  "CREATED_AT" BIGINT NOT NULL,
+  "UPDATED_AT" BIGINT NOT NULL
+);
+CREATE UNIQUE INDEX "PK_ORGANIZATIONS" ON "ORGANIZATIONS" ("UUID");
+CREATE UNIQUE INDEX "ORGANIZATION_KEY" ON "ORGANIZATIONS" ("KEE");
+
+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),
+  "KEE" VARCHAR(100) NOT NULL,
+  "ORGANIZATION_UUID" VARCHAR(40) NOT NULL,
+  "NAME" 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 "PROPERTIES" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "PROP_KEY" VARCHAR(512) NOT NULL,
+  "RESOURCE_ID" INTEGER,
+  "USER_ID" INTEGER,
+  "IS_EMPTY" BOOLEAN NOT NULL,
+  "TEXT_VALUE" VARCHAR(4000),
+  "CLOB_VALUE" CLOB(2147483647),
+  "CREATED_AT" BIGINT
+);
+CREATE INDEX "PROPERTIES_KEY" ON "PROPERTIES" ("PROP_KEY");
+
+CREATE TABLE "INTERNAL_PROPERTIES" (
+  "KEE" VARCHAR(50) NOT NULL PRIMARY KEY,
+  "IS_EMPTY" BOOLEAN NOT NULL,
+  "TEXT_VALUE" VARCHAR(4000),
+  "CLOB_VALUE" CLOB,
+  "CREATED_AT" BIGINT
+);
+CREATE UNIQUE INDEX "UNIQ_INTERNAL_PROPERTIES" ON "INTERNAL_PROPERTIES" ("KEE");