Resources

How to Implement Row-Level Security in Snowflake

Oct 13, 2024
/

Segmenting data by geographies is a common requirement for row access policies in Snowflake. Still, many data teams continue to be roadblocked when it comes to implementing complex policies for row-level security at scale – particularly for sensitive data – due to location-based data use regulations, data use agreements, and internal, company-specific rules. Role-based access control methods make row-level security policies even more challenging in these complex environments, limiting data use and leaving valuable data sitting idle.

In this blog, we’ll walk through two ways to implement and scale Snowflake geographic data segmentation using row-level security. The first shows how to mask data by geography using Snowflake’s built-in role-based controls, and the second demonstrates how to do the same task with Snowflake’s Immuta integration using attribute-based access control.

See steps 1-15 with native Snowflake controls, or jump to the single ABAC policy approach with Immuta.

Scenario: Snowflake Row-Level Security Policy for Geographic Segmentation

In this scenario, we’ll assume that a company has expanded into three new countries and needs to ensure that customer data remains protected. This will require a data access control policy that prevents users from seeing record-level information on individuals outside of their own country, unless explicitly authorized. The protected information includes customer names, as well as sales and returns from the company’s catalog, stores, and website. However, personnel based in the central office must be able to access data from any country.

Note: The tables used are from the TPC-DS sample data available from Snowflake.

Creating Row Access Policies in Snowflake

The first step in segmenting geographic data using native Snowflake data governance capabilities for row-level security is to create roles for each country.

1. Create Roles for Each Country

1USE ROLE ACCOUNTADMIN;
2USE TPCDS_DB;
3USE SCHEMA TPCDS_SF10TCL;
4CREATE ROLE IF NOT EXISTS ROLE_COUNTRY_USA;
5CREATE ROLE IF NOT EXISTS ROLE_COUNTRY_GERMANY;
6CREATE ROLE IF NOT EXISTS ROLE_COUNTRY_MEXICO;
1--View customer table
2select DISTINCT (c_birth_country) from customer;
3select c_birth_country from customer where c_birth_country like 'M%';

Next, we must create a table that incorporates each role, or country in which the company has a presence.

2. Create Country-Customer Mapping Table – country_group

1CREATE OR REPLACE TABLE country_group (
2    COUNTRY_NAME     VARCHAR(20),
3    COUNTRY_GROUP    VARCHAR(20)
4);
5
6INSERT INTO country_group values('UNITED STATES', 'ROLE_COUNTRY_USA');
7INSERT INTO country_group values('GERMANY', 'ROLE_COUNTRY_GERMANY');
8INSERT INTO country_group values('MEXICO', 'ROLE_COUNTRY_MEXICO');
9
10GRANT ALL PRIVILEGES on table country_group TO ROLE ROLE_COUNTRY_USA;
11GRANT ALL PRIVILEGES on table country_group TO ROLE ROLE_COUNTRY_GERMANY;
12GRANT ALL PRIVILEGES on table country_group TO ROLE ROLE_COUNTRY_MEXICO;
13GRANT ALL PRIVILEGES on table CUSTOMER TO ROLE ROLE_COUNTRY_USA;
14GRANT ALL PRIVILEGES on table CUSTOMER TO ROLE ROLE_COUNTRY_GERMANY;
15GRANT ALL PRIVILEGES on table CUSTOMER TO ROLE ROLE_COUNTRY_MEXICO;

The next step is to create tables for each combination of sales and returns across the three different sales mediums – catalog, store, and web – and add the country roles to the tables.

3. Create Country_Catalog_Returns Mapping Table

