2 Replies Latest reply on Aug 4, 2015 4:49 PM by mergatroid_1

    Restricted user access/related record question



      Restricted user access/related record question


      I am currently in the process of merging approximately 30 individual databases into a single database. The organization that hired me had originally assigned an individual database to each of their clients in order to protect their sensitive information. They have, however, discovered the many drawbacks to this approach and now want to consolidate them and protect/restrict the data through privilege sets.

      I have the new database largely ready to go and have started doing test imports, but I’m running into a problem that I’m having trouble solving. The database keeps track of  art collectors (the clients) and their collections (inventory). I created a field in both the Client table and the Inventory table called Record Owner that corresponds to the client’s account name, and I have set up a script that runs when they log in to search for those records.  This is working perfectly. But we have three other tables - all related to the inventory table - called Artist, Gallery, and Service Provider. Each artwork was (obviously) made by an artist, purchased from a gallery or auction house, and will be subjected to various services (crating, transportation, installing, etc). We want to also limit the records that each client sees to those that are related to their particular collection. So while some clients may have artworks by the same artist, other clients may have the only instance of an artist in the database. We don’t want clients to see artists that aren’t in their collection. The initial Artist layout is a list, so we want the list to display only artists in the given client’s collection. The same goes for the Gallery and Service provider tables/list layouts.

      I created join tables between Inventory and Artist, Inventory and Gallery, and Inventory and Service Provider that ties together the artwork to the other table and adds a Record Owner field. As of yet I have been unable to figure out how to display the related Artist, Gallery, and Service Provider related records via the list. I’m wondering if I’ve added unnecessary complications through these joins - if there is some way to limit the records without them.  Or maybe I'm on the right track but need to get my scripts working, because they're not right now. I am including some screenshots to help illustrate my question. Any help would be much appreciated. I’m in over my head!

        • 1. Re: Restricted user access/related record question

          Are you sure that you need the Join tables? These would only be necessary if you have a many to many relationship such as an artwork that has been shown in many galleries and a gallery that shows many artworks. Each Join table that you can remove, simplifies the issue.

          Are you familiar with record locking expressions in Manage | Security?

          I am assuming that you are. Those expressions can take advantage of relationships that you have in place to refer to data in a related table. So you could, in theory, record the client's account name in a single record in a table of clients and use it to limit access to all tables related to your clients table provided that your lock expression can access the correct client record.

          • 2. Re: Restricted user access/related record question

            Thanks Phil, as always. It turns out that I wasn't seeing data that I was expecting to see because it wasn't in the database that I was importing. Bad, bad data. I actually have this thing working.