Row Level Security (RLS)
  • 05 Jul 2022
  • 4 Minutes to read
  • Contributors
  • Dark
    Light

Row Level Security (RLS)

  • Dark
    Light

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.

Exclusionary_Base_Filter

Good to Know: RLS and SQL Lab

Please note that RLS is not applied to SQL Editor. Even though a user won't be able to see chart data using RLS, a Limited Contributor workspace role and above would still be able to query the data via SQL Editor.

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

Post-Base_Filter_No_Results

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.

Inclusive_Regular_Filter2

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.

DAR_User_Associated


RLS Filter Verification

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

DAR_Final

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

RLS_Final2

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

Chart_Final

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).


Was this article helpful?