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.

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.

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.

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.

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!

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.

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 ‘.’

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!

---

Dmitry is the Head of Low-Code Department 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 Retool to enhance business process efficiency.