Проверка дали имаме присвоени привилегии

Ticketa

Ticketa

Registered
Имам запис в таблицата test_roles и този запис има записи, създадени/съществуващи като привилегия в test_permissions, тогава тези привилегии са НЕПРЕДНАЗНАЧЕНИ.

Въпреки това, ако имаме записи в таблицата test_role_permissions, които съответстват на привилегиите от таблицата test_permissions, тогава тези привилегии са ПРИЗНАЧЕНИ. Обаче моята заявка изкарва грешен резултат и не мога вече над 1ч да го оправя (може би почивка? хах..)

SQL: 
SELECT
    p.controller,
    p.name AS action,
    IF(rp.role IS NULL, "UNASSIGNED", "ASSIGNED") AS assigned
FROM
    test_permissions p
    LEFT JOIN test_role_permissions rp ON p.name = rp.permission AND rp.role = "editors"
WHERE
    p.controller IN ("Admin/Blog", "Admin/Activities", "Admin/Languages", "Admin/Users")
ORDER BY
    p.controller, p.name;

виждам:

controller
Ascending
1 		action assigned
Admin/ActivitiesviewASSIGNED
Admin/BlogaddASSIGNED
Admin/BlogdeleteASSIGNED
Admin/BlogeditASSIGNED
Admin/BlogviewASSIGNED
Admin/LanguagesaddASSIGNED
Admin/LanguagesdeleteASSIGNED
Admin/LanguageseditASSIGNED
Admin/LanguagesviewASSIGNED
Admin/UsersaddASSIGNED
Admin/UserseditASSIGNED
Admin/UsersviewASSIGNED


а трябва да бъде

controller
Ascending
1 		action assigned
Admin/ActivitiesviewUNASSIGNED
Admin/BlogaddASSIGNED
Admin/BlogdeleteASSIGNED
Admin/BlogeditASSIGNED
Admin/BlogviewASSIGNED
Admin/LanguagesaddUNASSIGNED
Admin/LanguagesdeleteUNASSIGNED
Admin/LanguageseditUNASSIGNED
Admin/LanguagesviewUNASSIGNED
Admin/UsersaddUNASSIGNED
Admin/UserseditUNASSIGNED
Admin/UsersviewUNASSIGNED


SQL: 
CREATE TABLE `test_permissions` (
  `id` int NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `controller` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `test_permissions` (`id`, `name`, `controller`, `description`) VALUES
(1, '*', NULL, 'All permissions'),
(2, 'view', NULL, 'View permission'),
(3, 'edit', NULL, 'Edit permission'),
(4, 'delete', NULL, 'Delete permission'),
(5, 'add', NULL, 'Add permission'),
(6, 'view', 'Admin/Blog', 'View permission'),
(7, 'edit', 'Admin/Blog', 'Edit permission'),
(8, 'delete', 'Admin/Blog', 'Delete permission'),
(9, 'add', 'Admin/Blog', 'Add permission'),
(10, 'view', 'Admin/Users', 'View permission'),
(11, 'edit', 'Admin/Users', 'Edit permission'),
(12, 'add', 'Admin/Users', 'Add permission'),
(13, 'view', 'Admin/Languages', 'View permission'),
(14, 'edit', 'Admin/Languages', 'Edit permission'),
(15, 'delete', 'Admin/Languages', 'Delete permission'),
(16, 'add', 'Admin/Languages', 'Add permission'),
(17, 'view', 'Admin/Activities', 'View permission');
CREATE TABLE `test_roles` (
  `id` int NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `parent_role` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `test_roles` (`id`, `name`, `parent_role`, `Description`) VALUES
(1, 'root', NULL, 'Full access'),
(2, 'admin', NULL, 'Can access everything except delete'),
(3, 'user', NULL, 'Can only view'),
(4, 'editors', NULL, 'Can access everything except settings');
CREATE TABLE `test_role_permissions` (
  `id` int NOT NULL,
  `role` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `permission` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `controller` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `test_role_permissions` (`id`, `role`, `permission`, `controller`) VALUES
(1, 'root', '*', '*'),
(2, 'root', 'add', '*'),
(3, 'root', 'delete', '*'),
(4, 'root', 'edit', '*'),
(5, 'root', 'view', '*'),
(8, 'admin', 'view', '*'),
(9, 'admin', 'edit', '*'),
(10, 'admin', 'add', '*'),
(11, 'user', 'view', '*'),
(12, 'admin', 'delete', 'Admin/Home'),
(13, 'editors', 'view', 'Admin/Blog'),
(14, 'editors', 'edit', 'Admin/Blog'),
(15, 'editors', 'delete', 'Admin/Blog'),
(16, 'editors', 'add', 'Admin/Blog');
ALTER TABLE `test_permissions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `name` (`name`);
ALTER TABLE `test_roles`
  ADD PRIMARY KEY (`id`),
  ADD KEY `name` (`name`),
  ADD KEY `name_2` (`name`);
ALTER TABLE `test_role_permissions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `role` (`role`),
  ADD KEY `permission` (`permission`);
ALTER TABLE `test_permissions`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;
ALTER TABLE `test_roles`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
ALTER TABLE `test_role_permissions`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;
ALTER TABLE `test_role_permissions`
  ADD CONSTRAINT `test_role_permissions_ibfk_1` FOREIGN KEY (`role`) REFERENCES `test_roles` (`name`),
  ADD CONSTRAINT `test_role_permissions_ibfk_2` FOREIGN KEY (`permission`) REFERENCES `test_permissions` (`name`);
COMMIT;
 
Структурата не ми се нрави на мен. Имаш определен сет от привилегии (view, add, delete...). Ако трябва да ги асоциираш с контролер, то направи отделна N-N (permissions_controllers) таблица и използвай foreign keys.
Отделно определена роля е асоциирана с привилегии (roles_permissions). Когато вземеш дадена роля, то имаш асоциираните привилегии. Според асоциираните привилегии можеш да намериш за какво си привилегирован като провериш permissions_controllers.

Потенциални таблици: roles, permissions, controllers
и за релации: roles_permissions, permissions_controllers (и вероятно users_roles N-N ако даден юзър може да има няколко роли и просто сетваш Id-то на ролята в users таблицата, ако може да има само една роля).
 
Трябва да си влезнал или регистриран, за да отговориш тук.

Back
Горе