Permissions
Role based access control
Resource: https://retool.com/resources/secure-internal-tools-guide
Concepts
Role-Based Access Control (RBAC) is a widely used access control model that restricts system and data access based on predefined roles assigned to users. In RBAC, access permissions are associated with specific roles instead of directly with individual users. Users are then assigned one or more roles based on their responsibilities and job functions within an organization. This approach offers centralized administration, simplifies access management, improves security, and facilitates scalability in large systems. By granting or revoking permissions to roles, RBAC enables efficient control over users' access rights, ensuring they only perform authorized actions in a system or access data that is relevant to their roles.
Row Level Security (RLS) uses RBAC to control access to rows in a database table. With RLS, the visibility of individual rows in database tables is controlled based on predefined rules or policies. These rules are typically defined by certain attributes or conditions associated with the data, such as user roles, permissions, or other characteristic properties. RLS allows organizations to enforce fine-grained data access control, ensuring that users can only view or manipulate the rows of data that they have explicit permission to access, while hiding or blocking unauthorized rows.
Example
The example below has an orders table with orders for different clients (the group_id column designates the client assigned to each row). There's also a user_groups table which maps users to groups. When John or Emma selects data from the orders table, John or Emma should only see the first two rows. They should not be able to access the other rows, which belong to other clients.
Orders
| id | group_id | description |
|---|---|---|
| 1 | 1 | order 1 |
| 2 | 1 | order 2 |
| 3 | 2 | order 3 |
| 4 | 2 | order 4 |
| 5 | 3 | order 5 |
| 6 | 3 | order 6 |
User Groups
| group_id | name | |
|---|---|---|
| john@example.com | 1 | John |
| emma@example.com | 1 | Emma |
| david@example.com | 2 | David |
| sophia@example.com | 2 | Sophia |
| alex@example.com | 3 | Alex |
| olivia@example.com | 3 | Olivia |
You can apply this filter by using an inner join. The inner join will return only the rows where the group_id matches the group_id of the user who is logged in.
SELECT *
FROM orders as a
INNER JOIN (SELECT group_id from user_groups where email = {{ current_user.email }}) as b
ON a.group_id = b.group_id
Security considerations
The request passes from the client, to the Retool server, to the database, before returning along the same path.
Prevent query variable spoofing is available by default. It prevents the client from intercepting the initial request and inserting a different user’s email. This ensures that the parameterized statement which arrives at the server contains the same current_user.email as the client who is logged in.
To ensure the security of the request passing from the server to the database, you can take several approaches. Deploying the server and the DB within the same VPC avoids exposing these requests to the public internet. You can also enable encryption communication between the server and the DB using SSL certs.
This architecture has two advantages:
- Since the DB filters the result set, you avoid sending unfiltered data over the network. This minimizes the amount of data exposed over the network, and minimizes the blast radius of a breach.
- Data transmitted over the network is minimized, which reduces latency.