Project

General

Profile

metacat / src / convert_denyFirst.sql @ 8556

1
/**
2
 * Use parts of this script to judiciously remove/update denyFirst access rules before upgrading to Metacat 2.0.0
3
 * It is important to examine the access blocks that use denyFirst to be sure that you do not end up granting access to 
4
 * members of groups who should not have access to objects that their group might have access to.
5
 * The default behavior for Metacat is to deny public access when it is not explicitly listed as allowed, therefore "deny public" rules are
6
 * superfluous.
7
 */
8

    
9
-- Analyze the number of rules that need to be addressed:
10
select principal_name, perm_type, count(*) 
11
from xml_access 
12
where perm_order = 'denyFirst' 
13
and perm_type = 'deny' 
14
and principal_name != 'public' 
15
group by principal_name, perm_type;
16

    
17
-- Look at the complete set of records for anything that might need special attention
18
-- Pay special attention to group names where it makes the most sense to use a denyFirst policy
19
select * from xml_access 
20
where docid in (select docid from xml_access where perm_order = 'denyFirst' and perm_type = 'deny' and principal_name != 'public')
21
order by docid, principal_name, permission;
22

    
23
-- Then do these steps to update rules to use allowFirst only
24
-- 1a.) Look at the unnecessary public deny rules:
25
select count(*) 
26
from xml_access 
27
where perm_order = 'denyFirst' 
28
and perm_type = 'deny' 
29
and principal_name = 'public';
30
-- 1b.) Delete the unnecessary public deny rules (this is implicit behavior):
31
delete from xml_access 
32
where perm_order = 'denyFirst' 
33
and perm_type = 'deny' 
34
and principal_name = 'public';
35

    
36
-- 2a.) Examine the non-public deny rules for anything special:
37
select * 
38
from xml_access 
39
where perm_order = 'denyFirst' 
40
and perm_type = 'deny' 
41
and principal_name != 'public';
42
-- 2b.) Delete the non-public deny rules (after examining them!):
43
delete from xml_access 
44
where perm_order = 'denyFirst' 
45
and perm_type = 'deny' 
46
and principal_name != 'public';
47

    
48
-- 3a.) Summary of denyFirst rules
49
select perm_type, count(*) 
50
from xml_access 
51
where perm_order = 'denyFirst' 
52
group by perm_type;
53
-- 3b.) Update all denyFirst rules to be allowFirst
54
update xml_access 
55
set perm_order = 'allowFirst' 
56
where perm_order = 'denyFirst';