diff options
3 files changed, 259 insertions, 3 deletions
diff --git a/gn_auth/auth/authorisation/resources/models.py b/gn_auth/auth/authorisation/resources/models.py index b8eaacb..c3d0f7e 100644 --- a/gn_auth/auth/authorisation/resources/models.py +++ b/gn_auth/auth/authorisation/resources/models.py @@ -1,4 +1,5 @@ """Handle the management of resources.""" +from datetime import datetime from dataclasses import asdict from uuid import UUID, uuid4 from functools import reduce, partial @@ -46,17 +47,20 @@ def create_resource(# pylint: disable=[too-many-arguments, too-many-positional-a resource_category: ResourceCategory, user: User, group: Group, - public: bool + public: bool, + created_at: datetime = datetime.now() ) -> Resource: """Create a resource item.""" def __create_resource__(cursor: db.DbCursor) -> Resource: resource = Resource(uuid4(), resource_name, resource_category, public) cursor.execute( - "INSERT INTO resources VALUES (?, ?, ?, ?)", + "INSERT INTO resources VALUES (?, ?, ?, ?, ?, ?)", (str(resource.resource_id), resource_name, str(resource.resource_category.resource_category_id), - 1 if resource.public else 0)) + 1 if resource.public else 0, + str(user.user_id), + created_at.timestamp())) # TODO: @fredmanglis,@rookie101 # 1. Move the actions below into a (the?) hooks system # 2. Do more checks: A resource can have varying hooks depending on type diff --git a/migrations/auth/20260331_01_FV1sL-add-privileges-to-role-systemwide-data-curator.py b/migrations/auth/20260331_01_FV1sL-add-privileges-to-role-systemwide-data-curator.py new file mode 100644 index 0000000..22863ae --- /dev/null +++ b/migrations/auth/20260331_01_FV1sL-add-privileges-to-role-systemwide-data-curator.py @@ -0,0 +1,69 @@ +""" +Add privileges to role systemwide-data-curator +""" +import contextlib + +from yoyo import step + +__depends__ = {'20260311_03_vxBCX-restrict-access-to-resources-make-public-feature'} + + +__new_privileges__ = ( + ("system:system-wide:inbredset:view-case-attribute", + "Enable view of any and all inbredset case attributes system-wide."), + ("system:system-wide:inbredset:edit-case-attribute", + "Enable edit of any and all inbredset case attributes system-wide."), + ("system:system-wide:inbredset:delete-case-attribute", + "Enable deletion of any and all inbredset case attributes system-wide."), + ("system:system-wide:inbredset:apply-case-attribute-edit", + "Enable applying changes to any and all inbredset case attributes system-wide."), + ("system:system-wide:inbredset:reject-case-attribute-edit", + "Enable rejecting changes to any and all inbredset case attributes system-wide.")) + + +def fetch_systemwide_data_curator_role_id(cursor): + "Fetch the role's ID." + cursor.execute("SELECT role_id FROM roles " + "WHERE role_name='systemwide-data-curator'") + return cursor.fetchone()[0] + + +def create_new_privileges(conn): + """Create new privileges for the system.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.executemany( + "INSERT INTO privileges(privilege_id, privilege_description) " + "VALUES (?, ?)", + __new_privileges__) + + +def delete_new_privileges(conn): + """Delete these new privileges from the system.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.executemany("DELETE FROM privileges WHERE privilege_id=?", + tuple((priv[0],) for priv in __new_privileges__)) + + +def assign_new_privileges(conn): + """Assign the new privileges to the `systemwide-data-curator` role.""" + with contextlib.closing(conn.cursor()) as cursor: + role_id = fetch_systemwide_data_curator_role_id(cursor) + cursor.executemany( + "INSERT INTO role_privileges(role_id, privilege_id) VALUES (?, ?)", + tuple((role_id, privilege[0]) for privilege in __new_privileges__)) + + +def revoke_new_privileges(conn): + """Revoke the new privileges from the `systemwide-data-curator` role.""" + with contextlib.closing(conn.cursor()) as cursor: + role_id = fetch_systemwide_data_curator_role_id(cursor) + cursor.executemany( + "DELETE FROM role_privileges WHERE role_id=? AND privilege_id=?", + tuple((role_id, privilege[0]) for privilege in __new_privileges__)) + + + +steps = [ + step(create_new_privileges, delete_new_privileges), + step(assign_new_privileges, revoke_new_privileges) +] diff --git a/migrations/auth/20260402_01_Bf8nm-add-user-and-time-tracking-to-resources-table.py b/migrations/auth/20260402_01_Bf8nm-add-user-and-time-tracking-to-resources-table.py new file mode 100644 index 0000000..fd1e617 --- /dev/null +++ b/migrations/auth/20260402_01_Bf8nm-add-user-and-time-tracking-to-resources-table.py @@ -0,0 +1,183 @@ +""" +Add user and time tracking to resources table +""" +import random +import contextlib +from datetime import datetime + +from yoyo import step + +__depends__ = {'20260331_01_FV1sL-add-privileges-to-role-systemwide-data-curator'} + +GN_AUTH_INIT_TIMESTAMP = 1691130509.0 +__admin_id__ = "" + + +def fetch_acentenos_id(conn): + """Fetch the default resource creator.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("SELECT user_id FROM users WHERE email=?", + (("acent" "eno@" "uthsc" "." "edu"),)) + res = cursor.fetchone() + return res[0] if bool(res) else None + + +def fetch_a_sysadmin_id(conn, resources_table): + """Fetch one ID out of all system administrator users.""" + global __admin_id__ + + def __fetch__(): + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + f"SELECT ur.user_id FROM {resources_table} AS rsc " + "INNER JOIN user_roles AS ur ON rsc.resource_id=ur.resource_id " + "INNER JOIN roles AS r ON ur.role_id=r.role_id " + "WHERE resource_name='GeneNetwork System' " + "AND r.role_name='system-administrator'" + ) + return tuple(row[0] for row in cursor.fetchall()) + + if not bool(__admin_id__): + __admin_id__ = random.choice(__fetch__()) + + return __admin_id__ + + +def add_user_and_time_tracking_columns(conn): + """Add user and time tracking columns.""" + conn.execute( + """ + CREATE TABLE resources_new( + resource_id TEXT NOT NULL, + resource_name TEXT NOT NULL UNIQUE, + resource_category_id TEXT NOT NULL, + public INTEGER NOT NULL DEFAULT 0 CHECK (public=0 or public=1), + created_by TEXT NOT NULL, + created_at REAL NOT NULL DEFAULT '1691130509.0', + PRIMARY KEY(resource_id), + FOREIGN KEY(resource_category_id) + REFERENCES resource_categories(resource_category_id) + ON UPDATE CASCADE ON DELETE RESTRICT, + FOREIGN KEY(created_by) + REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + + +def drop_user_and_time_tracking_columns(conn): + """Drop user and time tracking columns.""" + conn.execute("PRAGMA foreign_keys = OFF") + conn.execute("DROP TABLE IF EXISTS resources") + conn.execute("ALTER TABLE resources_old RENAME TO resources") + conn.execute("PRAGMA foreign_key_check") + conn.execute("PRAGMA foreign_keys = ON") + + +def update_data_for_new_resources_table(conn): + """Add creator and time to original data.""" + __creator__ = ( + fetch_acentenos_id(conn) or fetch_a_sysadmin_id(conn, "resources")) + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("SELECT * FROM resources") + cursor.executemany( + "INSERT INTO resources_new(" + " resource_id," + " resource_name," + " resource_category_id," + " public," + " created_by," + " created_at" + ") VALUES (?, ?, ?, ?, ?, ?)", + tuple( + tuple(row) + (__creator__, GN_AUTH_INIT_TIMESTAMP) + for row in cursor.fetchall())) + + +def restore_data_for_old_resources_table(conn): + """Remove creator and time from data.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("SELECT * FROM resources") + cursor.executemany( + "INSERT INTO resources_old(" + " resource_id," + " resource_name," + " resource_category_id," + " public" + ") VALUES (?, ?, ?, ?)", + tuple(tuple(row)[0:4] for row in cursor.fetchall())) + + +def replace_old_table_with_new_table(conn): + """Restore old resources table with the new resources table.""" + conn.execute("PRAGMA foreign_keys = OFF") + conn.execute("DROP TABLE resources") + conn.execute("ALTER TABLE resources_new RENAME TO resources") + conn.execute("PRAGMA foreign_key_check") + conn.execute("PRAGMA foreign_keys = ON") + + +def restore_old_table(conn): + """Restore old 'resources' table schema.""" + conn.execute( + """ + CREATE TABLE resources_old( + resource_id TEXT NOT NULL, + resource_name TEXT NOT NULL UNIQUE, + resource_category_id TEXT NOT NULL, + public INTEGER NOT NULL DEFAULT 0 CHECK (public=0 or public=1), + PRIMARY KEY(resource_id), + FOREIGN KEY(resource_category_id) + REFERENCES resource_categories(resource_category_id) + ON UPDATE CASCADE ON DELETE RESTRICT + ) WITHOUT ROWID + """) + + +def parse_creator_and_time(cursor, row): + __return__ = None + + __name_parts__ = row[1].split("—") + if len(__name_parts__) == 4: + __email__, __inbredsetname__, __datetimestr__, count = __name_parts__ + cursor.execute("SELECT user_id FROM users WHERE email=?", + (__email__.strip(),)) + results = cursor.fetchone() + if bool(results): + __return__ = { + "resource_id": row[0], + "creator": results[0], + "created": datetime.fromisoformat(__datetimestr__).timestamp() + } + + return __return__ + + +def update_creators_and_time(conn): + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("SELECT resource_id, resource_name FROM resources") + cursor.executemany( + "UPDATE resources SET created_by=:creator, created_at=:created " + "WHERE resource_id=:resource_id", + tuple(item for item in + (parse_creator_and_time(cursor, row) + for row in cursor.fetchall()) + if item is not None)) + + + +def restore_default_creators_and_time(conn): + with contextlib.closing(conn.cursor()) as cursor: + __creator__ = ( + fetch_acentenos_id(conn) or fetch_a_sysadmin_id(conn, "resources")) + cursor.execute("UPDATE resources SET created_by=?, created_at=?", + (__creator__, GN_AUTH_INIT_TIMESTAMP)) + + +steps = [ + step(add_user_and_time_tracking_columns, + drop_user_and_time_tracking_columns), + step(update_data_for_new_resources_table, + restore_data_for_old_resources_table), + step(replace_old_table_with_new_table, restore_old_table), + step(update_creators_and_time, restore_default_creators_and_time) +] |