Share security and governance in Snowflake - Step-by-Step Execution

Process Flow - Share security and governance

Create Share

↓

Add Objects to Share

↓

Grant Access to Consumers

↓

Consumer Uses Share

↓

Monitor and Govern Usage

↓

Revoke or Modify Access

This flow shows how a data share is created, objects added, access granted, used by consumers, monitored, and access managed.

Execution Sample

Snowflake

CREATE SHARE my_share;
GRANT USAGE ON DATABASE my_db TO SHARE my_share;
GRANT USAGE ON SCHEMA my_db.my_schema TO SHARE my_share;
GRANT SELECT ON ALL TABLES IN SCHEMA my_db.my_schema TO SHARE my_share;
GRANT IMPORTED PRIVILEGES ON DATABASE my_shared_db TO ROLE consumer_role;

This code creates a share, adds database and schema objects, grants select permissions, and (consumer side) grants imported privileges to enable querying.

Process Table

Step	Action	Object Affected	Permission/State Change	Result
1	CREATE SHARE my_share	Share my_share	Created	Share object ready to add data
2	GRANT USAGE ON DATABASE my_db TO SHARE my_share	Database my_db	Usage granted to share	Share can reference database
3	GRANT USAGE ON SCHEMA my_db.my_schema TO SHARE my_share	Schema my_schema	Usage granted to share	Schema included in share
4	GRANT SELECT ON ALL TABLES IN SCHEMA my_db.my_schema TO SHARE my_share	Tables in my_schema	Select granted to share	Share can expose table data
5	GRANT IMPORTED PRIVILEGES ON DATABASE my_shared_db TO ROLE consumer_role	consumer_role	Imported privileges granted	Consumer can query shared data
6	Consumer queries shared data	Shared tables	Read access	Data accessed securely
7	Monitor usage	Share usage logs	Track queries	Governance enforced
8	Revoke or modify access	Share or consumer	Permissions updated	Access controlled as needed

💡 Access is controlled and monitored to maintain security and governance.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	Final
my_share	None	Created	Has usage on my_db	Includes my_schema	Has select on tables	Granted to consumer_account	Active and shared
consumer_account_access	None	None	None	None	None	Granted imported privileges	Can query shared data

Key Moments - 3 Insights

Why do we grant USAGE on the database to the share before adding schemas?

Granting USAGE on the database (see step 2) allows the share to reference objects inside it. Without this, adding schemas or tables to the share (steps 3 and 4) would fail.

How does granting SELECT on tables to the share affect consumer access?

Granting SELECT on tables (step 4) lets the share expose data. The consumer must then grant imported privileges on the shared database (step 5) to actually query the data.

What is the purpose of monitoring usage after sharing?

Monitoring usage (step 7) helps track who accessed data and how. This supports governance by ensuring data is used properly and securely.

Visual Quiz - 3 Questions

Look at the execution table, at which step is the share first able to reference the database?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Check the 'Permission/State Change' column for when USAGE on database is granted to the share.

According to the variable tracker, what is the state of 'consumer_account_access' after step 4?

A Granted imported privileges

B Can query shared data

C None

D Has select on tables

💡 Hint

Look at the 'consumer_account_access' row and the column 'After Step 4' in the variable tracker.

If we revoke imported privileges from the consumer account, which step in the execution table would reflect this change?

A Step 8

B Step 7

C Step 6

D Step 5

💡 Hint

Step 8 mentions revoking or modifying access.

Concept Snapshot

Snowflake Share Security & Governance:
- Create a share to group data objects.
- Grant USAGE on database to share before adding schemas.
- Grant USAGE on schema to share.
- Grant SELECT on tables to share to expose data.
- Consumer grants imported privileges on shared DB to roles.
- Monitor usage for governance.
- Revoke or modify access to control security.

Full Transcript

This visual execution traces how Snowflake data sharing works for security and governance. First, a share is created. Then, usage permission on the database is granted to the share so it can reference objects. Next, usage on schema and select on tables are granted to the share. Consumer creates a database from the share and grants imported privileges to a role, allowing secure data access. Usage is monitored to enforce governance. Finally, access can be revoked or modified to maintain security. Variables track the share state and consumer access through each step. Key moments clarify why permissions must be granted in order and how monitoring supports governance. Quizzes test understanding of when permissions are granted and how access changes affect usage.

Practice

1. What is the main purpose of a share in Snowflake?

easy

A. To safely share data without copying or moving it

B. To create a backup of data in another region

C. To encrypt data before storage

D. To delete data from multiple accounts

Solution

1. Step 1: Understand the concept of a share

   A share in Snowflake allows data to be accessed by other accounts without physically moving or copying the data.

2. Step 2: Compare options with the concept

   The options describing encryption before storage, creating backups in another region, and deleting data from multiple accounts are unrelated to sharing data safely.

