I think the problem is that there are a million ways to skin a cat, a million ways to design a database, and a million ways to design an app…and people will try them all regardless if it’s a best practice or not. Trying to address each and every possible scenario, and each best practice for all of those scenarios, can be difficult. Glide has added warnings here and there, such as on filters to indicate that filters alone are not secure. I also think they are planning to focus on simplification. I’m sure they would take other suggestions for warnings and other ideas as well.
Your case above could be considered unique to some level. Like I said, you are trying to dual purpose the role column. You are assigning the role column to the role setting in the user profile settings, which tells the app to grant access to the data if a user’s role matches the value in a column that is assigned as a row owner column. This setting will then allow glide to look at which role was assigned to the signed in user. Any row of data in a table that has a row owner column that contains a matching email or role value for the user that is signed in will grant that user access to that row. In your case, you also assigned that same user profile role column in the user table as a row owner column. So, it’s taking the role for the signed in user, and looking for any rows of data that have row owners applied and where that column contains the same value as the role of the signed in user. Thus the admin can only see rows that have admin as the row owner, and users can only see rows that have user as the row owner.
I do try to stress security as much as I can and I have seen some very questionable methods used in the forum. I will also admit that I have snooped data in these questionable apps and notified the owner if I feel they are not following best practices. I have no I’ll intent, but I want to make sure data isn’t being leaked somewhere due to a poorly laid out app.
If someone is working with sensitive data, I think it’s imperative to have a solid understanding of Glide’s core functionality before unleashing an app. If there are ever any doubts, it doesn’t hurt to just ask, or hire an expert to do a security audit. I think the experts have a very good understanding of how glide works and how to properly secure data.
I’ve been a developer for over 20 years, so yes I’m biased and my mind does think like a developer. To me glide is pretty straight forward out of the box, but many people, myself included, will try to push the boundaries of what glide is capable of doing instead of staying within it’s confines. It’s lead to a lot of feature creep over the years, which for many of us has been awesome and beneficial, but can be daunting to new users. It’s also lead to a lot of new users wanting to do things that require outside third parties, such as Zapier, Make, API’s, javascript, scripting, etc., or people trying to use CSS to hack their way around glide functionality. I think glide is trying to cater to many of the user’s needs, which also adds to the complexity. At what point do you sacrifice ease of use, while maintaining expansive functionality and flexibility? I think at some point you would have to take away features to keep things simple and understandable, but you also have to realize that this is a no-code development tool and it won’t have all of the features of full code development.
Overall, I think glide has a basic set of “rules”. It may take a bit to fully understand them all, but once you do, it’s pretty easy to build upon them and create some pretty wild functionality.
I always approach a problem by starting with the result I want. Then I think about what I need to get that result, and I keep working backwards until I get to the beginning, and at that point, I have my solution. Trying to start from the beginning first can lead to confusion sometimes. Let’s take your example for instance. You know that you want a User role to only have access to their own data, and that an Admin role should have access to everybody’s data. You know that assigning an email column as a row owner will grant a user, with that same email, access to their own row. You know that a user with an assigned role should have access to any rows with with a row owner column that contains their same role. You also know that you have assigned each user a role in the user profile settings by pointing it to that role column. You know the “rules” for how row owners work. Knowing that much, you know that a user has access to their own row at this point. All that’s left is to decide how you are going to indicate that an admin can access every row, while a user can still only access their own row? You have two ways to do it. First you create a new permission column and then you have to decide to either place the admin’s email on every single row in the table (which is not very scalable if you have multiple admins), or you place the admin role value in every single row in that table. Then you apply row owners to that new permission column. So in the end you now have two row owner columns. An email column with the email of each specific user in each row, and a permission column that contains the admin email or admin role. These are separate from the role column which is used in the user profile settings, and to match up with any column marked as a row owner column. When a User signs in, only the email colomn will match the email they signed in with. Their role won’t serve a purpose because there will be no row owner column that will contain a value of USER. When an Admin signs in, the email column will match, but also the new permission column with ADMIN will also match. Their own specific row will match in two cases (once for the email row owner column and once for the permission row owner column), but there will also be a single match for the admin on all other rows, because their role of ADMIN matches the new permission column which also contains ADMIN on every row.
I think you will be fine as long as you have a Role column (without row owners), which determines the role of the user in that row, and a separate Permission column (with row owner applied), which determines which user roles have access to that row of data.
As far as global variable using the user profile row, just remember, that accessing that global user profile (through a drop-down) will always always always refer to the signed in user’s row.
)