1CREATE OR REPLACE TABLE country_catalog_returns (
2    COUNTRY_ROLE                VARCHAR(20),
3    cr_refunded_customer_sk     NUMBER(38,0),
4    cr_returning_customer_sk    NUMBER(38,0)
5);
6
7INSERT INTO country_catalog_returns
8select 'ROLE_COUNTRY_USA', cr_refunded_customer_sk, null
9from CATALOG_RETURNS
10WHERE cr_refunded_customer_sk in (
11    select c_customer_sk from customer where c_birth_country='UNITED STATES'
12)limit 10;
13
14INSERT INTO country_catalog_returns
15select 'ROLE_COUNTRY_GERMANY', cr_refunded_customer_sk, null
16from CATALOG_RETURNS
17WHERE cr_refunded_customer_sk in (
18    select c_customer_sk from customer where c_birth_country='GERMANY'
19)limit 10;
20
21INSERT INTO country_catalog_returns
22select 'ROLE_COUNTRY_MEXICO', cr_refunded_customer_sk, null
23from CATALOG_RETURNS
24WHERE cr_refunded_customer_sk in (
25    select c_customer_sk from customer where c_birth_country='MEXICO'
26)limit 10;
27
28INSERT INTO country_catalog_returns
29select 'ROLE_COUNTRY_USA', null, cr_returning_customer_sk
30from CATALOG_RETURNS
31WHERE cr_returning_customer_sk in (
32    select c_customer_sk from customer where c_birth_country='UNITED STATES'
33)limit 10;
34
35INSERT INTO country_catalog_returns
36select 'ROLE_COUNTRY_GERMANY', null, cr_returning_customer_sk
37from CATALOG_RETURNS
38WHERE cr_returning_customer_sk in (
39    select c_customer_sk from customer where c_birth_country='GERMANY'
40)limit 10;
41
42INSERT INTO country_catalog_returns
43select 'ROLE_COUNTRY_MEXICO', null, cr_returning_customer_sk
44from CATALOG_RETURNS
45WHERE cr_returning_customer_sk in (
46    select c_customer_sk from customer where c_birth_country='MEXICO'
47)limit 10;
48
49GRANT ALL PRIVILEGES on table country_catalog_returns TO ROLE ROLE_COUNTRY_USA;
50GRANT ALL PRIVILEGES on table country_catalog_returns TO ROLE ROLE_COUNTRY_GERMANY;
51GRANT ALL PRIVILEGES on table country_catalog_returns TO ROLE ROLE_COUNTRY_MEXICO;
52GRANT ALL PRIVILEGES on table catalog_returns TO ROLE ROLE_COUNTRY_USA;
53GRANT ALL PRIVILEGES on table catalog_returns TO ROLE ROLE_COUNTRY_GERMANY;
54GRANT ALL PRIVILEGES on table catalog_returns TO ROLE ROLE_COUNTRY_MEXICO;

4. Create Country_Catalog_Sales Mapping Table

1CREATE OR REPLACE TABLE country_catalog_sales (
2    COUNTRY_ROLE           VARCHAR(20),
3    cs_bill_customer_sk    NUMBER(38,0),
4    cs_ship_customer_sk    NUMBER(38,0)
5);
6
7INSERT INTO country_catalog_sales
8select 'ROLE_COUNTRY_USA', cs_bill_customer_sk, null
9from CATALOG_SALES
10WHERE cs_bill_customer_sk in (
11    select c_customer_sk from customer where c_birth_country='UNITED STATES'
12)limit 10;
13
14INSERT INTO country_catalog_sales
15select 'ROLE_COUNTRY_GERMANY', cs_bill_customer_sk, null
16from CATALOG_SALES
17WHERE cs_bill_customer_sk in (
18    select c_customer_sk from customer where c_birth_country='GERMANY'
19)limit 10;
20
21INSERT INTO country_catalog_sales
22select 'ROLE_COUNTRY_MEXICO', cs_bill_customer_sk, null
23from CATALOG_SALES
24WHERE cs_bill_customer_sk in (
25    select c_customer_sk from customer where c_birth_country='MEXICO'
26)limit 10;
27
28INSERT INTO country_catalog_sales
29select 'ROLE_COUNTRY_USA', null, cs_ship_customer_sk
30from CATALOG_SALES
31WHERE cs_ship_customer_sk in (
32    select c_customer_sk from customer where c_birth_country='UNITED STATES'
33)limit 10;
34
35INSERT INTO country_catalog_sales
36select 'ROLE_COUNTRY_GERMANY', null, cs_ship_customer_sk
37from CATALOG_SALES
38WHERE cs_ship_customer_sk in (
39    select c_customer_sk from customer where c_birth_country='GERMANY'
40)limit 10;
41
42INSERT INTO country_catalog_sales
43select 'ROLE_COUNTRY_MEXICO', null, cs_ship_customer_sk
44from CATALOG_SALES
45WHERE cs_ship_customer_sk in (
46    select c_customer_sk from customer where c_birth_country='MEXICO'
47)limit 10;
48
49GRANT ALL PRIVILEGES on table country_catalog_sales TO ROLE ROLE_COUNTRY_USA;
50GRANT ALL PRIVILEGES on table country_catalog_sales TO ROLE ROLE_COUNTRY_GERMANY;
51GRANT ALL PRIVILEGES on table country_catalog_sales TO ROLE ROLE_COUNTRY_MEXICO;
52GRANT ALL PRIVILEGES on table catalog_sales TO ROLE ROLE_COUNTRY_USA;
53GRANT ALL PRIVILEGES on table catalog_sales TO ROLE ROLE_COUNTRY_GERMANY;
54GRANT ALL PRIVILEGES on table catalog_sales TO ROLE ROLE_COUNTRY_MEXICO;

