Restrict to view/edit own records

[crudyrudy]

For an application that I would like to implement with Budibase, I need the following setting options in the rights management:

1.) can view all records / can only view own records. 2.) can edit all records / can only edit own records

Is such functionality possibly already planned?

[lcslouis]

Yes I would love this fuctionality as well.

Mutiple projects that I have built and still use today have to have this feature.

an example would be that there are multiple users(customers) submitting a Registration Form for a Event. customers should only ever edit / view their own submitted records, but the edit needs to be disabled once a condition is met like Approved / Not Approved Staff - could view and edit all records regardless of the condition.

Both can be accomplished by adding 2 fields on a table a userID / OwnerField, and a 0 or 1 / True or False Field. Which as far as userID goes its a filter by userID. so only the records that match the userID of the current loggedin User are displayed. and to disable edit would be
If Role = user and locked = true Then Edit for that record = disabled Else If Role = Admin Edit = enabled

[aptkingston]

Hi @crudyrudy and @lcslouis. If you'd only like users to see records they created, then you can achieve that by changing the data source of your data provider component. If you've enabled the "created by" autocolumn on your table, then Budibase automatically stores who created a certain row. Note that this only works for internal tables - are you using internal tables or something like an external SQL database? You can do the same thing with SQL, but it just needs set up differently.

You can then use that "created by" relationship as the source of your data provider. Here's a screenshot of me using it:

This will only show data in my "Sales" table that was created by the current user. Does this solve your issue?

You could then use conditions (at the bottom of the settings panel) to change the source of the data provider back to all table data, if the current user has a certain role. This combination would let normal users only see their own records, but admin users see all rows.

For something like an approval workflow, where unapproved items can be edited but approved ones can't - the best way to model that is probably 2 different tables; one for unapproved and one for approved. Then you can add an edit button to your unapproved table, and just don't add one for your approved table.

[crudyrudy]

Hi aptkingston! Thank you for this hint. Not in all but in a lot of cases this should do the trick since often a user shouldn't even view content he is not allowed to edit. But in some cases you will still need read- without write-permissions for foreign content.

[lcslouis]

I use SQL mainly MySQL but sometime MS SQL. With MS SQL on Linux MS SQL has become easier to implement with client requirements.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[mmalessa80]

Hi @crudyrudy and @lcslouis. If you'd only like users to see records they created, then you can achieve that by changing the data source of your data provider component. If you've enabled the "created by" autocolumn on your table, then Budibase automatically stores who created a certain row. Note that this only works for internal tables - are you using internal tables or something like an external SQL database? You can do the same thing with SQL, but it just needs set up differently.

You can then use that "created by" relationship as the source of your data provider. Here's a screenshot of me using it:

This will only show data in my "Sales" table that was created by the current user. Does this solve your issue?

You could then use conditions (at the bottom of the settings panel) to change the source of the data provider back to all table data, if the current user has a certain role. This combination would let normal users only see their own records, but admin users see all rows.

For something like an approval workflow, where unapproved items can be edited but approved ones can't - the best way to model that is probably 2 different tables; one for unapproved and one for approved. Then you can add an edit button to your unapproved table, and just don't add one for your approved table.

Could someone explain the process of how to accomplish this using MySQL vs internal tables? I'm having a hard time finding the proper documentation. Thank you!

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity.

[Cityjohn]

Also looking for a solution to this.

[HVStechnik]

Also looking for a solution to this with a Mysql db. I'm currently using custom queries with manual created_by fields, which are filled within the save row action. Within the custom query you can then filter in the user_id. Yet, custom queries have substancial disadvantages as pagination and filtering does not work as smooth as for data providers using tables directly (#8214).

For me, row level access control is a key requirement. I think the following features would give us the possibility to implement very flexible row-level security:

1. Auto-generated fields for external datasources: While it is possible to build "created_by" and "updated_by" fields in external datasources manually and update them on the save opereration, it would be much more convenient if Budibase takes care of it. The "created_by" field is important for any kind of "read own records only" or "edit own records only" access control.

