From 9f4e9db223b4e2c052756208ecf035044db0451d Mon Sep 17 00:00:00 2001 From: Frederick Muriuki Muriithi Date: Tue, 26 Sep 2023 02:36:37 +0300 Subject: Add `public-view` role. Assign it to users. Add a new `public-view` role to be assigned to all users on all resources that are defined as publicly viewable. Update code to make assign `public-view` role to a newly registered user for all publicly viewable roles. Update the code to assign/revoke the `public-view` role to/from users whenever the resource is toggled to and from being publicly viewable. Ensure that `public-view` is not revoked from system-administrators. Ensure that `public-view` is not revoked from the group administrators of the group that owns the resource. --- gn_auth/auth/authorisation/resources/views.py | 76 +++++++++++++++++++--- gn_auth/auth/authorisation/roles/models.py | 38 ++++++++--- .../20230925_01_TWJuR-add-new-public-view-role.py | 61 +++++++++++++++++ 3 files changed, 159 insertions(+), 16 deletions(-) create mode 100644 migrations/auth/20230925_01_TWJuR-add-new-public-view-role.py diff --git a/gn_auth/auth/authorisation/resources/views.py b/gn_auth/auth/authorisation/resources/views.py index 0e44df5..f609303 100644 --- a/gn_auth/auth/authorisation/resources/views.py +++ b/gn_auth/auth/authorisation/resources/views.py @@ -18,9 +18,9 @@ from gn_auth.auth.authentication.users import User, user_by_id, user_by_email from .checks import authorised_for from .models import ( - Resource, save_resource, resource_data, resource_by_id, - resource_categories, assign_resource_user, link_data_to_resource, - unassign_resource_user, resource_category_by_id, unlink_data_from_resource, + Resource, resource_data, resource_by_id, resource_categories, + assign_resource_user, link_data_to_resource, unassign_resource_user, + resource_category_by_id, unlink_data_from_resource, create_resource as _create_resource) from .groups.models import Group, GroupRole, resource_owner, group_role_by_id @@ -253,6 +253,54 @@ def unassign_role_to_user(resource_id: uuid.UUID) -> Response: return jsonify(with_db_connection(__assign__)) +def __public_view_params__(cursor, user_id, resource_id): + ignore = (str(user_id),) + # sys admins + cursor.execute( + "SELECT ur.user_id FROM user_roles AS ur INNER JOIN roles AS r " + "ON ur.role_id=r.role_id WHERE r.role_name='system-administrator'") + ignore = ignore + tuple( + row["user_id"] for row in cursor.fetchall()) + # group admins + cursor.execute( + "SELECT DISTINCT gu.user_id FROM resource_ownership AS ro " + "INNER JOIN groups AS g ON ro.group_id=g.group_id " + "INNER JOIN group_users AS gu ON g.group_id=gu.group_id " + "INNER JOIN user_roles AS ur ON gu.user_id=ur.user_id " + "INNER JOIN roles AS r ON ur.role_id=r.role_id " + "WHERE ro.resource_id=? AND r.role_name='group-leader'", + (str(resource_id),)) + ignore = tuple(set( + ignore + tuple(row["user_id"] for row in cursor.fetchall()))) + + cursor.execute( + "SELECT user_id FROM users WHERE user_id NOT IN " + f"({', '.join(['?'] * len(ignore))})", + ignore) + user_ids = tuple(row["user_id"] for row in cursor.fetchall()) + cursor.execute( + "SELECT role_id FROM roles WHERE role_name='public-view'") + role_id = cursor.fetchone()["role_id"] + return tuple({ + "user_id": user_id, + "role_id": role_id, + "resource_id": str(resource_id) + } for user_id in user_ids) + +def __assign_revoke_public_view__(cursor, user_id, resource_id, public): + if public: + cursor.executemany( + "INSERT INTO user_roles(user_id, role_id, resource_id) " + "VALUES(:user_id, :role_id, :resource_id) " + "ON CONFLICT (user_id, role_id, resource_id) " + "DO NOTHING", + __public_view_params__(cursor, user_id, resource_id)) + return + cursor.executemany( + "DELETE FROM user_roles WHERE user_id=:user_id " + "AND role_id=:role_id AND resource_id=:resource_id", + __public_view_params__(cursor, user_id, resource_id)) + @resources.route("/toggle-public", methods=["POST"]) @require_oauth("profile group resource role") def toggle_public(resource_id: uuid.UUID) -> Response: @@ -260,11 +308,23 @@ def toggle_public(resource_id: uuid.UUID) -> Response: with require_oauth.acquire("profile group resource") as the_token: def __toggle__(conn: db.DbConnection) -> Resource: old_rsc = resource_by_id(conn, the_token.user, resource_id) - return save_resource( - conn, the_token.user, Resource( - old_rsc.resource_id, old_rsc.resource_name, - old_rsc.resource_category, not old_rsc.public, - old_rsc.resource_data)) + public = not old_rsc.public + new_resource = Resource( + old_rsc.resource_id, old_rsc.resource_name, + old_rsc.resource_category, public, + old_rsc.resource_data) + with db.cursor(conn) as cursor: + cursor.execute( + "UPDATE resources SET public=:public " + "WHERE resource_id=:resource_id", + { + "public": 1 if public else 0, + "resource_id": str(resource_id) + }) + __assign_revoke_public_view__( + cursor, the_token.user.user_id, resource_id, public) + return new_resource + return new_resource resource = with_db_connection(__toggle__) return jsonify({ diff --git a/gn_auth/auth/authorisation/roles/models.py b/gn_auth/auth/authorisation/roles/models.py index 4281446..7d78eac 100644 --- a/gn_auth/auth/authorisation/roles/models.py +++ b/gn_auth/auth/authorisation/roles/models.py @@ -133,18 +133,40 @@ def user_role(conn: db.DbConnection, user: User, role_id: UUID) -> Either: return Left(NotFoundError( f"Could not find role with id '{role_id}'",)) -def assign_default_roles(cursor: db.DbCursor, user: User): - """Assign `user` some default roles.""" +def __assign_group_creator_role__(cursor: db.DbCursor, user: User): cursor.execute( 'SELECT role_id FROM roles WHERE role_name IN ' '("group-creator")') - role_ids = cursor.fetchall() - str_user_id = str(user.user_id) - params = tuple( - {"user_id": str_user_id, "role_id": row["role_id"]} for row in role_ids) + role_id = cursor.fetchone()["role_id"] + cursor.execute( + "SELECT resource_id FROM resources AS r " + "INNER JOIN resource_categories AS rc " + "ON r.resource_category_id=rc.resource_category_id " + "WHERE rc.resource_category_key='system'") + resource_id = cursor.fetchone()["resource_id"] + cursor.execute( + ("INSERT INTO user_roles VALUES (:user_id, :role_id, :resource_id)"), + {"user_id": str(user.user_id), "role_id": role_id, + "resource_id": resource_id}) + +def __assign_public_view_role__(cursor: db.DbCursor, user: User): + cursor.execute("SELECT resource_id FROM resources WHERE public=1") + public_resources = tuple(row["resource_id"] for row in cursor.fetchall()) + cursor.execute("SELECT role_id FROM roles WHERE role_name='public-view'") + role_id = cursor.fetchone()["role_id"] cursor.executemany( - ("INSERT INTO user_roles VALUES (:user_id, :role_id)"), - params) + "INSERT INTO user_roles(user_id, role_id, resource_id " + "VALUES(:user_id, :role_id, :resource_id)", + tuple({ + "user_id": str(user.user_id), + "role_id": role_id, + "resource_id": resource_id + } for resource_id in public_resources)) + +def assign_default_roles(cursor: db.DbCursor, user: User): + """Assign `user` some default roles.""" + __assign_group_creator_role__(cursor, user) + __assign_public_view_role__(cursor, user) def revoke_user_role_by_name(cursor: db.DbCursor, user: User, role_name: str): """Revoke a role from `user` by the role's name""" diff --git a/migrations/auth/20230925_01_TWJuR-add-new-public-view-role.py b/migrations/auth/20230925_01_TWJuR-add-new-public-view-role.py new file mode 100644 index 0000000..1172034 --- /dev/null +++ b/migrations/auth/20230925_01_TWJuR-add-new-public-view-role.py @@ -0,0 +1,61 @@ +""" +Add new "public-view" role +""" + +import sqlite3 + +from yoyo import step + +__depends__ = {'20230912_02_hFmSn-drop-group-id-and-fix-foreign-key-references-on-group-user-roles-on-resources-table'} + +def grant_to_all_users_public_view_role(conn): + """Grant the `public-view` role to all existing users.""" + conn.row_factory = sqlite3.Row + conn.execute("PRAGMA foreign_keys = ON") + cursor = conn.cursor() + cursor.execute("SELECT user_id FROM users") + user_ids = tuple(row["user_id"] for row in cursor.fetchall()) + + cursor.execute("SELECT resource_id FROM resources WHERE public=1") + resource_ids = tuple(row["resource_id"] for row in cursor.fetchall()) + + params = tuple({ + "user_id": user_id, + "resource_id": resource_id, + "role_id": "fd88bfed-d869-4969-87f2-67c4e8446ecb" + } for user_id in user_ids for resource_id in resource_ids) + cursor.executemany( + "INSERT INTO user_roles(user_id, role_id, resource_id) " + "VALUES (:user_id, :role_id, :resource_id) ", + params) + +def revoke_from_all_users_public_view_role(conn): + """Revoke the `public-view` role from all existing users.""" + conn.execute("PRAGMA foreign_keys = ON") + conn.execute( + "DELETE FROM user_roles " + "WHERE role_id='fd88bfed-d869-4969-87f2-67c4e8446ecb'") + +steps = [ + step( + """ + INSERT INTO roles(role_id, role_name, user_editable) + VALUES('fd88bfed-d869-4969-87f2-67c4e8446ecb', 'public-view', 0) + """, + """ + DELETE FROM roles WHERE role_id='fd88bfed-d869-4969-87f2-67c4e8446ecb' + """), + step( + """ + INSERT INTO role_privileges(role_id, privilege_id) + VALUES( + 'fd88bfed-d869-4969-87f2-67c4e8446ecb', + 'group:resource:view-resource') + """, + """ + DELETE FROM role_privileges + WHERE role_id='fd88bfed-d869-4969-87f2-67c4e8446ecb' + """), + step(grant_to_all_users_public_view_role, + revoke_from_all_users_public_view_role) +] -- cgit v1.2.3