forked from fa/breadcrumb-the-shire
82 lines
3.0 KiB
SQL
82 lines
3.0 KiB
SQL
-- ============================================================
|
|
-- 2026-03-13 Seed default roles: MANAGER, AUDITOR, SUPPORT
|
|
-- Idempotent — safe to run multiple times.
|
|
-- ============================================================
|
|
|
|
-- 1. Create new roles (skip if already present)
|
|
INSERT INTO `roles` (`uuid`, `description`, `code`, `active`, `created`)
|
|
SELECT UUID(), 'Manager', 'MANAGER', 1, NOW()
|
|
WHERE NOT EXISTS (SELECT 1 FROM roles WHERE code = 'MANAGER');
|
|
|
|
INSERT INTO `roles` (`uuid`, `description`, `code`, `active`, `created`)
|
|
SELECT UUID(), 'Auditor', 'AUDITOR', 1, NOW()
|
|
WHERE NOT EXISTS (SELECT 1 FROM roles WHERE code = 'AUDITOR');
|
|
|
|
INSERT INTO `roles` (`uuid`, `description`, `code`, `active`, `created`)
|
|
SELECT UUID(), 'Support', 'SUPPORT', 1, NOW()
|
|
WHERE NOT EXISTS (SELECT 1 FROM roles WHERE code = 'SUPPORT');
|
|
|
|
-- 2. MANAGER permissions (15)
|
|
INSERT INTO `role_permissions` (`role_id`, `permission_id`, `created`)
|
|
SELECT r.id, p.id, NOW()
|
|
FROM roles r
|
|
JOIN permissions p ON p.`key` IN (
|
|
'users.view', 'users.view_meta', 'users.create', 'users.update',
|
|
'users.update_assignments', 'users.access_pdf', 'users.self_update',
|
|
'users.import', 'users.import_assignments',
|
|
'address_book.view',
|
|
'departments.view', 'departments.create', 'departments.update',
|
|
'roles.view',
|
|
'custom_fields.edit_values'
|
|
)
|
|
WHERE r.code = 'MANAGER'
|
|
ON DUPLICATE KEY UPDATE role_id = role_id;
|
|
|
|
-- 3. AUDITOR permissions (18)
|
|
INSERT INTO `role_permissions` (`role_id`, `permission_id`, `created`)
|
|
SELECT r.id, p.id, NOW()
|
|
FROM roles r
|
|
JOIN permissions p ON p.`key` IN (
|
|
'users.view', 'users.view_meta', 'users.view_audit', 'users.self_update',
|
|
'address_book.view',
|
|
'tenants.view', 'departments.view', 'roles.view', 'permissions.view',
|
|
'settings.view',
|
|
'imports.view', 'imports.audit.view',
|
|
'user_lifecycle_audit.view',
|
|
'mail_log.view', 'api_audit.view', 'system_audit.view',
|
|
'stats.view', 'jobs.view'
|
|
)
|
|
WHERE r.code = 'AUDITOR'
|
|
ON DUPLICATE KEY UPDATE role_id = role_id;
|
|
|
|
-- 4. SUPPORT permissions (10)
|
|
INSERT INTO `role_permissions` (`role_id`, `permission_id`, `created`)
|
|
SELECT r.id, p.id, NOW()
|
|
FROM roles r
|
|
JOIN permissions p ON p.`key` IN (
|
|
'users.view', 'users.view_meta', 'users.view_audit',
|
|
'users.update', 'users.self_update',
|
|
'address_book.view',
|
|
'departments.view', 'roles.view',
|
|
'api_tokens.manage',
|
|
'custom_fields.edit_values'
|
|
)
|
|
WHERE r.code = 'SUPPORT'
|
|
ON DUPLICATE KEY UPDATE role_id = role_id;
|
|
|
|
-- 5. MANAGER can assign USER and SUPPORT roles
|
|
INSERT IGNORE INTO `role_assignable_roles` (`role_id`, `assignable_role_id`)
|
|
SELECT mgr.id, target.id
|
|
FROM roles mgr
|
|
JOIN roles target ON target.code IN ('USER', 'SUPPORT')
|
|
WHERE mgr.code = 'MANAGER';
|
|
|
|
-- 6. Ensure ADMIN assignable_roles includes the new roles
|
|
-- (previous migration only seeded roles that existed at that time)
|
|
INSERT IGNORE INTO `role_assignable_roles` (`role_id`, `assignable_role_id`)
|
|
SELECT admin_role.id, new_role.id
|
|
FROM roles admin_role
|
|
CROSS JOIN roles new_role
|
|
WHERE (admin_role.description IN ('Admin', 'Administrator') OR admin_role.id = 1)
|
|
AND new_role.code IN ('MANAGER', 'AUDITOR', 'SUPPORT');
|