ALTR Quickstart - Data Access Control

Prerequisites

What You’ll Learn

What You’ll Build

Create a sample database

In This Step: We are creating a sample database by copying a small amount of data from Snowflakes shared SNOWFLAKE_SAMPLE_DATA database. We will use this sample database in the rest of this Quickstart.

Create a sample database

-- Use ACCOUNTADMIN role
USE ROLE ACCOUNTADMIN;

-- Create Sample Database
CREATE DATABASE ALTR_GETTING_STARTED_DB;

-- Set DB Context
USE DATABASE  ALTR_GETTING_STARTED_DB;

-- Populate Sample database from Snowflakes shared sample database. 
-- Usually named: SNOWFLAKE_SAMPLE_DATA
CREATE TABLE SAMPLE_CUSTOMER AS 
    SELECT 
        C_FIRST_NAME,
        C_LAST_NAME,
        C_EMAIL_ADDRESS,
        C_BIRTH_COUNTRY
    FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.CUSTOMER
    LIMIT 10000;

Note: If you can't get access to Snowflake's built in sample data and you want to use your own data go for it! To match this guide we recommend creating a separate database using the name ALTR_GETTING_STARTED_DB. Just populate it with fields from your own data set. Be sure to include some columns that contain PII like email addresses, names, addresses, SSN's etc.

Setup a new ALTR account

In This Step: We will create the ALTR account and connect it to Snowflake, all from within your browser. ALTR is a SaaS solution that integrates directly with Snowflake. There is no installation required on your premises. The process starts starts in Snowflake's Partner Connect portal

NOTE: If you already have an ALTR account and want to use it instead of creating a new one then skip to Step 4 ALTR Setup for existing accounts

Create a new ALTR Account

Set your ALTR Password and run ALTR Setup Wizard

NOTE: When you get to the choose a database to connect prompt, you should see ALTR_GETTING_STARTED_DB in the list. Choose it and then wait for the wizard to complete.

ALTR Setup for existing accounts

In This Step Only perform this step if you plan to use an ALTR account that existed before running this quick start. If you just created a new ALTR account in the previous step then skip to step 5 Build Data Classification Report

Log in to Snowflake and run the ALTR Stored Procedure

  CALL "PC_ALTR_DB"."PUBLIC"."SETUP_ALTR_SERVICE_ACCOUNT"(TRUE);

NOTE: If the stored procedure runs but you get an error as a result then try calling it again with the parameter set to "FALSE" instead of TRUE as follows:

  CALL "PC_ALTR_DB"."PUBLIC"."SETUP_ALTR_SERVICE_ACCOUNT"(FALSE);

Connect ALTR to Sample Database

NOTE: If your current ALTR account is a free tier account you are limited to a single connected database. You will need to disconnect the existing database from ALTR first in order to connect this quickstart's sample database. Or, you can use the database you already have connected, just remember the column names may not match what is in this guide.

NOTE: If your ALTR account was not created via Snowflake Partner Connect try setting ALTR_SERVICE_USER as the Service Account ID. One way to identify the Service Account ID: is to look at what value is set for other databases that are already connected to ALTR. Click on a database that is already connected in the Data Sources page and then expand the "Reconfigure Connection" drop down in the detail pane to see the name of the Service Account ID field.

Build Data Classification Report

In This Step The preceding steps are one time setup required for ALTR. Now that those tasks are complete we can start building our data access policy. The first task is to identify what data in our database contains sensitive information, what most people call classification. So in this step we will have ALTR generate a classification report sending a sample of data to Google DLP service to get classificaton results.

Start the data classification job

View the classification report

Classification Report Explainer:

• The classification job tags columns with the information type it believes are contained in the data column. We call these classifier tags
• A single column can be tagged with multiple classifier tags or none.
• The bar chart on the left lists the different types of classifier tags, and the percentage of columns in the database that have the tag.
• You can click on a single classifier tag's bar on the left and the chart on the right will update to list all the columns that have that classifier tag.
• Clicking the Add Data button next to a column will start ALTR following the column. That means ALTR gets placed in the "Query Stream" of any query that references that column (Including Views)
• Get more information on data classification in ALTR

Tell ALTR to start "Following" the C_EMAIL_ADDRESS column

Define Role Based Data Access Policy

In This Step We will build role based data access policies for EMAIL addresses from within the ALTR portal using ALTR Locks.

Create a policy defining what Snowflake roles will view email addresses unmasked

Lock Explainer

• Congrats you just defined your first policy!
• This lock defines the policy that will display EMAIL_ADDRESS values without any masking so long as the user querying snowflake is using the SECURITYADMIN role.
• This lock will not be applied to any other role because only SECURITYADMIN is selected in it.
• Any user querying for EMAIL_ADDRESS under any other snowflake role will get the masking type configured for locks applicable to that role.
• If a user is operating under a role where there is no applicable Lock then that user will get null's back for EMAIL_ADDRESS
• Get more information on using ALTR Locks

Create a policy defining which Snowflake roles will view email addresses partially masked

Create a policy defining which Snowflake roles will view email addresses fully masked

Test Policy in Snowflake

Copy test SQL into a new worksheet.

--
-- Grant access to the sample database to our test roles:
--
use database ALTR_GETTING_STARTED_DB;
use role accountadmin;

grant select on table SAMPLE_CUSTOMER to role SECURITYADMIN;
grant usage on database ALTR_GETTING_STARTED_DB to role SECURITYADMIN;
grant usage on schema PUBLIC to role SECURITYADMIN;
grant usage on warehouse COMPUTE_WH to role SECURITYADMIN;

grant select on table SAMPLE_CUSTOMER to role SYSADMIN;
grant usage on database ALTR_GETTING_STARTED_DB to role SYSADMIN;
grant usage on schema PUBLIC to role SYSADMIN;
grant usage on warehouse COMPUTE_WH to role SYSADMIN;

grant select on table SAMPLE_CUSTOMER to role PUBLIC;
grant usage on database ALTR_GETTING_STARTED_DB to role PUBLIC;
grant usage on schema PUBLIC to role PUBLIC;
grant usage on warehouse COMPUTE_WH to role PUBLIC;

-- warehouse
use COMPUTE_WH;

-- *** END OF GRANT ACCESS section ***


--
-- ALTR Policy Tests:
--

-- SECURITYADMIN gets to see emails with no masking at all:
use role SECURITYADMIN;
select * from SAMPLE_CUSTOMER;

-- SYSADMIN and ACCOUNT admin domain portion of emails only, email name portion is masked 
use role SYSADMIN;
select from SAMPLE_CUSTOMER;

use role ACCOUNTADMIN;
select from SAMPLE_CUSTOMER;

-- PUBLIC sees emails fully masked
use role PUBLIC;
select * from SAMPLE_CUSTOMER;

-- Now go into the ALTR portal and make the following changes 
-- 1. remove the PUBLIC role from the fully masked lock,
-- 2. add the PUBLIC role to the partially masked lock.

-- Run query as PUBLIC role again and observe that moving the role into the 
-- partially masked lock instantly updates access for that role

use role PUBLIC;
select * from SAMPLE_CUSTOMER;

-- See what happens when a role is not associated with any policy.  Do this by going
-- into the ALTR portal and unselecting the PUBLIC role from the partially masked lock.  
-- Make sure that the PUBLIC role is not a part of any other lock.
-- 

use role PUBLIC;
select * from SAMPLE_CUSTOMER;

Run the Grant Portion of SQL

Run the policy tests

Conclusion

What we covered:

Ideas for exploring futher:

Getting Help: