Sigma Computing is a robust cloud-based Business Intelligence platform that leverages a spreadsheet-like interface to explore your data at lightning-fast speed. It sits on top of your existing cloud database and uses a secure, live connection to query the data in your warehouse directly, returning your results right to you in your browser. Combined with a product like the Snowflake Data Cloud, Sigma allows you to easily view and analyze your data in various ways.
As with most cloud technologies today, one of the main concerns is how to secure this data and make sure it is handled and viewed by the right people. Sigma has several features that address these concerns. This blog will explore how Sigma provides peace of mind through its Row-Level Security feature.
What is Row Level Security, and Why Does it Matter?
Row-Level Security is the method of using filters on user information to restrict access to data at the row level in the database. Suppose you have a database with sales information for several employees that shows the number of sales made by each employee in a region, as shown by the picture below.
The database administrator will still have full access to view all of the data and will see what is shown in the picture above. However, to restrict what each individual user can access, you can implement a Row-Level security policy which will filter the records that show up for each employee based on their user_id.
In the picture above, you can see that when user_1 logs in, the rows of the database are filtered down on the user_id column to only the rows that match their user_id, and similarly with user_2.
Row-Level Security is important because it allows you to restrict users’ access at the database level rather than the application level. The security is handled all internally by the database with no external dependencies. No matter how a user accesses the database, these policies are in effect and will only allow them to interact with the appropriate data. It also allows you to give users direct access to query their own data instead of needing to work with someone who has enough access to get them the data they need.
Sigma offers two different types of Row-Level Security: Dataset Row-Level Security and Worksheet Row-Level Security.
How to Enable Dataset Row-Level Security
To enable Row-Level Security, you need to have a dataset in Sigma. Below, you can see an example dataset with Marketing Campaign data.
Click on the Worksheet Tab
Make sure you are in “Edit” mode in the top right.
Create New Column and Add Formula
Note: The formula for populating the new column needs to produce true/false values.
When creating a formula like in the screenshot above, the target column that will be used as input is enclosed with brackets. In our example, the new column was populated based on the value of the [Campaign] column.
For filters based on the current user, Sigma offers three functions: CurrentUserEmail, CurrentUserInTeam, and CurrentUserAttributeText.
CurrentUserEmail
CurrentUserEmail will retrieve the email of the user that is currently logged in and viewing the data. An example filter for CurrentUserEmail would be:
[Email] = CurrentUserEmail()
This will look at the column [Email] and compare the values to the user currently viewing the data.
CurrentUserAttributeText
Similarly, CurrentUserAttributeText takes an input of the name of an attribute, and will return the value of that attribute for the current user. For example, a filter for our example dataset might be:
CurrentUserAttributeText("Title Attribute") = (Title)
For this example, team members will only see the rows in the dataset that correspond to their value in the “Title” column.
CurrentUserInTeam
CurrentUserInTeam works a little differently by checking if the user is associated with a Sigma Team, which is an organized grouping of users. In our example, different people may follow up on leads with prospective companies based on the job title of the contact at the company. To set this up, we create a column that associates the row with the correct team in Sigma based on the contact’s job title, like below.
If( [Title] = "PHP Developer", "Developer", [Title] = "SSIS Programmer", "Developer", [Title] = "iOS Mobile Developer", "Developer")
You would need to set this logic to account for all values in the column in your dataset, and once this column has been created, then create the filter column and add the formula:
CurrentUserInTeam([Team])
This will return True for each row where the current user belongs to that row’s team.
Note: Using the ‘or’ operator, you can also set your filters to test for multiple conditions using a combination of CurrentUserEmail() and CurrentUserInTeam().
( [Email] = CurrentUserEmail() ) or ( CurrentUserInTeam("Developer") )
Add Filter to Column
Once you have the filter column created and populated with true/false values, you’ll need to exclude the false values by unchecking “False” and “Null”. You can filter either by clicking on the downward facing arrow next to the column name and selecting Add Filter. Or click the plus sign next to Filter on the far left side of the worksheet.
Hide Filter Column
In the “Base Columns” box on the right hand side, click the down arrow for your filtered column, and then select “Hide Column”. The filter column will display as grayed out in the column list and won’t display when users are viewing your table, avoiding any potential confusion from the extra information you have added to the table to perform filtering.
How to Enable Workbook Row-Level Security
Secure filters, which are filters hidden from all non-admin users or those with “Can Edit” access on the workbook, are used to implement Row-Level Security in Workbooks in Sigma. Note: you must be in edit mode to create a secure filter.
Create a New Page
Click the “+” button in the bottom left corner of the workbook.
Create Table for Filtering Data
Create a table on the newly created page with the data you would like to filter and share using the Elements menu on the left-hand side.
After the table has been created, create your filter column, add the formula to populate the column with True/False values, and add a filter to the column to only display the rows with “True” values, just like when working with a Dataset.
Hide the Page
Click on the caret icon on the page’s tab to open the menu.
Click “Hide” to hide the page, and rename the page to identify it as your hidden page for implementing Row-Level security.
Create Table to Display Filtered Data
On a non-hidden page in the workbook, create a new table with the source as the table on your hidden page.
In the “Select source for Table” dialog, select the “Page Elements” tab and click on the table from the hidden Row-Level Security filtered page.
When a user views this workbook, the hidden table will automatically filter so they will only see results based on the criteria you set. Only users with “Can Edit” access will be able to access the hidden page with the filters on it; all other users will only be able to see the filters and controls that are present on the visible pages.
Pro-Tip: To implement Dataset/Workbook Row-Level Security, you must have Can Edit access on the individual dataset/workbook, or have the “Creator” account type.
Closing
Sigma Computing is a very powerful, real-time analytics and visualization tool which leverages Row-Level Security to lock down access to your data. When combined with other cloud data solutions like Snowflake, which also leverage row and column level security, you can truly have peace of mind over the entire lifecycle of your data– no matter where it is accessed from and who is viewing it along the way.
In just a few steps, you can easily set up a variety of access for everyone to get to what they need, and only what they need.
If you have any further questions about how to set up a security plan within your Sigma Computing instance, we at phData have the resources and expertise to help. Reach out to us today!
