Row Level Security (RLS)

Overview

Row Level Security (RLS) is a Preset feature that—when used in tandem with Data Access Roles—enables you to exert a granular level of control over who can query and view specific data in selected datasets. This level of query-based access control empowers organizations to align data access permissions (via roles) with larger-scale business initiatives.

Concept

What is Row Level Security (RLS)?
RLS filters queries to a dataset using either a base or regular filter type. A base filter applies to all queries, except for roles defined in a filter. A regular filter applies to queries when the user belongs to a role.

What is the relationship between RLS and Data Access Roles?
Data Access Roles are used to define a role and then associate one or more users with that role. For example, a "Marketing Analyst" role that is assigned to five users from an external marketing contractor firm.

RLS is used to create filters that define how information within a dataset is queried. After an RLS filter is defined, it can then be assigned to a Data Access Role. For example, the five external users with the "Marketing Analyst" role, mentioned above, will only be able to query a dataset used for marketing-related dashboards, and be excluded from querying all other datasets.

Use Case Overview

To illustrate the relationship between RLS and Data Access Roles, we will create a base filter and a regular filter using the following details:

Datasource

Database: examples
Schema: public
Dataset: Video Game Sales
Description: This dataset contains sales data about popular video games in terms of platform, genre, publisher, etc.

Objective

Create a base filter that excludes query access to all game platforms within the dataset — this will be applied to all users. We will then create a regular filter that only grants access to video games using the Wii platform — this will be applied to a Data Access Role. The end result is that users who are assigned to the role will be able to view a chart that only includes video games on the Wii platform.

Define Exclusionary Base RLS Filter

To start, we will create a base filter that excludes access to all video game platforms within the dataset.

In the Toolbar, hover your cursor over Settings and then select Row Level Security.

Select + Add new record and complete the following fields:

• Filter Type: Select Base. This filter type applies to all queries.
• Tables: Select public.Video Game Sales.
• Roles: For this use case, leave blank (i.e., applies to everyone). Since this is a base type, the filter will not be applied to any roles that are defined in this field.
• Group Key: Enter platform. To view options, use SQL Editor to explore the dataset.
• Clause: Enter 1 = 0. This is a condition, expressed in SQL, that defines the rule to apply to the query. In this case, the false query "1 = 0" will always return no rows.

When done, select Save.

Now let's view the "Games" table chart to test out the base filter.

As expected, the Base RLS filter is preventing access to data within the Video Game Sales dataset.

Define Inclusive Regular RLS Filter

At the moment, users cannot access platform data within the Video Game Sales dataset. Now let's allow a specific user to view specific data from the dataset.

Create Data Access Role

The first step is to create a Data Access Role.

In the Toolbar, hover your cursor over Settings and then select Data Access Roles.

Select + Add new record and, in the Name field, enter Wii Platform. This should be a descriptive term for the new role.

For now, select Save and let's define an RLS filter.

Create RLS Regular Filter

As above, navigate to Row Level Security, add a new record, and define the following fields:

• Filter Type: Select Regular. This filter type adds WHERE clauses to queries if a user belongs to a role referenced in the filter.
• Tables: Select public.Video Game Sales.
• Roles: Select Wii Platform, as defined above. The filter will be applied to any role(s) selected here.
• Group Key: Enter platform. To view options, use SQL Editor to explore the dataset.
• Clause: Enter platform = 'Wii'. This is a condition, expressed in SQL, that defines the rule to apply to the query. In this case, the filter will limit the display of platform data to "Wii".

When done, select Save.

Associate Role with a User

Lastly, we will associate the Wii Platform role with a user.

Navigate back to Data Access Roles and select Edit record for the Wii Platform role you created earlier.

In the Users field, select one or more users to which the role will be applied.

When using RLS in conjunction with Data Access Roles, keep the Permissions field blank.

Select Save.

RLS Filter Verification

Your Data Access Role screen should look similar to the following:

...and your Row Level Security screen should look as follows:

Now let's test the RLS regular filter by navigating to the "Games" table chart and running the query.

As you can see, the user can now only access records from the dataset where the platform is "Wii" (note the Platform column in the table).

Advanced Row Level Security using Username

Admins who have user access information stored directly in their database can use RLS to automatically control data access scalably.

To set this up, you will use a combination of Jinja macros, APIs, and the Regular RLS filter.

Before creating the new RLS rule, you will need to use the Preset API.

Mapping Preset Usernames to Emails

Using the Get Team Members API, you can regularly retrieve a list of email addresses and Preset usernames for your users. This can be retrieved by your ingestion engine and stored in your database integration layer. You can then join these Preset usernames to any existing data tables that contain user access by email.

Set up the Row Level Security Rule

Once the Preset username is stored in your tabes, you can set up the Regular RLS filter using the Preset Jinja maco. First, choose Regular as the Filter type and select the tables in your database that both contain the user access information and require filtering based on the user access

Add a Clause statement at the bottom for the WHERE clause that filters data access based on the user's login username.

[myusercolumn]='{{ current_username()}}'

Replace [myusercolumn] with the column in your table that contains the Preset username of who should be able to access this data.

You're all set! Your users now will only see data filtered to them when querying the tables you have selected.