5 Replies Latest reply on Apr 8, 2014 6:33 AM by Vyke

    Confidential Record Access (Protected by NDA)


      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.

        • 1. Re: Confidential Record Access (Protected by NDA)

          I have a similar problem on the horizon where certain records should only be seen by certain clients. One of my solutions was a seperate file that pulls the needed data and makes new records from the main file via ExecuteSQL or some other calulation.


          I do not have a need for any changes to make it back to the main database so it is a little bit different.

          • 2. Re: Confidential Record Access (Protected by NDA)

            Michel -


            There's almost never a situation where you shouldn't use the native security features of FileMaker (as opposed to trying to create your own scheme). In a case like this, record-level access privileges would be the way to handle it.


            In this case, create a value list of NDAs, based on the relationship from Project to NDA. Create a second value list of NDAs that apply to the current user (again, using the current user's account name in an unstored calculation field). (You may need to create another relationship between Project and NDA to accomplish this.) Then, set a calculation in the security schema for the Project table that looks like this:


            not IsEmpty ( FilterValues ( ValueListItems ( Get ( FileName ) ; "{value list name}" ) ; ValueListItems ( Get ( FileName ) ; "{value list 2 name}" )))


            What this calculation will do is compare the list of NDAs for the current project against the list for the current user. If there's a result (i.e., if at least one NDA appears in both lists), then the result will be "true" and the person can see the record.





            • 3. Re: Confidential Record Access (Protected by NDA)



              Thank you for your insight. I like the approach. I think I understand, I am not 100% sure, but I'll give it a try. I may ask clarifying questions later.



              • 4. Re: Confidential Record Access (Protected by NDA)

                +1 on Mike's advice.  Using the User Interface as a substitute for security is fraught with problems that can sometimes compromised by users doing things you did not expect them to do.  Always use the FileMaker security to authentcate by a caluclation which records and/or fields they can see.  Then you can add additional user interface tools to make the interface flow well, but do not count on the UI to be the security. 

                • 5. Re: Confidential Record Access (Protected by NDA)

                  agreed. Using layouts and menus to limit record access works, but is fraught with holes. Hitting escape at the right time, doing a find on a certain value that wasnt accounted for etc can all easily bypass a security schema if not very careful about it.