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
- 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; avoidCURRENT_USERandCURRENT_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.
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 LISTINGandALTER LISTING, so a listing can be kept in source control. SHOW SHARESlists outbound shares and inbound shares;DESCRIBE SHAREshows the contents.- Use
CREATE SHAREprivilege on a custom role so that automation does not needACCOUNTADMIN. - Share the same object to several shares if audiences differ.
| Step | Provider | Consumer |
|---|---|---|
| 1 | CREATE SHARE | SHOW SHARES to see the inbound share |
| 2 | GRANT ... TO SHARE | CREATE DATABASE ... FROM SHARE |
| 3 | ALTER SHARE ... ADD ACCOUNTS | GRANT 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.
| Object | Shareable | Note |
|---|---|---|
| Tables, including dynamic tables, external tables and Iceberg tables | Yes | Grant SELECT |
| Secure views and secure materialized views | Yes | Best way to hide logic and rows |
| Non secure views | Yes, in current Snowflake | Definition is visible, no protection of logic |
| Secure UDFs | Yes | Grant USAGE on the function |
| Streams | No | Consumers create their own streams on shared tables or secure views |
| Stages, pipes, tasks, stored procedures | No | Not part of a share |
| Another share's imported database | No | Cannot 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.
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.
| Function | In 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.
Managing providers and consumers
The administrator needs a view of who shares with whom.
- Consumers of your shares:
SHOW GRANTS OF SHARE sales_sharelists the accounts.SHOW SHARESlists outbound shares. - Your providers: inbound shares in
SHOW SHARESshow 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; avoidCURRENT_USERandCURRENT_ROLE. SIMULATED_DATA_SHARING_CONSUMERtests 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_CONSUMERto 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.