Snowflake secure views offer a powerful way to control who gets to see what. One way to secure data is to mask a column’s value based on who is accessing the data set.
First a bit of background on Snowflake views. They are not dynamic. Here is the section of the documentation explaining this:
View definitions are not dynamic, i.e. a view is not automatically updated if the underlying sources are modified such that they no longer match the view definition, particularly when columns are dropped. For example:
A view is created referencing a specific column in a source table and the column is subsequently dropped from the table.
A view is created using SELECT * from a table and any column is subsequently dropped from the table.
In practice what this means for data security and data privacy is that Snowflake secure views will mask column values rather than suppress the column entirely. Here’s an example of how to mask a column based on who is viewing the data. This example uses the currently logged in role to determine visibility rights, which is a best practice, but it could also check the current logged in user as well.
-- Log in as DBA or a login with appropriate permissions
-- Set the context. We'll be using the TEST warehouse (Extra Small
-- size is fine for this test), and a database named TEST.
-- Create them if not already present.
use warehouse TEST;
use database TEST;
--Create and use schema TPCH_SF1, where we will copy some data from
--the Snowflake Sample data
create schema TPCH_SF1;
use schema TPCH_SF1;
-- Create a writable table from the Snowflake sample data.
create or replace table ORDERS as
select * from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS;
--Make sure there are 1.5 million rows in the order table:
select count(*) as ROW_COUNT from ORDERS;
-- Mask the sensitive column from anyone not logged in using the DBA role:
create or replace secure view ORDERS_SECURE_VIEW as
select
O_ORDERKEY, O_CUSTKEY, O_ORDERSTATUS, O_TOTALPRICE, O_ORDERDATE, O_ORDERPRIORITY,
case current_role()
when 'DBA' then O_CLERK
else '*****'
end as O_CLERK,
O_SHIPPRIORITY, O_COMMENT
from ORDERS;
--Grant permissions to the Analyst role
grant usage on warehouse TEST to role ANALYST;
grant usage on database TEST to role ANALYST;
grant select on table ORDERS to role ANALYST;
grant select on view ORDERS_SECURE_VIEW to role ANALYST;
grant usage on schema TEST.TPCH_SF1 to role ANALYST;
--Test the following sections with the DBA and Analyst roles.
--The DBA will see unmasked data in the O_CLERK column.
--The Analyst will see masked data.
--Confirm the currently logged-in session's role:
select current_role() as current_role;
--Test our view using different roles, only the DBA role
--should see the unmasked data:
use warehouse TEST;
use database test;
use schema TPCH_SF1;
select * from orders_secure_view limit 10;
--Suspend our warehouse to save credits.
alter warehouse test suspend;