hi bront,

ive struggled with many of the same issues in our modules. it is fairly easy to make a'security through obscurity' solution, where you do something like only exposing a filtered query over a raw table (essentially your suggestion); however, because a given folder within LabKey is generally either all-or-none for read permissions (with study datasets being an exception) that generally means the user could read the raw data if they could find it, either unintentionally (like being shown the wrong UI), or intentionally (like digging through schema browser or the wrong kind of webpart).

there are 2 better options, i think:

1) Linked Schemas. We had this feature added to support the scenario where you have a folder with raw data (like a core facility that runs an instrument), and another group of users who needs to see a subset of the raw data (like a client who views data filtered on userId). in this scenario you have one container with the raw data. In a second container you go to schema browser and created a linked schema. you define the source container and also which queries to expose. If you combined this with your original idea of making a query that filters on UserId, then I think you have a genuinely secure solution. Linked Schemas can be defined from XML, and we have a few examples checked in. See employeeData.template.xml in the EHRComplianceDB modules (or ctl+shift+n, then template.xml in intellij).

2) because you saw you're writing a java module, i'll throw this answer out there: when I need to solve this problem now I virtually always make a custom UserSchema and modify there. You can make your own custom permission/role, which will then appear in the normal folder permissions UI. For example, I have a lot of scenarios where I create a 'Data Admin' type role, which is used to enforce the ability to edit specific reference tables. See VariantDBUserSchema for one of the simplest examples. CustomPermissionsTable is something I wrote for this you're welcome to try. You add an additional permission that will be tested for each of the standard actions (Read, Insert, Update, Delete). I would use this scenario instead of option 1 when you have data that most naturally lives within a single container, but you have a class of users that should have elevated edit permissions relative to others. in the case of a lab, you might have lots of people entering/editing the data; however, you only want a few people able to modify reference tables or lists of allowable values.

-ben

Ben,

Thanks for your quick and thorough response. I am glad to know that someone else out there is struggling with a similar issue.

I will dig into your suggestions and try to implement.

Thanks again,

bront

in fairness to LK here, 'struggle' might not be the right word. it's just the all-or-none aspect of permissions within a single container leaves something to be desired when making anything but the simplest of apps. having worked w/ LK a long time now, i'd say it's really important for the developer/admin to understand how container/security work within LK; however, I try to make which folder you're in as invisible as i can to the user.

Ben,

I think that the second option you suggested sounds more in line with what I am trying to accomplish, but I am wondering if it is enough. I need to give users read as well as write permissions on a table, but their reads have to be limited to the rows they've inserted. Will a custom permission/role help with that?

Also, and sorry to ask what I think may be a dumb question, but I am not sure where to locate VariantDBUserSchema.

Thanks for your help.

bront

Fair enough on the use of the term "struggle." ;-)

I thought using custom-queries with the userid() would be sufficient, but then I began to have some doubts, which I see are not unfounded.

I then considered writing custom selects in Java, matching userids(), but then found that I couldn't convert a resultset to JSON for the ExtJs store to consume.

So, now I sort of stuck.

I do appreciate your insight.

b

here's a few other idea, which are probably more complicated:

1) make 2 tables at the user schema level. you have the 'real' table, which is what is exposed now. make this table non-editable to anyone.

then either:

2a) make a regular query that has a WHERE clause that appends a filter by userid. see ismemberof() in LK SQL for one option. this way it can get removed (unlike user filters). to this query, add custom buttons. these buttons could test permissions, and enforce row-level security. this would have problems exposing row-level update UI though.

2b) expose a table that only will ever show rows belonging to the current user. to do this, at the user schema level, expose the original table twice. expose the regular table (ie. as in option #1). then also expose a clone of this table that is filtered on UserId. This new table will only show only those rows belonging to the currently logged in User. LookupSetTable or LabworkType table in EHR module (see also EHRUserSchema) are roughly what you'd want. this new table would behave exactly like a regular LK table; however, it would only give the user the opportunity to edit their rows. unfortunately, it also only shows them their rows.

The above gives you one table for reading and one for editing. If you need to have a single table showing all records, but conditionally showing the EDIT link, then my only idea is:

to your original table, hide the normal edit UI (which always appears), and then add a custom java column that only shows the 'EDIT' link for rows belonging to the current users. to hide edit UI, make <updateURL></updateURL> empty. to your table, add a new AliasedColumn and make a java DisplayColumn for this new column. If you're interested in this route i'll write more. This would handle the UI part if rendering a DataRegion; however, you'd want to actually enforce edit permissions on the server. That's important b/c otherwise anyone could still edit rows through any other client API path. Ideas are:

a) create a JS trigger script and test/enforce permissions there. this would be simplest, i think. check out employeecategory.js as a simple example of what these trigger scripts are. in your example, you'd make a beforeUpdate(), and within this you'd test whether the current UserId matches the field in the oldRow (ie. original row) that corresponds to that User. if not, throw an error.

b) write your own java UpdateService. you could do something similar to above.

I've done things like both of these in different places.

Ben,

This is great. You've given me a lot to consider. Thank you so much for all of your help and ideas.

bront