Secure data sharing basics in Snowflake - Step-by-Step Execution

Process Flow - Secure data sharing basics

Create Data Provider Account

↓

Create Share Object

↓

Add Database Objects to Share

↓

Grant Access to Consumer Account

↓

Consumer Accesses Shared Data

↓

Data Remains in Provider Account

↓

End

This flow shows how a data provider creates a share, adds data, grants access, and the consumer accesses data without copying it.

Execution Sample

Snowflake

CREATE SHARE my_share;
GRANT USAGE ON DATABASE my_db TO SHARE my_share;
GRANT SELECT ON ALL TABLES IN SCHEMA my_db.public TO SHARE my_share;
ALTER SHARE my_share ADD ACCOUNTS = ('consumer_account');

This code creates a share, grants access to a database and tables, and allows a consumer account to access the shared data.

Process Table

Step	Action	Object Affected	Result	Notes
1	CREATE SHARE my_share	Share object 'my_share'	Share created	Ready to add data and consumers
2	GRANT USAGE ON DATABASE my_db TO SHARE my_share	Database 'my_db'	Share can use database	Allows access to database metadata
3	GRANT SELECT ON ALL TABLES IN SCHEMA my_db.public TO SHARE my_share	Tables in schema 'my_db.public'	Share can read tables	Allows consumer to query tables
4	ALTER SHARE my_share ADD ACCOUNTS = ('consumer_account')	Share 'my_share'	Consumer account added	Consumer can now access shared data
5	Consumer queries shared data	Shared tables	Data accessed successfully	No data copied, live access
6	Provider updates data	Tables in 'my_db.public'	Consumer sees updated data	Sharing reflects live data changes
7	Consumer tries to modify data	Shared tables	Access denied	Consumer has read-only access
8	End	-	Sharing setup complete	Secure, live data sharing without duplication

💡 Sharing ends when consumer access is revoked or share is dropped

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	Final
my_share	None	Created	Granted DB usage	Granted SELECT on tables	Consumer added	Consumer accesses data	Active and shared
consumer_account_access	No	No	No	No	Yes	Yes	Yes
data_location	Provider only	Provider only	Provider only	Provider only	Provider only	Provider only	Provider only

Key Moments - 3 Insights

Why doesn't the consumer get a copy of the data?

Because the share provides live access to the provider's data without copying it, as shown in step 5 of the execution_table.

Can the consumer modify the shared data?

No, the consumer has read-only access. Step 7 shows that modification attempts are denied.

What happens if the provider updates the data?

The consumer sees the updated data immediately, as shown in step 6, because sharing is live and not a snapshot.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the consumer account granted access to the share?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Check the 'Action' column for when consumer_account is added to the share.

According to variable_tracker, what is the state of 'data_location' after step 5?

A Provider only

B Copied to consumer

C Shared and copied

D Deleted

💡 Hint

Look at the 'data_location' row and its value after step 5.

If the provider revokes consumer access, which step in execution_table would change?

A Step 6

B Step 4

C Step 7

D Step 5

💡 Hint

Access is granted or revoked in the step where consumer_account is added or removed.

Concept Snapshot

Secure Data Sharing in Snowflake:
- Create a SHARE object.
- Grant USAGE on database and SELECT on tables to the share.
- Add consumer accounts to the share.
- Consumers get live, read-only access without data copying.
- Provider controls data and updates are instantly visible.
- Sharing ends when access is revoked or share is dropped.

Full Transcript

Secure data sharing in Snowflake starts with the provider creating a share object. The provider grants usage on the database and select permissions on tables to this share. Then, the provider adds consumer accounts to the share, allowing them to access the data. The consumer can query the shared data live without copying it. The provider retains control and can update data, which the consumer sees immediately. Consumers have read-only access and cannot modify the data. Sharing ends when the provider revokes access or drops the share.

Practice

1. What is the main benefit of using secure data sharing in Snowflake?

easy

A. Encrypt data before sharing it manually

B. Share live data without making copies

C. Create multiple copies of data for each user

D. Send data files via email securely

Solution

1. Step 1: Understand secure data sharing purpose

   Secure data sharing allows sharing data directly without duplication.

