Skip to main content

Restrict rows with filters

Learn how to add row filters to an access policy so a group uses only the rows it needs.

Add row filters to a granular access rule to limit which rows in a table the rule grants. For example, combining row filters with column grants allows access policies to express rules such as: an EMEA group can read the name and email columns, but only for customers in the EMEA region.

Create the rule the filters belong to first. Refer to Configure access policies for guidance on creating access policies and adding rules.

Add a row filter​

  1. On the resource's Access Enforcement tab, click the Edit ( ) icon to open a policy, then select Edit under Granular access.
  2. In the Tables pane, select the table you want to filter. Click + Add rules if there are no access rules attached.
  3. Under Row filters, select + Add filter.
  4. Choose a column, choose an operator, and enter a value. Is null and Is not null take no value.
  5. To add more filters, select Add filter again, then choose AND if all filters must match, or OR if at least one filter must match.
  6. Click Save edits.
  7. Then select Save changes on the Access Enforcement tab.

Each filter is one condition: a column, an operator, and a value. Multiple filters on a rule form a single flat list. Your choice of AND or OR applies to every filter in the rule, so a rule is either all AND or all OR. You cannot mix the two or group filters into nested expressions such as (A AND B) OR C.

A rule with no row filter grants every row in the columns it covers.

Refer to Row filter operators for every operator and how each column type is compared.

Filter on a range​

A column can appear in more than one filter, which is how you bound a range. Add two filters on the same column: one Greater than the lower bound and one Less than the upper bound. For example, a rule on an orders table with the filters total Greater than 100 and total Less than 500 grants only the orders in that range.

A range depends on both filters being required, so the rule must use AND. Choosing OR does not bound anything, because a row outside one bound usually satisfies the other.

Filter by the user running the query​

Use an {{ }} embedded expression as the filter value to make one policy behave differently for each member of a group. Retool evaluates the embedded expression per user when the query runs, using the current_user object to identify them.

To scope rows to the user who owns them, filter on a column holding their identity:

  1. Choose the column that identifies the owner, such as owner_email.
  2. Choose Equal to.
  3. Enter {{ current_user.email }} as the value.

To filter on a value that current_user does not provide, store it as a user attribute and reference it the same way. With a region code stored as an attribute, a filter of region Equal to {{ current_user.metadata.region }} scopes each user to their own region without a mapping table. Refer to Configure user attributes for more information.

Make sure every user the policy covers has the attributes its filters reference. Retool blocks a query rather than running it unfiltered when an embedded expression cannot be evaluated.

Refer to Supported embedded expressions for the full list, including which values cannot be used.