I’m in the midst of developing an app intended to provide our clients with access to their sales data from my company. However, I’ve encountered a challenge regarding data privacy and appropriate access control that I hope to get some advice on.
Here’s the structure of my data:
Users Table: Contains columns for the user’s name, email address, and the VAT ID of the company they represent. It is important, that there can be many users associated with one company (owners, managers etc.)
Sales Data Table: Includes columns for VAT ID, month, year, and sales figures. Each record corresponds to the sales for a specific month.
The Challenge: I need to ensure that users can only access sales data for their specific company, identified by matching VAT IDs. Additionally, I want to avoid front-end filtering or visibility controls that could allow all data to be downloaded and potentially exploited.
Constraints:
Directly linking email addresses to each sales record for row ownership isn’t practical due to the scale of data and the dynamic nature of email addresses. For example, one company has 600 historical records, and as the new user is added or changed, I would need to edit e-mail addresses in all those 600 records.
Utilizing front-end data visibility controls isn’t preferred due to security concerns.
I’m considering an API-driven script to dynamically update sales records with email addresses from the Users table, though I’m wary of the complexity and maintenance this approach might entail.
Question: Does anyone have alternative suggestions or best practices for securely managing access to sales data based on company affiliation without overly complex implementations or risking data privacy?
I’m open to creative solutions or insights into how others have tackled similar challenges. Any advice or direction would be greatly appreciated!
Have you looked into using Role functionality? You could use VAT ID or some other company ID as the user role, and then use that same value in your other tables as Row Owners.
I think I sort of tried this solution, but without success. I don’t know it has any importance, but both Users and Sales Data tables are linked tables from my other app - I changed source of Users to that linked table and removed original Users table. What I’ve tried at the beginning was setting VAT ID column both in Users Table and Sales Table as Row Owner, but it had no effect - meaning that all records were greyed out.
Assign each user an appropriate Role in your Users table (ie. the VatID)
You will probably also want to assign an Admin role for yourself (and anybody else that needs it), and add that as a Row Owner column in the Sales table - so that you get access to all records
Thanks for your response! I duplicated my app and removed all sensitive data, to show it in here. On the screenshots you can see that I duplicated proposed solution (at least I think I did) but it doesn’t work - all rows are unavailable. Can you please point me where I made mistake?
I just noticed your user profile is pointing to a phone number column. That may be corrupting the user profile configuration. Try pointing it to an image column.
I’m not sure what I’m missing. Typically I wouldn’t use the same column as the Role column as well as the Row owner column in a user table (Contacts), but I would think it should still work. I would recommend having a separate Role column to designate roles with your user profile configuration, and a separate row owner column that contains the role that should have access to that row. For something to try, try removing row owners from the VATID column in the contacts table.
Did you get this working? I tried recreating your problem in a new App (which started with Users/Contacts/Companies) and it seemed to work fine. I even imported in a Linked Table and Glide still dealt with the Row Owners correctly in the new App rows and the Linked Table rows. Here is the example:
Derek can only see Rows whose Row Owner (Role) matches Sales. Contacts is the User Table and Derek can see all Contacts whose Role matches ‘Sales’. In the SE-Companies table Only Company B and Company C match ‘Sales’ in the Row Owner column (named Role). He can not see Company A. SE-Companies is a Linked Table.
The Companies table has no row owners so Derek/everyone can see all the rows (nothing is greyed out).
Hi! I think I just figured out what is the reason for my problem. Row Owner column needs values that start with letter - as soon as they start with number, they stop working. So I need to append a letter at the beginning of every numerical ID I use in order to utilise Row Orders properly.
Glide support (which works terribly bad from my point of view), informed me that Row Owner cannot be Number column and it has to be Text column, which is not completely right since it also cannot be Text column in which values start with number.
