Copy over files from GN3 repository.

39 files changed, 1250 insertions, 0 deletions

diff --git a/migrations/auth/20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database.py b/migrations/auth/20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database.py
new file mode 100644
index 0000000..d511f5d
--- /dev/null
+++ b/migrations/auth/20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database.py
@@ -0,0 +1,19 @@
+"""
+Initialise the auth(entic|oris)ation database.
+"""
+
+from yoyo import step
+
+__depends__ = {} # type: ignore[var-annotated]
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS users(
+            user_id TEXT PRIMARY KEY NOT NULL,
+            email TEXT UNIQUE NOT NULL,
+            name TEXT
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS users")
+]
diff --git a/migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py b/migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py
new file mode 100644
index 0000000..48bd663
--- /dev/null
+++ b/migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py
@@ -0,0 +1,20 @@
+"""
+create user_credentials table
+"""
+
+from yoyo import step
+
+__depends__ = {'20221103_01_js9ub-initialise-the-auth-entic-oris-ation-database'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS user_credentials(
+            user_id TEXT PRIMARY KEY,
+            password TEXT NOT NULL,
+            FOREIGN KEY(user_id) REFERENCES users(user_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS user_credentials")
+]
diff --git a/migrations/auth/20221108_01_CoxYh-create-the-groups-table.py b/migrations/auth/20221108_01_CoxYh-create-the-groups-table.py
new file mode 100644
index 0000000..29f92d4
--- /dev/null
+++ b/migrations/auth/20221108_01_CoxYh-create-the-groups-table.py
@@ -0,0 +1,19 @@
+"""
+Create the groups table
+"""
+
+from yoyo import step
+
+__depends__ = {'20221103_02_sGrIs-create-user-credentials-table'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS groups(
+            group_id TEXT PRIMARY KEY NOT NULL,
+            group_name TEXT NOT NULL,
+            group_metadata TEXT
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS groups")
+]
diff --git a/migrations/auth/20221108_02_wxTr9-create-privileges-table.py b/migrations/auth/20221108_02_wxTr9-create-privileges-table.py
new file mode 100644
index 0000000..67720b2
--- /dev/null
+++ b/migrations/auth/20221108_02_wxTr9-create-privileges-table.py
@@ -0,0 +1,18 @@
+"""
+Create privileges table
+"""
+
+from yoyo import step
+
+__depends__ = {'20221108_01_CoxYh-create-the-groups-table'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE privileges(
+            privilege_id TEXT PRIMARY KEY,
+            privilege_name TEXT NOT NULL
+        ) WITHOUT ROWID
+        """,
+         "DROP TABLE IF EXISTS privileges")
+]
diff --git a/migrations/auth/20221108_03_Pbhb1-create-resource-categories-table.py b/migrations/auth/20221108_03_Pbhb1-create-resource-categories-table.py
new file mode 100644
index 0000000..ce752ef
--- /dev/null
+++ b/migrations/auth/20221108_03_Pbhb1-create-resource-categories-table.py
@@ -0,0 +1,19 @@
+"""
+Create resource_categories table
+"""
+
+from yoyo import step
+
+__depends__ = {'20221108_02_wxTr9-create-privileges-table'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE resource_categories(
+            resource_category_id TEXT PRIMARY KEY,
+            resource_category_key TEXT NOT NULL,
+            resource_category_description TEXT NOT NULL
+        ) WITHOUT ROWID
+        """,
+    "DROP TABLE IF EXISTS resource_categories")
+]
diff --git a/migrations/auth/20221108_04_CKcSL-init-data-in-resource-categories-table.py b/migrations/auth/20221108_04_CKcSL-init-data-in-resource-categories-table.py
new file mode 100644
index 0000000..76ffbef
--- /dev/null
+++ b/migrations/auth/20221108_04_CKcSL-init-data-in-resource-categories-table.py
@@ -0,0 +1,25 @@
+"""
+Init data in resource_categories table
+"""
+
+from yoyo import step
+
+__depends__ = {'20221108_03_Pbhb1-create-resource-categories-table'}
+
+steps = [
+    step(
+        """
+        INSERT INTO resource_categories VALUES
+        ('fad071a3-2fc8-40b8-992b-cdefe7dcac79', 'mrna', 'mRNA Dataset'),
+        ('548d684b-d4d1-46fb-a6d3-51a56b7da1b3', 'phenotype', 'Phenotype (Publish) Dataset'),
+        ('48056f84-a2a6-41ac-8319-0e1e212cba2a', 'genotype', 'Genotype Dataset')
+        """,
+        """
+        DELETE FROM resource_categories WHERE resource_category_id IN
+        (
+            'fad071a3-2fc8-40b8-992b-cdefe7dcac79',
+            '548d684b-d4d1-46fb-a6d3-51a56b7da1b3',
+            '48056f84-a2a6-41ac-8319-0e1e212cba2a'
+        )
+        """)
+]
diff --git a/migrations/auth/20221109_01_HbD5F-add-resource-meta-field-to-resource-categories-field.py b/migrations/auth/20221109_01_HbD5F-add-resource-meta-field-to-resource-categories-field.py
new file mode 100644
index 0000000..6c829b1
--- /dev/null
+++ b/migrations/auth/20221109_01_HbD5F-add-resource-meta-field-to-resource-categories-field.py
@@ -0,0 +1,17 @@
+"""
+Add 'resource_meta' field to 'resource_categories' field.
+"""
+
+from yoyo import step
+
+__depends__ = {'20221108_04_CKcSL-init-data-in-resource-categories-table'}
+
+steps = [
+    step(
+        """
+        ALTER TABLE resource_categories
+        ADD COLUMN
+            resource_meta TEXT NOT NULL DEFAULT '[]'
+        """,
+        "ALTER TABLE resource_categories DROP COLUMN resource_meta")
+]
diff --git a/migrations/auth/20221110_01_WtZ1I-create-resources-table.py b/migrations/auth/20221110_01_WtZ1I-create-resources-table.py
new file mode 100644
index 0000000..abc8895
--- /dev/null
+++ b/migrations/auth/20221110_01_WtZ1I-create-resources-table.py
@@ -0,0 +1,26 @@
+"""
+Create 'resources' table
+"""
+
+from yoyo import step
+
+__depends__ = {'20221109_01_HbD5F-add-resource-meta-field-to-resource-categories-field'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS resources(
+            group_id TEXT NOT NULL,
+            resource_id TEXT NOT NULL,
+            resource_name TEXT NOT NULL UNIQUE,
+            resource_category_id TEXT NOT NULL,
+            PRIMARY KEY(group_id, resource_id),
+            FOREIGN KEY(group_id) REFERENCES groups(group_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
+            FOREIGN KEY(resource_category_id)
+              REFERENCES resource_categories(resource_category_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS resources")
+]
diff --git a/migrations/auth/20221110_05_BaNtL-create-roles-table.py b/migrations/auth/20221110_05_BaNtL-create-roles-table.py
new file mode 100644
index 0000000..51e19e8
--- /dev/null
+++ b/migrations/auth/20221110_05_BaNtL-create-roles-table.py
@@ -0,0 +1,19 @@
+"""
+Create 'roles' table
+"""
+
+from yoyo import step
+
+__depends__ = {'20221110_01_WtZ1I-create-resources-table'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS roles(
+            role_id TEXT NOT NULL PRIMARY KEY,
+            role_name TEXT NOT NULL,
+            user_editable INTEGER NOT NULL DEFAULT 1 CHECK (user_editable=0 or user_editable=1)
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS roles")
+]
diff --git a/migrations/auth/20221110_06_Pq2kT-create-generic-roles-table.py b/migrations/auth/20221110_06_Pq2kT-create-generic-roles-table.py
new file mode 100644
index 0000000..2b55c2b
--- /dev/null
+++ b/migrations/auth/20221110_06_Pq2kT-create-generic-roles-table.py
@@ -0,0 +1,24 @@
+"""
+Create 'generic_roles' table
+
+The roles in this table will be template roles, defining some common roles that
+can be used within the groups.
+
+They could also be used to define system-level roles, though those will not be
+provided to the "common" users.
+"""
+
+from yoyo import step
+
+__depends__ = {'20221110_05_BaNtL-create-roles-table'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS generic_roles(
+            role_id TEXT PRIMARY KEY,
+            role_name TEXT NOT NULL
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS generic_roles")
+]
diff --git a/migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py b/migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py
new file mode 100644
index 0000000..0d0eeb9
--- /dev/null
+++ b/migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py
@@ -0,0 +1,29 @@
+"""
+Create 'role_privileges' table
+"""
+
+from yoyo import step
+
+__depends__ = {'20221110_06_Pq2kT-create-generic-roles-table'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS role_privileges(
+            role_id TEXT NOT NULL,
+            privilege_id TEXT NOT NULL,
+            PRIMARY KEY(role_id, privilege_id),
+            FOREIGN KEY(role_id) REFERENCES roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
+            FOREIGN KEY(privilege_id) REFERENCES privileges(privilege_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS role_privileges"),
+    step(
+        """
+        CREATE INDEX IF NOT EXISTS idx_tbl_role_privileges_cols_role_id
+        ON role_privileges(role_id)
+        """,
+        "DROP INDEX IF EXISTS idx_tbl_role_privileges_cols_role_id")
+]
diff --git a/migrations/auth/20221110_08_23psB-add-privilege-category-and-privilege-description-columns-to-privileges-table.py b/migrations/auth/20221110_08_23psB-add-privilege-category-and-privilege-description-columns-to-privileges-table.py
new file mode 100644
index 0000000..077182b
--- /dev/null
+++ b/migrations/auth/20221110_08_23psB-add-privilege-category-and-privilege-description-columns-to-privileges-table.py
@@ -0,0 +1,22 @@
+"""
+Add 'privilege_category' and 'privilege_description' columns to 'privileges' table
+"""
+
+from yoyo import step
+
+__depends__ = {'20221110_07_7WGa1-create-role-privileges-table'}
+
+steps = [
+    step(
+        """
+        ALTER TABLE privileges ADD COLUMN
+            privilege_category TEXT NOT NULL DEFAULT 'common'
+        """,
+        "ALTER TABLE privileges DROP COLUMN privilege_category"),
+    step(
+        """
+        ALTER TABLE privileges ADD COLUMN
+            privilege_description TEXT
+        """,
+        "ALTER TABLE privileges DROP COLUMN privilege_description")
+]
diff --git a/migrations/auth/20221113_01_7M0hv-enumerate-initial-privileges.py b/migrations/auth/20221113_01_7M0hv-enumerate-initial-privileges.py
new file mode 100644
index 0000000..072f226
--- /dev/null
+++ b/migrations/auth/20221113_01_7M0hv-enumerate-initial-privileges.py
@@ -0,0 +1,66 @@
+"""
+Enumerate initial privileges
+"""
+
+from yoyo import step
+
+__depends__ = {'20221110_08_23psB-add-privilege-category-and-privilege-description-columns-to-privileges-table'}
+
+steps = [
+    step(
+        """
+        INSERT INTO
+            privileges(privilege_id, privilege_name, privilege_category,
+                       privilege_description)
+        VALUES
+            -- group-management privileges
+            ('4842e2aa-38b9-4349-805e-0a99a9cf8bff', 'create-group',
+             'group-management', 'Create a group'),
+            ('3ebfe79c-d159-4629-8b38-772cf4bc2261', 'view-group',
+             'group-management', 'View the details of a group'),
+            ('52576370-b3c7-4e6a-9f7e-90e9dbe24d8f', 'edit-group',
+             'group-management', 'Edit the details of a group'),
+            ('13ec2a94-4f1a-442d-aad2-936ad6dd5c57', 'delete-group',
+             'group-management', 'Delete a group'),
+            ('ae4add8c-789a-4d11-a6e9-a306470d83d9', 'add-group-member',
+             'group-management', 'Add a user to a group'),
+            ('f1bd3f42-567e-4965-9643-6d1a52ddee64', 'remove-group-member',
+             'group-management', 'Remove a user from a group'),
+            ('80f11285-5079-4ec0-907c-06509f88a364', 'assign-group-leader',
+             'group-management', 'Assign user group-leader privileges'),
+            ('d4afe2b3-4ca0-4edd-b37d-966535b5e5bd',
+             'transfer-group-leadership', 'group-management',
+             'Transfer leadership of the group to some other member'),
+
+            -- resource-management privileges
+            ('aa25b32a-bff2-418d-b0a2-e26b4a8f089b', 'create-resource',
+             'resource-management', 'Create a resource object'),
+            ('7f261757-3211-4f28-a43f-a09b800b164d', 'view-resource',
+             'resource-management', 'view a resource and use it in computations'),
+            ('2f980855-959b-4339-b80e-25d1ec286e21', 'edit-resource',
+             'resource-management', 'edit/update a resource'),
+            ('d2a070fd-e031-42fb-ba41-d60cf19e5d6d', 'delete-resource',
+             'resource-management', 'Delete a resource'),
+
+            -- role-management privileges
+            ('221660b1-df05-4be1-b639-f010269dbda9', 'create-role',
+             'role-management', 'Create a new role'),
+            ('7bcca363-cba9-4169-9e31-26bdc6179b28', 'edit-role',
+             'role-management', 'edit/update an existing role'),
+            ('5103cc68-96f8-4ebb-83a4-a31692402c9b', 'assign-role',
+             'role-management', 'Assign a role to an existing user'),
+            ('1c59eff5-9336-4ed2-a166-8f70d4cb012e', 'delete-role',
+             'role-management', 'Delete an existing role'),
+
+            -- user-management privileges
+            ('e7252301-6ee0-43ba-93ef-73b607cf06f6', 'reset-any-password',
+             'user-management', 'Reset the password for any user'),
+            ('1fe61370-cae9-4983-bd6c-ce61050c510f', 'delete-any-user',
+             'user-management', 'Delete any user from the system'),
+
+            -- sytem-admin privileges
+            ('519db546-d44e-4fdc-9e4e-25aa67548ab3', 'masquerade',
+             'system-admin', 'Masquerade as some other user')
+        """,
+        "DELETE FROM privileges")
+]
diff --git a/migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py b/migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py
new file mode 100644
index 0000000..2048f4a
--- /dev/null
+++ b/migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py
@@ -0,0 +1,35 @@
+"""
+Create 'generic_role_privileges' table
+
+This table links the generic_roles to the privileges they provide
+"""
+
+from yoyo import step
+
+__depends__ = {'20221113_01_7M0hv-enumerate-initial-privileges'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS generic_role_privileges(
+            generic_role_id TEXT NOT NULL,
+            privilege_id TEXT NOT NULL,
+            PRIMARY KEY(generic_role_id, privilege_id),
+            FOREIGN KEY(generic_role_id) REFERENCES generic_roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
+            FOREIGN KEY(privilege_id) REFERENCES privileges(privilege_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS generic_role_privileges"),
+    step(
+        """
+        CREATE INDEX IF NOT EXISTS
+            idx_tbl_generic_role_privileges_cols_generic_role_id
+        ON generic_role_privileges(generic_role_id)
+        """,
+        """
+        DROP INDEX IF EXISTS
+            idx_tbl_generic_role_privileges_cols_generic_role_id
+        """)
+]
diff --git a/migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py b/migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py
new file mode 100644
index 0000000..6bd101b
--- /dev/null
+++ b/migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py
@@ -0,0 +1,41 @@
+"""
+Drop 'generic_role*' tables
+"""
+
+from yoyo import step
+
+__depends__ = {'20221114_01_n8gsF-create-generic-role-privileges-table'}
+
+steps = [
+    step(
+        """
+        DROP INDEX IF EXISTS
+            idx_tbl_generic_role_privileges_cols_generic_role_id
+        """,
+        """
+        CREATE INDEX IF NOT EXISTS
+            idx_tbl_generic_role_privileges_cols_generic_role_id
+        ON generic_role_privileges(generic_role_id)
+        """),
+    step(
+        "DROP TABLE IF EXISTS generic_role_privileges",
+        """
+        CREATE TABLE IF NOT EXISTS generic_role_privileges(
+            generic_role_id TEXT NOT NULL,
+            privilege_id TEXT NOT NULL,
+            PRIMARY KEY(generic_role_id, privilege_id),
+            FOREIGN KEY(generic_role_id) REFERENCES generic_roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
+            FOREIGN KEY(privilege_id) REFERENCES privileges(privilege_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """),
+    step(
+        "DROP TABLE IF EXISTS generic_roles",
+        """
+        CREATE TABLE IF NOT EXISTS generic_roles(
+            role_id TEXT PRIMARY KEY,
+            role_name TEXT NOT NULL
+        ) WITHOUT ROWID
+        """)
+]
diff --git a/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py b/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py
new file mode 100644
index 0000000..a7e7b45
--- /dev/null
+++ b/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py
@@ -0,0 +1,29 @@
+"""
+Create 'group_roles' table
+"""
+
+from yoyo import step
+
+__depends__ = {'20221114_02_DKKjn-drop-generic-role-tables'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS group_roles(
+            group_id TEXT NOT NULL,
+            role_id TEXT NOT NULL,
+            PRIMARY KEY(group_id, role_id),
+            FOREIGN KEY(group_id) REFERENCES groups(group_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
+            FOREIGN KEY(role_id) REFERENCES roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS group_roles"),
+    step(
+        """
+        CREATE INDEX IF NOT EXISTS idx_tbl_group_roles_cols_group_id
+        ON group_roles(group_id)
+        """,
+        "DROP INDEX IF EXISTS idx_tbl_group_roles_cols_group_id")
+]
diff --git a/migrations/auth/20221114_04_tLUzB-initialise-basic-roles.py b/migrations/auth/20221114_04_tLUzB-initialise-basic-roles.py
new file mode 100644
index 0000000..386f481
--- /dev/null
+++ b/migrations/auth/20221114_04_tLUzB-initialise-basic-roles.py
@@ -0,0 +1,56 @@
+"""
+Initialise basic roles
+"""
+
+from yoyo import step
+
+__depends__ = {'20221114_03_PtWjc-create-group-roles-table'}
+
+steps = [
+    step(
+        """
+        INSERT INTO roles(role_id, role_name, user_editable) VALUES
+            ('a0e67630-d502-4b9f-b23f-6805d0f30e30', 'group-leader', '0'),
+            ('522e4d40-aefc-4a64-b7e0-768b8be517ee', 'resource-owner', '0')
+        """,
+        "DELETE FROM roles"),
+    step(
+        """
+        INSERT INTO role_privileges(role_id, privilege_id)
+        VALUES
+            -- group-management
+            ('a0e67630-d502-4b9f-b23f-6805d0f30e30',
+             '4842e2aa-38b9-4349-805e-0a99a9cf8bff'),
+            ('a0e67630-d502-4b9f-b23f-6805d0f30e30',
+             '3ebfe79c-d159-4629-8b38-772cf4bc2261'),
+            ('a0e67630-d502-4b9f-b23f-6805d0f30e30',
+             '52576370-b3c7-4e6a-9f7e-90e9dbe24d8f'),
+            ('a0e67630-d502-4b9f-b23f-6805d0f30e30',
+             '13ec2a94-4f1a-442d-aad2-936ad6dd5c57'),
+            ('a0e67630-d502-4b9f-b23f-6805d0f30e30',
+             'ae4add8c-789a-4d11-a6e9-a306470d83d9'),
+            ('a0e67630-d502-4b9f-b23f-6805d0f30e30',
+             'f1bd3f42-567e-4965-9643-6d1a52ddee64'),
+            ('a0e67630-d502-4b9f-b23f-6805d0f30e30',
+             'd4afe2b3-4ca0-4edd-b37d-966535b5e5bd'),
+
+            -- resource-management
+            ('a0e67630-d502-4b9f-b23f-6805d0f30e30',
+             'aa25b32a-bff2-418d-b0a2-e26b4a8f089b'),
+            ('a0e67630-d502-4b9f-b23f-6805d0f30e30',
+             '7f261757-3211-4f28-a43f-a09b800b164d'),
+            ('a0e67630-d502-4b9f-b23f-6805d0f30e30',
+             '2f980855-959b-4339-b80e-25d1ec286e21'),
+            ('a0e67630-d502-4b9f-b23f-6805d0f30e30',
+             'd2a070fd-e031-42fb-ba41-d60cf19e5d6d'),
+            ('522e4d40-aefc-4a64-b7e0-768b8be517ee',
+             'aa25b32a-bff2-418d-b0a2-e26b4a8f089b'),
+            ('522e4d40-aefc-4a64-b7e0-768b8be517ee',
+             '7f261757-3211-4f28-a43f-a09b800b164d'),
+            ('522e4d40-aefc-4a64-b7e0-768b8be517ee',
+             '2f980855-959b-4339-b80e-25d1ec286e21'),
+            ('522e4d40-aefc-4a64-b7e0-768b8be517ee',
+             'd2a070fd-e031-42fb-ba41-d60cf19e5d6d')
+        """,
+        "DELETE FROM role_privileges")
+]
diff --git a/migrations/auth/20221114_05_hQun6-create-user-roles-table.py b/migrations/auth/20221114_05_hQun6-create-user-roles-table.py
new file mode 100644
index 0000000..e0de751
--- /dev/null
+++ b/migrations/auth/20221114_05_hQun6-create-user-roles-table.py
@@ -0,0 +1,29 @@
+"""
+Create 'user_roles' table.
+"""
+
+from yoyo import step
+
+__depends__ = {'20221114_04_tLUzB-initialise-basic-roles'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS user_roles(
+            user_id TEXT NOT NULL,
+            role_id TEXT NOT NULL,
+            PRIMARY KEY(user_id, role_id),
+            FOREIGN KEY(user_id) REFERENCES users(user_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
+            FOREIGN KEY(role_id) REFERENCES roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS user_roles"),
+    step(
+        """
+        CREATE INDEX IF NOT EXISTS idx_tbl_user_roles_cols_user_id
+        ON user_roles(user_id)
+        """,
+        "DROP INDEX IF EXISTS idx_tbl_user_roles_cols_user_id")
+]
diff --git a/migrations/auth/20221116_01_nKUmX-add-privileges-to-group-leader-role.py b/migrations/auth/20221116_01_nKUmX-add-privileges-to-group-leader-role.py
new file mode 100644
index 0000000..2e4ae28
--- /dev/null
+++ b/migrations/auth/20221116_01_nKUmX-add-privileges-to-group-leader-role.py
@@ -0,0 +1,35 @@
+"""
+Add privileges to 'group-leader' role.
+"""
+
+from yoyo import step
+
+__depends__ = {'20221114_05_hQun6-create-user-roles-table'}
+
+steps = [
+    step(
+        """
+        INSERT INTO role_privileges(role_id, privilege_id)
+        VALUES
+            -- role management
+            ('a0e67630-d502-4b9f-b23f-6805d0f30e30',
+             '221660b1-df05-4be1-b639-f010269dbda9'),
+            ('a0e67630-d502-4b9f-b23f-6805d0f30e30',
+             '7bcca363-cba9-4169-9e31-26bdc6179b28'),
+            ('a0e67630-d502-4b9f-b23f-6805d0f30e30',
+             '5103cc68-96f8-4ebb-83a4-a31692402c9b'),
+            ('a0e67630-d502-4b9f-b23f-6805d0f30e30',
+             '1c59eff5-9336-4ed2-a166-8f70d4cb012e')
+        """,
+        """
+        DELETE FROM role_privileges
+        WHERE
+            role_id='a0e67630-d502-4b9f-b23f-6805d0f30e30'
+        AND privilege_id IN (
+            '221660b1-df05-4be1-b639-f010269dbda9',
+            '7bcca363-cba9-4169-9e31-26bdc6179b28',
+            '5103cc68-96f8-4ebb-83a4-a31692402c9b',
+            '1c59eff5-9336-4ed2-a166-8f70d4cb012e'
+        )
+        """)
+]
diff --git a/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py b/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py
new file mode 100644
index 0000000..a4d7806
--- /dev/null
+++ b/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py
@@ -0,0 +1,52 @@
+"""
+Modify 'group_roles': add 'group_role_id'
+
+At this point, there is no data in the `group_roles` table  and therefore, it
+should be safe to simply recreate it.
+"""
+
+from yoyo import step
+
+__depends__ = {'20221116_01_nKUmX-add-privileges-to-group-leader-role'}
+
+steps = [
+    step(
+        "DROP INDEX IF EXISTS idx_tbl_group_roles_cols_group_id",
+        """
+        CREATE INDEX IF NOT EXISTS idx_tbl_group_roles_cols_group_id
+        ON group_roles(group_id)
+        """),
+    step(
+        "DROP TABLE IF EXISTS group_roles",
+        """
+        CREATE TABLE IF NOT EXISTS group_roles(
+            group_id TEXT NOT NULL,
+            role_id TEXT NOT NULL,
+            PRIMARY KEY(group_id, role_id),
+            FOREIGN KEY(group_id) REFERENCES groups(group_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
+            FOREIGN KEY(role_id) REFERENCES roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """),
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS group_roles(
+            group_role_id TEXT PRIMARY KEY,
+            group_id TEXT NOT NULL,
+            role_id TEXT NOT NULL,
+            UNIQUE (group_id, role_id),
+            FOREIGN KEY(group_id) REFERENCES groups(group_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
+            FOREIGN KEY(role_id) REFERENCES roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS group_roles"),
+    step(
+        """
+        CREATE INDEX IF NOT EXISTS idx_tbl_group_roles_cols_group_id
+        ON group_roles(group_id)
+        """,
+        "DROP INDEX IF EXISTS idx_tbl_group_roles_cols_group_id")
+]
diff --git a/migrations/auth/20221117_02_fmuZh-create-group-users-table.py b/migrations/auth/20221117_02_fmuZh-create-group-users-table.py
new file mode 100644
index 0000000..92885ef
--- /dev/null
+++ b/migrations/auth/20221117_02_fmuZh-create-group-users-table.py
@@ -0,0 +1,25 @@
+"""
+Create 'group_users' table.
+"""
+
+from yoyo import step
+
+__depends__ = {'20221117_01_RDlfx-modify-group-roles-add-group-role-id'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS group_users(
+            group_id TEXT NOT NULL,
+            user_id TEXT NOT NULL UNIQUE, -- user can only be in one group
+            PRIMARY KEY(group_id, user_id)
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS group_users"),
+    step(
+        """
+        CREATE INDEX IF NOT EXISTS tbl_group_users_cols_group_id
+        ON group_users(group_id)
+        """,
+        "DROP INDEX IF EXISTS tbl_group_users_cols_group_id")
+]
diff --git a/migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py b/migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py
new file mode 100644
index 0000000..9aa3667
--- /dev/null
+++ b/migrations/auth/20221206_01_BbeF9-create-group-user-roles-on-resources-table.py
@@ -0,0 +1,39 @@
+"""
+Create 'group_user_roles_on_resources' table
+"""
+
+from yoyo import step
+
+__depends__ = {'20221117_02_fmuZh-create-group-users-table'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE group_user_roles_on_resources (
+            group_id TEXT NOT NULL,
+            user_id TEXT NOT NULL,
+            role_id TEXT NOT NULL,
+            resource_id TEXT NOT NULL,
+            PRIMARY KEY (group_id, user_id, role_id, resource_id),
+            FOREIGN KEY (user_id)
+              REFERENCES users(user_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
+            FOREIGN KEY (group_id, role_id)
+              REFERENCES group_roles(group_id, role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
+            FOREIGN KEY (group_id, resource_id)
+              REFERENCES resources(group_id, resource_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS group_user_roles_on_resources"),
+    step(
+        """
+        CREATE INDEX IF NOT EXISTS
+            idx_tbl_group_user_roles_on_resources_group_user_resource
+        ON group_user_roles_on_resources(group_id, user_id, resource_id)
+        """,
+        """
+        DROP INDEX IF EXISTS
+            idx_tbl_group_user_roles_on_resources_group_user_resource""")
+]
diff --git a/migrations/auth/20221208_01_sSdHz-add-public-column-to-resources-table.py b/migrations/auth/20221208_01_sSdHz-add-public-column-to-resources-table.py
new file mode 100644
index 0000000..2238069
--- /dev/null
+++ b/migrations/auth/20221208_01_sSdHz-add-public-column-to-resources-table.py
@@ -0,0 +1,16 @@
+"""
+Add 'public' column to 'resources' table
+"""
+
+from yoyo import step
+
+__depends__ = {'20221206_01_BbeF9-create-group-user-roles-on-resources-table'}
+
+steps = [
+    step(
+        """
+        ALTER TABLE resources ADD COLUMN
+            public INTEGER NOT NULL DEFAULT 0 CHECK (public=0 or public=1)
+        """,
+        "ALTER TABLE resources DROP COLUMN public")
+]
diff --git a/migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py b/migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py
new file mode 100644
index 0000000..475be01
--- /dev/null
+++ b/migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py
@@ -0,0 +1,25 @@
+"""
+create oauth2_clients table
+"""
+
+from yoyo import step
+
+__depends__ = {'20221208_01_sSdHz-add-public-column-to-resources-table'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS oauth2_clients(
+            client_id TEXT NOT NULL,
+            client_secret TEXT NOT NULL,
+            client_id_issued_at INTEGER NOT NULL,
+            client_secret_expires_at INTEGER NOT NULL,
+            client_metadata TEXT,
+            user_id TEXT NOT NULL,
+            PRIMARY KEY(client_id),
+            FOREIGN KEY(user_id) REFERENCES users(user_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS oauth2_clients")
+]
diff --git a/migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py b/migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py
new file mode 100644
index 0000000..778282b
--- /dev/null
+++ b/migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py
@@ -0,0 +1,31 @@
+"""
+create oauth2_tokens table
+"""
+
+from yoyo import step
+
+__depends__ = {'20221219_01_CI3tN-create-oauth2-clients-table'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE oauth2_tokens(
+            token_id TEXT NOT NULL,
+            client_id TEXT NOT NULL,
+            token_type TEXT NOT NULL,
+            access_token TEXT UNIQUE NOT NULL,
+            refresh_token TEXT,
+            scope TEXT,
+            revoked INTEGER CHECK (revoked = 0 or revoked = 1),
+            issued_at INTEGER NOT NULL,
+            expires_in INTEGER NOT NULL,
+            user_id TEXT NOT NULL,
+            PRIMARY KEY(token_id),
+            FOREIGN KEY (client_id) REFERENCES oauth2_clients(client_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
+            FOREIGN KEY (user_id) REFERENCES users(user_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS oauth2_tokens")
+]
diff --git a/migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py b/migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py
new file mode 100644
index 0000000..1683f87
--- /dev/null
+++ b/migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py
@@ -0,0 +1,31 @@
+"""
+create authorisation_code table
+"""
+
+from yoyo import step
+
+__depends__ = {'20221219_02_buSEU-create-oauth2-tokens-table'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE authorisation_code (
+            code_id TEXT NOT NULL,
+            code TEXT UNIQUE NOT NULL,
+            client_id NOT NULL,
+            redirect_uri TEXT,
+            scope TEXT,
+            nonce TEXT,
+            auth_time INTEGER NOT NULL,
+            code_challenge TEXT,
+            code_challenge_method TEXT,
+            user_id TEXT NOT NULL,
+            PRIMARY KEY (code_id),
+            FOREIGN KEY (client_id) REFERENCES oauth2_clients(client_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
+            FOREIGN KEY (user_id) REFERENCES users(user_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS authorisation_code")
+]
diff --git a/migrations/auth/20230111_01_Wd6IZ-remove-create-group-privilege-from-group-leader.py b/migrations/auth/20230111_01_Wd6IZ-remove-create-group-privilege-from-group-leader.py
new file mode 100644
index 0000000..7e7fda2
--- /dev/null
+++ b/migrations/auth/20230111_01_Wd6IZ-remove-create-group-privilege-from-group-leader.py
@@ -0,0 +1,40 @@
+"""
+remove 'create-group' privilege from group-leader.
+"""
+
+from yoyo import step
+
+__depends__ = {'20221219_03_PcTrb-create-authorisation-code-table'}
+
+steps = [
+    step(
+        """
+        DELETE FROM role_privileges
+        WHERE role_id='a0e67630-d502-4b9f-b23f-6805d0f30e30'
+        AND privilege_id='4842e2aa-38b9-4349-805e-0a99a9cf8bff'
+        """,
+        """
+        INSERT INTO role_privileges VALUES
+        ('a0e67630-d502-4b9f-b23f-6805d0f30e30',
+        '4842e2aa-38b9-4349-805e-0a99a9cf8bff')
+        """),
+    step(
+        """
+        INSERT INTO roles(role_id, role_name, user_editable) VALUES
+          ('ade7e6b0-ba9c-4b51-87d0-2af7fe39a347', 'group-creator', '0')
+        """,
+        """
+        DELETE FROM roles WHERE role_id='ade7e6b0-ba9c-4b51-87d0-2af7fe39a347'
+        """),
+    step(
+        """
+        INSERT INTO role_privileges VALUES
+          ('ade7e6b0-ba9c-4b51-87d0-2af7fe39a347',
+           '4842e2aa-38b9-4349-805e-0a99a9cf8bff')
+        """,
+        """
+        DELETE FROM role_privileges
+        WHERE role_id='ade7e6b0-ba9c-4b51-87d0-2af7fe39a347'
+        AND privilege_id='4842e2aa-38b9-4349-805e-0a99a9cf8bff'
+        """)
+]
diff --git a/migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py b/migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py
new file mode 100644
index 0000000..1ef5ab0
--- /dev/null
+++ b/migrations/auth/20230116_01_KwuJ3-rework-privileges-schema.py
@@ -0,0 +1,111 @@
+"""
+rework privileges schema
+"""
+import contextlib
+
+from yoyo import step
+
+__depends__ = {'20230111_01_Wd6IZ-remove-create-group-privilege-from-group-leader'}
+
+privileges = ( # format: (original_id, original_name, new_id, category)
+    ("13ec2a94-4f1a-442d-aad2-936ad6dd5c57", "delete-group",
+     "system:group:delete-group", "group-management"),
+    ("1c59eff5-9336-4ed2-a166-8f70d4cb012e", "delete-role",
+     "group:role:delete-role", "role-management"),
+    ("1fe61370-cae9-4983-bd6c-ce61050c510f", "delete-any-user",
+     "system:user:delete-user", "user-management"),
+    ("221660b1-df05-4be1-b639-f010269dbda9", "create-role",
+     "group:role:create-role", "role-management"),
+    ("2f980855-959b-4339-b80e-25d1ec286e21", "edit-resource",
+     "group:resource:edit-resource", "resource-management"),
+    ("3ebfe79c-d159-4629-8b38-772cf4bc2261", "view-group",
+     "system:group:view-group", "group-management"),
+    ("4842e2aa-38b9-4349-805e-0a99a9cf8bff", "create-group",
+     "system:group:create-group", "group-management"),
+    ("5103cc68-96f8-4ebb-83a4-a31692402c9b", "assign-role",
+     "group:user:assign-role", "role-management"),
+    ("519db546-d44e-4fdc-9e4e-25aa67548ab3", "masquerade",
+     "system:user:masquerade", "system-admin"),
+    ("52576370-b3c7-4e6a-9f7e-90e9dbe24d8f", "edit-group",
+     "system:group:edit-group", "group-management"),
+    ("7bcca363-cba9-4169-9e31-26bdc6179b28", "edit-role",
+     "group:role:edit-role", "role-management"),
+    ("7f261757-3211-4f28-a43f-a09b800b164d", "view-resource",
+     "group:resource:view-resource", "resource-management"),
+    ("80f11285-5079-4ec0-907c-06509f88a364", "assign-group-leader",
+     "system:user:assign-group-leader", "group-management"),
+    ("aa25b32a-bff2-418d-b0a2-e26b4a8f089b", "create-resource",
+     "group:resource:create-resource", "resource-management"),
+    ("ae4add8c-789a-4d11-a6e9-a306470d83d9", "add-group-member",
+     "group:user:add-group-member", "group-management"),
+    ("d2a070fd-e031-42fb-ba41-d60cf19e5d6d", "delete-resource",
+     "group:resource:delete-resource", "resource-management"),
+    ("d4afe2b3-4ca0-4edd-b37d-966535b5e5bd", "transfer-group-leadership",
+     "system:group:transfer-group-leader", "group-management"),
+    ("e7252301-6ee0-43ba-93ef-73b607cf06f6", "reset-any-password",
+     "system:user:reset-password", "user-management"),
+    ("f1bd3f42-567e-4965-9643-6d1a52ddee64", "remove-group-member",
+     "group:user:remove-group-member", "group-management"))
+
+def rework_privileges_table(cursor):
+    "rework the schema"
+    cursor.executemany(
+        ("UPDATE privileges SET privilege_id=:id "
+         "WHERE privilege_id=:old_id"),
+        ({"id": row[2], "old_id": row[0]} for row in privileges))
+    cursor.execute("ALTER TABLE privileges DROP COLUMN privilege_category")
+    cursor.execute("ALTER TABLE privileges DROP COLUMN privilege_name")
+
+def restore_privileges_table(cursor):
+    "restore the schema"
+    cursor.execute((
+        "CREATE TABLE privileges_restore ("
+        "  privilege_id TEXT PRIMARY KEY,"
+        "  privilege_name TEXT NOT NULL,"
+        "  privilege_category TEXT NOT NULL DEFAULT 'common',"
+        "  privilege_description TEXT"
+        ")"))
+    id_dict = {row[2]: {"id": row[0], "name": row[1], "cat": row[3]}
+               for row in privileges}
+    cursor.execute(
+        "SELECT privilege_id, privilege_description FROM privileges")
+    params = ({**id_dict[row[0]], "desc": row[1]} for row in cursor.fetchall())
+    cursor.executemany(
+        "INSERT INTO privileges_restore VALUES (:id, :name, :cat, :desc)",
+        params)
+    cursor.execute("DROP TABLE privileges")
+    cursor.execute("ALTER TABLE privileges_restore RENAME TO privileges")
+
+def update_privilege_ids_in_role_privileges(cursor):
+    """Update the ids to new form."""
+    cursor.executemany(
+        ("UPDATE role_privileges SET privilege_id=:new_id "
+         "WHERE privilege_id=:old_id"),
+        ({"new_id": row[2], "old_id": row[0]} for row in privileges))
+
+def restore_privilege_ids_in_role_privileges(cursor):
+    """Restore original ids"""
+    cursor.executemany(
+        ("UPDATE role_privileges SET privilege_id=:old_id "
+         "WHERE privilege_id=:new_id"),
+        ({"new_id": row[2], "old_id": row[0]} for row in privileges))
+
+def change_schema(conn):
+    """Change the privileges schema and IDs"""
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.execute("PRAGMA foreign_keys=OFF")
+        rework_privileges_table(cursor)
+        update_privilege_ids_in_role_privileges(cursor)
+        cursor.execute("PRAGMA foreign_keys=ON")
+
+def restore_schema(conn):
+    """Change the privileges schema and IDs"""
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.execute("PRAGMA foreign_keys=OFF")
+        restore_privilege_ids_in_role_privileges(cursor)
+        restore_privileges_table(cursor)
+        cursor.execute("PRAGMA foreign_keys=ON")
+
+steps = [
+    step(change_schema, restore_schema)
+]
diff --git a/migrations/auth/20230207_01_r0bkZ-create-group-join-requests-table.py b/migrations/auth/20230207_01_r0bkZ-create-group-join-requests-table.py
new file mode 100644
index 0000000..ceae5ea
--- /dev/null
+++ b/migrations/auth/20230207_01_r0bkZ-create-group-join-requests-table.py
@@ -0,0 +1,29 @@
+"""
+Create group_requests table
+"""
+
+from yoyo import step
+
+__depends__ = {'20230116_01_KwuJ3-rework-privileges-schema'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS group_join_requests(
+            request_id TEXT NOT NULL,
+            group_id TEXT NOT NULL,
+            requester_id TEXT NOT NULL,
+            timestamp REAL NOT NULL,
+            status TEXT NOT NULL DEFAULT 'PENDING',
+            message TEXT,
+            PRIMARY KEY(request_id, group_id),
+            FOREIGN KEY(group_id) REFERENCES groups(group_id)
+            ON UPDATE CASCADE ON DELETE CASCADE,
+            FOREIGN KEY (requester_id) REFERENCES users(user_id)
+            ON UPDATE CASCADE ON DELETE CASCADE,
+            UNIQUE(group_id, requester_id),
+            CHECK (status IN ('PENDING', 'ACCEPTED', 'REJECTED'))
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS group_join_requests")
+]
diff --git a/migrations/auth/20230210_01_8xMa1-system-admin-privileges-for-data-distribution.py b/migrations/auth/20230210_01_8xMa1-system-admin-privileges-for-data-distribution.py
new file mode 100644
index 0000000..8b406a6
--- /dev/null
+++ b/migrations/auth/20230210_01_8xMa1-system-admin-privileges-for-data-distribution.py
@@ -0,0 +1,22 @@
+"""
+System admin privileges for data distribution
+
+These privileges are focussed on allowing the system administrator to link the
+datasets and traits in the main database to specific groups in the auth system.
+"""
+
+from yoyo import step
+
+__depends__ = {'20230207_01_r0bkZ-create-group-join-requests-table'}
+
+steps = [
+    step(
+        """
+        INSERT INTO privileges VALUES
+          ('system:data:link-to-group', 'Link a dataset or trait to a group.')
+        """,
+        """
+        DELETE FROM privileges WHERE privilege_id IN
+         ('system:data:link-to-group')
+        """)
+]
diff --git a/migrations/auth/20230210_02_lDK14-create-system-admin-role.py b/migrations/auth/20230210_02_lDK14-create-system-admin-role.py
new file mode 100644
index 0000000..9b3fc2b
--- /dev/null
+++ b/migrations/auth/20230210_02_lDK14-create-system-admin-role.py
@@ -0,0 +1,38 @@
+"""
+Create system-admin role
+"""
+import uuid
+from contextlib import closing
+
+from yoyo import step
+
+__depends__ = {'20230210_01_8xMa1-system-admin-privileges-for-data-distribution'}
+
+def create_sys_admin_role(conn):
+    with closing(conn.cursor()) as cursor:
+        role_id = uuid.uuid4()
+        cursor.execute(
+            "INSERT INTO roles VALUES (?, 'system-administrator', '0')",
+            (str(role_id),))
+
+        cursor.executemany(
+            "INSERT INTO role_privileges VALUES (:role_id, :privilege_id)",
+            ({"role_id": f"{role_id}", "privilege_id": priv}
+         for priv in (
+                 "system:data:link-to-group",
+                 "system:group:create-group",
+                 "system:group:delete-group",
+                 "system:group:edit-group",
+                 "system:group:transfer-group-leader",
+                 "system:group:view-group",
+                 "system:user:assign-group-leader",
+                 "system:user:delete-user",
+                 "system:user:masquerade",
+                 "system:user:reset-password")))
+
+def drop_sys_admin_role(conn):
+    pass
+
+steps = [
+    step(create_sys_admin_role, drop_sys_admin_role)
+]
diff --git a/migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py b/migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py
new file mode 100644
index 0000000..84bbd49
--- /dev/null
+++ b/migrations/auth/20230306_01_pRfxl-add-system-user-list-privilege.py
@@ -0,0 +1,26 @@
+"""
+Add system:user:list privilege
+"""
+import contextlib
+
+from yoyo import step
+
+__depends__ = {'20230210_02_lDK14-create-system-admin-role'}
+
+def insert_users_list_priv(conn):
+    """Create a new 'system:user:list' privilege."""
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.execute(
+            "INSERT INTO privileges(privilege_id, privilege_description) "
+            "VALUES('system:user:list', 'List users in the system') "
+            "ON CONFLICT (privilege_id) DO NOTHING")
+
+def delete_users_list_priv(conn):
+    """Delete the new 'system:user:list' privilege."""
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.execute(
+            "DELETE FROM privileges WHERE privilege_id='system:user:list'")
+
+steps = [
+    step(insert_users_list_priv, delete_users_list_priv)
+]
diff --git a/migrations/auth/20230306_02_7GnRY-add-system-user-list-privilege-to-system-administrator-and-group-leader-roles.py b/migrations/auth/20230306_02_7GnRY-add-system-user-list-privilege-to-system-administrator-and-group-leader-roles.py
new file mode 100644
index 0000000..3caad55
--- /dev/null
+++ b/migrations/auth/20230306_02_7GnRY-add-system-user-list-privilege-to-system-administrator-and-group-leader-roles.py
@@ -0,0 +1,42 @@
+"""
+Add system:user:list privilege to system-administrator and group-leader roles.
+"""
+import uuid
+import contextlib
+
+from yoyo import step
+
+__depends__ = {'20230306_01_pRfxl-add-system-user-list-privilege'}
+
+def role_ids(cursor):
+    """Get role ids from names"""
+    cursor.execute(
+        "SELECT * FROM roles WHERE role_name IN "
+        "('system-administrator', 'group-leader')")
+    return (uuid.UUID(row[0]) for row in cursor.fetchall())
+
+def add_privilege_to_roles(conn):
+    """
+    Add 'system:user:list' privilege to 'system-administrator' and
+    'group-leader' roles."""
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.executemany(
+            "INSERT INTO role_privileges(role_id,privilege_id) "
+            "VALUES(?, ?)",
+            tuple((str(role_id), "system:user:list")
+                  for role_id in role_ids(cursor)))
+
+def del_privilege_from_roles(conn):
+    """
+    Delete 'system:user:list' privilege to 'system-administrator' and
+    'group-leader' roles.
+    """
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.execute(
+            "DELETE FROM role_privileges WHERE "
+            "role_id IN (?, ?) AND privilege_id='system:user:list'",
+            tuple(str(role_id) for role_id in role_ids(cursor)))
+
+steps = [
+    step(add_privilege_to_roles, del_privilege_from_roles)
+]
diff --git a/migrations/auth/20230322_01_0dDZR-create-linked-phenotype-data-table.py b/migrations/auth/20230322_01_0dDZR-create-linked-phenotype-data-table.py
new file mode 100644
index 0000000..647325f
--- /dev/null
+++ b/migrations/auth/20230322_01_0dDZR-create-linked-phenotype-data-table.py
@@ -0,0 +1,30 @@
+"""
+Create linked-phenotype-data table
+"""
+
+from yoyo import step
+
+__depends__ = {'20230306_02_7GnRY-add-system-user-list-privilege-to-system-administrator-and-group-leader-roles'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS linked_phenotype_data
+        -- Link the data in MariaDB to user groups in the auth system
+        (
+          data_link_id TEXT NOT NULL PRIMARY KEY, -- A new ID for the auth system
+          group_id TEXT NOT NULL, -- The user group the data is linked to
+          SpeciesId TEXT NOT NULL, -- The species in MariaDB
+          InbredSetId TEXT NOT NULL, -- The traits group in MariaDB
+          PublishFreezeId TEXT NOT NULL, -- The dataset Id in MariaDB
+          dataset_name TEXT, -- dataset Name in MariaDB
+          dataset_fullname, -- dataset FullName in MariaDB
+          dataset_shortname, -- dataset ShortName in MariaDB
+          PublishXRefId TEXT NOT NULL, -- The trait's ID in MariaDB
+          FOREIGN KEY (group_id)
+            REFERENCES groups(group_id) ON UPDATE CASCADE ON DELETE RESTRICT
+          UNIQUE (SpeciesId, InbredSetId, PublishFreezeId, PublishXRefId)
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS linked_phenotype_data")
+]
diff --git a/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py b/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py
new file mode 100644
index 0000000..7c9e986
--- /dev/null
+++ b/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py
@@ -0,0 +1,29 @@
+"""
+Create phenotype_resources table
+"""
+
+from yoyo import step
+
+__depends__ = {'20230322_01_0dDZR-create-linked-phenotype-data-table'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS phenotype_resources
+        -- Link phenotype data to specific resources
+        (
+          group_id TEXT NOT NULL,
+          resource_id TEXT NOT NULL, -- A resource can have multiple data items
+          data_link_id TEXT NOT NULL,
+          PRIMARY KEY(group_id, resource_id, data_link_id),
+          UNIQUE (data_link_id), -- ensure data is linked to only one resource
+          FOREIGN KEY (group_id, resource_id)
+            REFERENCES resources(group_id, resource_id)
+            ON UPDATE CASCADE ON DELETE RESTRICT,
+          FOREIGN KEY (data_link_id)
+            REFERENCES linked_phenotype_data(data_link_id)
+            ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS phenotype_resources")
+]
diff --git a/migrations/auth/20230404_01_VKxXg-create-linked-genotype-data-table.py b/migrations/auth/20230404_01_VKxXg-create-linked-genotype-data-table.py
new file mode 100644
index 0000000..02e8718
--- /dev/null
+++ b/migrations/auth/20230404_01_VKxXg-create-linked-genotype-data-table.py
@@ -0,0 +1,29 @@
+"""
+Create linked genotype data table
+"""
+
+from yoyo import step
+
+__depends__ = {'20230322_02_Ll854-create-phenotype-resources-table'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS linked_genotype_data
+        -- Link genotype data in MariaDB to user groups in auth system
+        (
+          data_link_id TEXT NOT NULL PRIMARY KEY, -- A new ID for the auth system
+          group_id TEXT NOT NULL, -- The user group the data is linked to
+          SpeciesId TEXT NOT NULL, -- The species in MariaDB
+          InbredSetId TEXT NOT NULL, -- The traits group in MariaDB
+          GenoFreezeId TEXT NOT NULL, -- The dataset Id in MariaDB
+          dataset_name TEXT, -- dataset Name in MariaDB
+          dataset_fullname, -- dataset FullName in MariaDB
+          dataset_shortname, -- dataset ShortName in MariaDB
+          FOREIGN KEY (group_id)
+            REFERENCES groups(group_id) ON UPDATE CASCADE ON DELETE RESTRICT
+          UNIQUE (SpeciesId, InbredSetId, GenoFreezeId)
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS linked_genotype_data")
+]
diff --git a/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py b/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py
new file mode 100644
index 0000000..1a865e0
--- /dev/null
+++ b/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py
@@ -0,0 +1,29 @@
+"""
+Create genotype resources table
+"""
+
+from yoyo import step
+
+__depends__ = {'20230404_01_VKxXg-create-linked-genotype-data-table'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS genotype_resources
+        -- Link genotype data to specific resource
+        (
+          group_id TEXT NOT NULL,
+          resource_id TEXT NOT NULL, -- A resource can have multiple items
+          data_link_id TEXT NOT NULL,
+          PRIMARY KEY (group_id, resource_id, data_link_id),
+          UNIQUE (data_link_id) -- ensure data is linked to single resource
+          FOREIGN KEY (group_id, resource_id)
+            REFERENCES resources(group_id, resource_id)
+            ON UPDATE CASCADE ON DELETE RESTRICT,
+          FOREIGN KEY (data_link_id)
+            REFERENCES linked_genotype_data(data_link_id)
+            ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS genotype_resources")
+]
diff --git a/migrations/auth/20230410_01_8mwaf-create-linked-mrna-data-table.py b/migrations/auth/20230410_01_8mwaf-create-linked-mrna-data-table.py
new file mode 100644
index 0000000..db9a6bf
--- /dev/null
+++ b/migrations/auth/20230410_01_8mwaf-create-linked-mrna-data-table.py
@@ -0,0 +1,30 @@
+"""
+Create linked mrna data table
+"""
+
+from yoyo import step
+
+__depends__ = {'20230404_02_la33P-create-genotype-resources-table'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS linked_mrna_data
+        -- Link mRNA Assay data in MariaDB to user groups in auth system
+        (
+          data_link_id TEXT NOT NULL PRIMARY KEY, -- A new ID for the auth system
+          group_id TEXT NOT NULL, -- The user group the data is linked to
+          SpeciesId TEXT NOT NULL, -- The species in MariaDB
+          InbredSetId TEXT NOT NULL, -- The traits group in MariaDB
+          ProbeFreezeId TEXT NOT NULL, -- The study ID in MariaDB
+          ProbeSetFreezeId TEXT NOT NULL, -- The dataset Id in MariaDB
+          dataset_name TEXT, -- dataset Name in MariaDB
+          dataset_fullname, -- dataset FullName in MariaDB
+          dataset_shortname, -- dataset ShortName in MariaDB
+          FOREIGN KEY (group_id)
+            REFERENCES groups(group_id) ON UPDATE CASCADE ON DELETE RESTRICT
+          UNIQUE (SpeciesId, InbredSetId, ProbeFreezeId, ProbeSetFreezeId)
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS linked_mrna_data")
+]
diff --git a/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py b/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py
new file mode 100644
index 0000000..2ad1056
--- /dev/null
+++ b/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py
@@ -0,0 +1,28 @@
+"""
+Create mRNA resources table
+"""
+
+from yoyo import step
+
+__depends__ = {'20230410_01_8mwaf-create-linked-mrna-data-table'}
+
+steps = [
+    step(
+        """
+        CREATE TABLE IF NOT EXISTS mrna_resources
+        -- Link mRNA data to specific resource
+        (
+          group_id TEXT NOT NULL,
+          resource_id TEXT NOT NULL, -- A resource can have multiple items
+          data_link_id TEXT NOT NULL,
+          PRIMARY KEY (resource_id, data_link_id),
+          UNIQUE (data_link_id) -- ensure data is linked to single resource
+          FOREIGN KEY (group_id, resource_id)
+            REFERENCES resources(group_id, resource_id)
+            ON UPDATE CASCADE ON DELETE RESTRICT,
+          FOREIGN KEY (data_link_id) REFERENCES linked_mrna_data(data_link_id)
+            ON UPDATE CASCADE ON DELETE RESTRICT
+        ) WITHOUT ROWID
+        """,
+        "DROP TABLE IF EXISTS mrna_resources")
+]