2. Step 2: Compare options

   Options A, B, and D involve copying or manual steps, which are not the main benefit.

3. Final Answer:

   Share live data without making copies -> Option B

4. Quick Check:

   Secure sharing = live data, no copies [OK]

Hint: Secure sharing means no data copies needed [OK]

Common Mistakes:

• Thinking data is copied to recipients
• Confusing sharing with encryption only
• Assuming manual file transfer is secure sharing

2. Which Snowflake object do you create first to start sharing data securely?

easy

A. Share

B. Role

C. Warehouse

D. Database

Solution

1. Step 1: Identify the object for sharing

   In Snowflake, a Share object is created to define what data to share.

2. Step 2: Eliminate other options

   Databases hold data, warehouses process queries, roles manage permissions but do not start sharing.

3. Final Answer:

   Share -> Option A

4. Quick Check:

   Create a Share to share data [OK]

Hint: Start sharing by creating a Share object [OK]

Common Mistakes:

• Trying to share by creating a database
• Confusing warehouse with sharing object
• Assuming roles start sharing

3. Given this SQL snippet, what does it do?

CREATE SHARE my_share;
GRANT SELECT ON DATABASE sales_db TO SHARE my_share;
ALTER SHARE my_share ADD ACCOUNTS = ('account_123');

medium

A. Creates a share, grants access to sales_db, and shares with account_123

B. Creates a database named my_share and grants access to account_123

C. Adds a new database to sales_db and shares it with account_123

D. Creates a warehouse named my_share and assigns it to account_123

Solution

1. Step 1: Analyze each command

   CREATE SHARE my_share; creates a share object named my_share.
   GRANT SELECT ON DATABASE sales_db TO SHARE my_share; grants read access on sales_db to the share.
   ALTER SHARE my_share ADD ACCOUNTS = ('account_123'); adds account_123 as a recipient.

2. Step 2: Understand the overall effect

   This sequence shares sales_db data with account_123 via my_share.

3. Final Answer:

   Creates a share, grants access to sales_db, and shares with account_123 -> Option A

4. Quick Check:

   Share + grant + add account = share data [OK]

Hint: Look for CREATE SHARE then GRANT and ADD ACCOUNTS [OK]

Common Mistakes:

• Confusing share with database creation
• Thinking ALTER SHARE adds data, not accounts
• Mixing warehouse and share concepts

4. You run this command but get an error:

ALTER SHARE my_share ADD ACCOUNTS = ('account_123', 'account_123');

What is the likely cause?

medium

A. Account names must be numeric

B. Missing SELECT privilege on the share

C. Share name is invalid

D. Duplicate account names in the list

Solution

1. Step 1: Check the command syntax and parameters

   The command tries to add two accounts with the same name 'account_123' to the share.

2. Step 2: Identify the error cause

   Duplicate account names in the ADD ACCOUNTS list cause an error because each account must be unique.

3. Final Answer:

   Duplicate account names in the list -> Option D

4. Quick Check:

   Duplicate accounts cause errors [OK]

Hint: Avoid duplicate accounts in ADD ACCOUNTS list [OK]

Common Mistakes:

• Assuming share name is invalid without checking
• Thinking privileges cause this error
• Believing account names must be numbers

5. You want to share only specific tables from your database securely. Which approach should you use?

hard

A. Grant SELECT on the entire database to the share

B. Grant SELECT on specific schemas to the share

C. Grant SELECT on specific tables to the share

D. Create a new database with only those tables and share it

Solution

1. Step 1: Understand granularity of sharing

   Snowflake allows granting privileges at database, schema, or table level to shares.

2. Step 2: Choose the most precise option

   Granting SELECT on specific tables limits sharing to only those tables, which fits the requirement.

3. Step 3: Evaluate other options

   Granting on entire database or schema shares more than needed; creating a new database is extra work and less flexible.

4. Final Answer:

   Grant SELECT on specific tables to the share -> Option C

5. Quick Check:

   Grant on tables = share specific data [OK]

Hint: Grant on tables to share only those tables [OK]

Common Mistakes:

• Granting on database shares too much data
• Assuming schema-level grant is always best
• Creating new databases unnecessarily