Add new 'systemwide-data-curator' role.

Add a new role named 'systemwide-data-curator' that is able to edit
and/or delete any data on the system regardless of who own it.

1 files changed, 61 insertions, 0 deletions

diff --git a/migrations/auth/20260206_01_v3f4P-add-role-systemwide-data-curator.py b/migrations/auth/20260206_01_v3f4P-add-role-systemwide-data-curator.py
new file mode 100644
index 0000000..63e807a
--- /dev/null
+++ b/migrations/auth/20260206_01_v3f4P-add-role-systemwide-data-curator.py
@@ -0,0 +1,61 @@
+"""
+add role systemwide-data-curator.
+"""
+import uuid
+import contextlib
+
+from yoyo import step
+
+__depends__ = {'20250731_01_Ke1us-add-sysadmin-privileges-for-acting-on-groups-members'}
+
+
+def create_systemwide_data_curator_role(conn):
+    """Create a new 'systemwide-data-curator' role."""
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.execute(
+            "INSERT INTO roles(role_id, role_name, user_editable) "
+            "VALUES (?, 'systemwide-data-curator', 0)",
+            (str(uuid.uuid4()),))
+
+
+def link_privileges_to_role(conn):
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.execute("SELECT role_id FROM roles "
+                       "WHERE role_name='systemwide-data-curator'")
+        role_id = cursor.fetchone()[0]
+        cursor.executemany("INSERT INTO role_privileges(role_id, privilege_id) "
+                           "VALUES (?, ?)",
+                           tuple((role_id, priv) for priv in
+                                 ("system:system-wide:data:edit",
+                                  "system:system-wide:data:delete")))
+
+
+def unlink_privileges_from_role(conn):
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.execute("SELECT role_id FROM roles "
+                       "WHERE role_name='systemwide-data-curator'")
+        role_id = cursor.fetchone()[0]
+        cursor.executemany("DELETE FROM role_privileges "
+                           "WHERE role_id=? AND privilege_id=?",
+                           tuple((role_id, priv) for priv in
+                                 ("system:system-wide:data:edit",
+                                  "system:system-wide:data:delete")))
+
+
+steps = [
+    step(# Add new privileges
+        """
+        INSERT INTO privileges (privilege_id, privilege_description)
+        VALUES
+          ('system:system-wide:data:edit',
+           'A user with this privilege can edit any data on the entire system.'),
+          ('system:system-wide:data:delete',
+           'A user with this privilege can delete any data from the system.')
+        """,
+        """
+        DELETE FROM privileges WHERE privilege_id IN
+        ('system:system-wide:data:edit', 'system:system-wide:data:delete')"""),
+    step(create_systemwide_data_curator_role,
+         "DELETE FROM roles WHERE role_name='systemwide-data-curator'"),
+    step(link_privileges_to_role, unlink_privileges_from_role)
+]