5. Create Country_Store_Returns Mapping Table

1CREATE OR REPLACE TABLE country_store_returns (
2    COUNTRY_ROLE      VARCHAR(20),
3    sr_customer_sk    NUMBER(38,0)
4);
5
6INSERT INTO country_store_returns
7select 'ROLE_COUNTRY_USA', sr_customer_sk
8from STORE_RETURNS
9WHERE sr_customer_sk in (
10    select c_customer_sk from customer where c_birth_country='UNITED STATES'
11)limit 10;
12
13INSERT INTO country_store_returns
14select 'ROLE_COUNTRY_GERMANY', sr_customer_sk
15from STORE_RETURNS
16WHERE sr_customer_sk in (
17    select c_customer_sk from customer where c_birth_country='GERMANY'
18)limit 10;
19
20INSERT INTO country_store_returns
21select 'ROLE_COUNTRY_MEXICO', sr_customer_sk
22from STORE_RETURNS
23WHERE sr_customer_sk in (
24    select c_customer_sk from customer where c_birth_country='MEXICO'
25)limit 10;
26
27GRANT ALL PRIVILEGES on table country_store_returns TO ROLE ROLE_COUNTRY_USA;
28GRANT ALL PRIVILEGES on table country_store_returns TO ROLE ROLE_COUNTRY_GERMANY;
29GRANT ALL PRIVILEGES on table country_store_returns TO ROLE ROLE_COUNTRY_MEXICO;
30GRANT ALL PRIVILEGES on table store_returns TO ROLE ROLE_COUNTRY_USA;
31GRANT ALL PRIVILEGES on table store_returns TO ROLE ROLE_COUNTRY_GERMANY;
32GRANT ALL PRIVILEGES on table store_returns TO ROLE ROLE_COUNTRY_MEXICO;

6. Create Country_Store_Sales Mapping Table

1CREATE OR REPLACE TABLE country_store_sales (
2    COUNTRY_ROLE      VARCHAR(20),
3    ss_customer_sk    NUMBER(38,0)
4);
5
6INSERT INTO country_store_sales
7select 'ROLE_COUNTRY_USA', ss_customer_sk
8from STORE_SALES
9WHERE ss_customer_sk in (
10    select c_customer_sk from customer where c_birth_country='UNITED STATES'
11)limit 10;
12
13INSERT INTO country_store_sales
14select 'ROLE_COUNTRY_GERMANY', ss_customer_sk
15from STORE_SALES
16WHERE ss_customer_sk in (
17    select c_customer_sk from customer where c_birth_country='GERMANY'
18)limit 10;
19
20INSERT INTO country_store_sales
21select 'ROLE_COUNTRY_MEXICO', ss_customer_sk
22from STORE_SALES
23WHERE ss_customer_sk in (
24    select c_customer_sk from customer where c_birth_country='MEXICO'
25)limit 10;
26
27GRANT ALL PRIVILEGES on table country_store_sales TO ROLE ROLE_COUNTRY_USA;
28GRANT ALL PRIVILEGES on table country_store_sales TO ROLE ROLE_COUNTRY_GERMANY;
29GRANT ALL PRIVILEGES on table country_store_sales TO ROLE ROLE_COUNTRY_MEXICO;
30GRANT ALL PRIVILEGES on table store_sales TO ROLE ROLE_COUNTRY_USA;
31GRANT ALL PRIVILEGES on table store_sales TO ROLE ROLE_COUNTRY_GERMANY;
32GRANT ALL PRIVILEGES on table store_sales TO ROLE ROLE_COUNTRY_MEXICO;

7. Create Country_Web_Returns Mapping Table

