Data Sharing and Snowflake Marketplace · Topic 3 of 10

Programmatic Sharing, Shareable Objects, Context Functions and Providers

Configure sharing with SQL, know which object types can be shared, use context functions such as CURRENT_ACCOUNT safely, and manage providers and consumers

  • 5 min
In a nutshell
  • A share script: CREATE SHARE, GRANT ... TO SHARE, ALTER SHARE ... ADD ACCOUNTS; consumer: CREATE DATABASE ... FROM SHARE, GRANT IMPORTED PRIVILEGES.
  • Shareable: tables, secure views, secure materialized views, secure UDFs; not shareable: streams, stages, pipes, tasks.
  • Use CURRENT_ACCOUNT() in shared secure views; avoid CURRENT_USER and CURRENT_ROLE.

What it is

Everything done in Snowsight for sharing can be scripted. An administrator uses SQL, Snowflake CLI or the Snowflake APIs to create shares, grant objects and add consumers, so that sharing is repeatable across environments.

Two more skills sit beside that: knowing which object types a share can hold, and knowing which context functions return the right answer when a view is read by a consumer. Finally, the administrator keeps track of who the providers and consumers are.

ScriptSQL or CLICREATE SHAREShareobjects grantedADD ACCOUNTSConsumersnamed accountsconsumerCREATE DATABASE FROM SHAREIMPORTED PRIVILEGES

How it works

Configure sharing programmatically

A complete provider script is a short sequence. Use organization qualified account names.

USE ROLE ACCOUNTADMIN;
CREATE SHARE IF NOT EXISTS sales_share COMMENT = 'Orders for partners';
GRANT USAGE ON DATABASE sales TO SHARE sales_share;
GRANT USAGE ON SCHEMA sales.public TO SHARE sales_share;
GRANT SELECT ON VIEW sales.public.v_orders_secure TO SHARE sales_share;
ALTER SHARE sales_share ADD ACCOUNTS = myorg.partner_acct;
SHOW GRANTS TO SHARE sales_share;

The consumer side is also a script:

USE ROLE ACCOUNTADMIN;
CREATE DATABASE partner_sales FROM SHARE myorg.provider_acct.sales_share;
GRANT IMPORTED PRIVILEGES ON DATABASE partner_sales TO ROLE analyst;

Further scripting points:

  • To create a listing in code, use a listing manifest in YAML with CREATE EXTERNAL LISTING and ALTER LISTING, so a listing can be kept in source control.
  • SHOW SHARES lists outbound shares and inbound shares; DESCRIBE SHARE shows the contents.
  • Use CREATE SHARE privilege on a custom role so that automation does not need ACCOUNTADMIN.
  • Share the same object to several shares if audiences differ.
StepProviderConsumer
1CREATE SHARESHOW SHARES to see the inbound share
2GRANT ... TO SHARECREATE DATABASE ... FROM SHARE
3ALTER SHARE ... ADD ACCOUNTSGRANT IMPORTED PRIVILEGES

Sharing different types of objects

Not every object can be shared. The rule of thumb is that data bearing objects can be, and objects that act on the account cannot.

ObjectShareableNote
Tables, including dynamic tables, external tables and Iceberg tablesYesGrant SELECT
Secure views and secure materialized viewsYesBest way to hide logic and rows
Non secure viewsYes, in current SnowflakeDefinition is visible, no protection of logic
Secure UDFsYesGrant USAGE on the function
StreamsNoConsumers create their own streams on shared tables or secure views
Stages, pipes, tasks, stored proceduresNoNot part of a share
Another share's imported databaseNoCannot be re-shared directly

Secure functions deserve a note. A secure UDF hides its definition and prevents optimizations that could leak data. A provider shares it with GRANT USAGE ON FUNCTION ... TO SHARE, and a consumer calls it by its name inside the shared database. A normal UDF is not suitable for sharing.

Exam tip

When a scenario says "share a calculation or a transformation to consumers who must not see the logic", choose a secure view or a secure UDF.

Context functions in data sharing

A secure view or UDF runs in the consumer's session, so a function that reads the session returns the consumer's values. Pick the ones that make sense.

FunctionIn a shared view
CURRENT_ACCOUNT()Returns the consumer's account. The right choice for per consumer filtering with a mapping table
CURRENT_USER(), CURRENT_ROLE()Return consumer users and roles, which the provider does not manage. Not usable for filtering
INVOKER_SHARE()Returns the name of the share that exposes the object, or NULL when not accessed through a share
IS_DATABASE_ROLE_IN_SESSION()Tests whether a database role is active, for use with shared database roles
CREATE SECURE VIEW sales.public.v_orders_secure AS
SELECT o.*
FROM sales.public.orders o
JOIN sales.public.account_map m
  ON o.customer_id = m.customer_id
WHERE m.snowflake_account = CURRENT_ACCOUNT();

The session parameter SIMULATED_DATA_SHARING_CONSUMER lets the provider test what a given consumer account would see without sharing the view first.

Consumer queryrunsSecure viewCURRENT_ACCOUNTjoinsMapping tableaccount to rowsreturnsConsumer rowsonly its own

Managing providers and consumers

The administrator needs a view of who shares with whom.

  • Consumers of your shares: SHOW GRANTS OF SHARE sales_share lists the accounts. SHOW SHARES lists outbound shares.
  • Your providers: inbound shares in SHOW SHARES show the provider account and the database created from them.
  • Remove access: ALTER SHARE ... REMOVE ACCOUNTS, or drop the share. Consumers lose access at once.
  • Usage: listing activity is in SNOWFLAKE.DATA_SHARING_USAGE (see the monitoring page).
  • Access in your account: inbound databases are used through IMPORTED PRIVILEGES, so you control which roles see provider data.

Common mistakes

Sharing a stream. It is not possible. The consumer builds its own.

Using CURRENT_ROLE() in a shared view. It returns the consumer's role and breaks the filter.

Sharing a standard function for protection. Use a secure UDF.

Forgetting to re-grant. A recreated object is a new object. It must be granted to the share again.

Hard coding account names in scripts. Use organization qualified names and a mapping table.

What to remember for the exam

  • A share script: CREATE SHARE, GRANT ... TO SHARE, ALTER SHARE ... ADD ACCOUNTS; consumer: CREATE DATABASE ... FROM SHARE, GRANT IMPORTED PRIVILEGES.
  • Shareable: tables, secure views, secure materialized views, secure UDFs; not shareable: streams, stages, pipes, tasks.
  • Use CURRENT_ACCOUNT() in shared secure views; avoid CURRENT_USER and CURRENT_ROLE.
  • SIMULATED_DATA_SHARING_CONSUMER tests the consumer view.
  • Listings can be created in code with a YAML manifest.

Check yourself

  • Which context function returns the right value in a shared secure view?

    CURRENT_ACCOUNT(), because it returns the consumer account.

  • Can a stream be part of a share?

    No. The consumer creates a stream on the shared table or secure view.

  • How does a provider test what one consumer sees?

    Set SIMULATED_DATA_SHARING_CONSUMER to that account in the session.

Topic 3 of 10 in Data Sharing and Snowflake Marketplace

Test yourself on Data Sharing and Snowflake Marketplace

The full Snowflake ADA-C02 question bank has 814 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.