Is it possible to implement the concept of row-owner or user specific data? So, for example, I have database of all my clients. In entire database, I have 100 rows for client 1, 200 rows for client 2 etc. So what I want is, when Client 1 executes a snippet with database connect, he/she shall be able to see only his/her relevant rows. So if you can have an option to filter the records based on who is signed in to the textblaze. Is that possible or can be implemented?
Hi,
You can do this via an "Owner" or similar column where you list out the email of the user who should have access to the data.
For example, this restricts the data loaded to "jane_doe@example.com".
{dbselect: SELECT sku, count, owner FROM Orders WHERE Owner = "jane_doe@example.com"; space=id; menu=yes}
Data Blaze has a "currentuser()" function that returns the email of the user making the query. So to make this snippet dynamic for each of your users you can change the query to:
SELECT sku, count, owner FROM Orders WHERE Owner = currentuser()
Unfortunately, this forum doesn't support "currentuser()" so I can't demo that here.
Note that when you use this technique you should not share the space directly with the users. Instead you should share the space with with the folder by clicking the "Share" button here in the Connected Settings dialog for the folder:
This will allow users of the folder's snippets to run any BSQL in the folder, but they won't be able to access the space directly. So their access will be limited to the BSQL you've written.
2 Likes
That's amazing to know. Perfect for my use-case! Thank you so much for sharing.
I have couple of following questions.
- Can The owner column in space be lookup column? The reason I am asking is, there is a possibility that a client can have more than one user. (Can we implement something like currentuser() is included in the list of email id (lookup column)
- Is there a way where in I can assign a role to each user in Space and filter the list using Role of current user? As I understand, this may not be possible as TextBlaze does not have any option to add Role. But I wish you may add this feature in future (especially in Organisation plan wherein Admin of Organisation can control the role of user).
Thank you for sharing this.
- Yes you could do that. Lookup fields are lists of values so something like this should work as the WHERE condition if you had a lookup field called "owners_email_lookup_field":
WHERE includes(owners_email_lookup_field, currentuser())
- BSQL queries don't have any access to the user's permissions on the space itself. Maybe in the future we could add something like
currentuserrole()to enable BSQL logic based on that. However, you could specify the role in the table itself and implement logic based on it. That would allow you more flexibility than our viewer/editor/owner roles.
Yes, I have a Role column in Space for each of the user. But I guess, as you mentioned, there is nothing like currentuserrole(). So filter won't even work.
Perfect! That's good to know.
If you have a Role column in the linked table, you could pull it into another lookup and then include it as a another test in your query. For example something like this might work if you created another lookup field "owners_role_lookup_field" linking to the same table as "owners_email_lookup_field":
... WHERE
includes(owners_email_lookup_field, currentuser())
AND owners_role_lookup_field[location(owners_email_lookup_field, currentuser())] = "EDITOR"
"EDITOR" being whatever role you've defined that the user should have to have access to the row.
I am not sure if I perfectly understand that but what I wanted to assign Role something like Client, Admin, Project Manager, Coordinator. So, all users with Coordinator role will have access to same set of data of that table (instead of using email ids)
How a TextBlaze knows that Current User is Coordinator so that it talks to Datablaze and filter works?
Here is one approach to that:
{dbselect: SELECT sku, role, emails FROM Orders WHERE contains(join(emails,";"), "jane_doe@example.com"); space=id; menu=yes}
The BSQL query in this demo (as I can't use currentuser() in the forums):
SELECT
sku, role, emails
FROM Orders
WHERE contains(join(emails,";"), "jane_doe@example.com")
To make it dynamic, you would use:
SELECT
sku, role, emails
FROM Orders
WHERE contains(join(emails,";"), currentuser())
In this example, I list out the emails for each role on the "Roles" table, you could also add a third table "Users" and link that table to the Roles table.
Yes, all my use-cases will have User table. Let me check if that works. Thank you so much for detailed explanation and examples.
Here is an example with a separate Users table.
{dbselect: SELECT sku, role, emails FROM Orders WHERE contains(join(emails,";"), "jane_doe@example.com"); space=id; menu=yes}
Thank you. I will check it with my database and get back to you.