1CREATE OR REPLACE TABLE country_web_returns (
2    COUNTRY_ROLE                VARCHAR(20),
3    wr_refunded_customer_sk     NUMBER(38,0),
4    wr_returning_customer_sk    NUMBER(38,0)
5);
6
7INSERT INTO country_web_returns
8select 'ROLE_COUNTRY_USA', wr_refunded_customer_sk, null
9from WEB_RETURNS
10WHERE wr_refunded_customer_sk in (
11    select c_customer_sk from customer where c_birth_country='UNITED STATES'
12)limit 10;
13
14INSERT INTO country_web_returns
15select 'ROLE_COUNTRY_GERMANY', wr_refunded_customer_sk, null
16from WEB_RETURNS
17WHERE wr_refunded_customer_sk in (
18    select c_customer_sk from customer where c_birth_country='GERMANY'
19)limit 10;
20
21INSERT INTO country_web_returns
22select 'ROLE_COUNTRY_MEXICO', wr_refunded_customer_sk, null
23from WEB_RETURNS
24WHERE wr_refunded_customer_sk in (
25    select c_customer_sk from customer where c_birth_country='MEXICO'
26)limit 10;
27
28INSERT INTO country_web_returns
29select 'ROLE_COUNTRY_USA', null, wr_returning_customer_sk
30from WEB_RETURNS
31WHERE wr_returning_customer_sk in (
32    select c_customer_sk from customer where c_birth_country='UNITED STATES'
33)limit 10;
34
35INSERT INTO country_web_returns
36select 'ROLE_COUNTRY_GERMANY', null, wr_returning_customer_sk
37from WEB_RETURNS
38WHERE wr_returning_customer_sk in (
39    select c_customer_sk from customer where c_birth_country='GERMANY'
40)limit 10;
41
42INSERT INTO country_web_returns
43select 'ROLE_COUNTRY_MEXICO', null, wr_returning_customer_sk
44from WEB_RETURNS
45WHERE wr_returning_customer_sk in (
46    select c_customer_sk from customer where c_birth_country='MEXICO'
47)limit 10;
48
49GRANT ALL PRIVILEGES on table country_web_returns TO ROLE ROLE_COUNTRY_USA;
50GRANT ALL PRIVILEGES on table country_web_returns TO ROLE ROLE_COUNTRY_GERMANY;
51GRANT ALL PRIVILEGES on table country_web_returns TO ROLE ROLE_COUNTRY_MEXICO;
52GRANT ALL PRIVILEGES on table web_returns TO ROLE ROLE_COUNTRY_USA;
53GRANT ALL PRIVILEGES on table web_returns TO ROLE ROLE_COUNTRY_GERMANY;
54GRANT ALL PRIVILEGES on table web_returns TO ROLE ROLE_COUNTRY_MEXICO;

8. Create Country_Web_Sales Mapping Table

1CREATE OR REPLACE TABLE country_web_sales (
2    COUNTRY_ROLE           VARCHAR(20),
3    ws_bill_customer_sk    NUMBER(38,0),
4    ws_ship_customer_sk    NUMBER(38,0)
5);
6
7INSERT INTO country_web_sales
8select 'ROLE_COUNTRY_USA', ws_bill_customer_sk, null
9from WEB_SALES
10WHERE ws_bill_customer_sk in (
11    select c_customer_sk from customer where c_birth_country='UNITED STATES'
12)limit 10;
13
14INSERT INTO country_web_sales
15select 'ROLE_COUNTRY_GERMANY', ws_bill_customer_sk, null
16from WEB_SALES
17WHERE ws_bill_customer_sk in (
18    select c_customer_sk from customer where c_birth_country='GERMANY'
19)limit 10;
20
21INSERT INTO country_web_sales
22select 'ROLE_COUNTRY_MEXICO', ws_bill_customer_sk, null
23from WEB_SALES
24WHERE ws_bill_customer_sk in (
25    select c_customer_sk from customer where c_birth_country='MEXICO'
26)limit 10;
27
28INSERT INTO country_web_sales
29select 'ROLE_COUNTRY_USA', null, ws_ship_customer_sk
30from WEB_SALES
31WHERE ws_ship_customer_sk in (
32    select c_customer_sk from customer where c_birth_country='UNITED STATES'
33)limit 10;
34
35INSERT INTO country_web_sales
36select 'ROLE_COUNTRY_GERMANY', null, ws_ship_customer_sk
37from WEB_SALES
38WHERE ws_ship_customer_sk in (
39    select c_customer_sk from customer where c_birth_country='GERMANY'
40)limit 10;
41
42INSERT INTO country_web_sales
43select 'ROLE_COUNTRY_MEXICO', null, ws_ship_customer_sk
44from WEB_SALES
45WHERE ws_ship_customer_sk in (
46    select c_customer_sk from customer where c_birth_country='MEXICO'
47)limit 10;
48
49GRANT ALL PRIVILEGES on table country_web_sales TO ROLE ROLE_COUNTRY_USA;
50GRANT ALL PRIVILEGES on table country_web_sales TO ROLE ROLE_COUNTRY_GERMANY;
51GRANT ALL PRIVILEGES on table country_web_sales TO ROLE ROLE_COUNTRY_MEXICO;
52GRANT ALL PRIVILEGES on table web_sales TO ROLE ROLE_COUNTRY_USA;
53GRANT ALL PRIVILEGES on table web_sales TO ROLE ROLE_COUNTRY_GERMANY;
54GRANT ALL PRIVILEGES on table web_sales TO ROLE ROLE_COUNTRY_MEXICO;

