1 files changed, 53 insertions, 0 deletions
diff --git a/migrations/auth/20250729_02_7ycSm-assign-initial-system-wide-resources-access-privileges-to-sys-admins.py b/migrations/auth/20250729_02_7ycSm-assign-initial-system-wide-resources-access-privileges-to-sys-admins.py
new file mode 100644
index 0000000..e79ab1c
--- /dev/null
+++ b/migrations/auth/20250729_02_7ycSm-assign-initial-system-wide-resources-access-privileges-to-sys-admins.py
@@ -0,0 +1,53 @@
+"""
+Assign initial system-wide resources-access privileges to sys-admins.
+"""
+import contextlib
+
+from yoyo import step
+
+def system_administrator_role_id(cursor):
+    """Fetch ID for role 'system-administrator'."""
+    cursor.execute(
+        "SELECT role_id FROM roles WHERE role_name='system-administrator'")
+    return cursor.fetchone()[0]
+
+
+def assign_system_wide_resource_access_to_sysadmin(conn):
+    """
+    Assign initial system-wide resources-access privileges to
+    `system-administrator` role.
+    """
+    with contextlib.closing(conn.cursor()) as cursor:
+        sysadmin_role_id = system_administrator_role_id(cursor)
+        cursor.executemany(
+            "INSERT INTO role_privileges(role_id, privilege_id) "
+            "VALUES(?, ?)",
+            ((sysadmin_role_id, "system:resource:view"),
+             (sysadmin_role_id, "system:resource:edit"),
+             (sysadmin_role_id, "system:resource:delete"),
+             (sysadmin_role_id, "system:resource:reassign-group"),
+             (sysadmin_role_id, "system:resource:assign-owner")))
+
+
+def revoke_system_wide_resource_access_from_sysadmin(conn):
+    """
+    Revoke initial system-wide resources-access privileges from
+    `system-administrator` role.
+    """
+    with contextlib.closing(conn.cursor()) as cursor:
+        sysadmin_role_id = system_administrator_role_id(cursor)
+        cursor.executemany(
+            "DELETE FROM role_privileges "
+            "WHERE role_id=? AND privilege_id=?",
+            ((sysadmin_role_id, "system:resource:view"),
+             (sysadmin_role_id, "system:resource:edit"),
+             (sysadmin_role_id, "system:resource:delete"),
+             (sysadmin_role_id, "system:resource:reassign-group"),
+             (sysadmin_role_id, "system:resource:assign-owner")))
+
+__depends__ = {'20250729_01_CNn2p-create-initial-system-wide-resources-access-privileges'}
+
+steps = [
+    step(assign_system_wide_resource_access_to_sysadmin,
+         revoke_system_wide_resource_access_from_sysadmin)
+]