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.
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
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';
tasty_test_role in place, we can now begin to use GRANT statements to give access privileges to required Database objects and our
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
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:
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
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_tagfor attaching to our
pii_phone_number_tagfor attaching to our
pii_email_tagfor attaching to our
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:
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
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
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);
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
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
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
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.