Max String length on Database column

Hi team,

We are trying to store a JSON value on a data table which will then be parsed and used as roles and permissions for experience users. The problem we are facing is that we get an error that the string size is greater than the max size. For example:

permissions: string of size 809 is over max of 768 bytes

Please suggest is that something which we can overcome or alternatives we can take to solve issues like these.

Thanks,

Hey @Suroor_Wijdan,

There’s a great Losant Tip Post that walks through implementing user roles in Experiences. This examples uses Experience User Tags to store User Roles.

Other than using Experience User Tags, my suggestion would be to use columns in a data table, rather than trying to store an entire JSON object.

Please let me know if there is anything else I can help with!

Thank you,
Heath

Hi @Heath ,

Thanks for getting back.

We are actually building an advanced use case of roles and permissions on different modules the experienced user can have access to.

We are storing the JSON for the permissions object in a column on one of the DataTables named UserRoles which stores permission per user rather then storing module wise permission.

Is the hard limit on string length on a column value be changed or is that something we would need to work with?

The only option I can think of at the moment is to store module vise permissions for each user and then query all of it and combine through a workflow to get the final set.

@Suroor_Wijdan,

Is the hard limit on string length on a column value be changed or is that something we would need to work with?

The string length in data table columns is a hard limit, and not something that can be changed.

Our best practice is to store User Roles/Permissions in either a Data Table or as Experience User Tags. Could the JSON you have be split up to fit in multiple columns?

Thank you,
Heath

@Heath Yes it can certainly be broken down into different columns, but going in the future whenever a new a module is added, we would need to add the respective column on the data table as well. Is there a limit on the number of columns a data table can have?

Sample JSON which we are storing as of now:

{"Devices":{"c":0,"r":1,"u":1,"d":0},"Users":{"c":0,"r":1,"u":0,"d":1},"Device":{"c":1,"r":1,"u":1,"d":1},"User":{"c":1,"r":1,"u":1,"d":1},"Cartridge":{"c":1,"r":1,"u":1,"d":1},"Organization":{"c":0,"r":0,"u":0,"d":0}}

The string can certainly be shortened but that would just be a fix instead of a scalable solution. Any advice from your end?

@Suroor_Wijdan,

Is there a limit on the number of columns a data table can have?

The limit to the number of columns is 100. You can read more in our documentation.

Any advice from your end?

Would you be able to tell me more about your Experience? You mentioned that you will be giving users access to different modules. Could you explain “Modules” to me a bit more?

Thank you,
Heath

@Heath

We are building an RBAC system for the front-end application that is based of on the experience API’s that we built.

We are classifying each section on the front-end interface as a module, for eg. a Device module (CRUD), Users module (CRUD), etc. We are trying to give fine-grained control over these so that the admin is able to build their own Roles going forward and allow users deeper access into the front-end platform.

We are utilizing the datatables to store all the app data and as datatables currently only support number, string and boolean values, we are not able to store the Role data which is just permission on each module categoriesed into Read, Write, Update and Delete.