Once all the tables have been created, we will grant role permissions to users.

9. Create User for Each New Role

1USE ROLE ACCOUNTADMIN;
2    GRANT ROLE ROLE_COUNTRY_USA TO USER john;
3    GRANT ROLE ROLE_COUNTRY_GERMANY TO USER jane;
4    GRANT ROLE ROLE_COUNTRY_MEXICO TO USER bill;

User and Role Relationship:

Now we can start building our policies. We will create a policy for each of the seven tables we’ve created so far.

10. Create Row Access Policy for Customer Table

‍

1ALTER TABLE CUSTOMER DROP ROW ACCESS POLICY ROW_ACCESS_POLICY_CUSTOMER;
2
3CREATE OR REPLACE ROW ACCESS POLICY ROW_ACCESS_POLICY_CUSTOMER as (c_birth_country VARCHAR) returns boolean ->
4    exists (
5        select 1 from country_group cg
6        where cg.country_name = c_birth_country
7        and cg.country_group = current_role()
8    );
9
10ALTER TABLE CUSTOMER ADD ROW ACCESS POLICY ROW_ACCESS_POLICY_CUSTOMER ON (c_birth_country);

11. Create Row Access Policy for Catalog Returns Table

1ALTER TABLE catalog_returns DROP ROW ACCESS POLICY ROW_ACCESS_POLICY_catalog_returns;
2
3CREATE OR REPLACE ROW ACCESS POLICY ROW_ACCESS_POLICY_catalog_returns as (cr_refunded_customer_sk NUMBER, cr_returning_customer_sk NUMBER) returns boolean ->
4    exists (
5        select 1 from country_catalog_returns as ccr
6        where ccr.cr_refunded_customer_sk = cr_refunded_customer_sk
7        and ccr.COUNTRY_ROLE = current_role()
8    )
9    and exists (
10        select 1 from country_catalog_returns as ccr
11        where ccr.cr_returning_customer_sk = cr_returning_customer_sk
12        and ccr.COUNTRY_ROLE = current_role()
13    );
14
15ALTER TABLE catalog_returns ADD ROW ACCESS POLICY ROW_ACCESS_POLICY_catalog_returns ON (cr_refunded_customer_sk, cr_returning_customer_sk);

12. Create Row Access Policy for Catalog Sales Table

1ALTER TABLE catalog_sales DROP ROW ACCESS POLICY ROW_ACCESS_POLICY_catalog_sales;
2
3CREATE OR REPLACE ROW ACCESS POLICY ROW_ACCESS_POLICY_catalog_sales as (cs_bill_customer_sk NUMBER, cs_ship_customer_sk NUMBER) returns boolean ->
4    exists (
5        select 1 from country_catalog_sales as ccs
6        where ccs.cs_bill_customer_sk = cs_bill_customer_sk
7        and ccs.COUNTRY_ROLE = current_role())
8  
9    and exists (
10        select 1 from country_catalog_sales as ccs
11        where ccs.cs_ship_customer_sk = cs_ship_customer_sk
12        and ccs.COUNTRY_ROLE = current_role()); 
13
14ALTER TABLE catalog_sales ADD ROW ACCESS POLICY ROW_ACCESS_POLICY_catalog_sales ON(cs_bill_customer_sk, cs_ship_customer_sk);

