Consider a scenario where I have 10 columns in a table. The first 5 columns can be updated by one group of ppl and last 5 can be updated by another group.Each group will have access only to the fields that belong to them.Within a group I have to ensure that one person of a group should not override the changes made by other person in the same group. Can anybody suggest how can I accomplish this?
I don�t think there is any standard way to share your table columns between different groups, using any kind of container or database settings. Hence you have to do this entire job programmatically. I would recommend you using standard J2EE security and use global roles for assigning privileges to a group or another. At runtime you should be able to decide pretty easily what data one group of users can access or not, using the isUserInRole method for example. The second problem you face it�s about data integrity and here you have enough solutions. The most reliable one is to use appropriate transaction isolation levels. You might also review the version control design pattern for usescases that span more than one transaction. Of course this is a very high level overview and is entirely up to you to come up with a good design. Regards.
Originally posted by Valentin Tanase: I don�t think there is any standard way to share your table columns between different groups, using any kind of container or database settings.
I agree, I'm familiar with row level locking or table locking, but column locking among groups is a non-standard solution that only specific DBMS's would support.
I suggest looking into optimistic row-level locking and see if you can apply it in your current situation since this is the general solution for J2EE applications. Its worlds faster and doesn't drain the system nearly as much as pessamistic locking of specific rows/tables. [ November 29, 2005: Message edited by: Scott Selikoff ]
In case of version number pattern ,I will have to keep two version no(for the scenario I mentioned above) in table, each corresponding to one set of columns owned by one group since I want that columns belonging to different groups should be updatatable at the same time . To prohibit two users belonging to same group updating column belonging to the their group at the same time,the version no will be compared.