Snowflake offers many powerful tools to protect your data from unauthorized access but it can be difficult to know where to start. In this article I provide a high level overview of Snowflake security features that offer fine-grained access control to help you keep your data safe. I’ll start by providing an example of how to manage access using Snowflake roles since they are an important primitive used throughout Snowflake’s security offering. I’ll also demonstrate how to control access at the column and row levels and provide a summary of advanced Snowflake security features that can also be used to manage data access.

Role Based Access

An important capability enabled by Snowflake’s RBAC system is role hierarchies. A role in Snowflake can have its own child roles and inherit their privileges. Snowflake recommends leveraging role hierarchies to create two types of roles: object access roles (a collection of privileges) and functional roles (a collection of object access roles). There is no technical difference between these two types of roles. “Object access” and “functional” are simply terms to describe how roles can be structured. Designing a scheme with these two types of roles can be a handy technique to safely manage access to your data.

Think of the functional roles as representing business roles in your organization. They are the roles you would grant directly to your Snowflake database users to provide the access that enables them to do their jobs. If you design them correctly, they could also be mapped to external identity providers. For example, you could use group linking via Okta’s SCIM integration for Snowflake where your Okta groups are mapped to functional roles in Snowflake.

If this sounds like a useful approach, let’s work through an example. But first, I’ll pull some definitions from Snowflake’s Access Control docs.

• Securable object:
  • An entity to which access can be granted.
  • Most “things” in Snowflake are securable objects including: roles, users, schemas, databases, tables, etc.
• Role
  • An entity to which privileges can be granted.
  • Roles are in turn assigned to users.
  • Roles can also be assigned to other roles, creating a role hierarchy.
  • There are system defined roles and custom roles (which users create).
• Privilege
  • A defined level of access to an object.
  • Multiple distinct privileges may be used to control the granularity of access granted.
• User
  • A user identity recognized by Snowflake, whether associated with a person or program.

Each securable object resides in a logical container, in a hierarchy of containers. Here is a handy visualization also from Snowflake’s docs.

For our example, we’ll create a schema for a simple banking app along with sample data and two users: Alice and Bob. Alice is our CFO who will need access to analyze financial data and Bob is one of our Product Managers who wants to track product analytics. We’re going to create object access roles with privileges to relevant data. Then we will grant the object access roles to functional roles that we can in turn grant to our users. This is a hefty chunk of code below but it will help set up our next few examples.

-----------------------------------------------------
-- 1. Schema and sample data for a simple banking app
-----------------------------------------------------
create or replace table users (
  id integer not null,
  name varchar(100) not null,
  analytics_opt_in boolean not null
);

insert into users (id, name, analytics_opt_in) values
 (1, 'Alyssa P. Coder', TRUE),
 (2, 'Ben Bitdiddle', FALSE),
 (3, 'Cy D. Fect', TRUE);

create or replace table accounts (
  id integer not null,
  user_id integer not null,
  name varchar(100) not null,
  ts timestamp default current_timestamp()
);

insert into accounts (id, user_id, name) values
 (1, 1, 'Alyssa\'s Checking Account'),
 (2, 2, 'Ben\'s Savings'),
 (3, 3, 'Cy\'s Checking');

create or replace table transactions (
  account_id integer not null,
  amount integer not null,
  ts timestamp default current_timestamp()
);

insert into transactions (account_id, amount) values
 (1, 100),
 (2, 200),
 (3, 300),
 (1, -50);

-- events for product analytics
create or replace table app_events (
  user_id integer not null,
  name varchar(100) not null,
  ts timestamp default current_timestamp()
);

insert into app_events (user_id, name) values
 (1, 'login'),
 (1, 'deposit'),
 (1, 'logout'),
 (2, 'reset password');

-------------------------------------------------------------
-- 2. Create object access roles and grant privileges to them
-------------------------------------------------------------
create or replace role financial_reporting;
create or replace role app_events_reporting;
create or replace role user_reporting;

-- before we grant select on specific tables, we need to grant usage to
-- the database, schema and a warehouse for each role.
grant usage on warehouse demo_warehouse to role financial_reporting;
grant usage on database demo_db to role financial_reporting;
grant usage on schema demo_db.public to role financial_reporting;
grant usage on warehouse demo_warehouse to role user_reporting;
grant usage on database demo_db to role user_reporting;
grant usage on schema demo_db.public to role user_reporting;
grant usage on warehouse demo_warehouse to role app_events_reporting;
grant usage on database demo_db to role app_events_reporting;
grant usage on schema demo_db.public to role app_events_reporting;

-- Now grant the selects for the specific tables
grant select on demo_db.public.transactions to role financial_reporting;
grant select on demo_db.public.accounts to role financial_reporting;
grant select on demo_db.public.app_events to role app_events_reporting;
grant select on demo_db.public.users to role user_reporting;

---------------------------------------------------------------------------
-- 3. Create functional roles and grant them access via object access roles
---------------------------------------------------------------------------
create or replace role finance;
create or replace role analytics;

-- Give functional roles access by granting them object access roles
grant role financial_reporting to role finance;
grant role user_reporting to role finance;
grant role app_events_reporting to role analytics;
grant role user_reporting to role analytics;

------------------------------------------------------------
-- 4. Create users and grant the access via functional roles
------------------------------------------------------------
create or replace user alice password = 'CFO';
grant role finance to user alice;

create or replace user bob password = 'PM';
grant role analytics to user bob;

Now that we’ve set up data, roles and users, let’s login as our CFO Alice (username=alice, password=CFO) to make sure she has access to view financial data. First, let’s make sure our session has the proper primary role.

use role finance;

