Creating and managing shares in Snowflake - Visual Walkthrough

Process Flow - Creating and managing shares

Start

↓

Create Share

↓

Add Database Objects to Share

↓

Grant Access to Consumers

↓

Manage Share (Add/Remove Objects)

↓

Monitor Share Usage

↓

End

The flow shows creating a share, adding objects, granting access, managing content, and monitoring usage.

Execution Sample

Snowflake

CREATE SHARE my_share;
GRANT USAGE ON DATABASE my_db TO SHARE my_share;
ALTER SHARE my_share ADD SCHEMA my_db.my_schema;
GRANT SELECT ON TABLE my_db.my_schema.my_table TO SHARE my_share;

This code creates a share, adds a database and schema, and grants select access on a table.

Process Table

Step	Action	Object Affected	Result	Notes
1	CREATE SHARE my_share;	Share: my_share	Share created	Share is empty initially
2	GRANT USAGE ON DATABASE my_db TO SHARE my_share;	Database: my_db	Usage granted to share	Share can now reference database
3	ALTER SHARE my_share ADD SCHEMA my_db.my_schema;	Schema: my_schema	Schema added to share	Objects in schema become shareable
4	GRANT SELECT ON TABLE my_db.my_schema.my_table TO SHARE my_share;	Table: my_table	Select granted to share	Consumers can query this table
5	GRANT SHARE my_share TO ACCOUNT consumer_account;	Consumer Account	Access granted	Consumer can now use the share
6	ALTER SHARE my_share REMOVE SCHEMA my_db.my_schema;	Schema: my_schema	Schema removed from share	Objects no longer shared
7	DROP SHARE my_share;	Share: my_share	Share dropped	Share no longer exists

💡 Share dropped or no longer needed, ending management.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	After Step 7
my_share	undefined	created	created with db usage	created with schema	created with table select	shared with consumer	schema removed	dropped
my_db	exists	exists	usage granted to share	usage granted to share	usage granted to share	usage granted to share	usage granted to share	usage granted to share
consumer_account	no access	no access	no access	no access	no access	access granted	access granted	access revoked

Key Moments - 3 Insights

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

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 will fail.

What happens if we remove a schema from the share after granting access?

Removing the schema (Step 6) removes all objects inside it from the share, so consumers lose access to those objects even if they had access before.

Does creating a share automatically give access to any consumer accounts?

No, creating a share (Step 1) only creates it. Access must be explicitly granted to consumer accounts (Step 5) to allow usage.

Visual Quiz - 3 Questions

Look at the execution table, what is the state of 'my_share' after Step 3?

A Share created with database usage granted

B Share created with schema added

C Share dropped

D Share created with table select granted

💡 Hint

Check the 'Result' column in Step 3 of the execution table.

At which step does the consumer account gain access to the share?

A Step 5

B Step 4

C Step 2

D Step 6

💡 Hint

Look for 'Access granted' in the 'Result' column.

If we skip granting USAGE on the database to the share, what will happen when adding schemas?

A Schemas will be added successfully

B Consumer accounts get access automatically

C Adding schemas will fail

D Share will be dropped automatically

💡 Hint

Refer to the key moment about granting USAGE before adding schemas.

Concept Snapshot

CREATE SHARE share_name;
GRANT USAGE ON DATABASE db_name TO SHARE share_name;
ALTER SHARE share_name ADD SCHEMA db_name.schema_name;
GRANT SELECT ON TABLE db_name.schema_name.table_name TO SHARE share_name;
GRANT SHARE share_name TO ACCOUNT consumer_account;
Use ALTER SHARE to add or remove objects; DROP SHARE to delete.

Full Transcript

Creating and managing shares in Snowflake involves first creating a share object. Then, you grant usage on the database to the share so it can reference objects inside. Next, you add schemas and grant select privileges on tables to the share. After that, you grant access to consumer accounts so they can use the shared data. You can manage the share by adding or removing schemas and tables as needed. Finally, you can drop the share when it is no longer needed. Each step changes the state of the share and its accessibility.