RLS in SQL Reports

Understanding Row Level Security (RLS)

RLS is a powerful feature that allows you to restrict data visibility in reports based on user roles or attributes. This ensures that users only see the data relevant to them.

Sprinkle supports RLS for Table, SQL, and Model-based reports.

To apply RLS to a SQL report:

1. Create an RLS Attribute: Create and configure the required RLS attribute at the admin level, including assigning the appropriate values to users or groups. Refer to the detailed RLS documentation for more information.
2. Open the SQL Report: Access the SQL report where you want to apply RLS.
3. Add the RLS Filter: Declare the RLS attribute in the SQL query using the rls_filter syntax in the WHERE clause.
4. Specify the Table: Pass the table name to the rls_filter function. If the table has an alias, include the alias as the second parameter.
5. Save the Report: Save or run the SQL report to apply the RLS filter.

RLS Syntax

Use the following syntax to apply Row Level Security directly in your SQL query:

1. Without an alias:

{{rls_filter(<tablename>)}}

2. With an alias:

{{rls_filter(<tablename>,<tablealias>)}}

Both syntaxes should be used in the WHERE clause of the SQL query.

Example

Suppose an RLS attribute has been created at the admin level to restrict users based on their Country.

If the table is sales_data, you can apply the RLS filter as follows:

Without an alias:

SELECT *
FROM sales_data
WHERE {{rls_filter(sales_data)}}

If the table has an alias, for example s, use:

SELECT *
FROM sales_data s
WHERE {{rls_filter(sales_data,s)}}

The RLS filter uses the RLS attribute configuration and the values assigned to the logged-in user or their group to restrict the rows returned by the SQL report.

Note: The RLS attribute referenced in the rls_filter syntax must already be created and configured at the admin level before it can be used in an SQL report.

Finding the RLS Syntax

You can find the rls_filter syntax in the Info section of the left panel in the SQL Report editor. Use the syntax provided there when adding RLS to your SQL query.

Key Benefit: RLS can be applied directly within the SQL query, giving you control over which rows are returned based on the RLS values assigned to the user or group.