AnnaRodionEvgenyExpertExpertExpert
Cross
Got any questions?
Our domain expert is here to answer
Thanks for your question
We will contact you soon
We have a problem
Please, check the entered data

Retool Workflows: Improving Application Security and Permission Management (even on Free plan)

Dmitry Sitnik
Dmitry Sitnik

Low-Code Department Technical Lead

May 15, 2024

Updated:

Retool Workflows: Improving Application Security and Permission Management (even on Free plan)

Retool Workflows: Improving Application Security and Permission Management (even on Free plan)

Dmitry Sitnik
Dmitry Sitnik

Low-Code Department Technical Lead

May 15, 2024

Updated:

Retool Workflows: Improving Application Security and Permission Management (even on Free plan)

How often do you think about the Roman Empire? And what about data security? If you have answered ‘never’ twice, I’ve got some news for you: you are not a man, not yet a developer.  However, Retool Devs can sleep tight every night, because the Retool Team understands how important it is to keep your info safe. And that should be a top priority for you too, if you're building custom apps.

In this article, we will concentrate on how to manage user permissions in Retool and how to craft a user experience personally, even on Free or Team pricing plans.

How does Retool handle user permissions and access levels?

Retool has a built-in user management system available on their Business and Enterprise plans. This system lets you control exactly who can peek at and tinker with your apps, resources, and workflows. Here are two major concepts: groups and levels. Let’s start with groups.

Try to imagine a project where only specific team members can see documents. Permission groups in Retool work similarly. You create groups for users with similar roles (something like "Marketing Team" or "General Cleaning Division"). Then, you assign permission levels to those groups. These levels determine what each group member can do with your precious data.

Okay, we’ve met the word ‘levels’. Retool has three access levels: Use, Edit, and Own. Use is the basic level for your end-users. Accounts with this level can view and interact with the assigned app, resource, or workflow, but they can't make any changes. Think of it as read-only access.

Edit: This level unlocks some modification capabilities. You can still view and use apps and resources, but now you can also modify them. This access level is an ideal option for those who are more tech-aware and can make some adjustments by themselves.

Retool displays resources and access levels in a matrix form
Retool displays resources and access levels in a matrix form

Okay, but what to do if this granularity isn’t enough for your project? Or isn’t available, as long you are using the Free or Team plan?  Let’s manage it by ourselves.

Developing an Access Control List

We should start from the designing permissions to achieve the desired granularity level. Try to study your current business flows to understand how many different operations your team members have and describe them. Just to note: better avoid overlapping permissions. For example, it’s okay to have ‘Can Make Coffee’ and ‘Can Make Tea’ permissions, but not okay to have something like ‘Can Make Coffee’ and ‘Can Make Coffee and Tea’.

A free-to-use and convenient way to manage permissions might be a Google Sheet. Just organize your permissions as column headers, while in row titles put user emails. On the intersection of these two, you can put, to say, 0, if the permission is not granted to a user, and 1, if it is. Remember, it’s just an example. If you are already using an advanced ACL system, you can still benefit from these ideas.

Organizing an ACL system using Google Sheets
Organizing an ACL system using Google Sheets

Now we are ready to to proceed with applications.

Securing UI: How to disable buttons and other controls based on permissions

As long as we use a Google Sheet Document in this article, we are limited in our ability to filter data on the application side. Each user will store on their device a full configuration of all the users. This is not safe, is it?

To avoid this issue, let’s create a workflow. I’ve named it ‘GetPermissions’. It takes the user's email as input and returns only permissions that correspond to the active user.

Protecting the full ACL using a workflow
Protecting the full ACL using a workflow

Now it’s time to create a corresponding call of the workflow inside of the app. As an input, we will provide the reference to {{current_user.email}}. This query will return a set of permissions assigned to the user, or nothing if the user doesn’t exist.

Getting the current user permissions using a workflow
Getting the current user permissions using a workflow

What is left? Now we have to go to the UI components level: we can either hide or disable them based on permissions. What option is better depends on your project. Just go to the Inspector and refer to the permission you are going to check. Don’t forget to use ?. - the optional chaining operator. This will allow you to handle situations when a given permission doesn’t exist at all. Not using the optional chaining operator, in case of absence of property, Retool will fail to evaluate the expression - and will replace it with false. That’s not what we need!

Disabling a button based on current user permissions
Disabling a button based on current user permissions

Now we are good to go to the queries. Believe it or not, it’s even more interesting!

Securing SQL queries with custom user permissions

Okay, first of all, you can use the same approach as we have just demonstrated for UI components. To do so, just open the Advanced tab of the query editor and scroll down to the ‘Disable Query’ setting. Here you can put an expression that also should be correctly calculated either to false or true. In our example, the user doesn’t have the right to run this query, that’s why it won’t be run, even if the user ‘hacks’ your application and re-enables the ‘Download’ button back. Don’t forget the put the ‘?’ before the ‘.’ to correctly handle a case of absent permissions.

Disabling a query using current user permissions
Disabling a query using current user permissions

Is this enough? Yes, in most cases. But what if a user spoofs the data on his or her side, and re-enables back both the button and the query? Let’s add one more check. Now we will inject a permission check directly into the SQL query.

To do so, we should refer to permission in a WHERE clause to return rows only if the permission exists and equals 1. Nothing tricky, but to avoid problems with prepared statements enabled, we should wrap this check into a JOIN statement or a subquery. And remember: here’s the place we use the regular chaining operator ‘.’

No rows is returned if a permission is set to 0.
And the data is returned, if permission is set to 1.

Looks like we are all good! Just imagine: we have just implemented a simple access control system that works on no-paid tools. By the way, this approach is a simple fork of the solution that we have covered in the previous article. The only huge difference is that we use embedding user permissions inside the current_user object - but all other steps remain the same. Good luck!

---

Meet Dmitry, Low-Code Department Technical Lead at Akveo. With a rich background in media and marketing, Dmitry brings an extraordinary look at the world of IT technologies. He is deeply passionate about finding the most effective and elegant solutions to complex client problems. In our blog articles, Dmitry will share his expertise in Retool and other low-code platforms to offer you valuable insights on how to use these tools to enhance business process efficiency.

Dmitry Sitnik

If you have any questions, feel free to leave me a personal message on LinkedIn. We are here to help.

Retool Worflows
Subscription
Subscribe via Email

Want to know which websites saw the most traffic growth in your industry? Not sure why your SEO strategy doesn’t work?

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

By clicking “Subscribe” you agree to Akveo Privacy Policy and consent to Akveo using your contact data for newsletter purposes

More articles by themes

Why do I see it already open?

Only our dear subscribers can see locked content and exclusive publications in our blog. You see the locked content because you've already subscribed on Akveo publishing!

continue reading