NTP_V2/db/registry-create-permissions...

44 lines
2.7 KiB
SQL

-- Registry action rights (NAFITHTCS schema): who may REGISTER (add) and who may EDIT trucks, trailers and drivers.
-- Run on a database where GRAINS_SCREEN_PERMISSIONS already exists. Safe to re-run: a row is only inserted when it is not there
-- yet. Not executed by the application. SU is never stored: it always has every right.
--
-- add trucks : GOO, GPO
-- add trailers : GOO, GPO
-- add drivers : GTO, GPO
-- edit trucks : GTO, GGO, GOO, GPO (all roles)
-- edit trailers : GTO, GGO, GOO, GPO (all roles)
-- (drivers cannot be edited after registration in the legacy system)
--
-- To take a right away later, delete the row; the API applies it within about a minute (permissions are cached per role for
-- 60 seconds) and the user's browser picks it up on the next page reload or sign-in.
INSERT INTO GRAINS_SCREEN_PERMISSIONS (ROLECODE, RESOURCE_KEY)
SELECT v.rolecode, v.resource_key
FROM (
-- add
SELECT 'GTO' AS rolecode, 'grains.registry.drivers.create' AS resource_key FROM dual UNION ALL
SELECT 'GOO', 'grains.registry.trucks.create' FROM dual UNION ALL
SELECT 'GOO', 'grains.registry.trailers.create' FROM dual UNION ALL
SELECT 'GPO', 'grains.registry.trucks.create' FROM dual UNION ALL
SELECT 'GPO', 'grains.registry.trailers.create' FROM dual UNION ALL
SELECT 'GPO', 'grains.registry.drivers.create' FROM dual UNION ALL
-- edit (all roles)
SELECT 'GTO', 'grains.registry.trucks.edit' FROM dual UNION ALL
SELECT 'GTO', 'grains.registry.trailers.edit' FROM dual UNION ALL
SELECT 'GGO', 'grains.registry.trucks.edit' FROM dual UNION ALL
SELECT 'GGO', 'grains.registry.trailers.edit' FROM dual UNION ALL
SELECT 'GOO', 'grains.registry.trucks.edit' FROM dual UNION ALL
SELECT 'GOO', 'grains.registry.trailers.edit' FROM dual UNION ALL
SELECT 'GPO', 'grains.registry.trucks.edit' FROM dual UNION ALL
SELECT 'GPO', 'grains.registry.trailers.edit' FROM dual
) v
WHERE NOT EXISTS (
SELECT 1 FROM GRAINS_SCREEN_PERMISSIONS p
WHERE p.rolecode = v.rolecode AND p.resource_key = v.resource_key);
COMMIT;
-- Check (expect 14 rows):
-- SELECT rolecode, resource_key FROM GRAINS_SCREEN_PERMISSIONS
-- WHERE resource_key LIKE 'grains.registry.%.create' OR resource_key LIKE 'grains.registry.%.edit' ORDER BY 2, 1;