1
0
In the evolving landscape of data sharing, Snowflake offers two primary methods: Direct Share and Private Listing. While both facilitate data sharing, they cater to different needs and scenarios. This blog focuses on Private Listing and Masking Policies for Cross-Region Data Sharing, comparing it with Direct Share and examining how masking policies ensure data privacy in shared environments.
Private Listing vs. Direct Share: Key Differences
Direct Share
- Scope: Restricted to accounts within the same Snowflake region.
- Functionality: Enables the direct sharing of specific database objects (e.g., tables, views) with another account.
- Usage: Ideal for localized data sharing within the same region or cloud.
Private Listing
- Scope: Expands sharing across Snowflake regions and clouds.
- Enhanced Functionality:
- Auto-fulfillment: Automatically replicates shared data to other regions without manual replication.
- Metadata: Includes titles, descriptions, and sample SQL queries to help consumers quickly understand and utilize the data.
- Marketplace Integration: Enables publishing listings on the Snowflake Marketplace for broader reach.
- Use Case: Essential for organizations needing to share data globally across multiple clouds or geographic locations while maintaining control and governance.
Governance and security features in Snowflake, such as masking policies, ensure data integrity and confidentiality, even in cross-region and cross-cloud scenarios. Masking policies in Snowflake play a pivotal role in securing sensitive data.
Key Behavior of Masking Policies in Private Listing
Key Behavior of Masking Policies in Private Listing
- Policy Persistence: When data is shared via Private Listing, any masking policies applied in the provider account remain intact in the consumer account. This ensures that sensitive data is consistently protected, regardless of its destination.
- Role Independence: Even if the role names in the provider and consumer accounts are identical, Snowflake distinguishes between the accounts. This prevents unauthorized access and ensures the masking policy is enforced based on the provider’s settings.
Coupled with Snowflake’s masking policies, Private Listing ensures that sensitive data remains protected, even in cross-region and cross-cloud sharing. This combination of functionality and security makes Snowflake a powerful platform for modern data sharing
Technical Implementation:
Technical Implementation:
Following tables and Views to be shared with Snowflake Account hosted on AWS Cloud in different region .
Masking Policy in Provider with REP Role.
Masking Policy in Provider with FLM and ACCOUNTADMIN Role.
create or replace table ORDER_PRTY(O_PRI_CD varchar,RLES Varchar);
insert into ORDER_PRTY(O_PRI_CD,RLES) values
('5-LOW','REP1'),('4-NOT SPECIFIED','REP1'),('1-URGENT','ACCOUNTADMIN'),('3-MEDIUM','ACCOUNTADMIN'),('2-HIGH','ACCOUNTADMIN'), ('2-HIGH','FLM');
create or replace row access policy orders_role as (o_priority varchar) returns boolean ->
exists (
select 1
from ORDER_PRTY e
where o_priority = e.O_PRI_CD
and e.RLES = current_role());
alter table ORDERS add row access policy orders_role on (O_ORDERPRIORITY);
Observe the Row count w.r.t each role, the record count varies based on the Order priority for every role.
Now Create the Private Listing and shared the Tables with consumer account.
Go to your Consumer Account and see your listing. Click on Get and it will start replicating the data from provider Account
CREATE or replace ROLE REP1;
create or replace user REP1 password = 'REP1' default_role = REP1 must_change_password = false;
grant role REP1 to user REP1;
GRANT ROLE REP1 TO USER SACHINSNOWPRO;
grant IMPORTED PRIVILEGES on database ORDER_MGMT_SHARE to role REP1;
grant usage on warehouse COMPUTE_WH to role REP1;
CREATE or replace ROLE FLM;
create or replace user FLM password = 'FLM' default_role = FLM must_change_password = false;
grant role FLM to user FLM;
GRANT ROLE FLM TO USER SACHINSNOWPRO;
grant IMPORTED PRIVILEGES on database ORDER_MGMT_SHARE to role FLM;
grant usage on warehouse COMPUTE_WH to role FLM;
Similarly, when viewing the data in the ORDERS table using any of these roles, no data is visible. This indicates that the ACCOUNTADMIN, FLM, or REP1 roles in the consumer account are not being treated the same as those in the provider account.
Key points:
- Querying this table from the consumer account will still return masked values..
- Query execution utilizes the metadata from the provider account while leveraging the consumer account’s compute resources, ensuring secure and efficient data processing.
- Snowflake identifies the discrepancy in accounts despite the identical role names. It implies that Snowflake recognizes the distinct accounts associated with each role, even when the role names are the same.
