I have a database with records that track projects. Some of these projects are confidential and should only be accessible by certain people that have signed an NDA (Non Disclosure Agreement).
The way my database is structured:
One Project has Many Products associated (Project Table, Product Table)
Many Project are associated to Many Resources (one person can work on many projects, and one project can have many people working on it). (Project Table - Join Table - Resource Table)
The database has tables with resources. Resources may have signed many different NDA. A project can be confidential and required an NDA. (Resource Table - NDA Table)
The way i am handling this right now is as follow:
OnFistWindowOpen Script Trigger identifies the person that just login and looks in the resource table all the NDA that the person has signed. This NDA list is stored in a Global Variable ($$UserNDAList) as a value list (each NDA name enumarated with a carriage return).
Upon browsing through the Project Layout, I have a script trigger (OnRecordLoad) where i identify if the project is confidential (yes/No) and the NDA name for that project (ProjectNDA is a field in the project Table), then i check if the user NDA list includes the project NDA. If there is a match i set a global variable $$HasAccessRight to 1 (Bolean).
In the File Security Privilage Set, I have set Record view for the Project table to Limited based on the $$HasAccessRight bolean variable.
So when the person that just login access a project record that is confidential and for which the person NDA list doesn't match the project NDA, the record should not be visible.
This doesn't work very reliably. First the person's NDA list is identified just at login. If a new NDA is added to the person's record, it won't show up until the persons logs out an back in.
Does anyone have encountered this type of business problem (or equivalent), how do you, at a high level, suggest to handle this?
Any help, suggestion would be greatly appreciated.