forked from fa/breadcrumb-the-shire
32 lines
1.4 KiB
SQL
32 lines
1.4 KiB
SQL
-- Idempotent update: introduce assignable-roles mapping for privilege-escalation prevention.
|
|
-- Each role defines which other roles its members may assign to users.
|
|
|
|
CREATE TABLE IF NOT EXISTS `role_assignable_roles` (
|
|
`role_id` INT UNSIGNED NOT NULL,
|
|
`assignable_role_id` INT UNSIGNED NOT NULL,
|
|
`created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`role_id`, `assignable_role_id`),
|
|
KEY `idx_rar_assignable` (`assignable_role_id`),
|
|
CONSTRAINT `fk_rar_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
|
|
CONSTRAINT `fk_rar_assignable` FOREIGN KEY (`assignable_role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Permission: bypass assignable-roles check (super-admin)
|
|
INSERT INTO `permissions` (`key`, `description`, `active`, `is_system`)
|
|
VALUES ('roles.assign_all', 'permission.roles.assign_all', 1, 1)
|
|
ON DUPLICATE KEY UPDATE `key` = `key`;
|
|
|
|
-- Grant roles.assign_all to the ADMIN role
|
|
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
|
|
SELECT r.id, p.id
|
|
FROM roles r
|
|
JOIN permissions p ON p.`key` = 'roles.assign_all'
|
|
WHERE r.code = 'ADMIN';
|
|
|
|
-- Seed: ADMIN role can assign every existing role
|
|
INSERT IGNORE INTO `role_assignable_roles` (`role_id`, `assignable_role_id`)
|
|
SELECT admin_role.id, all_roles.id
|
|
FROM roles admin_role
|
|
CROSS JOIN roles all_roles
|
|
WHERE admin_role.code = 'ADMIN';
|