per-column update access?

Is it possible to restrict users to updating only certain columns of data in IJC? I don't see any support for this in the security policies.





Seems like MySQL and Oracle, but not Derby, can grant update permissions per column, but it's not clear how IJC will behave in this situation - does it disable editing of that column if the connected DB user does not have update privs for a column? What happens if the user has UPDATE but not INSERT privs on a table?





http://db.apache.org/derby/docs/10.3/ref/rrefsql9241891.html

IJC currently only supports specifying access levels at the database level.


You set up each user with rights to see data, edit data or edit the database schema as you need, but those rights apply to the whole database.





We do plan to add finer grain security in the future. With this you will be able to specify different rights to an individual table or an individual column.





Tim,

Any advancements on this topic with the new release 2.4.0 ?





I have created a shared project with tables for structures, adme, standard assays ...





I want to let the biologist update assay tables, and the registration people granting upated privs to the structure table.





Would this be possible?





Robert

We do plan to extend the access control to allow permissions to be defined on a per table or per column level as a numer of users have asked for this.


We don't currently have a precise timeline for this, but we do see this as an important feature that we want to add.





Tim

We have looked into this in more detail now.


The feature is quite complex, but important to many users. We plan to implement it in IJC 3.1 which is expected sometime in Q3 of this year.





Tim

Hi Tim,








I want to suggest a workaround for this:





For each data contributor an oracle database user (oracle_contributor) and a corresponding Instant Jchem database (contributing_ijc db) with an oracle table (contributing_table) is created. An IJC user (ijc_contributor) is having read/write access to this contributing_table.





The ijc_contributor has been granted a simple user role (read access) in an "IIJC_master" database (with a seperate oracle user), where the contributing table is popped into the schema via a materialized view of the contributing_table.





In this way one can plug together many data contributors having write grants on their data domain only, but can still see the whole picture of a project when loging on to the IJC_master database with read access.





I have implemented a small test case with IJC 2.4 for this and it is working so far. I would be interested to know if someone has experience with such a setup in a productive database.








Cheers, Robert

Robert,


Thanks for the suggestion. It seems to bea creative solution for this.


I'm not aware that this has been done before.





One thing to note is that we are currently improving IJCs ability to handle views and materailised views, so better support for things like this is coming.





Tim

In IJC 3.0 it is now possible to configure editablity on a per-column basis.

Tim

Has there been any change to this in version 5.3.1?

Specifically, I am looking for table specific user access. If I understand correctly, granting table specific privileges to users on the MySql side will gum up the works on the IJC/JCWS side.

Aside from having entirely separate databases/schemas for the tables which only have select user access (read-only for some, read-write for others), is there another way to control this with a single database?

Thanks!

There were no changes in 5.3.1 in this area.

Since 3.0 you can disallow editing of specific columns, but this is for all users. You can back this up with permissions in the DB should you wish, but this will only give you extra certainty.

Currently there is no way to defined different permisions for different users on a column by column or table by table basis. We do plan to add this as a future feature to IJC.

One possibility with MySQL might be to create 2 MySQL logins, one with all privileges and one with restricted privileges, and give each user the appropriate details for their connection to the database. We haven't tested this, but it sounds like it should work. Note that if you try this all users will need write privileges to some of the IJC_* tables as user related information is written to these tables.

Tim