Assign 'systemwide-docs-editor' role to sysadmins.

1 files changed, 66 insertions, 0 deletions

diff --git a/migrations/auth/20260311_02_v3EFQ-assign-systemwide-docs-editor-role-to-sysadmins.py b/migrations/auth/20260311_02_v3EFQ-assign-systemwide-docs-editor-role-to-sysadmins.py
new file mode 100644
index 0000000..e79ef6a
--- /dev/null
+++ b/migrations/auth/20260311_02_v3EFQ-assign-systemwide-docs-editor-role-to-sysadmins.py
@@ -0,0 +1,66 @@
+"""
+Assign 'systemwide-docs-editor' role to sysadmins
+"""
+import uuid
+import contextlib
+
+from yoyo import step
+
+__depends__ = {'20260311_01_TfRlV-add-privilege-for-gn-docs-documentation-editing'}
+
+
+def fetch_docs_editor_role_id(cursor):
+    """Fetch ID of systemwide-docs-editor role"""
+    cursor.execute(
+        "SELECT role_id FROM roles WHERE role_name='systemwide-docs-editor'")
+    return cursor.fetchone()[0]
+
+
+def fetch_sys_resource_id(cursor):
+    """Fetch the resource ID of the system."""
+    cursor.execute("SELECT resource_id FROM resources "
+                   "WHERE resource_name='GeneNetwork System'")
+    return cursor.fetchone()[0]
+
+
+def fetch_sys_admin_ids(cursor):
+    """Fetch the sysadmins' IDs."""
+    cursor.execute(
+        "SELECT user_roles.user_id FROM resources INNER JOIN user_roles "
+        "ON resources.resource_id=user_roles.resource_id INNER JOIN roles "
+        "ON user_roles.role_id=roles.role_id "
+        "WHERE resources.resource_name='GeneNetwork System' "
+        "AND roles.role_name='system-administrator'")
+    return tuple(row[0] for row in cursor.fetchall())
+
+
+def __build_params__(cursor):
+    sysresourceid = fetch_sys_resource_id(cursor)
+    sysadminids = fetch_sys_admin_ids(cursor)
+    roleid = fetch_docs_editor_role_id(cursor)
+    return tuple({
+        "user_id": userid,
+        "role_id": roleid,
+        "resource_id": sysresourceid
+    } for userid in sysadminids)
+
+
+def assign_systemwide_docs_editor_role_to_sysadmins(conn):
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.executemany(
+            "INSERT INTO user_roles(user_id, role_id, resource_id) "
+            "VALUES(:user_id, :role_id, :resource_id)",
+            __build_params__(cursor))
+
+
+def revoke_systemwide_docs_editor_role_from_sysadmins(conn):
+    with contextlib.closing(conn.cursor()) as cursor:
+        cursor.executemany(
+            "DELETE FROM user_roles WHERE user_id=:user_id "
+            "AND role_id=:role_id AND resource_id=:resource_id",
+            __build_params__(cursor))
+
+steps = [
+    step(assign_systemwide_docs_editor_role_to_sysadmins,
+         revoke_systemwide_docs_editor_role_from_sysadmins)
+]