forked from IQSS/dataverse
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathmigrate_permissions.sql
More file actions
122 lines (106 loc) · 4.55 KB
/
Copy pathmigrate_permissions.sql
File metadata and controls
122 lines (106 loc) · 4.55 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
-- reference queries for duplicate roles in vdc_role
-- (created when user accounts where merged)
-- NOTE: may need to run multiple times
/*
select * from _dvn3_vdcrole
where vdcuser_id || '|' || vdc_id || '|' || role_id in
(
select vdcuser_id || '|' || vdc_id || '|' || role_id from _dvn3_vdcrole
group by vdcuser_id, vdc_id, role_id
having count(*) > 1
)
order by vdcuser_id, vdc_id, role_id
delete from _dvn3_vdcrole where id in
(
select max(id) from _dvn3_vdcrole
group by vdcuser_id, vdc_id, role_id
having count(*) >1
order by max(id)
)
*/
-----------------------
-- dataverses role assignments
-----------------------
-- admin (from the vdcnetwork creator)
insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
select '@'|| useridentifier, vdcn.id, dr.id
from _dvn3_vdcnetwork vdcn, authenticateduser, dataverserole dr
where vdcn.creator_id = authenticateduser.id
and dr.alias='admin';
-- admin
insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
select '@'|| useridentifier, vdc_id, dr.id
from _dvn3_vdcrole, authenticateduser, dataverserole dr
where _dvn3_vdcrole.vdcuser_id = authenticateduser.id
and role_id=3 and dr.alias='admin';
-- curator
insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
select '@'|| useridentifier, vdc_id, dr.id
from _dvn3_vdcrole, authenticateduser, dataverserole dr
where _dvn3_vdcrole.vdcuser_id = authenticateduser.id
and role_id=2 and dr.alias='curator';
-- contributor
insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
select '@'|| useridentifier, vdc_id, dr.id
from _dvn3_vdcrole, authenticateduser, dataverserole dr
where _dvn3_vdcrole.vdcuser_id = authenticateduser.id
and role_id=1 and dr.alias='dsContributor';
-- member
insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
select '@'|| useridentifier, vdc_id, dr.id
from _dvn3_vdcrole, authenticateduser, dataverserole dr
where _dvn3_vdcrole.vdcuser_id = authenticateduser.id
and role_id=4 and dr.alias='member';
-- groups (as members)
insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
select '&'|| groupalias, vdcs_id, dr.id
from _dvn3_vdc_usergroup, explicitgroup, dataverserole dr
where _dvn3_vdc_usergroup.allowedgroups_id = explicitgroup.id
and dr.alias='member';
-----------------------
-- dataset role assignments
-----------------------
-- contributor (from the study creator)
insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
select '@'|| useridentifier, ds.id, dr.id
from _dvn3_study s, authenticateduser, dataverserole dr, dataset ds
where s.creator_id = authenticateduser.id
and ds.authority = s.authority
and ds.protocol = s.protocol
and ds.identifier = s.studyid
and dr.alias='editor';
-- member
insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
select '@'|| useridentifier, ds.id, dr.id
from _dvn3_study_vdcuser, _dvn3_study s, authenticateduser, dataverserole dr, dataset ds
where _dvn3_study_vdcuser.allowedusers_id = authenticateduser.id
and _dvn3_study_vdcuser.studies_id = s.id
and ds.authority = s.authority
and ds.protocol = s.protocol
and ds.identifier = s.studyid
and dr.alias='member';
-- groups (as members)
insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
select '&'|| groupalias, ds.id, dr.id
from _dvn3_study_usergroup, _dvn3_study s, explicitgroup, dataverserole dr, dataset ds
where _dvn3_study_usergroup.allowedgroups_id = explicitgroup.id
and _dvn3_study_usergroup.studies_id = s.id
and ds.authority = s.authority
and ds.protocol = s.protocol
and ds.identifier = s.studyid
and dr.alias='member';
-----------------------
-- file role assignments
-----------------------
insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
select '@'|| useridentifier, studyfiles_id, dr.id
from _dvn3_studyfile_vdcuser, authenticateduser, dataverserole dr
where _dvn3_studyfile_vdcuser.allowedusers_id = authenticateduser.id
and _dvn3_studyfile_vdcuser.studyfiles_id in (select id from datafile)
and dr.alias='fileDownloader';
insert into roleassignment ( assigneeidentifier, definitionpoint_id, role_id)
select '&'|| groupalias, studyfiles_id, dr.id
from _dvn3_studyfile_usergroup, explicitgroup, dataverserole dr
where _dvn3_studyfile_usergroup.allowedgroups_id = explicitgroup.id
and _dvn3_studyfile_usergroup.studyfiles_id in (select id from datafile)
and dr.alias='fileDownloader';