Let’s say I have a data table with items who all have an “owner”-attribute which links to my users-table. This means that whenever I return any one of these items to any of my users, by extension they also have access to all the linked rows in my users table (i.e. phone number, address of other users etc.) because they could always do something like self.item[owner][phone_number], correct?
I really enjoy the convenience of the linked tables but I guess this rules them out whenever data privacy is important. :-/
If my assessment is right, what is the next best way to do this?
After reading around a bit more I guess my assessment is correct and the solution is to not link TO any tables with sensitive data but instead link FROM these tables to tables with non sensitive data instead.
Edit: Or instead of linking directly to another table row I only link to the ID of that row - this way I can check on the server whether the user has permission to access this object.
If one of these is an obviously better strategy, please let me know!
Many apps can share the same Users table, so adding app specific data to the Users table could cause a never ending proliferation of columns. Try to keep the Users table as little as possible
Every time you access a linked column with something like self.item[owner][phone_number] you execute a new query on the database. This will not bother you much on the server side, but could cause your client side to run a burst of round trips and performance would suffer
Well, I don’t know if a query actually runs every time you access a linked field or if Anvil does some optimization. For example it could read many linked fields at once (fewer queries, but more data) or could read them one by one (less data but more queries). Either way it does a good job on most simple cases, but it’s unlikely to guess what’s your best strategy. That’s why with large and complex queries I often use SQL (not available on free plans) instead of (or together with) linked columns
I always try to make one call to the server on the form_show event. The server function gathers all the information from all the tables, puts it inside a dictionary and returns it to the client. That is, instead of asking the server for the items of one repeating panel, then the items for another repeating panel, then a few global settings, I call one function that returns one dictionary with all of the above. This function will take care of including all and only the required data, leaving out sensitive information from the user table
Could you explain a little more about what you’re trying to do? There are various ways you can manage data security for linked tables in Anvil, and a little more information about how you’d like to restrict data access on the client-side would be helpful.
It sounds like restricting views on your Data Tables may be useful. Anvil’s Data Tables have three view methods, which offer the client code different levels of access.
Hi Bridget, sure. The views are already restricted for the client.
I have two tables called “suggestions” and “users”. Every item in the suggestions-table is linked to the user who wrote the suggestion.
Now if I allow my users to see the suggestions made by other people it would be a problem if the users-table contained sensitive data because then a malicious user could access the other fields of any users-rows that are linked from the suggestions made by other people.
I fixed this by splitting the users-table into two (one for sensitive data and one for public data) and linking only to the table with public data.
It is precisely for this reason that I’ve suggested a fourth View method:
In the absence of such a View, I see two alternatives:
Do the filtering via Server Module functions, as noted above. This works best when returning relatively few rows, as they must all be returned at once (not a page at a time, as a real View can do).
Split the table, as you’ve done.
Depending on how many different “views” are needed, this might need several splits, resulting in a very fragmented/duplicated group of tables to maintain. On the other hand, it allows paging, which can improve performance (user experience) for larger numbers of rows.
I hadn’t considered your approach in detail. Thank you for making me think!
Sounds to me like another upvote for @p.colbert’s feature request for client-readable views on a selection of columns from the Data Table, which I agree would be very useful.
And yes, in this case I think splitting your users table sounds like a sensible approach.