Giving users access to all the data in your cloud data warehouse is extremely powerful for enabling them to make business decisions. But occasionally, we need to restrict the data they can access based on their role or personal attributes. This ensures one employee can not see the salary of another, a manager can only access data of their subordinates, or different teams only see what is needed within their team. Row-level security (RLS) is a way to ensure users only have access to the level of data they need.
Sigma Functions and Attributes
Sigma provides a variety of functions and attributes that can be leveraged to apply the proper RLS depending on who the user is, what team they are on, or any attributes they have been assigned. The below functions can all be leveraged to apply RLS in a similar fashion:
- CurrentUserEmail() : Returns the current user's email address. Used to limit the row based on email address.
- CurrentUserInTeam() : Returns True if the current user is a member of any of the given teams. Used to limit the row based on who is on the team.
- CurrentUserAttributeText() : Returns the attribute value for the current user as a text value.Used to limit data access based on attribute values assigned to a user or team.
Getting Started With RLS: CurrentUserEmail()
There are many ways to implement RLS in Sigma, but the recommended best practice is to start with a dataset. A dataset in Sigma can be set up as a standard object or data model that can be used across many workbooks. This means that any security, filters, calculations, etc. are locked into the dataset and cannot be modified by others, but they will still be able to use and reference the data model. Behind the scenes, we can then apply filters to limit and create rules for what a specific user or team is allowed to see.
In this example, we will start building out RLS based on a user’s email and leverage the CurrentUserEmail() function. In the worksheet tab of the dataset, there is a column named [User Email] which identifies which user can see this row of data.
First, you want to add a new column to calculate if the user who is looking at the data has access to this row. You can do this by adding a new column, writing the formula [User Email] = CurrentUserEmail(). This will evaluate to True or False as you can see in the example below.
You can see that my email does not match the user email field, and thus returns false.
The next step is to add this to a filter so that we can only see data when it evaluates to true. You can do this by adding a new filter on the [Current User] field, and setting it to True.
The last thing we want to do is Hide this column from the table so that when other users are using this dataset, they do not need to see these fields. You can do this by using the dropdown to hide them.
You can then publish your dataset and feel confident that anyone using this as a source will only see data associated with their email and the Current User filter is evaluating to True.
RLS: CurrentUserInTeam()
Teams in Sigma provide a way to manage many users as part of a user group.
Below is an example of how you can leverage Sigma Teams to apply RLS at a group level instead of an individual user.
In this example, we are checking the [Team Access] column to see if the current user is part of the team that is listed.
RLS: CurrentUserAttributeText()
User Attributes in Sigma provide a way for companies to assign a user or team attributes that can then be leveraged downstream in datasets and workbooks to provide RLS.Example: Region=”West”
Below is an example of how you can leverage Sigma User Attributes to apply RLS at a group level instead of at an individual user level.
In this example, we are checking the [Region] column to see if the current user has been assigned the “Store_Region” User Attribute and that only the values assigned within that attribute are evaluated to True.
RLS: OAUTH
The last way to implement RLS in Sigma is leveraging an OAUTH connection type. When using OAUTH, the user will inherit any security policies that have been defined for that user in the data warehouse. This means that you do not need to manage any user access policies within Sigma because they are already being managed in the warehouse.
Read the Top 20 Questions about Sigma here.
