Update query

Replace `group_user_roles_on_resources` table with `user_roles` for
the query that checks whether the user has appropriate permissions to
act on a specific resource.

1 files changed, 5 insertions, 7 deletions

diff --git a/gn_auth/auth/authorisation/resources/checks.py b/gn_auth/auth/authorisation/resources/checks.py
index db975de..717e5e4 100644
--- a/gn_auth/auth/authorisation/resources/checks.py
+++ b/gn_auth/auth/authorisation/resources/checks.py
@@ -24,14 +24,12 @@ def authorised_for(conn: db.DbConnection, user: User, privileges: tuple[str],
     """
     with db.cursor(conn) as cursor:
         cursor.execute(
-            ("SELECT guror.*, rp.privilege_id FROM "
-             "group_user_roles_on_resources AS guror "
-             "INNER JOIN group_roles AS gr ON  "
-             "(guror.group_id=gr.group_id AND guror.role_id=gr.role_id) "
-             "INNER JOIN roles AS r ON gr.role_id=r.role_id "
+            ("SELECT ur.*, rp.privilege_id FROM "
+             "user_roles AS ur "
+             "INNER JOIN roles AS r ON ur.role_id=r.role_id "
              "INNER JOIN role_privileges AS rp ON r.role_id=rp.role_id "
-             "WHERE guror.user_id=? "
-             f"AND guror.resource_id IN ({', '.join(['?']*len(resource_ids))})"
+             "WHERE ur.user_id=? "
+             f"AND ur.resource_id IN ({', '.join(['?']*len(resource_ids))})"
              f"AND rp.privilege_id IN ({', '.join(['?']*len(privileges))})"),
             ((str(user.user_id),) + tuple(
                 str(r_id) for r_id in resource_ids) + tuple(privileges)))