[ome-users] Group permissions

[Josiah Johnston]

Here's my best take on an upgrade script to fix the group permissions 
problem. It's been running for a week here at IICBU without bug reports 
from our users or developers. Any comments on whether this should be 
rolled into an automated database upgrade would be appreciated. Further 
details can be found at:
	http://bugs.openmicroscopy.org.uk/show_bug.cgi?id=618

BEGIN;
-- First, set the module_executions' group to the owner's group.
-- This handles most of the cases
UPDATE module_executions SET group_id = experimenters.group_id
WHERE experimenters.attribute_id = module_executions.experimenter_id AND
       module_executions.group_id is NULL;
-- Now, change the group ownership of MEX's that produced experimenters 
to -- those experimenter's groups. An experimenter is owned by the 
admin who
-- created it rather than owning itself. Ideally, this shouldn't be 
necessary,
-- but in reality it may be.
UPDATE module_executions SET group_id = experimenters.group_id
WHERE experimenters.module_execution_id = 
module_executions.module_execution_id;
-- Now, change the group ownership of MEX's that produced groups to -- 
those groups. UPDATE module_executions SET group_id = 
groups.attribute_id
WHERE groups.module_execution_id = 
module_executions.module_execution_id;
-- Now, change the group ownership of MEX's that linked experimenters 
to -- groups so that the links are visible to the groups.
UPDATE module_executions SET group_id = experimenter_group_map.group_id
WHERE experimenter_group_map.module_execution_id = 
module_executions.module_execution_id;
-- Now, give everyone permission to see repositories. This has the side 
affect
-- of allowing everyone to see what came in during import. That 
includes: the
-- superuser, the superuser's group, and several original files.
UPDATE module_executions set group_id=NULL
WHERE module_executions.module_execution_id = 
repositories.module_execution_id;
-- The final step will not apply universally. It gives everyone 
permission to -- see FilenamePatterns, the instructions for stacking 
images together during
-- import based on patterns in their names.
UPDATE module_executions set group_id=NULL
WHERE module_executions.module_execution_id = 
filename_pattern.module_execution_id;
COMMIT;

-Josiah