Allowing differential access within a database

From ICISWiki

A problem has been brought up at AAFC and at the University of Queensland with the need for differential access of data within a local database.

Agriculture and Agri-Food Canada (AAFC)

At AAFC, the situation is that certain breeders do not want to share all of their data across AAFC; they would like to keep their most recent work restricted to just their own research centre and project collaborators. Over time, this data will become open to other research centres and eventually, it will become public.
This creates the situation where 3 levels of restrictions are needed:

Level 1: Data has been published and is public for international sharing (Uploaded to the Central Database)

Level 2: Data has not been published, but can be shared within an organization

Level 3: Data has not been published, and can only be shared within the institute of the breeder and collaborators at other institutes working on the same project

Right now this problem has only been with DMS data, but very soon we will deal with the same situation with Pedigree information in the GMS (as Sandra has run into below).

University of Queensland

At the University of Queensland, the problem is as follows (from an email from Sandra Micallef):
At the University we do not run any breeding programs as such, but we do get data from different sources, some data is available only to a couple of institutes (usually involved in the same project), and not to others. – how can you have a one database with all the data from all the sources, and control access to different users to different parts of the data?

Comments/Suggestions

Shawn Yates (AAFC):
At AAFC, I have dealt with this situation by having different locals at each research centre, and because there is no Central Wheat DMS database being shared within the ICIS community, I use the Central DMS (which I will call an AAFC Central DMS) to store all data that can be shared across AAFC research centres. Each of the local databases contain only data that is restricted to that particular research centre.

The problem with this solution is:

1) I am constantly loading 1 or 2 Studies that can be viewed across AAFC research centres into a blank Local database and then going through the 80+ queries to load it into the AAFC Central DMS. A very inefficient and time-consuming process.

2) If an error occurs in one of those studies loaded into the AAFC Central DMS, it is nearly impossible to remove the erroneous study from the Central. Therefore, I have to make backups of the AAFC Central before every study is loaded to it.

3) If down the road a Central Wheat DMS is distributed among ICIS wheat users, I will have to create a new installation of ICIS for AAFC users to use it. A minor problem, but one that creates an extra step for the user especially if they want to compare data for an Australian or CIMMYT line with and AAFC line, for instance.

4) When breeders from 2 Research centres are collaborating, I have to load the studies from their project into each of their locals, and then remember to update each local as new data or edits are made. This can be a very confusing task when a number of breeders are collaborating.

My proposal is as follows:

1) Create a new field in the GERMPLASM and STUDY tables, called SHAREID

2) If SHAREID is blank then the study or germplasm is available to all users and institutions

3) To restrict the data to certain users, enter a "U:" followed by the USERID of the user Ex) U:-1, for User -1.

4) To restrict the data to a certain institute, enter a "I:" followed by the INSTITID Ex) I:-1, for Institute -1.

5) Create a module in SetGen to set the SHAREID for each Germplasm, or for a list of Germplasm

6) Create a module in the DMS Workbook that reads each STUDY for a CONSTANT called SHAREID and if it is missing, then the Study is deemed accessable by all users, otherwise it restricts accordingly.

This solution would allow for data access as it is restricted to just the breeder, then his research centre and finally to everyone.