13. Create Row Access Policy for Store Returns Table

1ALTER TABLE store_returns DROP ROW ACCESS POLICY ROW_ACCESS_POLICY_store_returns;
2
3CREATE OR REPLACE ROW ACCESS POLICY ROW_ACCESS_POLICY_store_returns as (sr_customer_sk NUMBER) returns boolean ->
4    
5    exists (
6        select 1 from country_store_returns as csr
7        where csr.sr_customer_sk = sr_customer_sk
8        and csr.COUNTRY_ROLE = current_role()); 
9
10ALTER TABLE store_returns ADD ROW ACCESS POLICY ROW_ACCESS_POLICY_store_returns ON(sr_customer_sk);

14. Create Row Access Policy for Store Sales Table

1ALTER TABLE store_sales DROP ROW ACCESS POLICY ROW_ACCESS_POLICY_store_sales;
2
3CREATE OR REPLACE ROW ACCESS POLICY ROW_ACCESS_POLICY_store_sales as (ss_customer_sk NUMBER) returns boolean ->
4    
5    exists (
6        select 1 from country_store_sales as css
7        where css.ss_customer_sk = ss_customer_sk
8        and css.COUNTRY_ROLE = current_role()); 
9
10ALTER TABLE store_sales ADD ROW ACCESS POLICY ROW_ACCESS_POLICY_store_sales ON(ss_customer_sk);

15. Create Row Access Policy for Web Returns Table

1ALTER TABLE web_returns DROP ROW ACCESS POLICY ROW_ACCESS_POLICY_web_returns;
2
3CREATE OR REPLACE ROW ACCESS POLICY ROW_ACCESS_POLICY_web_returns as (wr_refunded_customer_sk NUMBER, wr_returning_customer_sk NUMBER) returns boolean ->
4
5    exists (
6        select 1 from country_web_returns as cwr
7        where cwr.wr_refunded_customer_sk = wr_refunded_customer_sk
8        and cwr.COUNTRY_ROLE = current_role())
9    
10    and exists (
11        select 1 from country_web_returns as cwr
12        where cwr.wr_returning_customer_sk = wr_returning_customer_sk
13        and cwr.COUNTRY_ROLE = current_role()); 
14
15ALTER TABLE web_returns ADD ROW ACCESS POLICY ROW_ACCESS_POLICY_web_returns ON(wr_refunded_customer_sk, wr_returning_customer_sk);

16. Create Row Access Policy for Web Sales Table

1ALTER TABLE web_sales DROP ROW ACCESS POLICY ROW_ACCESS_POLICY_web_sales;
2
3CREATE OR REPLACE ROW ACCESS POLICY ROW_ACCESS_POLICY_web_sales as (ws_bill_customer_sk NUMBER, ws_ship_customer_sk NUMBER) returns boolean ->
4
5    exists (
6        select 1 from country_web_sales as cws
7        where cws.ws_bill_customer_sk = ws_bill_customer_sk
8        and cws.COUNTRY_ROLE = current_role())
9    
10    and exists (
11        select 1 from country_web_sales as cws
12        where cws.ws_ship_customer_sk = ws_ship_customer_sk
13        and cws.COUNTRY_ROLE = current_role());
14
15ALTER TABLE web_sales ADD ROW ACCESS POLICY ROW_ACCESS_POLICY_web_sales ON(ws_bill_customer_sk, ws_ship_customer_sk);

Once all of the row access policies have been created, we must confirm that they’re working as intended. This is a critical step for pushing policies into production and being able to audit data use.

17. Verify that employees in the US may only view Customers and Store/Catalog/Web Returns/Sales in the US

Login as John (Role USA), execute the following Snowflake SQLs:

