Recipes
Separation of Duty
Conflicting roles and role cardinality limits.
Situation. Regulation at Valmont: whoever writes a prescription must not
be the one who fills it. Also, the group tolerates at most two auditor
accounts at a time.
Assumes the shared Valmont setup.
Declare the conflict
INSERT INTO morbac.sod_conflicts (role_a_id, role_b_id, org_id, description)
SELECT p.id, ph.id, o.id, 'Prescriber and dispenser must differ'
FROM morbac.orgs o
JOIN morbac.roles p ON p.org_id = o.id AND p.name = 'physician'
JOIN morbac.roles ph ON ph.org_id = o.id AND ph.name = 'pharmacist'
WHERE o.name = 'Clinique Valmont Nord'
ON CONFLICT DO NOTHING;Enforcement happens at assignment
-- Sylvie Marchand is a pharmacist: fine
SELECT morbac.assign_role(
'33333333-3333-4333-8333-333333333333',
(SELECT r.id FROM morbac.roles r JOIN morbac.orgs o ON o.id = r.org_id
WHERE o.name = 'Clinique Valmont Nord' AND r.name = 'pharmacist'),
(SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Nord'));
-- now also make her a physician: rejected
SELECT morbac.assign_role(
'33333333-3333-4333-8333-333333333333',
(SELECT r.id FROM morbac.roles r JOIN morbac.orgs o ON o.id = r.org_id
WHERE o.name = 'Clinique Valmont Nord' AND r.name = 'physician'),
(SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Nord'));
-- ERROR: SoD violationUse
morbac.assign_role(), not a raw INSERT intouser_roles. The constraint checks live in the function; a direct insert bypasses them.
Cardinality: at most two auditors
INSERT INTO morbac.role_cardinality (role_id, min_users, max_users, description)
SELECT r.id, NULL, 2, 'At most two auditors at group level'
FROM morbac.roles r JOIN morbac.orgs o ON o.id = r.org_id
WHERE o.name = 'Groupe Hospitalier Valmont' AND r.name = 'auditor'
ON CONFLICT (role_id) DO UPDATE SET max_users = EXCLUDED.max_users;Assign Bertrand Delacroix and one colleague and the role is full; the third
assign_role call raises.
SELECT * FROM morbac.check_cardinality_violation(
(SELECT r.id FROM morbac.roles r JOIN morbac.orgs o ON o.id = r.org_id
WHERE o.name = 'Groupe Hospitalier Valmont' AND r.name = 'auditor'),
FALSE);Auditing an existing database
SoD is enforced going forward, not retroactively. Before declaring a conflict, find users who already hold both roles:
SELECT ur1.user_id
FROM morbac.user_roles ur1
JOIN morbac.user_roles ur2
ON ur2.user_id = ur1.user_id AND ur2.org_id = ur1.org_id
JOIN morbac.roles r1 ON r1.id = ur1.role_id AND r1.name = 'physician'
JOIN morbac.roles r2 ON r2.id = ur2.role_id AND r2.name = 'pharmacist';Resolve those before adding the conflict row, otherwise the violation stays invisible until someone touches the assignment.
Related tools
- A negative role assignment bans one specific user from one role, regardless of conflicts.
- A prohibition rule blocks an action rather than a role, which is the right tool when the person may keep the job title but not the capability.