1 files changed, 26 insertions, 2 deletions
diff --git a/gn_auth/auth/authorisation/resources/common.py b/gn_auth/auth/authorisation/resources/common.py
index 5d2b72b..fd358f1 100644
--- a/gn_auth/auth/authorisation/resources/common.py
+++ b/gn_auth/auth/authorisation/resources/common.py
@@ -1,10 +1,10 @@
 """Utilities common to more than one resource."""
 import uuid
 
-from sqlite3 import Cursor
+from gn_auth.auth.db import sqlite3 as db
 
 def assign_resource_owner_role(
-        cursor: Cursor,
+        cursor: db.DbCursor,
         resource_id: uuid.UUID,
         user_id: uuid.UUID
 ) -> dict:
@@ -22,3 +22,27 @@ def assign_resource_owner_role(
         "ON CONFLICT (user_id, role_id, resource_id) DO NOTHING",
         params)
     return params
+
+
+def grant_access_to_sysadmins(
+        cursor: db.DbCursor,
+        resource_id: uuid.UUID,
+        system_resource_id: uuid.UUID
+):
+    """Grant sysadmins access to resource identified by `resource_id`."""
+    cursor.execute(
+        "SELECT role_id FROM roles WHERE role_name='system-administrator'")
+    sysadminroleid = cursor.fetchone()[0]
+
+    cursor.execute(# Fetch sysadmin IDs.
+        "SELECT user_roles.user_id FROM roles INNER JOIN user_roles "
+        "ON roles.role_id=user_roles.role_id "
+        "WHERE role_name='system-administrator' AND resource_id=?",
+        (str(system_resource_id),))
+
+    cursor.executemany(
+        "INSERT INTO user_roles(user_id, role_id, resource_id) "
+        "VALUES (?, ?, ?) "
+        "ON CONFLICT (user_id, role_id, resource_id) DO NOTHING",
+        tuple((row["user_id"], sysadminroleid, str(resource_id))
+              for row in cursor.fetchall()))