How do I create privacy rules that reference other DB records?

I have system where Users create Products, then share them with other Users. I’m using a Thing in the database called Shares that link the User and the Product (so I can capture other information about the type of sharing like when it happened and is it read/edit access etc).

I want to create a privacy rule for Products that allows Users that a Product has been shared with to view the Product info, but no one else. Normally I’d just “Do a search for Shares where Product = This Product: Each Item’s User” but apparently “Do a search for” is not an option in privacy rules.

How would I accomplish this? I really don’t want to add a list of Users to the Product to reference, that would become a very large list and potentially cause performance issues, not to mention the additional work of making sure that list is up to date in every relevant workflow.

Any ideas? Thanks!

Hi there, @johndurso… based on what you described and to the best of my knowledge, I think you are going to need a list field. You have already ruled out the list of users on the Product data type, but how about a list of products on the User data type? How large would that list be? True, you still have to deal with keeping the list up to date as products are shared/unshared with users, but unless I am missing something, I don’t think you can construct a privacy rule any other way given your setup.

Best…
Mike

That’s a bummer, but thanks for the answer. This is a many-to-many relationship between Users and Products and the list could get lengthy either place. Seems like I have to choose between performance and security; I guess I have to choose security and see where I can make up for performance in other areas.

A third approach, which is kind of hacky and isn’t great, is to put some of the identifying product information in the Shares datatable, and set the privacy rules on that table. Then if you are showing shared products to a user, use that datatable as the source.

You could replicate all the products info in those Shares entries, of just put some of the identifying info there, and pull in the rest from the Product datatable when you are displaying it (but this pulled in info would not be private).

This will mean that when a user updates a product, you’ll need to also run a backend workflow to flow those changes through the Shares datatable.