Skip to main content

How to Configure Row-Level Permissions for Data Tables

Introduction​

Row-level security control (RLS) is one of the important mechanisms for data access control. AskTable allows users to access part of the data in database tables based on specific conditions. By defining row filtering rules, it ensures that users can only access data that meets certain conditions.

In AskTable, row-level permission rules are defined through the rows_filters configuration in policies. This article will introduce how to write row filtering rule expressions to correctly configure row-level permissions for data tables.

Basic Format of Row Filtering Rules​

Row filtering rules follow the following format:

<schema_name>.<table_name>.<field_name> <expression>
  • schema_name: Database schema (DB), can use * for wildcard matching. Excel/CSV uses the file name as the schema_name.
  • table_name: Database table name, can use * for wildcard matching. Excel uses the sheet name as the table_name.
  • field_name: The field name used to filter rows.
  • expression: A condition expression that defines the constraints for user access to data.

Example:

db1.users.user_id = {{user_id}}

This means users can only access rows in the db1.users table where the user_id matches their own ID.

Supported Operators​

Row filtering expressions support the following operators:

  • Comparison operators: =, >, <, >=, <=, <>, !=
  • Set operators: IN, NOT IN
  • Fuzzy matching: LIKE, NOT LIKE
  • Logical operators: IS NULL, IS NOT NULL

Examples:

db1.orders.customer_id = {{customer_id}}
db1.employees.department_id IN {{allowed_departments}}
db1.sales.region LIKE {{region_pattern}}

Using Variables​

Variables must be wrapped with {{ }} and corresponding values must be passed when calling APIs.

Example:

db1.employees.manager_id = {{manager_id}}

If the value of the manager_id variable is 123, then the SQL resolves to:

SELECT * FROM db1.employees WHERE manager_id = 123;

Variable Examples​

VariableDescriptionExample Value
{{user_id}}Current user's ID1001
{{department_id}}User's department ID5
{{region_pattern}}Region filtering pattern'East%'
{{start_date}}Start date'2024-01-01'

Time Condition Filtering​

Time-based filtering rules are supported, and dynamic time ranges can be calculated using NOW().

Example:

db1.orders.created_at > NOW() - INTERVAL '1 YEAR'

This means users can only access order records from the past year.

Usage Restrictions for Complex Conditions​

Currently, rows_filters only supports single-condition filtering and does not support combining multiple conditions with AND, OR.

Example (Not Supported):

db1.orders.customer_id = {{customer_id}} AND db1.orders.status = 'active'

It needs to be split into two rules (both must be satisfied):

db1.orders.customer_id = {{customer_id}}

and

db1.orders.status = 'active'

Typical Use Cases​

1. Users Can Only View Their Own Data​

db1.users.user_id = {{user_id}}

2. Managers Can Only View Employees in Their Own Department​

db1.employees.department_id = {{department_id}}

3. Only View Orders from the Recent Year​

db1.orders.created_at > NOW() - INTERVAL '1 YEAR'

4. Access Only Specific City Customers​

db1.customers.city_id = {{city_id}}

Conclusion​

By configuring row-level permissions through rows_filters, flexible data access control can be achieved. Ensure that rules are written correctly according to business requirements and appropriate variable values are passed when calling APIs to ensure correct data access.