Welcome to the Powered by Tasty Bytes - Zero to Snowflake Quickstart focused on Data Governance!
Within this Quickstart we will learn about Snowflake Roles, Role Based Access Control and deploy both Column and Row Level Security that can scale with your business.
Within this Quickstart we will follow a Tasty Bytes themed story via a Snowsight SQL Worksheet with this page serving as a side by side guide complete with additional commentary, images and documentation links.
This section will walk you through logging into Snowflake, Creating a New Worksheet, Renaming the Worksheet, Copying SQL from GitHub, and Pasting the SQL we will be leveraging within this Quickstart.
Our Tasty Bytes Adminstrator has been tasked with learning the process of deploying Role Based Access Control (RBAC) and proper Data Governance across our Snowflake Account.
To begin, let's first dive into the Snowflake System Defined Roles provided by default in all accounts and learn a bit more on their privileges.
Before we can begin executing queries within the Snowflake Snowsight interface we must first set our context by running USE ROLE and USE WAREHOUSE commands or manually setting these in the top-right corner.
For this step, we will do this by executing our first two queries by highlighting them both and clicking the "▶ Run" button.
Once complete our results pane will result in a Statement Executed Successfully
message.
USE ROLE accountadmin;
USE WAREHOUSE tasty_dev_wh;
Once the above queries are executed we can see in the top-right corner the exact role and warehouse we instructed Snowflake to use.
With our context set, we can continue on our learning journey.
Now let's run the next query which leverages SHOW ROLES to provide a result set consisting of all roles currently deployed in our account.
SHOW ROLES;
If you are operating in a trial account with only Tasty Bytes deployed your result set may match the above screenshot closely, however if you are using an existing Snowflake account your list may be more extensive.
Thankfully we can filter down this result set which we will cover in the next step.
To filter on just the Snowflake System Defined Roles from our previous output please execute the next query which utilizes RESULT_SCAN and LAST_QUERY_ID to query our previous SHOW ROLES results as if they were a table giving us the ability to add a WHERE clause.
SELECT
"name",
"comment"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "name" IN ('ORGADMIN','ACCOUNTADMIN','SYSADMIN','USERADMIN','SECURITYADMIN','PUBLIC');
In our result set we can see the high-level descriptions of what these Snowflake System Defined Roles have privileges to do.
Now that we understand these System Defined roles, let's begin leveraging them to create a test role and grant it access to the Customer Loyalty data we will deploy our initial Data Governance features against and also providing the ability to use our tasty_dev_wh
Warehouse.
As we saw, a useradmin
can create and manage users and roles. Please kick off the next two queries with the first assuming that useradmin
role and the second leveraging a CREATE ROLE command to generate a new tasty_test_role
we will use throughout this Quickstart.
USE ROLE useradmin;
CREATE OR REPLACE ROLE tasty_test_role
COMMENT = 'test role for tasty bytes';
With our tasty_test_role
in place, we can now begin to use GRANT statements to give access privileges to required Database objects and our tasty_dev_wh
Warehouse.
To begin, please run the next two queries which will first assume the securityadmin
role that we learned can manage security aspects in our account and second grant OPERATE and USAGE on our tasty_dev_wh
to our tasty_test_role
.
When completed we will recieve a Statement executed successfully.
message.
GRANT OPERATE, USAGE ON WAREHOUSE tasty_dev_wh TO ROLE tasty_test_role;
For more on Snowflake Warehouse Privilege Grants please see below:
With the Warehouse privileges in place, please execute the next two queries which will provide the tasty_test_role
with the USAGE privilege on the frostbyte_tasty_bytes
database and all schemas within.
Once again when completed we will recieve a Statement executed successfully. X objects affected.
message.
GRANT USAGE ON DATABASE frostbyte_tasty_bytes TO ROLE tasty_test_role;
GRANT USAGE ON ALL SCHEMAS IN DATABASE frostbyte_tasty_bytes TO ROLE tasty_test_role;
For more on Snowflake Database and Schema Grants please see below:
As we will be testing several Data Governance features as our tasty_test_role
let's ensure it can run SELECT statements across our entire Data Model.
Please now execute the next three queries of this section which will once again result in a Statement executed successfully. X objects affected.
message.
GRANT SELECT ON ALL TABLES IN SCHEMA frostbyte_tasty_bytes.raw_customer TO ROLE tasty_test_role;
GRANT SELECT ON ALL TABLES IN SCHEMA frostbyte_tasty_bytes.raw_pos TO ROLE tasty_test_role;
GRANT SELECT ON ALL VIEWS IN SCHEMA frostbyte_tasty_bytes.analytics TO ROLE tasty_test_role;
For more on Snowflake View and Table Privilege Grants please see below:
With our tasty_test_role
properly privileged, let's now execute the last two queries of this section. Here we will first SET a SQL Variable called my_user_var
equal to our CURRENT_USER.
The second query will then grant our role to our user we are logged in as which has been stored in our $my_user_var
.
As we've seen before, our query will result in a Statement executed successfully
message.
SET my_user_var = CURRENT_USER();
GRANT ROLE tasty_test_role TO USER identifier($my_user_var);
Awesome! We now have a tasty_test_role
with required Database Object and Warehouse privileges and our user is able to leverage it.
The first Data Governance feature set we want to deploy and test will be Snowflake Tag Based Dynamic Data Masking. This feature will allow us to mask PII data in columns at query run time from our test role but leave it exposed to more privileged roles.
Before we can begin masking data, let's first explore what PII exists in our Customer Loyalty data.
Thanks to our last step, we can now officially use our tasty_test_role
Role and leverage the tasty_dev_wh
Warehouse from that role.
Please execute the next three queries which will first handle setting that context. With the context in place the third query will explore our raw_customer.customer_loyalty
which we ingest from the Tasty Bytes Customer Loyalty Program provider.
USE ROLE tasty_test_role;
USE WAREHOUSE tasty_dev_wh;
SELECT
cl.customer_id,
cl.first_name,
cl.last_name,
cl.e_mail,
cl.phone_number,
cl.city,
cl.country
FROM frostbyte_tasty_bytes.raw_customer.customer_loyalty cl;
Woah!! there is a lot of PII we need to take care before our users can touch this data. Luckily we can use Snowflakes native Tag-Based Masking functionality to do just this.
To begin our masking process, please run this steps three CREATE TAG queries.
Within these queries we are creating:
pii_name_tag
for attaching to our first_name
and last_name
columns.pii_phone_number_tag
for attaching to our phone_number
column.pii_email_tag
for attaching to our e_mail
column.USE ROLE accountadmin;
CREATE OR REPLACE TAG frostbyte_tasty_bytes.raw_customer.pii_name_tag
COMMENT = 'PII Tag for Name Columns';
CREATE OR REPLACE TAG frostbyte_tasty_bytes.raw_customer.pii_phone_number_tag
COMMENT = 'PII Tag for Phone Number Columns';
CREATE OR REPLACE TAG frostbyte_tasty_bytes.raw_customer.pii_email_tag
COMMENT = 'PII Tag for E-mail Columns';
With the Tags in place, let's now execute the next four queries one by one which use ALTER TABLE... MODIFY COLUMN to set our tags to each respective column.
Each of these queries will result in a Statement executed successfully.
message.
ALTER TABLE frostbyte_tasty_bytes.raw_customer.customer_loyalty
MODIFY COLUMN first_name
SET TAG frostbyte_tasty_bytes.raw_customer.pii_name_tag = 'First Name';
ALTER TABLE frostbyte_tasty_bytes.raw_customer.customer_loyalty
MODIFY COLUMN last_name
SET TAG frostbyte_tasty_bytes.raw_customer.pii_name_tag = 'Last Name';
ALTER TABLE frostbyte_tasty_bytes.raw_customer.customer_loyalty
MODIFY COLUMN phone_number
SET TAG frostbyte_tasty_bytes.raw_customer.pii_phone_number_tag = 'Phone Number';
ALTER TABLE frostbyte_tasty_bytes.raw_customer.customer_loyalty
MODIFY COLUMN e_mail
SET TAG frostbyte_tasty_bytes.raw_customer.pii_email_tag = 'E-mail Address';
With our Tags created and applied to Columns, please kick off the next query where we leverage the TAG_REFERENCES_ALL_COLUMNS function to validate the work we just completed.
SELECT
tag_database,
tag_schema,
tag_name,
column_name,
tag_value
FROM TABLE(frostbyte_tasty_bytes.information_schema.tag_references_all_columns
('frostbyte_tasty_bytes.raw_customer.customer_loyalty','table'));
Perfect! Just as desired, we see all of our created tags are associated to the PII columns we will look to mask in the next section.
With our Tag foundation in place, we can now begin to develop Dynamic Masking Policies to support different masking requirements for our name, phone number and e-mail columns.
For the three different column types we are looking to mask, we want to address the following:
accountadmin
or sysadmin
accountadmin
or sysadmin
.accountadmin
or sysadmin
.Please now execute the four queries in this step one by one, which will first set our sysadmin
Role context and then create the three Masking Policies necessary to address the masking requirements above.
Along the way we will see the use of CREATE MASKING POLICY as well as the other Snowflake feature functions required to complete the partial masking including CURRENT_ROLE, CONCAT, LEFT, and SPLIT_PART.
USE ROLE sysadmin;
CREATE OR REPLACE MASKING POLICY frostbyte_tasty_bytes.raw_customer.name_mask AS (val STRING) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('SYSADMIN', 'ACCOUNTADMIN') THEN val
ELSE '**~MASKED~**'
END;
CREATE OR REPLACE MASKING POLICY frostbyte_tasty_bytes.raw_customer.phone_mask AS (val STRING) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('SYSADMIN', 'ACCOUNTADMIN') THEN val
ELSE CONCAT(LEFT(val,3), '-***-****')
END;
CREATE OR REPLACE MASKING POLICY frostbyte_tasty_bytes.raw_customer.email_mask AS (val STRING) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('SYSADMIN', 'ACCOUNTADMIN') THEN val
ELSE CONCAT('**~MASKED~**','@', SPLIT_PART(val, '@', -1))
END;
With each Tag now having a Masking Policy let's apply those Masking Policies to our Tags which have already been applied to our PII columns.
Please now execute the four queries in this step which sets the accountadmin
Role context and applies each Masking Policy to the appropriate Tag using ALTER TAG... SET MASKING POLICY command.
With each query we will recieve a Statement executed successfully.
message indicating the association is successful.
USE ROLE accountadmin;
ALTER TAG frostbyte_tasty_bytes.raw_customer.pii_name_tag
SET MASKING POLICY frostbyte_tasty_bytes.raw_customer.name_mask;
ALTER TAG frostbyte_tasty_bytes.raw_customer.pii_phone_number_tag
SET MASKING POLICY frostbyte_tasty_bytes.raw_customer.phone_mask;
ALTER TAG frostbyte_tasty_bytes.raw_customer.pii_email_tag
SET MASKING POLICY frostbyte_tasty_bytes.raw_customer.email_mask;
We can now officially say we have deployed Tag Based Masking Policies. Let's move on to the next step where we will validate our work.
With deployment of our Tag Based Masking Policies in place let's validate what we have conducted so far to confirm we were successful in meeting Tasty Bytes Customer Loyalty PII Data Masking requirements.
Putting together what we have done so far, let's once again assume our tasty_test_role
Role, leverage the tasty_test_wh
Warehouse and query the raw_customer.customer_loyalty
Table directly.
Go ahead and kick off the next three queries to complete this.
USE ROLE tasty_test_role;
USE WAREHOUSE tasty_dev_wh;
SELECT
cl.customer_id,
cl.first_name,
cl.last_name,
cl.phone_number,
cl.e_mail,
cl.city,
cl.country
FROM frostbyte_tasty_bytes.raw_customer.customer_loyalty cl
WHERE cl.country IN ('United States','Canada','Brazil');
Amazing work! Exactly as we have been tasked to do, we have successfully dynamically masked our PII from our tasty_test_role
.
Since we reference this raw_customer.customer_loyalty
Table in downstream Views let's see if this Masking Policy impacts us there as well.
Please kick off the next query which calculates our Customer Loyalty members lifetime_sales_usd
totals and sorts them in descending order by this amount.
SELECT TOP 10
clm.customer_id,
clm.first_name,
clm.last_name,
clm.phone_number,
clm.e_mail,
SUM(clm.total_sales) AS lifetime_sales_usd
FROM frostbyte_tasty_bytes.analytics.customer_loyalty_metrics_v clm
WHERE clm.city = 'San Mateo'
GROUP BY clm.customer_id, clm.first_name, clm.last_name, clm.phone_number, clm.e_mail
ORDER BY lifetime_sales_usd DESC;
Awesome! Exactly as we expected these Masking Policies are truly dynamic and scalable.
To finish our testing, let's now kick off the next two queries which will run the same query as above but this time as our most privileged accountadmin
Role.
USE ROLE accountadmin;
SELECT TOP 10
clm.customer_id,
clm.first_name,
clm.last_name,
clm.phone_number,
clm.e_mail,
SUM(clm.total_sales) AS lifetime_sales_usd
FROM frostbyte_tasty_bytes.analytics.customer_loyalty_metrics_v clm
WHERE 1=1
AND clm.city = 'San Mateo'
GROUP BY clm.customer_id, clm.first_name, clm.last_name, clm.phone_number, clm.e_mail
ORDER BY lifetime_sales_usd DESC;
Yay! Once again this is working as exactly as desired. Our Admin Roles have access to PII but nobody else in the company will ever be able to see this data in its raw form.
Happy with our Tag Based Dynamic Masking controlling masking at the Column level, we will now look to restrict access at the Row level for our Test Role.
Within our Customer Loyalty table, our Test Role should only see Customers who are based in Tokyo. Thankfully, Snowflake has another powerful native Data Governance feature that can handle this at scale called Row Access Policies.
For our use case, we will leverage the mapping table approach.
To begin our Row Level Security journey we will start by creating a row_policy_map
table that will serve as our Mapping Table which a Row Access Policy condition can reference to filter the query result set.
Let's now kick off the first two queries in this section which will assume the sysadmin
Role and create our Mapping Table that will accept Roles and the City they should have permission to see.
USE ROLE sysadmin;
CREATE OR REPLACE TABLE frostbyte_tasty_bytes.public.row_policy_map
(role STRING, city_permissions STRING);
With our row_policy_map
in place, please kick off the next query which will INSERT a record that maps our tasty_test_role
to Tokyo.
INSERT INTO frostbyte_tasty_bytes.public.row_policy_map
VALUES ('TASTY_TEST_ROLE','Tokyo');
Now that we have a record in our row_policy_map
Table, we can kick off our next query to create our Row Access Policy.
Within this query we are using CREATE ROW ACCESS POLICY that will allow our Admin and Tasty Workload Roles to see all rows but if a user is using a Role that is seen in our Mapping Table they will only be allowed to see the rows relevant to the City they are mapped to.
CREATE OR REPLACE ROW ACCESS POLICY frostbyte_tasty_bytes.public.customer_city_row_policy
AS (city STRING) RETURNS BOOLEAN ->
CURRENT_ROLE() IN
(
'ACCOUNTADMIN','SYSADMIN', 'TASTY_ADMIN', 'TASTY_DATA_ENGINEER',
'TASTY_DATA_APP','TASTY_BI','TASTY_DATA_SCIENTIST','TASTY_DEV'
)
OR EXISTS
(
SELECT rp.role
FROM frostbyte_tasty_bytes.public.row_policy_map rp
WHERE 1=1
AND rp.role = CURRENT_ROLE()
AND rp.city_permissions = city
);
Please kick off the next query which will associate our Row Access Policy to our city
Column within the customer_loyalty
table.
Once complete we will recieve a Statement executed successfully.
message indicating our policy is applied.
ALTER TABLE frostbyte_tasty_bytes.raw_customer.customer_loyalty
ADD ROW ACCESS POLICY frostbyte_tasty_bytes.public.customer_city_row_policy ON (city);
With everything in place, we can begin to test that our Row Access Policy is operating as expected. Please now kick off the next two queries which set our tasty_test_role
Role context and query the customer_loyalty
table to return a list of our Customer Loyalty members including a calculated age
Column that leverages DATEDIFF and CURRENT_DATE functions.
USE ROLE tasty_test_role;
SELECT
cl.customer_id,
cl.first_name,
cl.last_name,
cl.city,
cl.marital_status,
DATEDIFF(year, cl.birthday_date, CURRENT_DATE()) AS age
FROM frostbyte_tasty_bytes.raw_customer.customer_loyalty cl
GROUP BY cl.customer_id, cl.first_name, cl.last_name, cl.city, cl.marital_status, age;
Yay! Our Row Access Policy is working as expected and we are also seeing our masking in place since we are using the tasty_test_role
.
One cool Snowsight feature is the query stats pane which will automatically appear when clicking within a Column. In the screenshot above you can see how this was leveraged to confirm 100% of the records returned were for Tokyo.
As we did previously, let's now confirm our Row Access Policy is in effect when we query our downstream Analytics customer_loyalty_metrics_v
View by running the next query.
SELECT
clm.city,
SUM(clm.total_sales) AS total_sales_usd
FROM frostbyte_tasty_bytes.analytics.customer_loyalty_metrics_v clm
GROUP BY clm.city;
Just as expected, our Data Governance features are seen downstream despite only having to define them once and apply them to the Raw Tables our Analytics Views retrieve query results from.
To conclude things, let's make sure a privileged user is once again able to see all records.
Please kick off the next two queries which switches our Role context back to sysadmin
and runs the same SQL we saw filtered down only to Tokyo for our tasty_test_role
earlier.
USE ROLE sysadmin;
SELECT
cl.customer_id,
cl.first_name,
cl.last_name,
cl.city,
cl.marital_status,
DATEDIFF(year, cl.birthday_date, CURRENT_DATE()) AS age
FROM frostbyte_tasty_bytes.raw_customer.customer_loyalty cl
GROUP BY cl.customer_id, cl.first_name, cl.last_name, cl.city, cl.marital_status, age;
Remarkable! We have now successfully learned what an end to end Data Governance workflow can look like in Snowflake showcasing the ease of use and scalability our Column Masking and Row Level Security functionality can provide.
Fantastic work! You have successfully completed the Tasty Bytes - Zero to Snowflake - Data Governance Quickstart.
By doing so you have now:
If you would like to re-run this Quickstart please leverage the Reset scripts in the bottom of your associated Worksheet.
To continue your journey in the Snowflake Data Cloud, please now visit the link below to see all other Powered by Taste Bytes - Quickstarts available to you.