1USE ROLE ROLE_COUNTRY_USA;
2USE TPCDS_DB;
3USE SCHEMA TPCDS_SF10TCL;
4select current_role();
5select C_BIRTH_COUNTRY, * from CUSTOMER limit 60;
1select cr_refunded_customer_sk, cr_returning_customer_sk, *
2from catalog_returns
3limit 10;
1select cs_bill_customer_sk, cs_ship_customer_sk, * 
2from catalog_sales 
3limit 10;
1select sr_customer_sk, * 
2from store_returns 
3limit 10;
1select ss_customer_sk, * 
2from store_sales 
3limit 10;
1select wr_refunded_customer_sk, wr_returning_customer_sk, * 
2from web_returns 
3limit 20;
1select ws_bill_customer_sk, ws_ship_customer_sk, * 
2from web_sales 
3limit 10;

18. Verify that employees in GERMANY may only view Customers and Store/Catalog/Web Returns/Sales in GERMANY

Login as Jane (Role GERMANY), execute the following Snowflake SQLs:

1USE ROLE ROLE_COUNTRY_GERMANY;
2USE TPCDS_DB;
3USE SCHEMA TPCDS_SF10TCL;
4select C_BIRTH_COUNTRY, * 
5from CUSTOMER 
6limit 60;
1select cr_refunded_customer_sk, cr_returning_customer_sk, * 
2from catalog_returns 
3limit 10;
1select cs_bill_customer_sk, cs_ship_customer_sk, * 
2from catalog_sales 
3limit 10;
1select sr_customer_sk, * 
2from store_returns 
3limit 10;
1select ss_customer_sk, * 
2from store_sales 
3limit 10;
1select wr_refunded_customer_sk, wr_returning_customer_sk, * 
2from web_returns 
3limit 20;
1select ws_bill_customer_sk, ws_ship_customer_sk, * 
2from web_sales 
3limit 10;

19. Verify that employees in MEXICO may only view Customers and Store/Catalog/Web Returns/Sales in MEXICO

1USE ROLE ROLE_COUNTRY_GERMANY;
2USE TPCDS_DB;
3USE SCHEMA TPCDS_SF10TCL;
4select C_BIRTH_COUNTRY, * 
5from CUSTOMER 
6limit 60;
1select cr_refunded_customer_sk, cr_returning_customer_sk, * 
2from catalog_returns
3limit 10;
1select cs_bill_customer_sk, cs_ship_customer_sk, * 
2from catalog_sales 
3limit 10;
1select sr_customer_sk, * 
2from store_returns 
3limit 10;
1select ss_customer_sk, * 
2from store_sales 
3limit 10;
1select wr_refunded_customer_sk, wr_returning_customer_sk, * 
2from web_returns 
3limit 20;
1select ws_bill_customer_sk, ws_ship_customer_sk, * 
2from web_sales 
3limit 10;

As you can see, we’ve now built seven tables and seven access control policies to segment the data across the three countries. As countries and users are added, the data team will have to build out additional tables, users, and policies to ensure that these controls still work.

Now, let’s see how it’s done when Immuta is integrated with Snowflake.

Dynamic Row-Level Security Policies with Snowflake + Immuta

To accomplish the same objective in Snowflake using a single policy in Immuta that implements a dynamic attribute-based access control (ABAC) model, we start by creating a new group that indicates users’ countries and applying it to individual users.

Next, the country group is mapped to a new attribute, CountryAuthorizations. This means that users in the “country-us” group are mapped to CountryAuthorizations>US and central office personnel are assigned to the CountryAuthorizations>All attribute.

Once the groups have been mapped to attributes, we can write a new SQL-based row-level global policy for everyone except users that possess the AuthorizedCountries>All attribute, which looks like this:

1select c_customer_sk 
2from public.customer 
3where c_birth_country in (@attributes('AuthorizedCountries')))

Finally, for the seven affected tables, we will add the CustomerId tag to the relevant columns. This ensures that as new users are added, they will be added to the appropriate country and only able to access authorized data, regardless of where they are located. No additional policies or roles need to be created or modified using this dynamic ABAC model.

Next Steps for Snowflake Row-Level Security

For Snowflake users sharing data across geographies using row-level security, dynamic attribute-based access controls that are flexible and scalable are key to maximizing data’s value while minimizing risk.

By abstracting policy from platform, Immuta’s universal data access control solution eliminates the need for multiple policies and roles, and automatically applies policies consistently across all platforms in a data ecosystem. This not just saves time, but also simplifies policy management and auditing – all from a layer that is invisible to Snowflake users. As a result, organizations are able to onboard and scale workloads faster, delivering real-time insights that allow them to derive greater ROI from their data and cloud platform investments.