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
USE ROLE ACCOUNTADMIN;
USE TPCDS_DB;
USE SCHEMA TPCDS_SF10TCL;
CREATE ROLE IF NOT EXISTS ROLE_COUNTRY_USA;
CREATE ROLE IF NOT EXISTS ROLE_COUNTRY_GERMANY;
CREATE ROLE IF NOT EXISTS ROLE_COUNTRY_MEXICO;--View customer table
select DISTINCT (c_birth_country) from customer;
select 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
CREATE OR REPLACE TABLE country_group (COUNTRY_NAME VARCHAR(20), COUNTRY_GROUP VARCHAR(20));
INSERT INTO country_group values('UNITED STATES', 'ROLE_COUNTRY_USA');
INSERT INTO country_group values('GERMANY', 'ROLE_COUNTRY_GERMANY');
INSERT INTO country_group values('MEXICO', 'ROLE_COUNTRY_MEXICO');
GRANT ALL PRIVILEGES on table country_group TO ROLE ROLE_COUNTRY_USA;
GRANT ALL PRIVILEGES on table country_group TO ROLE ROLE_COUNTRY_GERMANY;
GRANT ALL PRIVILEGES on table country_group TO ROLE ROLE_COUNTRY_MEXICO;
GRANT ALL PRIVILEGES on table CUSTOMER TO ROLE ROLE_COUNTRY_USA;
GRANT ALL PRIVILEGES on table CUSTOMER TO ROLE ROLE_COUNTRY_GERMANY;
GRANT 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
CREATE OR REPLACE TABLE country_catalog_returns (COUNTRY_ROLE VARCHAR(20), cr_refunded_customer_sk NUMBER(38,0), cr_returning_customer_sk NUMBER(38,0));
INSERT INTO country_catalog_returns select 'ROLE_COUNTRY_USA', cr_refunded_customer_sk, null from CATALOG_RETURNS
WHERE cr_refunded_customer_sk in (select c_customer_sk from customer where c_birth_country='UNITED STATES') limit 10; INSERT INTO country_catalog_returns select 'ROLE_COUNTRY_GERMANY', cr_refunded_customer_sk, null from CATALOG_RETURNS WHERE cr_refunded_customer_sk in (select c_customer_sk from customer where c_birth_country='GERMANY') limit 10;
INSERT INTO country_catalog_returns select 'ROLE_COUNTRY_MEXICO', cr_refunded_customer_sk, null from CATALOG_RETURNS
WHERE cr_refunded_customer_sk in (select c_customer_sk from customer where c_birth_country='MEXICO') limit 10;
INSERT INTO country_catalog_returns select 'ROLE_COUNTRY_USA', null, cr_returning_customer_sk from CATALOG_RETURNS
WHERE cr_returning_customer_sk in (select c_customer_sk from customer where c_birth_country='UNITED STATES') limit 10; INSERT INTO country_catalog_returns select 'ROLE_COUNTRY_GERMANY', null, cr_returning_customer_sk from CATALOG_RETURNS WHERE cr_returning_customer_sk in (select c_customer_sk from customer where c_birth_country='GERMANY') limit 10;
INSERT INTO country_catalog_returns select 'ROLE_COUNTRY_MEXICO', null, cr_returning_customer_sk from CATALOG_RETURNS WHERE cr_returning_customer_sk in (select c_customer_sk from customer where c_birth_country='MEXICO') limit 10;
GRANT ALL PRIVILEGES on table country_catalog_returns TO ROLE ROLE_COUNTRY_USA;
GRANT ALL PRIVILEGES on table country_catalog_returns TO ROLE ROLE_COUNTRY_GERMANY;
GRANT ALL PRIVILEGES on table country_catalog_returns TO ROLE ROLE_COUNTRY_MEXICO;
GRANT ALL PRIVILEGES on table catalog_returns TO ROLE ROLE_COUNTRY_USA;
GRANT ALL PRIVILEGES on table catalog_returns TO ROLE ROLE_COUNTRY_GERMANY;
GRANT ALL PRIVILEGES on table catalog_returns TO ROLE ROLE_COUNTRY_MEXICO;
4. Create Country_Catalog_Sales Mapping Table
CREATE OR REPLACE TABLE country_catalog_sales (COUNTRY_ROLE VARCHAR(20), cs_bill_customer_sk NUMBER(38,0), cs_ship_customer_sk NUMBER(38,0));
INSERT INTO country_catalog_sales select 'ROLE_COUNTRY_USA', cs_bill_customer_sk, null from CATALOG_SALES
WHERE cs_bill_customer_sk in (select c_customer_sk from customer where c_birth_country='UNITED STATES') limit 10;
INSERT INTO country_catalog_sales select 'ROLE_COUNTRY_GERMANY', cs_bill_customer_sk, null from CATALOG_SALES
WHERE cs_bill_customer_sk in (select c_customer_sk from customer where c_birth_country='GERMANY') limit 10;
INSERT INTO country_catalog_sales select 'ROLE_COUNTRY_MEXICO', cs_bill_customer_sk, null from CATALOG_SALES
WHERE cs_bill_customer_sk in (select c_customer_sk from customer where c_birth_country='MEXICO') limit 10;
INSERT INTO country_catalog_sales select 'ROLE_COUNTRY_USA', null, cs_ship_customer_sk from CATALOG_SALES
WHERE cs_ship_customer_sk in (select c_customer_sk from customer where c_birth_country='UNITED STATES') limit 10;
INSERT INTO country_catalog_sales select 'ROLE_COUNTRY_GERMANY', null, cs_ship_customer_sk from CATALOG_SALES
WHERE cs_ship_customer_sk in (select c_customer_sk from customer where c_birth_country='GERMANY') limit 10;
INSERT INTO country_catalog_sales select 'ROLE_COUNTRY_MEXICO', null, cs_ship_customer_sk from CATALOG_SALES
WHERE cs_ship_customer_sk in (select c_customer_sk from customer where c_birth_country='MEXICO') limit 10;
GRANT ALL PRIVILEGES on table country_catalog_sales TO ROLE ROLE_COUNTRY_USA;
GRANT ALL PRIVILEGES on table country_catalog_sales TO ROLE ROLE_COUNTRY_GERMANY;
GRANT ALL PRIVILEGES on table country_catalog_sales TO ROLE ROLE_COUNTRY_MEXICO;
GRANT ALL PRIVILEGES on table catalog_sales TO ROLE ROLE_COUNTRY_USA;
GRANT ALL PRIVILEGES on table catalog_sales TO ROLE ROLE_COUNTRY_GERMANY;
GRANT ALL PRIVILEGES on table catalog_sales TO ROLE ROLE_COUNTRY_MEXICO;
5. Create Country_Store_Returns Mapping Table
CREATE OR REPLACE TABLE country_store_returns (COUNTRY_ROLE VARCHAR(20), sr_customer_sk NUMBER(38,0));
INSERT INTO country_store_returns select 'ROLE_COUNTRY_USA', sr_customer_sk from STORE_RETURNS
WHERE sr_customer_sk in (select c_customer_sk from customer where c_birth_country='UNITED STATES') limit 10;
INSERT INTO country_store_returns select 'ROLE_COUNTRY_GERMANY', sr_customer_sk from STORE_RETURNS
WHERE sr_customer_sk in (select c_customer_sk from customer where c_birth_country='GERMANY') limit 10;
INSERT INTO country_store_returns select 'ROLE_COUNTRY_MEXICO', sr_customer_sk from STORE_RETURNS
WHERE sr_customer_sk in (select c_customer_sk from customer where c_birth_country='MEXICO') limit 10;
GRANT ALL PRIVILEGES on table country_store_returns TO ROLE ROLE_COUNTRY_USA;
GRANT ALL PRIVILEGES on table country_store_returns TO ROLE ROLE_COUNTRY_GERMANY;
GRANT ALL PRIVILEGES on table country_store_returns TO ROLE ROLE_COUNTRY_MEXICO;
GRANT ALL PRIVILEGES on table store_returns TO ROLE ROLE_COUNTRY_USA;
GRANT ALL PRIVILEGES on table store_returns TO ROLE ROLE_COUNTRY_GERMANY;
GRANT ALL PRIVILEGES on table store_returns TO ROLE ROLE_COUNTRY_MEXICO;
6. Create Country_Store_Sales Mapping Table
CREATE OR REPLACE TABLE country_store_sales (COUNTRY_ROLE VARCHAR(20), ss_customer_sk NUMBER(38,0));
INSERT INTO country_store_sales select 'ROLE_COUNTRY_USA', ss_customer_sk from STORE_SALES
WHERE ss_customer_sk in (select c_customer_sk from customer where c_birth_country='UNITED STATES') limit 10;
INSERT INTO country_store_sales select 'ROLE_COUNTRY_GERMANY', ss_customer_sk from STORE_SALES
WHERE ss_customer_sk in (select c_customer_sk from customer where c_birth_country='GERMANY') limit 10;
INSERT INTO country_store_sales select 'ROLE_COUNTRY_MEXICO', ss_customer_sk from STORE_SALES
WHERE ss_customer_sk in (select c_customer_sk from customer where c_birth_country='MEXICO') limit 10;
GRANT ALL PRIVILEGES on table country_store_sales TO ROLE ROLE_COUNTRY_USA;
GRANT ALL PRIVILEGES on table country_store_sales TO ROLE ROLE_COUNTRY_GERMANY;
GRANT ALL PRIVILEGES on table country_store_sales TO ROLE ROLE_COUNTRY_MEXICO;
GRANT ALL PRIVILEGES on table store_sales TO ROLE ROLE_COUNTRY_USA;
GRANT ALL PRIVILEGES on table store_sales TO ROLE ROLE_COUNTRY_GERMANY;
GRANT ALL PRIVILEGES on table store_sales TO ROLE ROLE_COUNTRY_MEXICO;
7. Create Country_Web_Returns Mapping Table
CREATE OR REPLACE TABLE country_web_returns (COUNTRY_ROLE VARCHAR(20), wr_refunded_customer_sk NUMBER(38,0), wr_returning_customer_sk NUMBER(38,0));
INSERT INTO country_web_returns select 'ROLE_COUNTRY_USA', wr_refunded_customer_sk, null from WEB_RETURNS
WHERE wr_refunded_customer_sk in (select c_customer_sk from customer where c_birth_country='UNITED STATES') limit 10;
INSERT INTO country_web_returns select 'ROLE_COUNTRY_GERMANY', wr_refunded_customer_sk, null from WEB_RETURNS
WHERE wr_refunded_customer_sk in (select c_customer_sk from customer where c_birth_country='GERMANY') limit 10;
INSERT INTO country_web_returns select 'ROLE_COUNTRY_MEXICO', wr_refunded_customer_sk, null from WEB_RETURNS
WHERE wr_refunded_customer_sk in (select c_customer_sk from customer where c_birth_country='MEXICO') limit 10;
INSERT INTO country_web_returns select 'ROLE_COUNTRY_USA', null, wr_returning_customer_sk from WEB_RETURNS
WHERE wr_returning_customer_sk in (select c_customer_sk from customer where c_birth_country='UNITED STATES') limit 10;
INSERT INTO country_web_returns select 'ROLE_COUNTRY_GERMANY', null, wr_returning_customer_sk from WEB_RETURNS
WHERE wr_returning_customer_sk in (select c_customer_sk from customer where c_birth_country='GERMANY') limit 10;
INSERT INTO country_web_returns select 'ROLE_COUNTRY_MEXICO', null, wr_returning_customer_sk from WEB_RETURNS
WHERE wr_returning_customer_sk in (select c_customer_sk from customer where c_birth_country='MEXICO') limit 10;
GRANT ALL PRIVILEGES on table country_web_returns TO ROLE ROLE_COUNTRY_USA;
GRANT ALL PRIVILEGES on table country_web_returns TO ROLE ROLE_COUNTRY_GERMANY;
GRANT ALL PRIVILEGES on table country_web_returns TO ROLE ROLE_COUNTRY_MEXICO;
GRANT ALL PRIVILEGES on table web_returns TO ROLE ROLE_COUNTRY_USA;
GRANT ALL PRIVILEGES on table web_returns TO ROLE ROLE_COUNTRY_GERMANY;
GRANT ALL PRIVILEGES on table web_returns TO ROLE ROLE_COUNTRY_MEXICO;
8. Create Country_Web_Sales Mapping Table
CREATE OR REPLACE TABLE country_web_sales (COUNTRY_ROLE VARCHAR(20), ws_bill_customer_sk NUMBER(38,0), ws_ship_customer_sk NUMBER(38,0));
INSERT INTO country_web_sales select 'ROLE_COUNTRY_USA', ws_bill_customer_sk, null from WEB_SALES
WHERE ws_bill_customer_sk in (select c_customer_sk from customer where c_birth_country='UNITED STATES') limit 10;
INSERT INTO country_web_sales select 'ROLE_COUNTRY_GERMANY', ws_bill_customer_sk, null from WEB_SALES
WHERE ws_bill_customer_sk in (select c_customer_sk from customer where c_birth_country='GERMANY') limit 10;
INSERT INTO country_web_sales select 'ROLE_COUNTRY_MEXICO', ws_bill_customer_sk, null from WEB_SALES
WHERE ws_bill_customer_sk in (select c_customer_sk from customer where c_birth_country='MEXICO') limit 10;
INSERT INTO country_web_sales select 'ROLE_COUNTRY_USA', null, ws_ship_customer_sk from WEB_SALES
WHERE ws_ship_customer_sk in (select c_customer_sk from customer where c_birth_country='UNITED STATES') limit 10;
INSERT INTO country_web_sales select 'ROLE_COUNTRY_GERMANY', null, ws_ship_customer_sk from WEB_SALES
WHERE ws_ship_customer_sk in (select c_customer_sk from customer where c_birth_country='GERMANY') limit 10;
INSERT INTO country_web_sales select 'ROLE_COUNTRY_MEXICO', null, ws_ship_customer_sk from WEB_SALES
WHERE ws_ship_customer_sk in (select c_customer_sk from customer where c_birth_country='MEXICO') limit 10;
GRANT ALL PRIVILEGES on table country_web_sales TO ROLE ROLE_COUNTRY_USA;
GRANT ALL PRIVILEGES on table country_web_sales TO ROLE ROLE_COUNTRY_GERMANY;
GRANT ALL PRIVILEGES on table country_web_sales TO ROLE ROLE_COUNTRY_MEXICO;
GRANT ALL PRIVILEGES on table web_sales TO ROLE ROLE_COUNTRY_USA;
GRANT ALL PRIVILEGES on table web_sales TO ROLE ROLE_COUNTRY_GERMANY;
GRANT 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
USE ROLE ACCOUNTADMIN;
GRANT ROLE ROLE_COUNTRY_USA TO USER john;
GRANT ROLE ROLE_COUNTRY_GERMANY TO USER jane;
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
ALTER TABLE CUSTOMER DROP ROW ACCESS POLICY ROW_ACCESS_POLICY_CUSTOMER;
CREATE OR REPLACE ROW ACCESS POLICY ROW_ACCESS_POLICY_CUSTOMER as (c_birth_country VARCHAR) returns boolean ->
exists (
select 1 from country_group cg
where cg.country_name = c_birth_country
and cg.country_group = current_role());
ALTER TABLE CUSTOMER ADD ROW ACCESS POLICY ROW_ACCESS_POLICY_CUSTOMER ON(c_birth_country);
11. Create Row Access Policy for Catalog Returns Table
ALTER TABLE catalog_returns DROP ROW ACCESS POLICY ROW_ACCESS_POLICY_catalog_returns;
CREATE OR REPLACE ROW ACCESS POLICY ROW_ACCESS_POLICY_catalog_returns as (cr_refunded_customer_sk NUMBER, cr_returning_customer_sk NUMBER) returns boolean ->
exists (
select 1 from country_catalog_returns as ccr
where ccr.cr_refunded_customer_sk = cr_refunded_customer_sk
and ccr.COUNTRY_ROLE = current_role())
and exists (
select 1 from country_catalog_returns as ccr
where ccr.cr_returning_customer_sk = cr_returning_customer_sk
and ccr.COUNTRY_ROLE = current_role());
ALTER 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
ALTER TABLE catalog_sales DROP ROW ACCESS POLICY ROW_ACCESS_POLICY_catalog_sales;
CREATE OR REPLACE ROW ACCESS POLICY ROW_ACCESS_POLICY_catalog_sales as (cs_bill_customer_sk NUMBER, cs_ship_customer_sk NUMBER) returns boolean ->
exists (
select 1 from country_catalog_sales as ccs
where ccs.cs_bill_customer_sk = cs_bill_customer_sk
and ccs.COUNTRY_ROLE = current_role())
and exists (
select 1 from country_catalog_sales as ccs
where ccs.cs_ship_customer_sk = cs_ship_customer_sk
and ccs.COUNTRY_ROLE = current_role());
ALTER 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
ALTER TABLE store_returns DROP ROW ACCESS POLICY ROW_ACCESS_POLICY_store_returns;
CREATE OR REPLACE ROW ACCESS POLICY ROW_ACCESS_POLICY_store_returns as (sr_customer_sk NUMBER) returns boolean ->
exists (
select 1 from country_store_returns as csr
where csr.sr_customer_sk = sr_customer_sk
and csr.COUNTRY_ROLE = current_role());
ALTER 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
ALTER TABLE store_sales DROP ROW ACCESS POLICY ROW_ACCESS_POLICY_store_sales;
CREATE OR REPLACE ROW ACCESS POLICY ROW_ACCESS_POLICY_store_sales as (ss_customer_sk NUMBER) returns boolean ->
exists (
select 1 from country_store_sales as css
where css.ss_customer_sk = ss_customer_sk
and css.COUNTRY_ROLE = current_role());
ALTER 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
ALTER TABLE web_returns DROP ROW ACCESS POLICY ROW_ACCESS_POLICY_web_returns;
CREATE OR REPLACE ROW ACCESS POLICY ROW_ACCESS_POLICY_web_returns as (wr_refunded_customer_sk NUMBER, wr_returning_customer_sk NUMBER) returns boolean ->
exists (
select 1 from country_web_returns as cwr
where cwr.wr_refunded_customer_sk = wr_refunded_customer_sk
and cwr.COUNTRY_ROLE = current_role())
and exists (
select 1 from country_web_returns as cwr
where cwr.wr_returning_customer_sk = wr_returning_customer_sk
and cwr.COUNTRY_ROLE = current_role());
ALTER 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
ALTER TABLE web_sales DROP ROW ACCESS POLICY ROW_ACCESS_POLICY_web_sales;
CREATE OR REPLACE ROW ACCESS POLICY ROW_ACCESS_POLICY_web_sales as (ws_bill_customer_sk NUMBER, ws_ship_customer_sk NUMBER) returns boolean ->
exists (
select 1 from country_web_sales as cws
where cws.ws_bill_customer_sk = ws_bill_customer_sk
and cws.COUNTRY_ROLE = current_role())
and exists (
select 1 from country_web_sales as cws
where cws.ws_ship_customer_sk = ws_ship_customer_sk
and cws.COUNTRY_ROLE = current_role());
ALTER 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:
USE ROLE ROLE_COUNTRY_USA;
USE TPCDS_DB;
USE SCHEMA TPCDS_SF10TCL;
select current_role();
select C_BIRTH_COUNTRY, * from CUSTOMER limit 60;
select cr_refunded_customer_sk, cr_returning_customer_sk, * from catalog_returns limit 10;
select cs_bill_customer_sk, cs_ship_customer_sk, * from catalog_sales limit 10;
select sr_customer_sk, * from store_returns limit 10;
select ss_customer_sk, * from store_sales limit 10;
select wr_refunded_customer_sk, wr_returning_customer_sk, * from web_returns limit 20;
select ws_bill_customer_sk, ws_ship_customer_sk, * from web_sales limit 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:
USE ROLE ROLE_COUNTRY_GERMANY;
USE TPCDS_DB;
USE SCHEMA TPCDS_SF10TCL;
select C_BIRTH_COUNTRY, * from CUSTOMER limit 60;
select cr_refunded_customer_sk, cr_returning_customer_sk, * from catalog_returns limit 10;
select cs_bill_customer_sk, cs_ship_customer_sk, * from catalog_sales limit 10;
select sr_customer_sk, * from store_returns limit 10;
select ss_customer_sk, * from store_sales limit 10;
select wr_refunded_customer_sk, wr_returning_customer_sk, * from web_returns limit 20;
select ws_bill_customer_sk, ws_ship_customer_sk, * from web_sales limit 10;
19. Verify that employees in MEXICO may only view Customers and Store/Catalog/Web Returns/Sales in MEXICO
USE ROLE ROLE_COUNTRY_GERMANY;
USE TPCDS_DB;
USE SCHEMA TPCDS_SF10TCL;
select C_BIRTH_COUNTRY, * from CUSTOMER limit 60;
select cr_refunded_customer_sk, cr_returning_customer_sk, * from catalog_returns limit 10;
select cs_bill_customer_sk, cs_ship_customer_sk, * from catalog_sales limit 10;
select sr_customer_sk, * from store_returns limit 10;
select ss_customer_sk, * from store_sales limit 10;
select wr_refunded_customer_sk, wr_returning_customer_sk, * from web_returns limit 20;
select ws_bill_customer_sk, ws_ship_customer_sk, * from web_sales limit 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:
select c_customer_sk from public.customer where 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.