V14.0.0.20190618__new_permission_model.sql 3.5 KB
Newer Older
1
2
3
--remove artifacts (privileges to non-existing projects)
delete from privilege_table where (type = 'VIEW_PROJECT' or type ='EDIT_COMMENTS_PROJECT') and not id_object in (select id from project_table); 

4
5
6
alter table privilege_table rename column id_object to object_id;
delete from privilege_table where level = 0;
alter table privilege_table drop column level;
7
alter table privilege_table drop column privilege_class_type_db;
8

9
10
insert into privilege_table (type, object_id)
values ('IS_ADMIN',  null);
11

12
13
insert into privilege_table (type, object_id)
values ('IS_CURATOR',  null);
14

15
16
insert into privilege_table (type, object_id)
values ('CAN_CREATE_OVERLAYS',  null);
17

18
insert into privilege_table (type, object_id)
19
20
select 'READ_PROJECT', id
from project_table;
21

22
insert into privilege_table (type, object_id)
23
24
select 'WRITE_PROJECT', id
from project_table;
25

26
27
create table user_privilege_map_table (
    user_id integer not null references user_table(id),
28
    privilege_id integer not null references privilege_table(id)
29
30
);

31
32
insert into user_privilege_map_table (user_id, privilege_id)
select s1.user_id, s2.id
33
from (select user_id, object_id from privilege_table where type = 'VIEW_PROJECT') s1
34
inner join (select id, object_id from privilege_table where type = 'READ_PROJECT') s2
35
on s1.object_id = s2.object_id;
36

37
38
39
40
41
42
insert into user_privilege_map_table (user_id, privilege_id)
select s1.user_id, s2.id 
from (select user_id, object_id from privilege_table where type = 'VIEW_PROJECT' and user_id in (select user_id from privilege_table where type = 'ADD_MAP')) s1
inner join (select id, object_id from privilege_table where type = 'WRITE_PROJECT') s2
on s1.object_id = s2.object_id;

43
insert into user_privilege_map_table (user_id, privilege_id)
44
select user_id, (select id from privilege_table where type = 'IS_ADMIN')
45
from privilege_table where type = 'USER_MANAGEMENT';
46

47
insert into user_privilege_map_table (user_id, privilege_id)
48
select user_id, (select id from privilege_table where type = 'IS_CURATOR')
49
from privilege_table where type = 'ADD_MAP';
50

51
insert into user_privilege_map_table (user_id, privilege_id)
52
select user_id, (select id from privilege_table where type = 'CAN_CREATE_OVERLAYS')
53
from privilege_table where type = 'CUSTOM_LAYOUTS';
54

55
56
57
58
59
60
61
62
63
64
65
delete from privilege_table where type = 'VIEW_PROJECT'
                               or type = 'ADD_MAP'
                               or type = 'EDIT_COMMENTS_PROJECT'
                               or type = 'PROJECT_MANAGEMENT'
                               or type = 'USER_MANAGEMENT'
                               or type = 'CUSTOM_LAYOUTS'
                               or type = 'LAYOUT_VIEW'
                               or type = 'CONFIGURATION_MANAGE'
                               or type = 'LAYOUT_MANAGEMENT'
                               or type = 'MANAGE_GENOMES'
                               or type = 'MANAGE_PLUGINS';
66

67
delete from user_privilege_map_table t1 using user_privilege_map_table t2
68
where t1.CTID != t2.CTID
69
  and t1.user_id = t2.user_id
70
  and t1.privilege_id = t2.privilege_id;
71

72
alter table privilege_table drop column user_id;
73

74
75
76
alter table privilege_table add constraint unique_rows unique (type, object_id);

alter table user_privilege_map_table add primary key (user_id, privilege_id);
Sascha Herzinger's avatar
Sascha Herzinger committed
77
78
79


-- objectId is now a String to account for non DB generated ids
80
81
82
83
alter table privilege_table alter column object_id type varchar;

-- change id to project_id for project prvileges
update privilege_table set object_id = (select project_id from project_table where id::text = object_id) where object_id is not null;