Row Level Security (RLS)
  • 24 Oct 2021
  • 6 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

Row Level Security (RLS)

  • Dark
    Light
  • PDF

Video Walkthrough


Overview

Row Level Security (RLS) is a powerful new feature that 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.

The RLS system uses a variety of filter types that can be applied, such as a Base filter that can be applied to all queries except for those defined in the filter and a Regular filter that supports the fine-tuning of query access via Data Access Roles.

In this article we will describe a walkthrough of how to create both Data Access Roles and Row Level Security filters, how to correlate them with each other and, lastly, how the filters are applied during the chart visualization process.


Goal

In this walkthrough, we will demonstrate the functionality of Preset's Row Level Security (RLS) feature using the birth_names dataset.

We will start by creating 4 data access roles:

  • All Boys
  • All Girls
  • All Names Starting with "A"
  • All Names Starting with "B"

After defining these, we will create a series of filters designed to do the following:

  • Show no gender data.
  • Show no name data.
  • Show all boys.
  • Show all girls.
  • Show names starting with "A".
  • Show names starting with "B".

After defining the roles and filters, we will use a Word Cloud chart to demonstrate that:

  • By default, all users will not see any gender or name data.
  • When we assign a specific user to a role, they will be able to view the relevant data (e.g., if we assign user Michael to the "All Girls" role, then he will only be able access records with girls from the birth_names dataset).

Step 1: Define Roles

Our first step is to define the roles listed above. To do this, just hover your cursor over Settings in the Toolbar and, in the drop-down menu, select Data Access Roles.

Select_Data_Access_Roles

On this screen, we're going to add the 4 data access roles listed above. Start by selecting the plus icon.

Data_Access_Roles2

In the Name field, enter A Names and then select Save. Later on, when we assigned a user to this role, the user will only be able access names that start with the letter A from the birth_names dataset.

A_Names_1a

Continue the process for the remaining 3 data access roles. Your finalized table should look as follows:

Data_Access_Roles3

Great job! Your data access roles are all set. Now let's set up the filters.


Step 2: Define RLS Filters

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

Select_Row_Level_Security

Like before, select the plus icon to add a new record.

Row_Level_Security2

Filters: Don't Show Any Gender and Name Data

The first two filters we will add are designed to not show any gender or name data to all users. These types of filters are called Base filters because they are applied to all queries except for the roles defined in the filter (if applicable).

To create the filter to not show gender data:

  1. In the Filter Type field, select Base.
  2. In the Tables field, select the birth_names dataset.
  3. In the Roles field, leave this blank so that it will be applied to all users.
  4. In the Group Key field, enter the term gender.
  5. In the Clause field, enter 1 = 0.
  6. Select Save.

Here is what the Base filter looks like for the gender group:

Show_no_gender_to_all1a

After saving this, just repeat the process again for names: just use the Group Key of name for the second filter, but leave all other fields the same. By the way, you'll note that we used 1 = 0 in the Clause field — this is a simple SQL code that always returns a false result.

Once both filters are defined, they should look as follows:

Show_no_gender_and_name_to_all1

Filters: Show All Boys and All Girls

Our next pair of filters will show All Boys and Girls. Like before, select the plus icon to create a new filter.

Unlike the previous pair of filters, these filters will be a Regular type. A regular filter is simply one that applies a specific filter to a role.

To create the filter for All Boys:

  1. In the Filter Type field, select Regular.
  2. In the Tables field, select birth_names.
  3. In the Roles field, select Boys.
  4. In the Group Key field, enter gender.
  5. In the Clause field, enter gender = 'boy'.
  6. Select Save.

Here is what the Regular filter looks like for the gender group:

RLS3a

Like before, repeat this process to create another filter for All Girls. The only differences are:

  • In the Roles field, select Girls.
  • In the Clause field, enter gender = 'girl'.

Your completed filters should look as follows:

Filters_All_Boys_and_All_Girls2

Filters: Names Start with "A", Names Start with "B"

Our last pair of filters will be used to display all names starting with the letters "A" and "B" — let's start with the letter A.

Like before, select the plus icon to create a new filter.

  1. In the Filter Type field, select Regular.
  2. In the Tables field, select birth_names.
  3. In the Roles field, select A Names.
  4. In the Group Key, enter name.
  5. In the Clause field, enter name like 'A%'.
  6. Select Save.

Your new filter should look as follows:

Add_A_Name

You guessed it! We'll repeat the process to create our last filter for names starting with the letter "B". All fields are the same, except for the following:

  • In the Roles field, select B Names.
  • In the Clause field, enter name like 'B%'.

Great work, all done! Here's what your 6 new filters should look like:

All_Filters1


Step 3: Assign User to Role and Test

For these tests, we are going to use the Word Cloud chart type and, as mentioned above, the birth_names dataset.

Start by creating a new chart and selecting both of these values. If you need assistance, please read our Create a Chart topic for guidance.

Test 1: No Results

If we immediately try to run the query, we will return no results. This is because the first pair of filters we created—shown in the graphic below—returns no results for all gender and name queries from the birth_names dataset.

Show_no_gender_and_name_to_all1

Test 2: Show Names Starting with "A"

Now, let's navigate to the Data Access Roles screen. In the A Names row, select the Edit record icon.

Test_2_A_Names

In the Users field, enter/select your username and then select Save.

Add_User_A_Name

Now, let's try running the query again. You will notice that, like last time, there are no results returned.

Word_Cloud_No_Results1

Why? This is because the gender filter is still being applied (i.e., all genders will not be retrieved from the dataset). Let's try again.

Test 3: Show All Girls

We can address this by adding your username to the Girls data access role. As above, navigate to the Data Access Roles screen and select Edit record for the Girls record.

Add_User_Girls1

In the Users field, add your username and then select Save.

Your Data Access Roles screen should now look as follows:

All_Girls_All_A_Names1

Try running the query! The Word Cloud should now appear featuring girls whose name begins with the letter "A":

Word_Cloud_Results_Girls_A_Names

Test 4: Show All Boys, All Girls Starting with A

Great work! Repeat the process above for the Boys data access record. Your Word Cloud should now show all boys and all girls whose name starts with the letter A:

Word_Cloud_Results_Boys_Girls_A_Names

Test 5: All Boys, All Girls Starting with A and B

As a final step, let's go ahead and add your username to the B Names data access record. Your Data Access Roles screen should now look similar to the following, with your username indicated for all roles:

Data_Access_Roles_ALL_Roles1

...and, to finalize this walkthrough, run the query. All of the Row Level Security filters of the Regular type should now be applied:

Word_Cloud_Final


Was this article helpful?

What's Next