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.