2. Copy the user table to external datasources: With the Budibase DB it's very convenient to use the ´Current User.Table_Created_by` relationship for "read own records only" access. Yet, for external databases, this currently is much more difficult. I suggest an option to define a table in the external datasource that mirrows the user table (except PW). Thereby, we could retrieve user names and attributes in custom queries

3. Access control on data retrieval Within the data provider we can restrict data retrieval quite well already. One limitiation is that, in the data provider, we can only either join multiple filter with AND or with OR. It would be great, if the query could be more fine grained. For example: ((condition1 OR condition2) AND condition3). The current solution is to use a custom query on external datasources.

4. Most important: Check individual access queries on update, create and delete actions. To build secure web apps there should be a server-side access control check of form actions. Currently, we can hide the "Create" or "Edit" button conditionally and we can also prevent that a user opens a form that he/she shouldn't edit. Yet, with some technical knowledge, users can easily modify the frontend js to also update records they should not have row-level access to.

5. Feature #8214

The solution for row-level security then looks like:

1. Create the following table named user_access_levels:

You can have budibase screens in your app to manage the access levels for each user.

2. Create custom queries for each table similar to this:

SELECT * FROM table1 INNER JOIN user_access_levels ON user_access_levels.table_name = "table1" WHERE user_access_levels.user_id = {{ CURRENT_USER }} AND (user_access_levels.Acl_Read = "ALL" OR (user_access_levels.Acl_Read = "OWN" AND user_access_levels.user_id = table1.created_by) AND {{ FILTERS }} ORDER BY {{ SORT }} LIMIT {{ PAGINATION }}

Where {{ CURRENT_USER }} is a binding in the query for the current user id, and all other bindings are explained in the #8214 (feature request not yet available).

Within save, update and delete row actions, there should be an option to execute a query and to check the acl_create, acl_update, and acl_delete fields, before the CRUD action is performed.

[Cityjohn]

Hmm I see. This is a functional workaround although I don't think that's the best way either.

Personally I am leaning toward a perfect duplicate of the Users table from bodybase to SQL by means of so it can also better serve the purpose of user relationships where some user may now be able to select a subset of users from a dropdown based on their user role or team cluster relationship hashtables for example.

I haven't implemented it yet but I am going to install a cron job on my server that checks the budibase users database for updates and directly copies them into my SQL database also adding the weird prefix that I don't understand yet to their ID's. The problem with this ofcourse is that it needs to run every second eating up resources just in case someone signs up and they want instant access to manage their data.

Realistically, yes ofcourse someone could manually do this but we're just absolutely addicted to automation and on demand delivery.

It would be great if budibase could allow developers to opt for a user table in any data source, not just the couchDB. This could also be circumvented in one very simple way and that is if the budibase automation screen also included the trigger of changes to the budibase Users table since we can't control the user registration and login screens.

[HVStechnik]

Just noticed that my above comment regarding access control is highly insecure, because Budibase handles bindings for custom queries client-side. As long as the {{ Current User._id }} server-side binding is not availalbe within custom queries (see #3884) it is not a good idea to use the user id provided with a standard binding as it's value can easily been faked / modified in the frontend.

[Cityjohn]

Just noticed that my above comment regarding access control is highly insecure, because Budibase handles bindings for custom queries client-side. As long as the {{ Current User._id }} server-side binding is not availalbe within custom queries (see #3884) it is not a good idea to use the user id provided with a standard binding as it's value can easily been faked / modified in the frontend.

I'm not quite sure I understand the machanism of this, because if all bindings are client-side, wouldn't that make ALL budibase bindings highly insecure and effectively render any user data completely insecure?

In the second command on https://github.com/Budibase/budibase/issues/4139 Micheal says that datasources are handled server side.

---

I am using this method (https://github.com/Budibase/budibase/discussions/6098) to let users register and also added an SQL query beside the post methods to duplicate the users into the sql database. Then I create a serverside SQL view which includes the users id. This looks like it is happening in the back-end and not inside a screen or component therefor I assumed it was secure.

[HVStechnik]

Hi @Cityjohn, I'm not in the details. For custom REST api calls, the {{Current User._id}} binding seems to be implemented (#6315). For custom SQL queries, I tried to insert a binding like this:

This allows me to create a custom SQL query like this: SELECT * FROM table1 INNER JOIN table2 ON ....... WHERE table2.user = {{ USER_ID }}

The User ID is inserted per default.

From Michel's comment I assumed that this would be handled server-side. Yet, if you inspect the network communication of the published app, it becomes clear, that the user id is provided from the client and placed in a request to the server:

I think, that this might be highly insecure. Yet, I haven't investigated in detail, whether there are any checks server-side that prevent users from modifying the value and retrieving data, which the user is not supposed to retrieve.

If you use a different way to insert the current user id server-side into the custom SQL query, I would apprechiate if you could share the solution. I tried {{Current User._id}} without success.

[Cityjohn]

@HVStechnik I use that exact method of fetching user data. I would imagine at the very least it will be extremely hard to find any other matching number with limited users and the reasonably long code.

@mike12345567 Terribly sorry for the bother, could you comment on whether this is or isn't a security issue? Can the user spoof a USER_ID in the client?

[HVStechnik]

@HVStechnik I use that exact method of fetching user data. I would imagine at the very least it will be extremely hard to find any other matching number with limited users and the reasonably long code.

@Cityjohn I disagree, because if you have a created_by or updated_by column in your data many different user ids might be retrieved from the db and avaialbe client-side. This obviously depends on how the app looks like. But it should not be too difficult for a user to get the id of another user, if the user is not purely allowed to view own records on all tables with created_by / updated_by columns. My opinion: Bindings in custom queries that are not filled in the data provider, but filled with the default value must be handled server-side.

[Cityjohn]

@HVStechnik I use that exact method of fetching user data. I would imagine at the very least it will be extremely hard to find any other matching number with limited users and the reasonably long code.

@Cityjohn I disagree, because if you have a created_by or updated_by column in your data many different user ids might be retrieved from the db and avaialbe client-side. This obviously depends on how the app looks like. But it should not be too difficult for a user to get the id of another user, if the user is not purely allowed to view own records on all tables with created_by / updated_by columns. My opinion: Bindings in custom queries that are not filled in the data provider, but filled with the default value must be handled server-side.

Indeed, maybe we are overlooking something. Like you mentioned it could be that there is a server-side check before allowing a data request. Maybe we can test this somehow quickly.

In my case I exclude the user ID from the query return so that people can for example only view each others names or Email adresses, or an internal identification number.

[Dominic-Wagner]

@HVStechnik I use that exact method of fetching user data. I would imagine at the very least it will be extremely hard to find any other matching number with limited users and the reasonably long code.

@Cityjohn I disagree, because if you have a created_by or updated_by column in your data many different user ids might be retrieved from the db and avaialbe client-side. This obviously depends on how the app looks like. But it should not be too difficult for a user to get the id of another user, if the user is not purely allowed to view own records on all tables with created_by / updated_by columns. My opinion: Bindings in custom queries that are not filled in the data provider, but filled with the default value must be handled server-side.

Indeed, maybe we are overlooking something. Like you mentioned it could be that there is a server-side check before allowing a data request. Maybe we can test this somehow quickly.

In my case I exclude the user ID from the query return so that people can for example only view each others names or Email adresses, or an internal identification number.

So currently server-side is only available within the body of custom Rest-Requests. Then you can't change it client-side.

[muscovitebob]

This feature would considerably simplify building submission forms where we want the user to be able to come back and edit an overview of the information they submitted since we could simply give them a Table view of all their data.

[Dominic-Wagner]

So its working now with custom sql queries with {{ user.email }}

[muscovitebob]

So its working now with custom sql queries with {{ user.email }}

Could you give me an example of a Table backed by an SQL table using this functionality? Thank you.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity.

[HVStechnik]

Not stale - highly relevant! Highly appreciate that this is on the roadmap for one of the next releases! Thanks.