1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
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)
]
|
