Snowflake Data Sharing: How to Share Data Securely

Snowflake's Secure Data Sharing feature allows you to share databases and their objects with multiple accounts efficiently.

Here are two primary methods to set up data sharing, along with examples to help you get started.

Sharing Data Option 1: Grant Database Roles to a Share

Advantages: Allows for granular access control by segmenting database objects among multiple roles within a share. This approach is ideal if different consumer groups require access to different subsets of data.
Limitations: Cannot include data from multiple databases in a single share due to privilege restrictions. This method is less flexible if your data sharing needs span multiple databases.

Sharing Data Option 2: Grant Privileges on Objects Directly to a Share

Advantages: Supports including data from multiple databases within a single share, provided all databases belong to the same account. Simplifies access control by providing a single IMPORTED PRIVILEGES level to consumer accounts.
Limitations: Offers less control over which objects specific consumer groups can access. It's an all-or-nothing approach unless multiple shares are created.

Consuming Shared Data

After data is shared by the provider, the consumer account must have an ACCOUNTADMIN role or a role granted the IMPORT SHARE privilege to access the shared data. This allows the consumer to create databases from shares, manage access, and perform queries, ensuring a controlled and secure data sharing environment.

Examples

Sharing Data Option 1: Granting Database Roles to a Share

This approach uses database roles to control access, allowing for granular management of data permissions.

Create Database Roles

Start by creating roles specific to data access needs.


_10USE DATABASE data_db;
_10
_10CREATE DATABASE ROLE sales_role;
_10CREATE DATABASE ROLE hr_role;

Grant Privileges to Roles

Assign appropriate privileges to these roles for specific schemas or tables.


_10GRANT USAGE ON SCHEMA sales TO DATABASE ROLE sales_role;
_10GRANT SELECT ON ALL TABLES IN SCHEMA sales TO DATABASE ROLE sales_role;
_10
_10GRANT USAGE ON SCHEMA hr TO DATABASE ROLE hr_role;
_10GRANT SELECT ON ALL TABLES IN SCHEMA hr TO DATABASE ROLE hr_role;

Create a Share

Create a share that will be used to distribute data.


_10CREATE SHARE company_share;

Grant Roles to the Share

Assign the created roles to the share.


_10GRANT USAGE ON DATABASE company_test TO SHARE company_share;
_10
_10GRANT DATABASE ROLE sales_role TO SHARE company_share;
_10GRANT DATABASE ROLE hr_role TO SHARE company_share;

Add Accounts to the Share

Specify the accounts that can access the shared data.


_10ALTER SHARE company_share ADD ACCOUNTS = org1.consumer1, org1.consumer2;

Sharing Data Option 2: Granting Direct Privileges to a Share

This method grants direct privileges to a share, suitable for situations where role-based access control is not necessary.

Create a Share

Establish a new share.


_10CREATE SHARE direct_share;

Grant Direct Privileges

Provide direct access to specific databases, schemas, or tables.


_10GRANT USAGE ON DATABASE company_data TO SHARE direct_share;
_10GRANT USAGE ON SCHEMA sales TO SHARE direct_share;
_10GRANT SELECT ON TABLE sales.transactions TO SHARE direct_share;

Add Consumer Accounts

Include accounts that will consume the shared data.


_10ALTER SHARE direct_share ADD ACCOUNTS = org1.consumer1, org1.consumer2;

Consuming Shared Data

Consuming shared data involves creating a database from the shares provided by data providers. You'll need the ACCOUNTADMIN role or a role with the IMPORT SHARE privilege to perform these tasks.

Viewing Available Shares

First, check the shares that are available to your account.


_10SHOW SHARES;

Creating a Database from a Share

After identifying the share, create a database from it.


_10CREATE DATABASE my_sales_db FROM SHARE provider_acc.provider_sales_share;

Granting Privileges on a Shared Database

Once the database is created from a share, you need to grant privileges to roles within your account to access this database. This step is required for managing who can query the shared data.


_10GRANT IMPORTED PRIVILEGES ON DATABASE my_sales_db TO ROLE analyst;

Querying the Shared Database

Now that the roles have access, you can perform queries on the shared database just as you would with any other database in your account.


_10USE ROLE analyst;
_10USE DATABASE my_sales_db;
_10SELECT * FROM your_shared_table;

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Get Started