Embedding 09: Dynamic Role Switching with Snowflake

This QuickStart introduces you to Sigma embedding using Dynamic Connection & Role Switching with Snowflake; (DRS) to limit the data exposed to a user based on values passed to Sigma at runtime. This QuickStart assumes you have already taken the QuickStart Embedding 1: Prerequisites so that you have a sample environment to complete the tasks in this QuickStart.

We also recommend you take the the QuickStart Embedding 3: Secure Embedding as we will build on that content.

Some steps may not be shown in detail as we assume you have taken these other two QuickStarts or are familiar with Sigma workflows.

DRS allows run-time swapping of the Snowflake Warehouse and Role for a Sigma connection. There are ways to leverage this using one or both Warehouse and/or Role along with different methods to restrict data access in Snowflake itself.

This QuickStart will cover how to implement RDS using User Attributes defined in Sigma, with a Secure Embed.

Many customers will use Snowflake's column masking functionality and also also manage their Snowflake Row Access Policy in a Table. Sigma fully supports these methods although we want to keep this simple and focused on the main task; DRS.

In light of that, we will just use trial accounts and the provided Snowflake AccountAdmin Role for the scripts we will run in Snowflake.

Target Audience

Semi-technical users who will be aiding in the planning or implementation of Sigma. Limited SQL and technical data skills are needed to do this QuickStart. It does assume some common computer skills like installing software, using Terminal, navigating folders and edit/copy/paste operations.

Prerequisites

A computer with a current browser. It does not matter which browser you want to use.
Access to your Sigma environment. A Sigma trial environment is acceptable and preferred.
A working web server based on Node.js as demonstrated in the QuickStart Embedding 1: Prerequisites
Some content to embed. You can embed a workbook, Table or Visualization.
Downloadable project files discussed later in this document.
A Snowflake account with the proper administrative and security admin access.

Sigma Free Trial Snowflake Free Trial

Node.js and required Packages (all free, discussed in prior QuickStarts in detail):
- Express
- Node-supervisor
- crypto (included with Node.js installation)

What You'll Learn

The exercises in this QuickStart will discuss and walk you through the steps to implement DRS with User Attributes (UA).

What You'll Build

We will embed Sigma content inside a Node.js web application, passing runtime parameters to configure the embed and demonstrate limiting data based on passed Snowflake Warehouse and/or Role.

Footer

Let's start by logging into our Snowflake trial account, verify we are using an AccountAdmin role and that there is a Warehouse for us to use:

Our Warehouse name is COMPUTE_WH and is started for us.

Navigate to Worksheets and click the button + Worksheet:

Click on the 3 vertical dots (as shown below) and rename the Worksheet to Sigma DRS Configuration Scripts.

We will proceed slowly now, cutting and pasting operations one by one so that we can clearly see what is happening as we go. Comments are included for those less familiar with Snowflake commands.

Confirm or change your working Role and Warehouse to ACCOUNTADMIN and COMPUTE_WH as:

Copy and paste this code into the new Worksheet:

// SECTION 1: DATA CONFIGURATION
// ---------------------------------------------------------------------------------------------------------------

// 1: CREATE OR REPLACE DATABASE DRS
CREATE OR REPLACE DATABASE SIGMA_DRS;
USE DATABASE SIGMA_DRS;

Highlight the command CREATE OR REPLACE DATABASE SIGMA_DRS; and click the run button in the upper right corner.

