-
Notifications
You must be signed in to change notification settings - Fork 177
WebAPI 3.0 Permission Migration
Chris Knoll edited this page Feb 25, 2026
·
1 revision
The WebAPI 3.0 release introduces a new and simpler permission structure. However, for migration of WebAPI 2.x releases, a migration script was built to migrate existing permissions to the new permission structure and makes an attempt to assign the new permissions to the correct roles. The following query describes the logic that you can run on your own environment to understand how permissions will be re-assigned.
In WebAPI 2.x, all permissions were driven by Wildcard permissions, that led to severe permission bloat and difficulty managing permissions and introducing new features. While we are retaining the core tables sec_user, sec_role, sec_permission, and the relationship tables (user-role, role-permission), we are introducing new sec_{entity} tables to manage entity-based permissions for easier cleanup and faster lookups. Wildcard permissions still exist, but they are 'global' entitlements, not per-entity.
The global permissions are:
- admin:(source|cache|tags|tools|security): grants permissions to manage sources, caches, tags, tools and user/role operations (assignment and import)
- create:(conceptset|cohort-definition|cohort-characterization|incidence..etc): grants rights to create these assets.
- read:(...same as create... plus source): grants read access to any asset.
- write:(...same as create.. plus source): grants write access to any asset.
Note: Sources are not considered 'assets'. Read/Write to source means you can query results and generate results. Management of creating/deleting sources is granted under the admin:source permission.
Entity specific permissions are captured in new sec_ tables: sec_cohortdefinition, sec_conceptset, etc. These tables contain colums: {entity}_id, role_id, access_type, where access type is a value from enum (READ|WRITE). New access types could be added later. The goal with having separate entity tables is to allow foreign keys clean up deleted entities or roles through database coordination, as well as faster query for sets of permissions (such as read/write permissions can be found can be handled in-database for list operations).
The following script demonstrates what will be the permission migration script for WebAPI 2.x to 3.0. You can review the logic and apply it to your own environment to understand how permissions will be re-shaped.
This example script assumes you have your current webapi tables in a schema called webapi, and it makes copies of your webapi schema into a new 'migration_poc' schema.
drop schema if exists migration_poc CASCADE;
create schema migration_poc;
create table migration_poc.sec_permission_copy as
select * from webapi.sec_permission;
---
--- Remove obsolete permissions (estimation, prediction)
---
delete from migration_poc.sec_permission_copy
where value like 'prediction%'
or value like 'plp%'
or value like 'estimation%'
or value like 'comparativecohortanalysis%'
or value like 'cohortresults%'
or value like 'feasibility%'
or value like 'executionservice%'
;
-- Entity -> ROLE Permissions
-- Step 1: take care of individual entity->Role read/write permissions by creating a migration_poc.{entity}_sec
-- Step 2: remove any entity-specific permissons for this entity type (this will be handed by {entity}_sec)
-- CONCEPT_SET:
-- temp table to hold fnal concept set permission->user assignment
create temp table sec_concept_set (
role_id int NOT NULL,
concept_set_id int NOT NULL,
access_type varchar(50) NOT NULL,
CONSTRAINT PK_concept_set_sec PRIMARY KEY (role_id, concept_set_id, access_type)
);
-- READ Access Type
-- The patterns for read permissons are:
-- put("conceptset:%s:get", "view conceptset definition with id %s");
-- put("conceptset:%s:expression:get", "Resolve concept set %s expression");
-- put("conceptset:%s:annotation:get", "Resolve concept set annotations");
-- put("conceptset:%s:version:*:expression:get", "Get expression for concept set %s items for default source");
WITH read_permission_templates(template) AS (
VALUES
('conceptset:%s:get'),
('conceptset:%s:expression:get'),
('conceptset:%s:annotation:get'),
('conceptset:%s:version:*:expression:get')
),
expanded_permissions AS (
SELECT
cs.concept_set_id,
r.id as created_by_id,
r.name as role_name,
u.login,
replace(replace(t.template, '%s', cs.concept_set_id::text), '*', '\*') AS permission_pattern
FROM webapi.concept_set cs
JOIN webapi.sec_user_role ur on ur.user_id = cs.created_by_id
JOIN webapi.sec_role r on r.id = ur.role_id and r.system_role is false
join webapi.sec_user u on ur.user_id = u.id
CROSS JOIN read_permission_templates t
where r.name = u.login
),
role_permissions as (
select distinct role_id, p.value as permission_value
from webapi.sec_role_permission rp
join webapi.sec_permission p on rp.permission_id = p.id
),
matched_permissions AS (
SELECT DISTINCT
ep.concept_set_id,
p.role_id,
ep.created_by_id
FROM role_permissions p
join expanded_permissions ep on p.permission_value = ep.permission_pattern
)
insert into sec_concept_set (role_id, concept_set_id, access_type)
SELECT DISTINCT
role_id,
concept_set_id,
'READ' AS permission
FROM matched_permissions
WHERE role_id <> created_by_id;
-- select * from sec_concept_set
-- Clean up permissions we matched to READ from permisson table
WITH read_permission_templates(template) AS (
VALUES
('conceptset:%s:get'),
('conceptset:%s:expression:get'),
('conceptset:%s:annotation:get'),
('conceptset:%s:version:*:expression:get')
),
permission_regex AS (
SELECT
'^' ||
replace(replace(template, '%s', '[0-9]+'),'*', '\*') ||
'$' AS regex
FROM read_permission_templates
)
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- WRITE Access
-- put("conceptset:%s:put", "Update Concept Set with ID = %s");
-- put("conceptset:%s:items:put", "Update Items of Concept Set with ID = %s");
-- put("conceptset:%s:annotation:*:delete", "Delete Annotations of Concept Set with ID = %s");
-- put("conceptset:%s:delete", "Delete Concept Set with ID = %s");
WITH write_permission_templates(template) AS (
VALUES
('conceptset:%s:put'),
('conceptset:%s:items:put'),
('conceptset:%s:annotation:*:delete'),
('conceptset:%s:delete')
),
expanded_permissions AS (
SELECT
cs.concept_set_id,
r.id as created_by_id,
r.name as role_name,
u.login,
replace(replace(t.template, '%s', cs.concept_set_id::text), '*', '\*') AS permission_pattern
FROM webapi.concept_set cs
JOIN webapi.sec_user_role ur on ur.user_id = cs.created_by_id
JOIN webapi.sec_role r on r.id = ur.role_id and r.system_role is false
join webapi.sec_user u on ur.user_id = u.id
CROSS JOIN write_permission_templates t
where r.name = u.login
),
role_permissions as (
select distinct role_id, p.value as permission_value
from webapi.sec_role_permission rp
join webapi.sec_permission p on rp.permission_id = p.id
),
matched_permissions AS (
SELECT DISTINCT
ep.concept_set_id,
p.role_id,
ep.created_by_id
FROM role_permissions p
join expanded_permissions ep on p.permission_value = ep.permission_pattern
)
insert into sec_concept_set (role_id, concept_set_id, access_type)
SELECT DISTINCT
role_id,
concept_set_id,
'WRITE' AS permission
FROM matched_permissions
WHERE role_id <> created_by_id;
-- select * from sec_concept_set
-- Clean up permissions we matched to WRITE from permisson table
WITH write_permission_templates(template) AS (
VALUES
('conceptset:%s:put'),
('conceptset:%s:items:put'),
('conceptset:%s:annotation:*:delete'),
('conceptset:%s:delete')
),
permission_regex AS (
SELECT
'^' ||
replace(replace(template, '%s', '[0-9]+'),'*', '\*') ||
'$' AS regex
FROM write_permission_templates
)
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- COHORT_DEFINITION:
-- temp table to hold final cohort definition permission->user assignment
create temp table sec_cohort_definition (
role_id int NOT NULL,
cohort_definition_id int NOT NULL,
access_type varchar(50) NOT NULL,
CONSTRAINT PK_cohort_definition_sec
PRIMARY KEY (role_id, cohort_definition_id, access_type)
);
-- READ Access Type
-- The patterns for read permissions are:
-- put("cohortdefinition:%s:get", "Get Cohort Definition by ID");
-- put("cohortdefinition:%s:info:get", "");
-- put("cohortdefinition:%s:version:get", "Get list of cohort versions");
-- put("cohortdefinition:%s:version:*:get", "Get cohort version");
WITH read_permission_templates(template) AS (
VALUES
('cohortdefinition:%s:get'),
('cohortdefinition:%s:info:get'),
('cohortdefinition:%s:version:get'),
('cohortdefinition:%s:version:*:get')
),
expanded_permissions AS (
SELECT
cd.id as cohort_definition_id,
r.id as created_by_id,
r.name as role_name,
u.login,
replace(replace(t.template, '%s', cd.id::text),'*', '\*') AS permission_pattern
FROM webapi.cohort_definition cd
JOIN webapi.sec_user_role ur on ur.user_id = cd.created_by_id
JOIN webapi.sec_role r on r.id = ur.role_id and r.system_role is false
join webapi.sec_user u on ur.user_id = u.id
CROSS JOIN read_permission_templates t
where r.name = u.login
),
role_permissions as (
select distinct role_id, p.value as permission_value
from webapi.sec_role_permission rp
join webapi.sec_permission p on rp.permission_id = p.id
),
matched_permissions AS (
SELECT DISTINCT
ep.cohort_definition_id,
p.role_id,
ep.created_by_id
FROM role_permissions p
join expanded_permissions ep on p.permission_value = ep.permission_pattern
)
INSERT INTO sec_cohort_definition (role_id, cohort_definition_id, access_type)
SELECT DISTINCT
role_id,
cohort_definition_id,
'READ' AS access_type
FROM matched_permissions
WHERE role_id <> created_by_id;
-- cleanup cohort definition read permissions
WITH read_permission_templates(template) AS (
VALUES
('cohortdefinition:%s:get'),
('cohortdefinition:%s:info:get'),
('cohortdefinition:%s:version:get'),
('cohortdefinition:%s:version:*:get')
),
permission_regex AS (
SELECT
'^' ||
replace(replace(template, '%s', '[0-9]+'),'*', '\*') ||
'$' AS regex
FROM read_permission_templates
)
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- WRITE Access
-- put("cohortdefinition:%s:put", "Update Cohort Definition with ID = %s");
-- put("cohortdefinition:%s:delete", "Delete Cohort Definition with ID = %s");
-- put("cohortdefinition:%s:check:post", "Fix Cohort Definition with ID = %s");
WITH write_permission_templates(template) AS (
VALUES
('cohortdefinition:%s:put'),
('cohortdefinition:%s:delete'),
('cohortdefinition:%s:check:post')
),
expanded_permissions AS (
SELECT
cd.id as cohort_definition_id,
r.id as created_by_id,
r.name as role_name,
u.login,
replace(replace(t.template, '%s', cd.id::text),'*', '\*') AS permission_pattern
FROM webapi.cohort_definition cd
JOIN webapi.sec_user_role ur on ur.user_id = cd.created_by_id
JOIN webapi.sec_role r on r.id = ur.role_id and r.system_role is false
join webapi.sec_user u on ur.user_id = u.id
CROSS JOIN write_permission_templates t
where r.name = u.login
),
role_permissions as (
select distinct role_id, p.value as permission_value
from webapi.sec_role_permission rp
join webapi.sec_permission p on rp.permission_id = p.id
),
matched_permissions AS (
SELECT DISTINCT
ep.cohort_definition_id,
p.role_id,
ep.created_by_id
FROM role_permissions p
join expanded_permissions ep on p.permission_value = ep.permission_pattern
)
INSERT INTO sec_cohort_definition (role_id, cohort_definition_id, access_type)
SELECT DISTINCT
role_id,
cohort_definition_id,
'WRITE' AS access_type
FROM matched_permissions
WHERE role_id <> created_by_id;
-- Cleanup write permissions
WITH write_permission_templates(template) AS (
VALUES
('cohortdefinition:%s:put'),
('cohortdefinition:%s:delete'),
('cohortdefinition:%s:check:post')
),
permission_regex AS (
SELECT
'^' ||
replace(replace(template, '%s', '[0-9]+'),'*', '\*') ||
'$' AS regex
FROM write_permission_templates
)
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- COHORT SAMPLES: We don't need sample permissions, so we delete everything related to that
DELETE FROM migration_poc.sec_permission_copy p
where p.value like 'cohortsample%';
-- COHORT_CHARACTERIZATION:
-- temp table to hold final cohort characterization permission->user assignment
create temp table sec_cohort_characterization (
role_id int NOT NULL,
cohort_characterization_id int NOT NULL,
access_type varchar(50) NOT NULL,
CONSTRAINT PK_cohort_characterization_sec
PRIMARY KEY (role_id, cohort_characterization_id, access_type)
);
-- READ Access Type
-- The patterns for read permissions are:
-- put("cohort-characterization:%s:get", "Get cohort characterization");
-- put("cohort-characterization:%s:generation:get", "Get cohort characterization generations");
-- put("cohort-characterization:%s:design:get", "Get cohort characterization design");
-- put("cohort-characterization:design:%s:get", "view cohort characterization with id %s");
-- put("cohort-characterization:%s:version:get", "Get list of characterization versions");
-- put("cohort-characterization:%s:version:*:get", "Get list of characterization version");
WITH read_permission_templates(template) AS (
VALUES
('cohort-characterization:%s:get'),
('cohort-characterization:%s:generation:get'),
('cohort-characterization:%s:design:get'),
('cohort-characterization:design:%s:get'),
('cohort-characterization:%s:version:get'),
('cohort-characterization:%s:version:*:get')
),
expanded_permissions AS (
SELECT
cc.id as cohort_characterization_id,
r.id as created_by_id,
r.name as role_name,
u.login,
replace(replace(t.template, '%s', cc.id::text),'*','\*') AS permission_pattern
FROM webapi.cohort_characterization cc
JOIN webapi.sec_user_role ur on ur.user_id = cc.created_by_id
JOIN webapi.sec_role r on r.id = ur.role_id and r.system_role is false
join webapi.sec_user u on ur.user_id = u.id
CROSS JOIN read_permission_templates t
where r.name = u.login
),
role_permissions as (
select distinct role_id, p.value as permission_value
from webapi.sec_role_permission rp
join webapi.sec_permission p on rp.permission_id = p.id
),
matched_permissions AS (
SELECT DISTINCT
ep.cohort_characterization_id,
p.role_id,
ep.created_by_id
FROM role_permissions p
join expanded_permissions ep on p.permission_value = ep.permission_pattern
)
INSERT INTO sec_cohort_characterization
(role_id, cohort_characterization_id, access_type)
SELECT DISTINCT
role_id,
cohort_characterization_id,
'READ' AS access_type
FROM matched_permissions
WHERE role_id <> created_by_id;
-- cleanup read permissions
WITH read_permission_templates(template) AS (
VALUES
('cohort-characterization:%s:get'),
('cohort-characterization:%s:generation:get'),
('cohort-characterization:%s:design:get'),
('cohort-characterization:design:%s:get'),
('cohort-characterization:%s:version:get'),
('cohort-characterization:%s:version:*:get')
),
permission_regex AS (
SELECT
'^' ||
replace(replace(template, '%s', '[0-9]+'),'*', '\*') ||
'$' AS regex
FROM read_permission_templates
)
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- WRITE Access
-- put("cohort-characterization:%s:put", "Update Cohort Characterization with ID = %s");
-- put("cohort-characterization:%s:delete", "Delete Cohort Characterization with ID = %s");
WITH write_permission_templates(template) AS (
VALUES
('cohort-characterization:%s:put'),
('cohort-characterization:%s:delete')
),
expanded_permissions AS (
SELECT
cc.id as cohort_characterization_id,
r.id as created_by_id,
r.name as role_name,
u.login,
replace(replace(t.template, '%s', cc.id::text),'*','\*') AS permission_pattern
FROM webapi.cohort_characterization cc
JOIN webapi.sec_user_role ur on ur.user_id = cc.created_by_id
JOIN webapi.sec_role r on r.id = ur.role_id and r.system_role is false
join webapi.sec_user u on ur.user_id = u.id
CROSS JOIN write_permission_templates t
where r.name = u.login
),
role_permissions as (
select distinct role_id, p.value as permission_value
from webapi.sec_role_permission rp
join webapi.sec_permission p on rp.permission_id = p.id
),
matched_permissions AS (
SELECT DISTINCT
ep.cohort_characterization_id,
p.role_id,
ep.created_by_id
FROM role_permissions p
join expanded_permissions ep on p.permission_value = ep.permission_pattern
)
INSERT INTO sec_cohort_characterization
(role_id, cohort_characterization_id, access_type)
SELECT DISTINCT
role_id,
cohort_characterization_id,
'WRITE' AS access_type
FROM matched_permissions
WHERE role_id <> created_by_id;
-- cleanup permissions
WITH write_permission_templates(template) AS (
VALUES
('cohort-characterization:%s:put'),
('cohort-characterization:%s:delete')
),
permission_regex AS (
SELECT
'^' ||
replace(replace(template, '%s', '[0-9]+'),'*', '\*') ||
'$' AS regex
FROM write_permission_templates
)
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- INCIDENCE_RATE:
-- temp table to hold final incidence rate permission->role assignment
create temp table sec_incidence_rate (
role_id int NOT NULL,
ir_id int NOT NULL,
access_type varchar(50) NOT NULL,
CONSTRAINT PK_incidence_rate_sec
PRIMARY KEY (role_id, ir_id, access_type)
);
-- READ Access Type
-- put("ir:%s:get", "view list of incident rates");
-- put("ir:%s:version:get", "Get list of IR analysis versions");
-- put("ir:%s:version:*:get", "Get IR analysis version");
-- put("ir:%s:copy:get", "Copy incidence rate");
-- put("ir:%s:info:get", "Get IR info");
-- put("ir:%s:design:get", "Export Incidence Rates design");
WITH read_permission_templates(template) AS (
VALUES
('ir:%s:get'),
('ir:%s:version:get'),
('ir:%s:version:*:get'),
('ir:%s:copy:get'),
('ir:%s:info:get'),
('ir:%s:design:get')
),
expanded_permissions AS (
SELECT
ir.id AS ir_id,
r.id AS created_by_id,
r.name AS role_name,
u.login,
replace(replace(t.template, '%s', ir.id::text), '*', '\*') AS permission_pattern
FROM webapi.ir_analysis ir
JOIN webapi.sec_user_role ur
ON ur.user_id = ir.created_by_id
JOIN webapi.sec_role r
ON r.id = ur.role_id
AND r.system_role IS FALSE
JOIN webapi.sec_user u
ON ur.user_id = u.id
CROSS JOIN read_permission_templates t
WHERE r.name = u.login
),
role_permissions AS (
SELECT DISTINCT
rp.role_id,
p.value AS permission_value
FROM webapi.sec_role_permission rp
JOIN webapi.sec_permission p
ON rp.permission_id = p.id
),
matched_permissions AS (
SELECT DISTINCT
ep.ir_id,
rp.role_id,
ep.created_by_id
FROM role_permissions rp
JOIN expanded_permissions ep
ON rp.permission_value = ep.permission_pattern
)
INSERT INTO sec_incidence_rate (role_id, ir_id, access_type)
SELECT DISTINCT
role_id,
ir_id,
'READ' AS access_type
FROM matched_permissions
WHERE role_id <> created_by_id;
-- cleanup permissions
WITH read_permission_templates(template) AS (
VALUES
('ir:%s:get'),
('ir:%s:version:get'),
('ir:%s:version:*:get'),
('ir:%s:copy:get'),
('ir:%s:info:get'),
('ir:%s:design:get')
),
permission_regex AS (
SELECT
'^' ||
replace(replace(template, '%s', '[0-9]+'),'*', '\*') ||
'$' AS regex
FROM read_permission_templates
)
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- WRITE Access
-- put("ir:%s:get", "Read Incidence Rate with ID=%s"); -- TODO
-- put("ir:%s:export:get", "Export Incidence Rate with ID=%s");
-- put("ir:%s:put", "Edit Incidence Rate with ID=%s");
-- put("ir:%s:delete", "Delete Incidence Rate with ID=%s");
WITH write_permission_templates(template) AS (
VALUES
('ir:%s:get'),
('ir:%s:export:get'),
('ir:%s:put'),
('ir:%s:delete')
),
expanded_permissions AS (
SELECT
ir.id AS ir_id,
r.id AS created_by_id,
r.name AS role_name,
u.login,
replace(replace(t.template, '%s', ir.id::text), '*', '\*') AS permission_pattern
FROM webapi.ir_analysis ir
JOIN webapi.sec_user_role ur
ON ur.user_id = ir.created_by_id
JOIN webapi.sec_role r
ON r.id = ur.role_id
AND r.system_role IS FALSE
JOIN webapi.sec_user u
ON ur.user_id = u.id
CROSS JOIN write_permission_templates t
WHERE r.name = u.login
),
role_permissions AS (
SELECT DISTINCT
rp.role_id,
p.value AS permission_value
FROM webapi.sec_role_permission rp
JOIN webapi.sec_permission p
ON rp.permission_id = p.id
),
matched_permissions AS (
SELECT DISTINCT
ep.ir_id,
rp.role_id,
ep.created_by_id
FROM role_permissions rp
JOIN expanded_permissions ep
ON rp.permission_value = ep.permission_pattern
)
INSERT INTO sec_incidence_rate (role_id, ir_id, access_type)
SELECT DISTINCT
role_id,
ir_id,
'WRITE' AS access_type
FROM matched_permissions
WHERE role_id <> created_by_id;
--- Cleanup Permissions
WITH write_permission_templates(template) AS (
VALUES
('ir:%s:get'),
('ir:%s:export:get'),
('ir:%s:put'),
('ir:%s:delete')
),
permission_regex AS (
SELECT
'^' ||
replace(replace(template, '%s', '[0-9]+'),'*', '\*') ||
'$' AS regex
FROM write_permission_templates
)
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- FEATURE_ANALYSIS:
-- temp table to hold final feature analysis permission->role assignment
create temp table sec_feature_analysis (
role_id int NOT NULL,
fe_analysis_id int NOT NULL,
access_type varchar(50) NOT NULL,
CONSTRAINT PK_feature_analysis_sec
PRIMARY KEY (role_id, fe_analysis_id, access_type)
);
-- READ Access Type
-- put("feature-analysis:%s:get", "get feature analysis");
-- put("feature-analysis:aggregates:get", "feature-analysis:aggregates:get");
WITH read_permission_templates(template) AS (
VALUES
('feature-analysis:%s:get')
),
expanded_permissions AS (
SELECT
fe.id AS fe_analysis_id,
r.id AS created_by_id,
r.name AS role_name,
u.login,
replace(replace(t.template, '%s', fe.id::text), '*', '\*') AS permission_pattern
FROM webapi.fe_analysis fe
JOIN webapi.sec_user_role ur
ON ur.user_id = fe.created_by_id
JOIN webapi.sec_role r
ON r.id = ur.role_id
AND r.system_role IS FALSE
JOIN webapi.sec_user u
ON ur.user_id = u.id
CROSS JOIN read_permission_templates t
WHERE r.name = u.login
),
role_permissions AS (
SELECT DISTINCT
rp.role_id,
p.value AS permission_value
FROM webapi.sec_role_permission rp
JOIN webapi.sec_permission p
ON rp.permission_id = p.id
),
matched_permissions AS (
SELECT DISTINCT
ep.fe_analysis_id,
rp.role_id,
ep.created_by_id
FROM role_permissions rp
JOIN expanded_permissions ep
ON rp.permission_value = ep.permission_pattern
)
INSERT INTO sec_feature_analysis (role_id, fe_analysis_id, access_type)
SELECT DISTINCT
role_id,
fe_analysis_id,
'READ' AS access_type
FROM matched_permissions
WHERE role_id <> created_by_id;
--- cleanup
WITH read_permission_templates(template) AS (
VALUES
('feature-analysis:%s:get')
),
permission_regex AS (
SELECT
'^' ||
replace(template, '%s', '[0-9]+') ||
'$' AS regex
FROM read_permission_templates
)
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- WRITE Access
-- put("feature-analysis:%s:put", "Update Feature Analysis with ID = %s");
-- put("feature-analysis:%s:delete", "Delete Feature Analysis with ID = %s");
WITH write_permission_templates(template) AS (
VALUES
('feature-analysis:%s:put'),
('feature-analysis:%s:delete')
),
expanded_permissions AS (
SELECT
fe.id AS fe_analysis_id,
r.id AS created_by_id,
r.name AS role_name,
u.login,
replace(replace(t.template, '%s', fe.id::text), '*', '\*') AS permission_pattern
FROM webapi.fe_analysis fe
JOIN webapi.sec_user_role ur
ON ur.user_id = fe.created_by_id
JOIN webapi.sec_role r
ON r.id = ur.role_id
AND r.system_role IS FALSE
JOIN webapi.sec_user u
ON ur.user_id = u.id
CROSS JOIN write_permission_templates t
WHERE r.name = u.login
),
role_permissions AS (
SELECT DISTINCT
rp.role_id,
p.value AS permission_value
FROM webapi.sec_role_permission rp
JOIN webapi.sec_permission p
ON rp.permission_id = p.id
),
matched_permissions AS (
SELECT DISTINCT
ep.fe_analysis_id,
rp.role_id,
ep.created_by_id
FROM role_permissions rp
JOIN expanded_permissions ep
ON rp.permission_value = ep.permission_pattern
)
INSERT INTO sec_feature_analysis (role_id, fe_analysis_id, access_type)
SELECT DISTINCT
role_id,
fe_analysis_id,
'WRITE' AS access_type
FROM matched_permissions
WHERE role_id <> created_by_id;
--- cleanup
WITH write_permission_templates(template) AS (
VALUES
('feature-analysis:%s:put'),
('feature-analysis:%s:delete')
),
permission_regex AS (
SELECT
'^' ||
replace(template, '%s', '[0-9]+') ||
'$' AS regex
FROM write_permission_templates
)
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- ============================================================
-- PATHWAY_ANALYSIS
-- ============================================================
-- temp table to hold final pathway analysis permission->role assignment
create temp table if not exists sec_pathway_analysis (
role_id int NOT NULL,
pathway_analysis_id int NOT NULL,
access_type varchar(50) NOT NULL,
CONSTRAINT PK_pathway_analysis_sec
PRIMARY KEY (role_id, pathway_analysis_id, access_type)
);
-- ============================================================
-- READ ACCESS
-- ============================================================
-- Read permission templates:
-- put("pathway-analysis:%s:get", "Get Pathways Analysis instance");
-- put("pathway-analysis:%s:generation:get", "Get Pathways Analysis generations list");
-- put("pathway-analysis:%s:version:get", "Get list of pathway analysis versions");
-- put("pathway-analysis:%s:version:*:get", "Get pathway analysis version");
WITH read_permission_templates(template) AS (
VALUES
('pathway-analysis:%s:get'),
('pathway-analysis:%s:generation:get'),
('pathway-analysis:%s:version:get'),
('pathway-analysis:%s:version:*:get')
),
expanded_permissions AS (
SELECT
pa.id AS pathway_analysis_id,
r.id AS created_by_id,
replace(replace(t.template, '%s', pa.id::text), '*', '\*') AS permission_pattern
FROM webapi.pathway_analysis pa
JOIN webapi.sec_user_role ur
ON ur.user_id = pa.created_by_id
JOIN webapi.sec_role r
ON r.id = ur.role_id
AND r.system_role IS FALSE
JOIN webapi.sec_user u
ON u.id = ur.user_id
CROSS JOIN read_permission_templates t
WHERE r.name = u.login
),
role_permissions AS (
SELECT DISTINCT
rp.role_id,
p.value AS permission_value
FROM webapi.sec_role_permission rp
JOIN webapi.sec_permission p
ON p.id = rp.permission_id
),
matched_permissions AS (
SELECT DISTINCT
ep.pathway_analysis_id,
rp.role_id,
ep.created_by_id
FROM role_permissions rp
JOIN expanded_permissions ep
ON rp.permission_value = ep.permission_pattern
)
INSERT INTO sec_pathway_analysis (role_id, pathway_analysis_id, access_type)
SELECT DISTINCT
role_id,
pathway_analysis_id,
'READ'
FROM matched_permissions
WHERE role_id <> created_by_id;
-- ------------------------------------------------------------
-- CLEAN UP READ PERMISSIONS
-- ------------------------------------------------------------
WITH read_permission_templates(template) AS (
VALUES
('pathway-analysis:%s:get'),
('pathway-analysis:%s:generation:get'),
('pathway-analysis:%s:version:get'),
('pathway-analysis:%s:version:*:get')
),
permission_regex AS (
SELECT
'^' ||
replace(replace(template, '%s', '[0-9]+'),'*', '\*') ||
'$' AS regex
FROM read_permission_templates
)
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- ============================================================
-- WRITE ACCESS
-- ============================================================
-- Write permission templates:
-- put("pathway-analysis:%s:put", "Update Pathway Analysis with ID = %s");
-- put("pathway-analysis:%s:sql:*:get", "Get analysis sql for Pathway Analysis with ID = %s");
-- put("pathway-analysis:%s:delete", "Delete Pathway Analysis with ID = %s");
WITH write_permission_templates(template) AS (
VALUES
('pathway-analysis:%s:put'),
('pathway-analysis:%s:sql:*:get'),
('pathway-analysis:%s:delete')
),
expanded_permissions AS (
SELECT
pa.id AS pathway_analysis_id,
r.id AS created_by_id,
replace(replace(t.template, '%s', pa.id::text), '*', '\*') AS permission_pattern
FROM webapi.pathway_analysis pa
JOIN webapi.sec_user_role ur
ON ur.user_id = pa.created_by_id
JOIN webapi.sec_role r
ON r.id = ur.role_id
AND r.system_role IS FALSE
JOIN webapi.sec_user u
ON u.id = ur.user_id
CROSS JOIN write_permission_templates t
WHERE r.name = u.login
),
role_permissions AS (
SELECT DISTINCT
rp.role_id,
p.value AS permission_value
FROM webapi.sec_role_permission rp
JOIN webapi.sec_permission p
ON p.id = rp.permission_id
),
matched_permissions AS (
SELECT DISTINCT
ep.pathway_analysis_id,
rp.role_id,
ep.created_by_id
FROM role_permissions rp
JOIN expanded_permissions ep
ON rp.permission_value = ep.permission_pattern
)
INSERT INTO sec_pathway_analysis (role_id, pathway_analysis_id, access_type)
SELECT DISTINCT
role_id,
pathway_analysis_id,
'WRITE'
FROM matched_permissions
WHERE role_id <> created_by_id;
-- ------------------------------------------------------------
-- CLEAN UP WRITE PERMISSIONS
-- ------------------------------------------------------------
WITH write_permission_templates(template) AS (
VALUES
('pathway-analysis:%s:put'),
('pathway-analysis:%s:sql:*:get'),
('pathway-analysis:%s:delete')
),
permission_regex AS (
SELECT
'^' ||
replace(replace(template, '%s', '[0-9]+'),'*', '\*') ||
'$' AS regex
FROM write_permission_templates
)
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- ============================================================
-- REUSABLE
-- ============================================================
-- temp table to hold final reusable permission->role assignment
create temp table if not exists sec_reusable (
role_id int NOT NULL,
reusable_id int NOT NULL,
access_type varchar(50) NOT NULL,
CONSTRAINT PK_reusable_sec
PRIMARY KEY (role_id, reusable_id, access_type)
);
-- ============================================================
-- READ ACCESS
-- ============================================================
-- Read permission templates:
-- put("reusable:%s:get", "view reusable with id %s");
-- put("reusable:%s:expression:get", "Resolve reusable %s expression");
-- put("reusable:%s:version:*:get", "Get expression for reusable %s items for default source");
WITH read_permission_templates(template) AS (
VALUES
('reusable:%s:get'),
('reusable:%s:expression:get'),
('reusable:%s:version:*:get')
),
expanded_permissions AS (
SELECT
rbl.id AS reusable_id,
r.id AS created_by_id,
replace(replace(t.template, '%s', rbl.id::text), '*', '\*') AS permission_pattern
FROM webapi.reusable rbl
JOIN webapi.sec_user_role ur
ON ur.user_id = rbl.created_by_id
JOIN webapi.sec_role r
ON r.id = ur.role_id
AND r.system_role IS FALSE
JOIN webapi.sec_user u
ON u.id = ur.user_id
CROSS JOIN read_permission_templates t
WHERE r.name = u.login
),
role_permissions AS (
SELECT DISTINCT
rp.role_id,
p.value AS permission_value
FROM webapi.sec_role_permission rp
JOIN webapi.sec_permission p
ON p.id = rp.permission_id
),
matched_permissions AS (
SELECT DISTINCT
ep.reusable_id,
rp.role_id,
ep.created_by_id
FROM role_permissions rp
JOIN expanded_permissions ep
ON rp.permission_value = ep.permission_pattern
)
INSERT INTO sec_reusable (role_id, reusable_id, access_type)
SELECT DISTINCT
role_id,
reusable_id,
'READ'
FROM matched_permissions
WHERE role_id <> created_by_id;
-- ------------------------------------------------------------
-- CLEAN UP READ PERMISSIONS
-- ------------------------------------------------------------
WITH read_permission_templates(template) AS (
VALUES
('reusable:%s:get'),
('reusable:%s:expression:get'),
('reusable:%s:version:*:get')
),
permission_regex AS (
SELECT
'^' ||
replace(replace(template, '%s', '[0-9]+'),'*', '\*') ||
'$' AS regex
FROM read_permission_templates
)
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- ============================================================
-- WRITE ACCESS
-- ============================================================
-- Write permission templates:
-- put("reusable:%s:delete", "Delete reusable");
-- put("reusable:%s:put", "Update reusable");
WITH write_permission_templates(template) AS (
VALUES
('reusable:%s:delete'),
('reusable:%s:put')
),
expanded_permissions AS (
SELECT
rbl.id AS reusable_id,
r.id AS created_by_id,
replace(replace(t.template, '%s', rbl.id::text), '*', '\*') AS permission_pattern
FROM webapi.reusable rbl
JOIN webapi.sec_user_role ur
ON ur.user_id = rbl.created_by_id
JOIN webapi.sec_role r
ON r.id = ur.role_id
AND r.system_role IS FALSE
JOIN webapi.sec_user u
ON u.id = ur.user_id
CROSS JOIN write_permission_templates t
WHERE r.name = u.login
),
role_permissions AS (
SELECT DISTINCT
rp.role_id,
p.value AS permission_value
FROM webapi.sec_role_permission rp
JOIN webapi.sec_permission p
ON p.id = rp.permission_id
),
matched_permissions AS (
SELECT DISTINCT
ep.reusable_id,
rp.role_id,
ep.created_by_id
FROM role_permissions rp
JOIN expanded_permissions ep
ON rp.permission_value = ep.permission_pattern
)
INSERT INTO sec_reusable (role_id, reusable_id, access_type)
SELECT DISTINCT
role_id,
reusable_id,
'WRITE'
FROM matched_permissions
WHERE role_id <> created_by_id;
-- ------------------------------------------------------------
-- CLEAN UP WRITE PERMISSIONS
-- ------------------------------------------------------------
WITH write_permission_templates(template) AS (
VALUES
('reusable:%s:delete'),
('reusable:%s:put')
),
permission_regex AS (
SELECT
'^' ||
replace(replace(template, '%s', '[0-9]+'),'*', '\*') ||
'$' AS regex
FROM write_permission_templates
)
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- ============================================================
-- TAG
-- ============================================================
-- temp table to hold final tag permission->role assignment
create temp table if not exists sec_tag (
role_id int NOT NULL,
tag_id int NOT NULL,
access_type varchar(50) NOT NULL,
CONSTRAINT PK_tag_sec
PRIMARY KEY (role_id, tag_id, access_type)
);
-- ============================================================
-- READ ACCESS
-- ============================================================
-- Read permission templates (GLOBAL, no %s):
-- put("tag:get", "view tag with id %s");
-- put("tag:search:get", "Resolve tag %s expression");
-- Note, we do not migrate anything here beacuse there are no
-- entity-specific permissions to assign
-- ------------------------------------------------------------
-- CLEAN UP READ PERMISSIONS
-- ------------------------------------------------------------
WITH read_permission_templates(template) AS (
VALUES
('tag:get'),
('tag:search:get')
)
DELETE FROM migration_poc.sec_permission_copy p
USING read_permission_templates t
WHERE p.value = t.template;
-- ============================================================
-- WRITE ACCESS
-- ============================================================
-- Write permission templates:
-- put("tag:%s:delete", "Delete tag");
-- put("tag:%s:put", "Update tag");
WITH write_permission_templates(template) AS (
VALUES
('tag:%s:delete'),
('tag:%s:put')
),
expanded_permissions AS (
SELECT
t.id AS tag_id,
r.id AS created_by_id,
replace(replace(wpt.template, '%s', t.id::text), '*', '\*') AS permission_pattern
FROM webapi.tag t
JOIN webapi.sec_user_role ur
ON ur.user_id = t.created_by_id
JOIN webapi.sec_role r
ON r.id = ur.role_id
AND r.system_role IS FALSE
JOIN webapi.sec_user u
ON u.id = ur.user_id
CROSS JOIN write_permission_templates wpt
WHERE r.name = u.login
),
role_permissions AS (
SELECT DISTINCT
rp.role_id,
p.value AS permission_value
FROM webapi.sec_role_permission rp
JOIN webapi.sec_permission p
ON p.id = rp.permission_id
),
matched_permissions AS (
SELECT DISTINCT
ep.tag_id,
rp.role_id,
ep.created_by_id
FROM role_permissions rp
JOIN expanded_permissions ep
ON rp.permission_value = ep.permission_pattern
)
INSERT INTO sec_tag (role_id, tag_id, access_type)
SELECT DISTINCT
role_id,
tag_id,
'WRITE'
FROM matched_permissions
WHERE role_id <> created_by_id;
-- ------------------------------------------------------------
-- CLEAN UP WRITE PERMISSIONS
-- ------------------------------------------------------------
WITH write_permission_templates(template) AS (
VALUES
('tag:%s:delete'),
('tag:%s:put')
),
permission_regex AS (
SELECT
'^' ||
replace(template, '%s', '[0-9]+') ||
'$' AS regex
FROM write_permission_templates
)
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- ============================================================
-- TOOL
-- ============================================================
-- temp table to hold final tool permission->role assignment
create temp table if not exists sec_tool (
role_id int NOT NULL,
tool_id int NOT NULL,
access_type varchar(50) NOT NULL,
CONSTRAINT PK_tool_sec
PRIMARY KEY (role_id, tool_id, access_type)
);
-- ============================================================
-- READ ACCESS
-- ============================================================
-- Read permission templates:
-- put("tool:%s:get", "View Tool with id = %s");
WITH read_permission_templates(template) AS (
VALUES
('tool:%s:get')
),
expanded_permissions AS (
SELECT
t.id AS tool_id,
r.id AS created_by_id,
replace(replace(rpt.template, '%s', t.id::text), '*', '\*') AS permission_pattern
FROM webapi.tool t
JOIN webapi.sec_user_role ur
ON ur.user_id = t.created_by_id
JOIN webapi.sec_role r
ON r.id = ur.role_id
AND r.system_role IS FALSE
JOIN webapi.sec_user u
ON u.id = ur.user_id
CROSS JOIN read_permission_templates rpt
WHERE r.name = u.login
),
role_permissions AS (
SELECT DISTINCT
rp.role_id,
p.value AS permission_value
FROM webapi.sec_role_permission rp
JOIN webapi.sec_permission p
ON p.id = rp.permission_id
),
matched_permissions AS (
SELECT DISTINCT
ep.tool_id,
rp.role_id,
ep.created_by_id
FROM role_permissions rp
JOIN expanded_permissions ep
ON rp.permission_value = ep.permission_pattern
)
INSERT INTO sec_tool (role_id, tool_id, access_type)
SELECT DISTINCT
role_id,
tool_id,
'READ'
FROM matched_permissions
WHERE role_id <> created_by_id;
-- ------------------------------------------------------------
-- CLEAN UP READ PERMISSIONS
-- ------------------------------------------------------------
WITH read_permission_templates(template) AS (
VALUES
('tool:%s:get')
),
permission_regex AS (
SELECT
'^' || replace(template, '%s', '[0-9]+') || '$' AS regex
FROM read_permission_templates
)
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- ============================================================
-- WRITE ACCESS
-- ============================================================
-- Write permission templates:
-- put("tool:%s:delete", "Delete Tool with id = %s");
WITH write_permission_templates(template) AS (
VALUES
('tool:%s:delete')
),
expanded_permissions AS (
SELECT
t.id AS tool_id,
r.id AS created_by_id,
replace(replace(wpt.template, '%s', t.id::text), '*', '\*') AS permission_pattern
FROM webapi.tool t
JOIN webapi.sec_user_role ur
ON ur.user_id = t.created_by_id
JOIN webapi.sec_role r
ON r.id = ur.role_id
AND r.system_role IS FALSE
JOIN webapi.sec_user u
ON u.id = ur.user_id
CROSS JOIN write_permission_templates wpt
WHERE r.name = u.login
),
role_permissions AS (
SELECT DISTINCT
rp.role_id,
p.value AS permission_value
FROM webapi.sec_role_permission rp
JOIN webapi.sec_permission p
ON p.id = rp.permission_id
),
matched_permissions AS (
SELECT DISTINCT
ep.tool_id,
rp.role_id,
ep.created_by_id
FROM role_permissions rp
JOIN expanded_permissions ep
ON rp.permission_value = ep.permission_pattern
)
INSERT INTO sec_tool (role_id, tool_id, access_type)
SELECT DISTINCT
role_id,
tool_id,
'WRITE'
FROM matched_permissions
WHERE role_id <> created_by_id;
-- ------------------------------------------------------------
-- CLEAN UP WRITE PERMISSIONS
-- ------------------------------------------------------------
WITH write_permission_templates(template) AS (
VALUES
('tool:%s:delete')
),
permission_regex AS (
SELECT
'^' || replace(template, '%s', '[0-9]+') || '$' AS regex
FROM write_permission_templates
)
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- ------------------------------------------------------------
-- SOURCE Permissions: note, WebAPI2.x made 'read permission schema'
-- cover read and write (generate) permissons on a source, so
-- translating read permissions in 2.x to WRITE permissisons in 3.x
-- ------------------------------------------------------------
-- TEMP TABLE TO HOLD ROLE -> SOURCE PERMISSIONS
CREATE TEMP TABLE sec_source (
role_id INT NOT NULL,
source_id INT NOT NULL,
access_type VARCHAR(50) NOT NULL,
CONSTRAINT PK_sec_source PRIMARY KEY(role_id, source_id, access_type)
);
-- WRITE Access Type (WebAPI 2.x read templates -> WRITE access)
WITH write_permission_templates(template) AS (
VALUES
('cohortdefinition:*:report:%s:get'),
('cohortdefinition:*:generate:%s:get'),
('cohortdefinition:*:cancel:%s:get'),
('vocabulary:%s:*:get'),
('vocabulary:%s:included-concepts:count:post'),
('vocabulary:%s:resolveConceptSetExpression:post'),
('vocabulary:%s:lookup:identifiers:post'),
('vocabulary:%s:lookup:identifiers:ancestors:post'),
('vocabulary:%s:lookup:mapped:post'),
('vocabulary:%s:lookup:recommended:post'),
('vocabulary:%s:compare:post'),
('vocabulary:%s:optimize:post'),
('vocabulary:%s:concept:*:get'),
('vocabulary:%s:concept:*:related:get'),
('vocabulary:%s:search:post'),
('vocabulary:%s:search:*:get'),
('cdmresults:%s:*:get'),
('cdmresults:%s:conceptRecordCount:post'),
('cdmresults:%s:*:*:get'),
('cdmresults:%s:clearcache:post'),
('cohortresults:%s:*:*:get'),
('cohortresults:%s:*:*:*:get'),
('cohortresults:%s:*:healthcareutilization:*:*:get'),
('cohortresults:%s:*:healthcareutilization:*:*:*:get'),
('ir:*:execute:%s:get'),
('ir:*:execute:%s:delete'),
('ir:*:info:%s:get'),
('ir:*:report:%s:get'),
('ir:%s:info:*:delete'),
('%s:person:*:get'),
('vocabulary:%s:lookup:sourcecodes:post'),
('cohort-characterization:*:generation:%s:post'),
('cohort-characterization:*:generation:%s:delete'),
('pathway-analysis:*:generation:%s:post'),
('pathway-analysis:*:generation:%s:delete'),
('vocabulary:%s:concept:*:ancestorAndDescendant:get'),
('source:%s:access')
),
expanded_permissions AS (
SELECT
s.source_id,
REPLACE(REPLACE(t.template, '%s', s.source_key), '*', '\*') AS permission_pattern
FROM webapi.source s
CROSS JOIN write_permission_templates t
),
role_permissions AS (
SELECT DISTINCT
rp.role_id,
p.value AS permission_value
FROM webapi.sec_role_permission rp
JOIN webapi.sec_permission p ON rp.permission_id = p.id
),
matched_permissions AS (
SELECT DISTINCT
ep.source_id,
rp.role_id
FROM role_permissions rp
JOIN expanded_permissions ep ON rp.permission_value = ep.permission_pattern
)
INSERT INTO sec_source(role_id, source_id, access_type)
SELECT DISTINCT
m.role_id,
m.source_id,
'WRITE' AS access_type
FROM matched_permissions m
;
-- CLEANUP: Remove old WebAPI 2.x permissions that matched source placeholders
-- Step 1: Define the write permission templates for sources
WITH write_permission_templates(template) AS (
VALUES
('cohortdefinition:*:report:%s:get'),
('cohortdefinition:*:generate:%s:get'),
('cohortdefinition:*:cancel:%s:get'),
('vocabulary:%s:*:get'),
('vocabulary:%s:included-concepts:count:post'),
('vocabulary:%s:resolveConceptSetExpression:post'),
('vocabulary:%s:lookup:identifiers:post'),
('vocabulary:%s:lookup:identifiers:ancestors:post'),
('vocabulary:%s:lookup:mapped:post'),
('vocabulary:%s:lookup:recommended:post'),
('vocabulary:%s:compare:post'),
('vocabulary:%s:optimize:post'),
('vocabulary:%s:concept:*:get'),
('vocabulary:%s:concept:*:related:get'),
('vocabulary:%s:search:post'),
('vocabulary:%s:search:*:get'),
('cdmresults:%s:*:get'),
('cdmresults:%s:conceptRecordCount:post'),
('cdmresults:%s:*:*:get'),
('cdmresults:%s:clearcache:post'),
('cohortresults:%s:*:*:get'),
('cohortresults:%s:*:*:*:get'),
('cohortresults:%s:*:healthcareutilization:*:*:get'),
('cohortresults:%s:*:healthcareutilization:*:*:*:get'),
('ir:*:execute:%s:get'),
('ir:*:execute:%s:delete'),
('ir:*:info:%s:get'),
('ir:*:report:%s:get'),
('ir:%s:info:*:delete'),
('%s:person:*:get'),
('vocabulary:%s:lookup:sourcecodes:post'),
('cohort-characterization:*:generation:%s:post'),
('cohort-characterization:*:generation:%s:delete'),
('pathway-analysis:*:generation:%s:post'),
('pathway-analysis:*:generation:%s:delete'),
('vocabulary:%s:concept:*:ancestorAndDescendant:get'),
('source:%s:access')
),
-- Step 2: Build regex patterns replacing %s with [^:]+ (any value except colon)
-- and escaping literal * characters
permission_regex AS (
SELECT
'^' ||
replace(replace(template, '%s', '[^:]+'),'*', '\*') ||
'$' AS regex
FROM write_permission_templates
)
-- Step 3: Delete any concrete permission values that match these patterns
DELETE FROM migration_poc.sec_permission_copy p
USING permission_regex r
WHERE p.value ~ r.regex;
-- Global Entitlements (permissions that greant global read/write to entities) and administrative tasks
--------------------------
-- Permissions for managing sources, tags, tools, role fall under admin rights
-- and are not entity specific.
-- The process to migrate these permissions are:
-- 1. Define new permissions in migration temp table
-- 2. Migrate each entitlement to the new permission wildcard structure via role assignments
-- 3. Reset sec_permission_seq to reset auto-number
-- Result: a temp table containing new entries for sec_permission
--------------------------
-------------------------------------
-- Define new permissions in a sec_permisson_migration table
-------------------------------------
create temp table sec_permission_migration (
id int NOT NULL,
value varchar(255) NOT NULL,
description varchar(255),
CONSTRAINT PK_sec_permission_id PRIMARY KEY (id),
CONSTRAINT uq_sec_permission_value UNIQUE (value)
);
create temp table sec_role_permission_migration (
role_id int NOT NULL,
permission_id int NOT NULL,
CONSTRAINT PK_sec_role_permission PRIMARY KEY (role_id, permission_id)
);
insert into sec_permission_migration(id, value, description)
select row_number() over(), value, description
FROM (
VALUES
('*', 'All Permissions'),
('admin', 'All Admin Permissions'),
('admin:source', 'Manage Sources'),
('admin:tags', 'Manage Tags'),
('admin:tools', 'Manage Tools'),
('admin:security', 'Manage users, roles, permissions'),
('admin:cache', 'View and manage chache functions'),
('create', 'Create any asset'),
('create:conceptset', 'Create concept sets'),
('create:cohort-definition', 'Create cohort definitions'),
('create:cohort-characterization', 'Create characterization designs'),
('create:feature-analysis', 'Create feature analysis'),
('create:incidence', 'Create incidence designs'),
('create:pathway', 'Create pathway designs'),
('create:reusable', 'Create reusable components'),
('read', 'Read any asset'),
('read:conceptset', 'Read concept sets'),
('read:cohort-definition', 'Read cohort definitions'),
('read:cohort-characterization', 'Read characterization designs'),
('read:feature-analysis', 'Read feature analysis'),
('read:incidence', 'Read incidence designs'),
('read:pathway', 'Read pathway designs'),
('read:reusable', 'Read reusable components'),
('read:source', 'Read source results'),
('write', 'Update any asset'),
('write:conceptset', 'Update concept sets'),
('write:cohort-definition', 'Update cohort definitions'),
('write:cohort-characterization', 'Update characterization designs'),
('write:feature-analysis', 'Update feature analysis'),
('write:incidence', 'Update incidence designs'),
('write:pathway', 'Update pathway designs'),
('write:reusable', 'Update reusable components'),
('write:source', 'Generate source results')
) p (value, description)
;
-- The remaining permissions are * permissions which we can map to the entitlement perms above
WITH perm_map AS (
SELECT DISTINCT
p.value AS from_perm,
CASE
/* ---------- ADMIN-Privs ---------- */
WHEN p.value ~ '^source:\*:(put|post|delete)$'
THEN 'admin:source'
WHEN p.value ~ '^(role:post|role:\*:(put|delete)|role:\*:users:\*:(put|delete)|role:\*:permissions:\*:(put|delete)|user:import:\*:(post|put|delete)|user:runas:post)$'
THEN 'admin:security'
WHEN p.value ~ '^(tag:\*:(put|delete)|tag:(post|management)|tag:multi(Assign|Unassign):post)$'
THEN 'admin:tags'
WHEN p.value ~ '^(tool:\*:(put|delete)|tool:(post|put))$'
THEN 'admin:tools'
WHEN p.value ~ '^(cache:.*|cdmresults:clearcache:post)$'
THEN 'admin:cache'
/* ---------- CREATE ---------- */
WHEN p.value ~ '^conceptset:post$'
THEN 'create:conceptset'
WHEN p.value ~ '^cohortdefinition:post$'
THEN 'create:cohort-definition'
WHEN p.value ~ '^cohort-characterization:post$'
THEN 'create:cohort-characterization'
WHEN p.value ~ '^feature-analysis:post$'
THEN 'create:feature-analysis'
WHEN p.value ~ '^ir:post$'
THEN 'create:incidence'
WHEN p.value ~ '^pathway-analysis:post$'
THEN 'create:pathway'
WHEN p.value ~ '^reusable:post$'
THEN 'create:reusable'
/* ---------- READ ---------- */
WHEN p.value ~ '^conceptset:\*:get$'
THEN 'read:conceptset'
WHEN p.value ~ '^cohortdefinition:\*:get$'
THEN 'read:cohort-definition'
WHEN p.value ~ '^cohort-characterization:\*:get$'
THEN 'read:cohort-characterization'
WHEN p.value ~ '^feature-analysis:\*:get$'
THEN 'read:feature-analysis'
WHEN p.value ~ '^ir:\*:get$'
THEN 'read:incidence'
WHEN p.value ~ '^pathway-analysis:\*:get$'
THEN 'read:pathway'
WHEN p.value ~ '^reusable:\*:get$'
THEN 'read:reusable'
/* ---------- WRITE (PUT / DELETE) ---------- */
WHEN p.value ~ '^conceptset:\*:(put|delete)$'
THEN 'write:conceptset'
WHEN p.value ~ '^cohortdefinition:\*:(put|delete)$'
THEN 'write:cohort-definition'
WHEN p.value ~ '^cohort-characterization:\*:(put|delete)$'
THEN 'write:cohort-characterization'
WHEN p.value ~ '^feature-analysis:\*:(put|delete)$'
THEN 'write:feature-analysis'
WHEN p.value ~ '^ir:\*:(put|delete)$'
THEN 'write:incidence'
WHEN p.value ~ '^pathway-analysis:\*:(put|delete)$'
THEN 'write:pathway'
WHEN p.value ~ '^reusable:\*:(put|delete)$'
THEN 'write:reusable'
ELSE NULL
END AS to_perm
FROM webapi.sec_permission p
),
role_permissions AS (
SELECT DISTINCT
rp.role_id,
p.value AS permission_value
FROM webapi.sec_role_permission rp
JOIN webapi.sec_permission p ON p.id = rp.permission_id
),
matched_permissions AS (
select
rp.role_id,
p.id as permission_id,
p.value,
pm.from_perm,
pm.to_perm
FROM role_permissions rp
JOIN perm_map pm ON rp.permission_value = pm.from_perm
join sec_permission_migration p on p.value = pm.to_perm
join webapi.sec_role r on r.id = rp.role_id
)
INSERT INTO sec_role_permission_migration (role_id, permission_id)
SELECT DISTINCT
mp.role_id,
mp.permission_id
FROM matched_permissions mp;
select r.name, p.value
from sec_role_permission_migration srp
join sec_permission_migration p on srp.permission_id = p.id
join webapi.sec_role r on srp.role_id = r.id
order by p.value, r.name;
-- cleanup matched permissions
WITH perm_map AS (
SELECT DISTINCT
p.value AS from_perm,
CASE
/* ---------- ADMIN-Privs ---------- */
WHEN p.value ~ '^source:\*:(put|post|delete)$'
THEN 'admin:source'
WHEN p.value ~ '^(role:post|role:\*:(put|delete)|role:\*:users:\*:(put|delete)|role:\*:permissions:\*:(put|delete)|user:import:\*:(post|put|delete)|user:runas:post)$'
THEN 'admin:security'
WHEN p.value ~ '^(tag:\*:(put|delete)|tag:(post|management)|tag:multi(Assign|Unassign):post)$'
THEN 'admin:tags'
WHEN p.value ~ '^(tool:\*:(put|delete)|tool:(post|put))$'
THEN 'admin:tools'
WHEN p.value ~ '^(cache:.*|cdmresults:clearcache:post)$'
THEN 'admin:cache'
/* ---------- CREATE ---------- */
WHEN p.value ~ '^conceptset:post$'
THEN 'create:conceptset'
WHEN p.value ~ '^cohortdefinition:post$'
THEN 'create:cohort-definition'
WHEN p.value ~ '^cohort-characterization:post$'
THEN 'create:cohort-characterization'
WHEN p.value ~ '^feature-analysis:post$'
THEN 'create:feature-analysis'
WHEN p.value ~ '^ir:post$'
THEN 'create:incidence'
WHEN p.value ~ '^pathway-analysis:post$'
THEN 'create:pathway'
WHEN p.value ~ '^reusable:post$'
THEN 'create:reusable'
/* ---------- READ ---------- */
WHEN p.value ~ '^conceptset:\*:get$'
THEN 'read:conceptset'
WHEN p.value ~ '^cohortdefinition:\*:get$'
THEN 'read:cohort-definition'
WHEN p.value ~ '^cohort-characterization:\*:get$'
THEN 'read:cohort-characterization'
WHEN p.value ~ '^feature-analysis:\*:get$'
THEN 'read:feature-analysis'
WHEN p.value ~ '^ir:\*:get$'
THEN 'read:incidence'
WHEN p.value ~ '^pathway-analysis:\*:get$'
THEN 'read:pathway'
WHEN p.value ~ '^reusable:\*:get$'
THEN 'read:reusable'
/* ---------- WRITE (PUT / DELETE) ---------- */
WHEN p.value ~ '^conceptset:\*:(put|delete)$'
THEN 'write:conceptset'
WHEN p.value ~ '^cohortdefinition:\*:(put|delete)$'
THEN 'write:cohort-definition'
WHEN p.value ~ '^cohort-characterization:\*:(put|delete)$'
THEN 'write:cohort-characterization'
WHEN p.value ~ '^feature-analysis:\*:(put|delete)$'
THEN 'write:feature-analysis'
WHEN p.value ~ '^ir:\*:(put|delete)$'
THEN 'write:incidence'
WHEN p.value ~ '^pathway-analysis:\*:(put|delete)$'
THEN 'write:pathway'
WHEN p.value ~ '^reusable:\*:(put|delete)$'
THEN 'write:reusable'
ELSE NULL
END AS to_perm
FROM webapi.sec_permission p
)
DELETE FROM migration_poc.sec_permission_copy p
USING perm_map m
WHERE to_perm is not null and p.value = m.from_perm;
select r.name, p.value
from sec_role_permission_migration rp
join sec_permission_migration p on rp.permission_id = p.id
join webapi.sec_role r on r.id = rp.role_id
order by r.name, p.value;
select * from migration_poc.sec_permission_copy order by value; -- what's left are general permissions we need to figure out.
-- what roles do remaining permissions belong to?
select m.*, r.name
from migration_poc.sec_permission_copy m
join webapi.sec_role_permission rp on rp.permission_id = m.id
join webapi.sec_role r on r.id = rp.role_id
order by m.value, r.name;
-- debug query
select u.login as created_by, c.concept_set_id, r.name as assigned_to, access_type as with_permission
from sec_concept_set sc
join webapi.concept_set c on c.concept_set_id = sc.concept_set_id
join webapi.sec_user u on c.created_by_id = u.id
join webapi.sec_role r on sc.role_id = r.id
order by c.concept_set_id;
select u.login as created_by, c.id, r.name as assigned_to, access_type as with_permission
from sec_cohort_definition sc
join webapi.cohort_definition c on c.id = sc.cohort_definition_id
join webapi.sec_user u on c.created_by_id = u.id
join webapi.sec_role r on sc.role_id = r.id
order by cohort_definition_id;
select u.login as created_by, c.id, r.name as assigned_to, access_type as with_permission
from sec_cohort_characterization sc
join webapi.cohort_characterization c on c.id = sc.cohort_characterization_id
join webapi.sec_user u on c.created_by_id = u.id
join webapi.sec_role r on sc.role_id = r.id
order by c.id;
select u.login as created_by, c.id, r.name as assigned_to, access_type as with_permission
from sec_incidence_rate sc
join webapi.ir_analysis c on c.id = sc.ir_id
join webapi.sec_user u on c.created_by_id = u.id
join webapi.sec_role r on sc.role_id = r.id
order by c.id;
select u.login as created_by, c.id, r.name as assigned_to, access_type as with_permission
from sec_feature_analysis sc
join webapi.fe_analysis c on c.id = sc.fe_analysis_id
join webapi.sec_user u on c.created_by_id = u.id
join webapi.sec_role r on sc.role_id = r.id
order by c.id;
select u.login as created_by, c.id, r.name as assigned_to, access_type as with_permission
from sec_pathway_analysis sc
join webapi.pathway_analysis c on c.id = sc.pathway_analysis_id
join webapi.sec_user u on c.created_by_id = u.id
join webapi.sec_role r on sc.role_id = r.id
order by c.id;
select u.login as created_by, c.id, r.name as assigned_to, access_type as with_permission
from sec_reusable sc
join webapi.reusable c on c.id = sc.reusable_id
join webapi.sec_user u on c.created_by_id = u.id
join webapi.sec_role r on sc.role_id = r.id
order by c.id;
select u.login as created_by, c.id, r.name as assigned_to, access_type as with_permission
from sec_tag sc
join webapi.tag c on c.id = sc.tag_id
join webapi.sec_user u on c.created_by_id = u.id
join webapi.sec_role r on sc.role_id = r.id
order by c.id;
select u.login as created_by, c.id, r.name as assigned_to, access_type as with_permission
from sec_tool sc
join webapi.tool c on c.id = sc.tool_id
join webapi.sec_user u on c.created_by_id = u.id
join webapi.sec_role r on sc.role_id = r.id
order by c.id;
select u.login as created_by, c.source_id, r.name as assigned_to, access_type as with_permission
from sec_source sc
join webapi.source c on c.source_id = sc.source_id
join webapi.sec_user u on c.created_by_id = u.id
join webapi.sec_role r on sc.role_id = r.id
order by c.source_id;
-- permission count was ~ 165,000 permissions
-- new permisison count:
select count(*)
from (
select 1 from sec_concept_set
UNION ALL
select 1 from sec_cohort_definition
UNION ALL
select 1 from sec_cohort_characterization
UNION ALL
select 1 from sec_feature_analysis
UNION ALL
select 1 from sec_pathway_analysis
UNION ALL
select 1 from sec_reusable
UNION ALL
select 1 from sec_tag
UNION ALL
select 1 from sec_tool
UNION ALL
select 1 from sec_source
) c