Hi - is there a way to get a list of all tables that is created by a user? I have an app which lets user create tables (dynamic schema). I need to have users see all the tables that they have created (and also restrict them to their tables only). What’s the best way to achieve this?
There is no built-in API that would return a list of tables. However, a solution I’d use would be having a separate table where you store a list of tables created by a user (say in a JSON column) and a related column to the Users table. This way you will have a mapping between a user and the tables they created. Any time when they create/rename/delete a table, you’d need to update the JSON value.
Thanks for quick reply. Interesting idea. I will try to implement it right now and let you know how it goes.
Hi Mike - how do you make sure a user can only access their own tables? I have multiple users who sign up and create tables dynamically and I want to make sure they can only access their tables. Thanks.
This is not what I am looking for. As mentioned, users sign up and then create tables (dynamically). The entire process is automatic. I cannot manually change any permissions. I only want users to be able to access their own tables that they created.
Hi @LetMeTest,
To do this, you can use the following API to give the user access to a specific table:
Method: PUT
Request URL: https://xxxx.backendless.app/api/data/<tableName>/permissions/<permissionType>
Request body:
{
"permission": <permission>,
"user":<objectId>
or
"role":<role-name>
}
In the request URL, you must specify the table name in <tableName> and GRANT or DENY in <permissionType>.
In the body of the request:
- For the
permissionparameter, specify one of the following operations:
ADD,
UPDATE,
FIND,
REMOVE,
DESCRIBE,
PERMISSION,
LOAD_RELATIONS,
ADD_RELATION,
DELETE_RELATION,
UPSERT
Or instead of a specific operation, you can use * to change permissions for all operations.
- The body should contain
user(object id) orrole(role name) property. If both presentrolewill be ignored.
Regards
Nazar
Hi Nazar - when user creates the table (I am using dynamic schema), the automatically have access to that table. So it’s not about giving access. I need to know how to restrict access to only the tables they have created.
Do you need to do it programmatically or manually in Backendless Console?
Programtically. I am using rest API to connect another front end to BE.
Here’s how I would structure it:
- Create a custom security role in Backendless.
- Configure that custom security role to deny access to ALL tables (this would be a global permission denial done on the following screen):
- When a new user is created, assign that security role (there is an API for that)
- When a user creates a table, use the API described by Nazar here. This will grant access to that specific user to access that specific table.
Mark
Step 1,2 and 3 are working but 4 doesn’t work. As mentioned, customer create tables on the fly (using dynamic schema). The API Nazar mentioned (List of tables by user - #7 by Nazar_Dmytryshyn) only works on existing tables. How can a user create a table without permission in the first place?
Concept is simple: I want users to create tables dynamically and I want to make sure they only have access to their OWN tables.
Hello, @LetMeTest.
There are not many options here. Just immediately after creating the table, call the route that @Nazar_Dmytryshyn suggested.
Regards, Nikita.
How does the user create table without access? Are you saying to give user access to all tables and then restrict access? Big security concern.
I’m not entirely sure it’s possible to do this. Have you thought of any other ways to implement the functionality you want to achieve?
Since you mentioned security, an even bigger concern is to let the users create their own data tables. I strongly believe there are other approaches where the database schema is not exposed to the users neither partially nor fully.
1 Like
@mark-piller For the past few years, we’ve been utilizing an on-premise server system to handle exactly just this. Due to the high maintenance requirements, we are now planning to migrate some of our services to the cloud.
One of our challenges is with an app that has more than 1400 active users. The app’s analytics capabilities are limited, and granting users access to their transactional data within the app is complex. To tackle this issue, our engineers devised a solution: they clone user’s transactional data outside of the on-premise server to BL servers. Users can then connect to this BL server to run reports and perform analytics independently of the on-premise app.
When a user “subscribes” to this analytics service and triggers the data synchronization to BL server by pressing a “button,” in our on-premise app, only their specific data is transferred. That’s what we’re trying to achieve here. How do we make sure they can only access their transferred data within BL app? One thought is to create tables dynamically, to clone their on prem data structure.
Thank you for sharing the details. Would it be correct to assume that over time all of the data would be transferred to Backendless? If so, would it make sense to transfer all of the data now and avoid the complexity you’re dealing with? By transferring all the data, you could provide a simpler filtering mechanism for a user to see their data.
The data constantly changes at the source. Users add and delete tables at and data the source. The idea is to sync BL with the changes, so users can login to BL and run their reporting/analytics when needed. Again, security is the main concern so want to ensure they can only access their data.
Let’s go back to the problem. From what I understood, the API call to assign permissions to a new table does not work, is that correct?