We should get a message (#2 in the screenshot) that the command was successful.

Click the Databases tab and refresh the list.

We now have a new Database called SIGMA_DRSin our Warehouse.

Now lets create a new Schema in our database. Copy/Paste and run this command:

// 2: CREATE A NEW SCHEMA TO HOLD OUR FUTURE SAMPLE DATA TABLE:
CREATE SCHEMA IF NOT EXISTS DRS_DATA;

Results should be as follows. Note the selection of current Database / Schema is switched to our new one for us.

We may have to refresh the Database objects list if the new Schema is not present after running the command.

Next step we will create an empty table to hold a very small (30 rows) of sample data.

Same operation please:

// 3: CREATE THE SAMPLE DATA TABLE SCHEMA after switching context to drs.public.
CREATE OR REPLACE TABLE SIGMA_DRS.DRS_DATA.DRS_SAMPLE_DATA (
store_region varchar,
cust_key varchar,
order_number varchar,
date varchar,
quantity varchar,
cost varchar,
price varchar,
product_type varchar,
product_family varchar,
product_name varchar,
store_name varchar,
store_state varchar,
store_city varchar
);

Result will be a new Table with our columns defined. All columns are varchar as that is not important in this exercise:

Do this QuickStart we will load our sample data from a public S3 bucket. We will copy the data into a Snowflake Stage and them load it from the Stage into our Table.

Copy/Paste and run the following commands one at a time so we can observe the success messages:

// 4: CREATE OR REPLACE SCHEMA FOR EXTERNAL STORAGE OF CSV FILE
CREATE STAGE IF NOT EXISTS S3_DATA URL='s3://sigma-quickstarts-main/embedding/DRS_Sample_Data.csv';

// 5: COPY INTO SE_INTERNAL_DB.SCHEMA_PHIL_BALLAI.DRS_Sample_Data  FROM  's3://sigma-quickstarts-main/embedding/DRS_Sample_Data.csv';
COPY INTO SIGMA_DRS.DRS_DATA.DRS_SAMPLE_DATA FROM @S3_DATA;

// 6:CONFIRM DATA IS LANDED IN SNOWFLAKE TABLE:
SELECT * FROM DRS_SAMPLE_DATA;

The last command should list the 35 rows. Notice that there are 20 values in STORE_REGION for East, 10 for West and the rest are South but there will be no Role for South so only the Executive Role will see those extra 5 rows. We will use the STORE_REGION column to limit our data in Sigma later:

Footer

Now that we are familiar with using cut/paste into the Snowflake Worksheet we can move a little faster. We need to create a few Roles in Snowflake so that Sigma can leverage them later.

Copy/Paste and run section 2 as a group of commands (highlight all the code in Snowflake and click the run arrow) and make sure you see a success message:

// ----------------------------------------------------------------------------------------------------------
// SECTION 2: ROLE CONFIGURATION
// ----------------------------------------------------------------------------------------------------------

// 1: CREATE ROLES FOR OUR USE CASES:
CREATE OR REPLACE ROLE DRS_Executive;
CREATE OR REPLACE ROLE DRS_West;
CREATE OR REPLACE ROLE DRS_East;

// 2: GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLES
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE DRS_Executive ;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE DRS_West;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE DRS_East;

// 3: GRANT USAGE PERMISSION ON THE DATABASE TO NEW ROLES:
GRANT USAGE ON DATABASE SIGMA_DRS TO DRS_Executive;
GRANT USAGE ON DATABASE SIGMA_DRS TO DRS_West;
GRANT USAGE ON DATABASE SIGMA_DRS TO DRS_East;

// 4: GRANT SELECT ON SCHEMA TO NEW ROLES:
GRANT USAGE ON SCHEMA SIGMA_DRS.DRS_DATA TO DRS_Executive;
GRANT USAGE ON SCHEMA SIGMA_DRS.DRS_DATA TO DRS_West;
GRANT USAGE ON SCHEMA SIGMA_DRS.DRS_DATA TO DRS_East;

// 5: ALLOW NEW ROLES TO QUERY TABLE WE CREATED:
GRANT SELECT ON ALL TABLES IN SCHEMA SIGMA_DRS.DRS_DATA TO ROLE DRS_Executive;
GRANT SELECT ON ALL TABLES IN SCHEMA SIGMA_DRS.DRS_DATA TO ROLE DRS_West;
GRANT SELECT ON ALL TABLES IN SCHEMA SIGMA_DRS.DRS_DATA TO ROLE DRS_East;

You should receive a message in Snowflake Statement executed successfully.

Footer

We are now ready to define our Snowflake Row Access Policy. Same process as before with these new commands:

// ----------------------------------------------------------------------------------------------------------
// SECTION 3: CREATE ROW ACCESS POLICY BASED ON REGION COLUMN
// ----------------------------------------------------------------------------------------------------------

// 1: SETUP AND CREATE ROLE ACCESS POLICY
USE ROLE ACCOUNTADMIN;
USE SIGMA_DRS.DRS_DATA;
CREATE OR REPLACE ROW ACCESS POLICY DRS_REGION
    AS (STORE_REGION STRING) RETURNS BOOLEAN ->
    CASE WHEN 'DRS_EXECUTIVE' = CURRENT_ROLE() THEN TRUE
         WHEN 'DRS_WEST' = CURRENT_ROLE() AND STORE_REGION = 'West' THEN TRUE
         WHEN 'DRS_EAST' = CURRENT_ROLE() AND STORE_REGION = 'East' THEN TRUE
         WHEN 'ACCOUNTADMIN' = CURRENT_ROLE() THEN TRUE
    ELSE FALSE
END;

SHOW ROW ACCESS POLICIES;

// 2: APPLY THE ROW ACCESS POLICY TO THE TABLE
ALTER TABLE SIGMA_DRS.DRS_DATA.DRS_SAMPLE_DATA
ADD ROW ACCESS POLICY DRS_REGION
    ON (STORE_REGION);

In this next section we can run the commands for each role by themselves and observe how many rows are returned.

// ----------------------------------------------------------------------------------------------------------
// SECTION 5:  TEST OUT ROLES AND SEE DIFFERENT RESULTS. *** RUN ONE ROLE AT A TIME ***
// ----------------------------------------------------------------------------------------------------------

// 1: FOR TESTING IN SNOWFLAKE, GRANT THESE ROLES TO YOUR SNOWFLAKE USERNAME:
GRANT ROLE DRS_Executive TO USER {YOUR SNOWFLAKE USERNAME HERE};
GRANT ROLE DRS_West TO USER {YOUR SNOWFLAKE USERNAME HERE};
GRANT ROLE DRS_East TO USER {YOUR SNOWFLAKE USERNAME HERE};

// 2: EXECUTIVE = 35 ROWS
USE ROLE DRS_EXECUTIVE;
SELECT * FROM DRS_SAMPLE_DATA;

// 3: EAST = 20 ROWS
USE ROLE DRS_EAST;
SELECT * FROM DRS_SAMPLE_DATA;

// 4: WEST = 10 ROWS
USE ROLE DRS_WEST;
SELECT * FROM DRS_SAMPLE_DATA;

Notice that when you ran the command to 1select from the table1 using the role DRS_WEST that only 10 rows are returned.

This is expected and we change the Role to DRS_EAST or DRS_Executive to see different row counts.

Footer

Sigma supports dynamically changing the Warehouse and Role at embedded runtime.

First, we need to add a User Attribute in Sigma for our Connection / Role to evaluate.

In Sigma, navigate to Administration / User Attributes.

Configure the new User Attibute as shown:

Snowflake will evaluate the passed value as a Role assignment.

Create another User Attribute for Warehouse as shown:

Navigate to Administration / Connection and edit the connection called Snowflake Trial Account.

Click the bars in Role and the new User Attribute called DRS_REGION should be available.

Do the same for Warehouse.

Save the connection (you will need to supply your Snowflake account password again).

Create a Connection to Snowflake

From Sigma / Administration / Connections, create a new connection in Sigma to our Snowflake trial account.

Creating Snowflake Connection online help is here

You will need your Snowflake details to connect. To get the account url click as shown below:

Use this Account URL to configure a new Connection in Sigma, replacing the information for your Snowflake instance:

Create New Sigma Team

Create a new Sigma / Administration / Team called DRS_Team.

We will use this later to control which users have rights to access the Sigma workbook.

Accessing the workbook does not automatically mean that users also get to see all the data, since we will be limiting that based on their Snowflake Role.

Footer

Create a new workbook in Sigma and select the new Snowflake connection we just created.

Use the DRS_SAMPLE_DATA table and all columns.

There should be 35 rows shown:

Lets add two Text boxes to the workbook Page to show the current user's Role and Warehouse. Configure each text box as follows:

ROLE TEXTBOX FUNCTION:

=CurrentUserAttributeText("DRS_WAREHOUSE")

WAREHOUSE TEXTBOX FUNCTION:

=CurrentUserAttributeText("DRS_REGION")

The workbook now looks like this. Some additional text description was added to each text box but that is optional. Add text after the function is set and showing a value. Then just type the text you want, either before or after the returned value:

The values initially shown are the User Attribute default values we set earlier.

Share the workbook with the DRS_Team with Can View access.

Create an Embed for the workbook and copy off the Embed Path for use in our embed-api.js.

Save and Publish the workbook.

We are now ready to use this workbook embedded in our parent application.

Footer

We will move through this quickly, as it is assumed you have done this operation before in earlier QuickStarts in the Embedding Series. If not, go back and review them as mentioned in the Overview section of this QuickStart.

Click here to download sigma_embed_drs.zip

The zip file contains these two files:

index.html: the web page that contains the iframe we are embedding into. No changes are required for this file.

embed-api.js: a JavaScript routine that sets up the services required and configuration of the Sigma options. This is a example of an Embed API.

Install Node Packages for Folder

If you haven't installed Node already, please do so by referring to section 3 of the QuickStart: Embedding 01: Prerequisites.

If you have already installed Node, recall that we still need to install the required Node packages for our new sigma_embed_drs folder that was created when we unzipped the download into the sigma_embedding folder.

Open a new Terminal session from the folder sigma_embed_drs and run this command:

Run the command tro install the Express web-server:

npm init

As in the prerequisites QuickStart, accept all the defaults by pressing enter until completed.

Run the command (unless previously installed globally):

npm install express

Run the command:

npm install supervisor

Edit embed-api.js

Open embed-api.js in a text editor and review all the comments (lines starting with "//").

Required Changes:

We made some changes for you, to pass the tag to Sigma (and add it to the URL) at runtime.

Section 6 of embed-api.js should look like this:

Start node in Terminal:

cd\site
supervisor embed-api.js

Browse to:

http//localhost:3000

Our application should render with the workbook embedded and showing 35 rows as we have not applied any DRS in Sigma yet. We are just using the hard coded values in the Sigma Snowflake Connection we added earlier for Role.

Footer

To save time we will not worry about changing embed users in embed-api.js.

We will just change the Role the embed user configured in embed-api.js uses.

You can create different values in embed-api.js for email address, userid and account type if you prefer; that is not really the point of the exercise but will also work as expected.

In embed-api.js, add this one line of code as shown to section 6:

Snowflake will use this to evaluate against it's Row Access Policy we created in Step 4 of this QuickStart.

Save embed-api.js.

Refresh the browser. We should only return 20 rows and not the full 36. Only STORE_REGION = EAST should exist in the Table:

Go ahead and change embed-api.js to use the DRS_WEST role. We expect that will return only 10 rows:

For completeness, change embed-api.js to use the DRS_Executive role. We expect that will return all 35 rows including the South region rows:

Footer

Given we have already guided on how to configure all of this, we will step through this example pretty quickly.

We already created a User Attribute for Warehouse in Sigma and also created a second Warehouse in Snowflake earlier.

Create Warehouse

In Snowflake, we need to create an additional warehouse for this use-case.

// SECTION 6: CREATE A SECOND WAREHOUSE AND GRANT USAGE PERMISSION ON THE WAREHOUSES TO NEW ROLES:
USE ROLE ACCOUNTADMIN;
CREATE WAREHOUSE COMPUTE_WH2 WAREHOUSE_SIZE=SMALL AUTO_SUSPEND = 5;
GRANT USAGE ON WAREHOUSE COMPUTE_WH2 TO DRS_Executive;
GRANT USAGE ON WAREHOUSE COMPUTE_WH2 TO DRS_West;
GRANT USAGE ON WAREHOUSE COMPUTE_WH2 TO DRS_East;
USE WAREHOUSE COMPUTE_WH2;

Once the query is completed, Snowflake will return Statement executed successfully.

Now simply adjust the embed-api.js file to send the name of the new Snowflake Warehouse to Sigma:

The change uses this code:

searchParams += '&:ua_DRS_WAREHOUSE=COMPUTE_WH2';

Save the change to embed-api.js and refresh the Browser using localhost:3000:

Footer

In this QuickStart we created Snowflake assets and a Row Access Policy and used a Sigma Dynamic Connection & Role Switching to limit the data exposed to a user based on values passed to Sigma at runtime.

Additional Resource Links

Be sure to check out all the latest developments at Sigma's First Friday Feature page!

Help Center Home
Sigma Community
Sigma Blog

Footer