Secure data sharing basics in Snowflake - Commands & Configuration

Introduction

Sharing data securely means giving others access to your data without copying or moving it. Snowflake lets you share data safely with other accounts while keeping control over who sees what.

When you want to share live data with a partner company without sending files.

When you need to give your sales team access to customer data without exposing sensitive details.

When you want to share data with a vendor for analysis but keep your original data safe.

When you want to avoid data duplication and keep one source of truth.

When you want to control and revoke access easily without complex setups.

Config File - share_creation.sql

share_creation.sql

CREATE SHARE my_secure_share;

GRANT USAGE ON DATABASE my_database TO SHARE my_secure_share;
GRANT SELECT ON ALL TABLES IN SCHEMA my_database.public TO SHARE my_secure_share;

ALTER SHARE my_secure_share ADD ACCOUNTS = ('partner_account');

This SQL script creates a secure data share named my_secure_share. It grants usage on the database my_database and select access on all tables in the public schema to the share. Finally, it adds the external Snowflake account partner_account as a recipient of the share.

This setup allows the partner account to query the shared data live without copying it.

Commands

This command creates a new data share named 'my_secure_share' in Snowflake.

Terminal

snowsql -q "CREATE SHARE my_secure_share;"

Expected Output

Done. Snowflake successfully created share 'my_secure_share'.

This command allows the share to access the database 'my_database'.

Terminal

snowsql -q "GRANT USAGE ON DATABASE my_database TO SHARE my_secure_share;"

Expected Output

Done. Granted USAGE on DATABASE my_database to SHARE my_secure_share.

This command grants read-only access to all tables in the 'public' schema of 'my_database' to the share.

Terminal

snowsql -q "GRANT SELECT ON ALL TABLES IN SCHEMA my_database.public TO SHARE my_secure_share;"

Expected Output

Done. Granted SELECT on ALL TABLES in SCHEMA my_database.public to SHARE my_secure_share.

This command adds the external Snowflake account 'partner_account' to receive the shared data.

Terminal

snowsql -q "ALTER SHARE my_secure_share ADD ACCOUNTS = ('partner_account');"

Expected Output

Done. Added accounts to share 'my_secure_share'.

This command lists all data shares to verify that 'my_secure_share' exists and is configured.

Terminal

snowsql -q "SHOW SHARES;"

Expected Output

name             database_name   owner   created_on           comment

my_secure_share   my_database    ADMIN   2024-06-01 12:00:00  

Key Concept

If you remember nothing else from this pattern, remember: Snowflake shares let you give live, controlled access to your data without copying it.

Common Mistakes

Not granting USAGE on the database to the share.

Without USAGE permission, the share cannot access the database, so the recipient cannot see any data.

Always grant USAGE on the database to the share before granting table-level permissions.

Forgetting to add the recipient account to the share.

If the recipient account is not added, they cannot access the shared data even if permissions are set.

Use ALTER SHARE to add the recipient account explicitly.

Granting SELECT on individual tables but missing new tables created later.

New tables won't be accessible unless permissions are granted again.

Grant SELECT on ALL TABLES in the schema to cover existing and future tables.

Summary

Create a share to start secure data sharing.

Grant USAGE on the database and SELECT on all tables to the share.

Add recipient accounts to the share to give them access.

Verify the share exists and is configured correctly.

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