And once we’re in the right warehouse, database and schema, we can query our financial data.

select users.name,
       sum(transactions.amount)
from users
     inner join accounts on accounts.user_id = users.id
     inner join transactions on transactions.account_id = accounts.id
group by users.name

If we set up our roles and privileges correctly, we should see the following results.

+------------------+--------------------------+                                 
| NAME             | SUM(TRANSACTIONS.AMOUNT) |
|------------------+--------------------------|
| Alyssa P. Coder  |                       50 |
| Ben Bitdiddle    |                      200 |
| Cy D. Fect       |                      300 |
+------------------+--------------------------+

It worked! Now that we’ve confirmed she has access to financial data, let’s test that Alice does not have access to analytics data. Let’s run a query on the app_events table.

select app_events.name event,
       users.name user,
       to_char(convert_timezone('America/New_York', app_events.ts), 'MON dd, hh12:mi pm') ts
from app_events
     inner join users on users.id = app_events.user_id

002003 (42S02): SQL compilation error:                                          
Object 'APP_EVENTS' does not exist or not authorized.

Alice is prevented from accessing app_events which is what we wanted.

Now let’s test Bob’s access by logging in as him (username=bob, password=PM). His experience should be the opposite so if we run the analytics query that Alice just ran, he should see the following results.

+----------------+------------------+------------------+                        
| EVENT          | USER             | TS               |
|----------------+------------------+------------------|
| login          | Alyssa P. Coder  | Jun 03, 09:54 AM |
| deposit        | Alyssa P. Coder  | Jun 03, 09:54 AM |
| logout         | Alyssa P. Coder  | Jun 03, 09:54 AM |
| reset password | Ben Bitdiddle    | Jun 03, 09:54 AM |
+----------------+------------------+------------------+

And he should not be able to run the first query Alice ran on the transactions table.

002003 (42S02): SQL compilation error:                                          
Object 'ACCOUNTS' does not exist or not authorized.

Excellent! So now we have two functional roles, finance and analytics, that enable access to our IRL internal business functions. They are composed of object access roles that provide the precise combination of privileges required for their jobs. Note that both functional roles share the user_reporting object access role. If you design your object access roles well, you’ll be able to reuse them (as we did here) and ideally compose them. If you’ve designed your functional roles well, they should correspond to roles defined in your security and compliance policies. If you’re using an identity provider (IdP) or directory service (such as Active Directory) you could map your functional roles to roles in external services as part of a scalable access management solution.

Column Level Access

Masking policies in Snowflake allow you to control access at the column level. Let’s say your company needs to prevent employees from viewing personally identifiable information of your users. Building on the banking app schema, let’s create a masking policy and apply it to our users table.

create or replace masking policy pii_visibility as
(name varchar) returns varchar ->
  case
    when current_role() like '%ADMIN%' then name
    else '***PII***'
  end;

alter table if exists users modify column name
set masking policy pii_visibility using (name);

If Alice runs her finance query now she’ll see her user’s names are masked.

+-----------+--------------------------+                                        
| NAME      | SUM(TRANSACTIONS.AMOUNT) |
|-----------+--------------------------|
| ***PII*** |                       50 |
| ***PII*** |                      200 |
| ***PII*** |                      300 |
+-----------+--------------------------+

Masking policies can be set on tables, views and used inlined in queries. They are very flexible and allow you to use context from the data, session and metadata to support making the right access decisions. One important caveat is that masking policies are only available in Snowflake’s Enterprise Edition. Unless you’re already on enterprise, you’ll need to reach out to Snowflake support to learn more.

Row Level Access

You can also apply a row access policy (another Enterprise Edition feature) to remove access to certain rows. Let’s prevent Bob from seeing any users who have opted out of analytics.

create or replace row access policy analytics_opt_in as (analytics_opt_in boolean) returns boolean ->
  'analytics' = current_role() and analytics_opt_in;

alter table users add row access policy analytics_opt_in on (analytics_opt_in);

Now when Bob queries app_events Ben Bitdiddle’s events are excluded because, from our sample data above, he had opted out of analytics.

+---------+------------------+------------------+                               
| EVENT   | USER             | TS               |
|---------+------------------+------------------|
| login   | Alyssa P. Coder | Jun 03, 09:54 AM |
| deposit | Alyssa P. Coder | Jun 03, 09:54 AM |
| logout  | Alyssa P. Coder | Jun 03, 09:54 AM |
+---------+------------------+------------------+

Row access policies share many of the capabilities as masking policies. Snowflake’s documentation on both demonstrate several capabilities beyond what I show here.

Snowflake Security and Governance Features

There is a long list of Snowflake governance features, many of which can be used specifically to help control access.

• Object Tagging and Data Classification allow you to assign metadata to Snowflake objects which can then be used by policies or views to restrict access.
• Network policies can be used to whitelist or block IP addresses and CIDR blocks at the account level.
• Customer-Managed Keys allows you to use an external key provider to encrypt data in Snowflake. If you were to use AWS Key Management Service (AWS KMS) for example, you could control access to unencrypted Snowflake data via AWS IAM.
• Session policies can be used to control session timeouts and force re-authentication.

Temporary Access with Sym

Finding the right combination of controls to get your access policies approaching least privilege can be an ongoing task of chipping away at open access. Snowflake provides you with many powerful tools to help whittle away at overprovisioned roles.

Using Sym with Snowflake can help you move further along your least privilege journey. With Sym, you can grant just in time, temporary access so it’s only available when needed and revoked when it’s not. You can also enable approvals that provide additional guardrails and visibility to access to sensitive data. Stay tuned for a follow up on how to use Sym to protect access for Snowflake.