3. Final Answer:

   To safely share data without copying or moving it -> Option A

4. Quick Check:

   Share = Safe data access without moving [OK]

Hint: Shares let others see data without moving it [OK]

Common Mistakes:

• Thinking shares copy data to other accounts
• Confusing shares with backups
• Assuming shares encrypt data automatically

2. Which SQL command correctly grants usage on a share to a specific account in Snowflake?

easy

A. GRANT USAGE TO SHARE my_share ACCOUNT 'account_name';

B. GRANT SHARE ON USAGE my_share TO ACCOUNT 'account_name';

C. GRANT USAGE ON SHARE my_share TO ACCOUNT = 'account_name';

D. GRANT ACCOUNT USAGE ON SHARE my_share TO 'account_name';

Solution

1. Step 1: Recall correct GRANT syntax for shares

   The correct syntax is: GRANT USAGE ON SHARE <share_name> TO ACCOUNT = '<account_name>';

2. Step 2: Check each option's syntax

   Only GRANT USAGE ON SHARE my_share TO ACCOUNT = 'account_name'; matches the correct syntax. Others have incorrect order or missing keywords.

3. Final Answer:

   GRANT USAGE ON SHARE my_share TO ACCOUNT = 'account_name'; -> Option C

4. Quick Check:

   GRANT USAGE ON SHARE ... TO ACCOUNT = ... [OK]

Hint: GRANT USAGE ON SHARE ... TO ACCOUNT = 'name' [OK]

Common Mistakes:

• Swapping keywords order
• Omitting the equal sign after ACCOUNT
• Using incorrect object types in GRANT

3. Given the following SQL commands, what will be the result when account 'A' tries to query the shared table?

CREATE SHARE my_share;
GRANT USAGE ON DATABASE my_db TO SHARE my_share;
GRANT SELECT ON TABLE my_db.public.my_table TO SHARE my_share;
GRANT USAGE ON SHARE my_share TO ACCOUNT = 'A';

medium

A. Account 'A' cannot query because database usage is not granted

B. Account 'A' can query my_table successfully

C. Account 'A' cannot query because SELECT privilege is missing

D. Account 'A' cannot query because share usage is not granted

Solution

1. Step 1: Check granted privileges on share

   The share has USAGE on the database and SELECT on the table granted, and account 'A' has USAGE on the share.

2. Step 2: Confirm account 'A' can access shared data

   With these grants, account 'A' can query the shared table successfully.

3. Final Answer:

   Account 'A' can query my_table successfully -> Option B

4. Quick Check:

   Proper grants = Access allowed [OK]

Hint: Grant database usage, table select, and share usage to account [OK]

Common Mistakes:

• Forgetting to grant USAGE on database to share
• Missing SELECT privilege on table
• Not granting USAGE on share to account

4. You receive an error when trying to grant SELECT on a table to a share: SQL compilation error: Insufficient privileges. What is the most likely cause?

medium

A. You forgot to create the database

B. The share does not exist

C. The account name is misspelled

D. You do not have OWNERSHIP privilege on the table

Solution

1. Step 1: Understand privilege requirements for granting SELECT to share

   Granting SELECT on a table to a share requires OWNERSHIP or appropriate privileges on that table.

2. Step 2: Analyze error cause

   Insufficient privileges error usually means the user lacks OWNERSHIP or necessary rights on the table.

3. Final Answer:

   You do not have OWNERSHIP privilege on the table -> Option D

4. Quick Check:

   OWNERSHIP needed to grant SELECT to share [OK]

Hint: OWNERSHIP needed to grant privileges on objects [OK]

Common Mistakes:

• Assuming share existence causes privilege errors
• Confusing account name typos with privilege errors
• Ignoring ownership requirements

5. You want to share a subset of data securely with multiple external accounts but restrict access to only certain tables and columns. Which Snowflake feature combination should you use?

hard

A. Create a share with specific tables and use secure views to limit columns, then grant share usage to accounts

B. Create a share with all tables and rely on account-level access control

C. Copy data to each account and grant full access

D. Use database replication to share data and restrict columns via masking policies

Solution

1. Step 1: Identify how to limit tables and columns in shares

   Shares can include specific tables only. To restrict columns, secure views can be created exposing only allowed columns.

2. Step 2: Confirm how to share with multiple accounts securely

   Granting USAGE on the share to multiple accounts allows controlled access without copying data.

3. Final Answer:

   Create a share with specific tables and use secure views to limit columns, then grant share usage to accounts -> Option A

4. Quick Check:

   Shares + secure views = controlled multi-account access [OK]

Hint: Use secure views inside shares to limit columns [OK]

Common Mistakes:

• Sharing all tables without column restrictions
• Copying data instead of sharing
• Using replication which is for disaster recovery, not sharing