NTP_V2/db/role-co.sql

31 lines
1.9 KiB
SQL

-- New role CO with the same authority as GTO. Safe to re-run; not run by the app.
-- Run order: part 1 in the NTP schema, part 2 in the primary (NAFITHTCS) schema.
-- Afterwards assign role CO to the users who need it (NTPUSERS) - they sign in as before.
-- The API applies the permission rows within about a minute, the browser on the next reload / sign-in.
-- ---------------------------------------------------------------------------------------------------------------------------
-- Part 1 (NTP schema): the role itself, copied from GTO (level, type, manager columns). Rename it in NAME if needed.
-- ---------------------------------------------------------------------------------------------------------------------------
INSERT INTO ROLES (CODE, NAME, DIRECTMANAGER, EMPLOYEES, MANAGERS, LEVELNUMBER, ROLETYPE, TENDER)
SELECT 'CO', 'CO', r.DIRECTMANAGER, r.EMPLOYEES, r.MANAGERS, r.LEVELNUMBER, r.ROLETYPE, r.TENDER
FROM ROLES r
WHERE r.CODE = 'GTO'
AND NOT EXISTS (SELECT 1 FROM ROLES x WHERE x.CODE = 'CO');
COMMIT;
-- ---------------------------------------------------------------------------------------------------------------------------
-- Part 2 (primary schema): every screen / action / report right GTO has (grains-screen-permissions.sql, gto-reports.sql,
-- menu-reorganisation.sql, registry-create-permissions.sql ...) is copied to CO. Later GTO rows: run this part again.
-- ---------------------------------------------------------------------------------------------------------------------------
INSERT INTO GRAINS_SCREEN_PERMISSIONS (ROLECODE, RESOURCE_KEY)
SELECT 'CO', g.RESOURCE_KEY
FROM GRAINS_SCREEN_PERMISSIONS g
WHERE g.ROLECODE = 'GTO'
AND NOT EXISTS (SELECT 1 FROM GRAINS_SCREEN_PERMISSIONS c WHERE c.ROLECODE = 'CO' AND c.RESOURCE_KEY = g.RESOURCE_KEY);
COMMIT;
-- Take a right away from CO by deleting its row, e.g.:
-- DELETE FROM GRAINS_SCREEN_PERMISSIONS WHERE ROLECODE = 'CO' AND RESOURCE_KEY = 'grains.permits.voiceCall';