diff options
Diffstat (limited to 'migrations')
6 files changed, 326 insertions, 0 deletions
diff --git a/migrations/auth/20250722_02_M8TXv-add-system-user-edit-privilege-to-system-admin-role.py b/migrations/auth/20250722_02_M8TXv-add-system-user-edit-privilege-to-system-admin-role.py new file mode 100644 index 0000000..b956bef --- /dev/null +++ b/migrations/auth/20250722_02_M8TXv-add-system-user-edit-privilege-to-system-admin-role.py @@ -0,0 +1,36 @@ +""" +Add 'system:user:edit' privilege to 'system-admin' role. +""" +import contextlib + +from yoyo import step + +__depends__ = {'20250722_01_7Gro7-create-new-system-user-edit-privilege'} + + +def system_administrator_role_id(cursor): + """Fetch ID for role 'system-administrator'.""" + cursor.execute( + "SELECT role_id FROM roles WHERE role_name='system-administrator'") + return cursor.fetchone()[0] + + +def add_system_user_edit_privilege(conn): + """Add the 'system:user:edit' to the 'system-administrator' role.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "INSERT INTO role_privileges(role_id, privilege_id) " + "VALUES(?, ?)", + (system_administrator_role_id(cursor), 'system:user:edit')) + + +def remove_system_user_edit_privilege(conn): + """Remove the 'system:user:edit' from the 'system-administrator' role.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "DELETE FROM role_privileges WHERE role_id=? AND privilege_id=?", + (system_administrator_role_id(cursor), 'system:user:edit')) + +steps = [ + step(add_system_user_edit_privilege, remove_system_user_edit_privilege) +] diff --git a/migrations/auth/20250729_01_CNn2p-create-initial-system-wide-resources-access-privileges.py b/migrations/auth/20250729_01_CNn2p-create-initial-system-wide-resources-access-privileges.py new file mode 100644 index 0000000..be0d022 --- /dev/null +++ b/migrations/auth/20250729_01_CNn2p-create-initial-system-wide-resources-access-privileges.py @@ -0,0 +1,31 @@ +""" +Create initial system-wide resources access privileges +""" + +from yoyo import step + +__depends__ = {'20250722_02_M8TXv-add-system-user-edit-privilege-to-system-admin-role'} + +steps = [ + step( + """ + INSERT INTO privileges(privilege_id, privilege_description) + VALUES + ("system:resource:view", + "View the wrapper resource object (not attached data). This is mostly for administration purposes."), + ("system:resource:edit", + "Edit/update the wrapper resource object (not attached data). This is mostly for administration purposes."), + ("system:resource:delete", + "Delete the wrapper resource object (not attached data). This is mostly for administration purposes."), + ("system:resource:reassign-group", + "Reassign the resource, and its data, to a different user group."), + ("system:resource:assign-owner", + "Assign ownership of any resource to any user.") + """, + """ + DELETE FROM privileges WHERE privilege_id IN + ("system:resource:view", "system:resource:edit", + "system:resource:delete", "system:resource:reassign-group", + "system:resource:assign-owner") + """) +] diff --git a/migrations/auth/20250729_02_7ycSm-assign-initial-system-wide-resources-access-privileges-to-sys-admins.py b/migrations/auth/20250729_02_7ycSm-assign-initial-system-wide-resources-access-privileges-to-sys-admins.py new file mode 100644 index 0000000..e79ab1c --- /dev/null +++ b/migrations/auth/20250729_02_7ycSm-assign-initial-system-wide-resources-access-privileges-to-sys-admins.py @@ -0,0 +1,53 @@ +""" +Assign initial system-wide resources-access privileges to sys-admins. +""" +import contextlib + +from yoyo import step + +def system_administrator_role_id(cursor): + """Fetch ID for role 'system-administrator'.""" + cursor.execute( + "SELECT role_id FROM roles WHERE role_name='system-administrator'") + return cursor.fetchone()[0] + + +def assign_system_wide_resource_access_to_sysadmin(conn): + """ + Assign initial system-wide resources-access privileges to + `system-administrator` role. + """ + with contextlib.closing(conn.cursor()) as cursor: + sysadmin_role_id = system_administrator_role_id(cursor) + cursor.executemany( + "INSERT INTO role_privileges(role_id, privilege_id) " + "VALUES(?, ?)", + ((sysadmin_role_id, "system:resource:view"), + (sysadmin_role_id, "system:resource:edit"), + (sysadmin_role_id, "system:resource:delete"), + (sysadmin_role_id, "system:resource:reassign-group"), + (sysadmin_role_id, "system:resource:assign-owner"))) + + +def revoke_system_wide_resource_access_from_sysadmin(conn): + """ + Revoke initial system-wide resources-access privileges from + `system-administrator` role. + """ + with contextlib.closing(conn.cursor()) as cursor: + sysadmin_role_id = system_administrator_role_id(cursor) + cursor.executemany( + "DELETE FROM role_privileges " + "WHERE role_id=? AND privilege_id=?", + ((sysadmin_role_id, "system:resource:view"), + (sysadmin_role_id, "system:resource:edit"), + (sysadmin_role_id, "system:resource:delete"), + (sysadmin_role_id, "system:resource:reassign-group"), + (sysadmin_role_id, "system:resource:assign-owner"))) + +__depends__ = {'20250729_01_CNn2p-create-initial-system-wide-resources-access-privileges'} + +steps = [ + step(assign_system_wide_resource_access_to_sysadmin, + revoke_system_wide_resource_access_from_sysadmin) +] diff --git a/migrations/auth/20250729_03_oCvvq-grant-role-to-all-resources-to-sys-admin-users.py b/migrations/auth/20250729_03_oCvvq-grant-role-to-all-resources-to-sys-admin-users.py new file mode 100644 index 0000000..e3bdc8f --- /dev/null +++ b/migrations/auth/20250729_03_oCvvq-grant-role-to-all-resources-to-sys-admin-users.py @@ -0,0 +1,75 @@ +""" +Grant role to ALL resources to sys-admin users. +""" +import itertools +import contextlib + +from yoyo import step + +__depends__ = {'20250729_02_7ycSm-assign-initial-system-wide-resources-access-privileges-to-sys-admins'} + + +def system_administrator_role_id(cursor): + """Fetch ID for role 'system-administrator'.""" + cursor.execute( + "SELECT role_id FROM roles WHERE role_name='system-administrator'") + return cursor.fetchone()[0] + + +def system_resource_id(cursor): + cursor.execute( + "SELECT resources.resource_id FROM resource_categories " + "INNER JOIN resources ON resource_categories.resource_category_id=resources.resource_category_id " + "WHERE resource_category_key = 'system'") + return cursor.fetchone()[0] + + +def fetch_ids_for_sysadmin_users(cursor): + """Fetch all sysadmin users' IDs.""" + cursor.execute( + "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=?", + (system_resource_id(cursor),)) + return tuple(row[0] for row in cursor.fetchall()) + + +def fetch_non_system_resources(cursor): + """Fetch IDs for all resources that are not of the 'system' category.""" + cursor.execute( + "SELECT resources.resource_id FROM resource_categories " + "INNER JOIN resources " + "ON resource_categories.resource_category_id=resources.resource_category_id " + "WHERE resource_category_key != 'system'") + return tuple(row[0] for row in cursor.fetchall()) + + +def assign_sysadmin_role_on_non_system_resources(conn): + """Assign sysadmins the sysadmin role on all non-system resources.""" + with contextlib.closing(conn.cursor()) as cursor: + sysadminroleid = system_administrator_role_id(cursor) + cursor.executemany( + "INSERT INTO user_roles(user_id, resource_id, role_id) " + "VALUES (?, ?, ?)", + tuple(item + (sysadminroleid,) + for item in itertools.product( + fetch_ids_for_sysadmin_users(cursor), + fetch_non_system_resources(cursor)))) + + +def revoke_sysadmin_role_on_non_system_resources(conn): + """Revoke sysadmins the sysadmin role on all non-system resources.""" + with contextlib.closing(conn.cursor()) as cursor: + sysadminroleid = system_administrator_role_id(cursor) + cursor.executemany( + "DELETE FROM user_roles " + "WHERE user_id=? AND resource_id=? AND role_id=?", + tuple(item + (sysadminroleid,) + for item in itertools.product( + fetch_ids_for_sysadmin_users(cursor), + fetch_non_system_resources(cursor)))) + +steps = [ + step(assign_sysadmin_role_on_non_system_resources, + revoke_sysadmin_role_on_non_system_resources) +] diff --git a/migrations/auth/20250731_01_Ke1us-add-sysadmin-privileges-for-acting-on-groups-members.py b/migrations/auth/20250731_01_Ke1us-add-sysadmin-privileges-for-acting-on-groups-members.py new file mode 100644 index 0000000..95a6fbb --- /dev/null +++ b/migrations/auth/20250731_01_Ke1us-add-sysadmin-privileges-for-acting-on-groups-members.py @@ -0,0 +1,70 @@ +""" +Add sysadmin privileges for acting on groups: mostly handling user management. +""" +import itertools +import contextlib + +from yoyo import step + +__depends__ = {'20250729_03_oCvvq-grant-role-to-all-resources-to-sys-admin-users'} + + +def system_administrator_role_id(cursor): + """Fetch ID for role 'system-administrator'.""" + cursor.execute( + "SELECT role_id FROM roles WHERE role_name='system-administrator'") + return cursor.fetchone()[0] + + +def add_group_privileges_to_sysadmin_role(conn): + """Add group-management privileges to sysadmin role.""" + with contextlib.closing(conn.cursor()) as cursor: + sysadminroleid = system_administrator_role_id(cursor) + cursor.executemany( + "INSERT INTO role_privileges(role_id, privilege_id) VALUES (?, ?)", + tuple(itertools.product( + (sysadminroleid,), + ('system:group:add-group-member', + 'system:group:remove-group-member', + 'system:group:assign-group-leader', + 'system:group:revoke-group-leader')))) + + +def remove_group_privileges_to_sysadmin_role(conn): + """Remove group-management privileges from sysadmin role.""" + with contextlib.closing(conn.cursor()) as cursor: + sysadminroleid = system_administrator_role_id(cursor) + cursor.executemany( + "DELETE FROM role_privileges WHERE role_id=? AND privilege_id=?", + tuple(itertools.product( + (sysadminroleid,), + ('system:group:add-group-member', + 'system:group:remove-group-member', + 'system:group:assign-group-leader', + 'system:group:revoke-group-leader')))) + + +steps = [ + step( + """ + INSERT INTO privileges(privilege_id, privilege_description) + VALUES + ('system:group:add-group-member', + 'Make an existing user a member of a group.'), + ('system:group:remove-group-member', + 'Remove a member user from a group.'), + ('system:group:assign-group-leader', + 'Assign an existing group member the group-leader role'), + ('system:group:revoke-group-leader', + 'Revoke the group-leader role from a group member with the role.') + """, + """ + DELETE FROM privileges WHERE privilege_id IN + ('system:group:add-group-member', + 'system:group:remove-group-member', + 'system:group:assign-group-leader', + 'system:group:revoke-group-leader') + """), + step(add_group_privileges_to_sysadmin_role, + remove_group_privileges_to_sysadmin_role) +] diff --git a/migrations/auth/20260206_01_v3f4P-add-role-systemwide-data-curator.py b/migrations/auth/20260206_01_v3f4P-add-role-systemwide-data-curator.py new file mode 100644 index 0000000..63e807a --- /dev/null +++ b/migrations/auth/20260206_01_v3f4P-add-role-systemwide-data-curator.py @@ -0,0 +1,61 @@ +""" +add role systemwide-data-curator. +""" +import uuid +import contextlib + +from yoyo import step + +__depends__ = {'20250731_01_Ke1us-add-sysadmin-privileges-for-acting-on-groups-members'} + + +def create_systemwide_data_curator_role(conn): + """Create a new 'systemwide-data-curator' role.""" + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute( + "INSERT INTO roles(role_id, role_name, user_editable) " + "VALUES (?, 'systemwide-data-curator', 0)", + (str(uuid.uuid4()),)) + + +def link_privileges_to_role(conn): + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("SELECT role_id FROM roles " + "WHERE role_name='systemwide-data-curator'") + role_id = cursor.fetchone()[0] + cursor.executemany("INSERT INTO role_privileges(role_id, privilege_id) " + "VALUES (?, ?)", + tuple((role_id, priv) for priv in + ("system:system-wide:data:edit", + "system:system-wide:data:delete"))) + + +def unlink_privileges_from_role(conn): + with contextlib.closing(conn.cursor()) as cursor: + cursor.execute("SELECT role_id FROM roles " + "WHERE role_name='systemwide-data-curator'") + role_id = cursor.fetchone()[0] + cursor.executemany("DELETE FROM role_privileges " + "WHERE role_id=? AND privilege_id=?", + tuple((role_id, priv) for priv in + ("system:system-wide:data:edit", + "system:system-wide:data:delete"))) + + +steps = [ + step(# Add new privileges + """ + INSERT INTO privileges (privilege_id, privilege_description) + VALUES + ('system:system-wide:data:edit', + 'A user with this privilege can edit any data on the entire system.'), + ('system:system-wide:data:delete', + 'A user with this privilege can delete any data from the system.') + """, + """ + DELETE FROM privileges WHERE privilege_id IN + ('system:system-wide:data:edit', 'system:system-wide:data:delete')"""), + step(create_systemwide_data_curator_role, + "DELETE FROM roles WHERE role_name='systemwide-data-curator'"), + step(link_privileges_to_role, unlink_privileges_from_role) +] |