Working with Shares

This topic describes the tasks associated with a data provider account creating and configuring shares, sharing the shares with other (i.e. consumer) accounts, and performing ongoing maintenance of the shares.

You must use the ACCOUNTADMIN role (or a role granted the CREATE SHARES global privilege) to perform these tasks. For more details about the CREATE SHARES privilege, see Granting CREATE SHARE and IMPORT SHARE to Other Roles.

The one exception to this rule is preparing your objects (database, tables, etc.) to share, which can be performed using any role.

In this Topic:

Data Sharing and ESD (Enterprise for Sensitive Data) Accounts

If you have an ESD account, please note the following conditions for sharing data with other (i.e. consumer) accounts:

Provider   Consumer Supported Notes
ESD (HIPAA) to ESD (HIPAA) Both HIPAA accounts should have a signed BAA with each other.
ESD (HIPAA) to ESD   Snowflake does not allow HIPAA accounts to share data with non-HIPAA accounts, even if the other account is an ESD account.
ESD to ESD or ESD (HIPAA) Enabled by default.
ESD to All other editions Not enabled by default; please contact Snowflake to enable for your account.

For all other Snowflake Editions:

  • VPS (Virtual Private Snowflake) does not support Data Sharing due to the current limitations against cross-region Data Sharing.
  • Enterprise, Premiere, and Standard Editions support Data Sharing with the usual caveats.

For more details, see the next section in this topic.

Attention

Snowflake is not responsible for ensuring that HIPAA accounts who engage in Data Sharing have a signed BAA with each other; this is at the discretion of the accounts that are sharing data. Note that failure to have a signed BAA may impact the HIPAA compliance of both accounts, particularly the provider account.

Also, if you have an ESD account, to maintain the expected level of data protection provided by ESD, we strongly recommend considering the following before requesting Snowflake to enable Data Sharing with non-ESD accounts:

  • Do not share sensitive data with non-ESD accounts.
  • Consider creating a second, non-ESD account where you store less sensitive data and share this data with non-ESD accounts.
  • If you are using Tri-Secret Secure with your ESD account and you share data with other accounts, Snowflake treats the data access from these accounts as if the access occurred from within your own account. Specifically, granting access to the consumer account may require Snowflake to access your AWS KMS.

These are only recommendations and are not enforced by Snowflake. The decision to share data is always at the discretion of the data provider and Snowflake does not assume any responsibility for data that is improperly shared.

DDL for Creating and Managing Shares

To support creating and managing shares, Snowflake provides the following set of special DDL commands:

In addition, providers can view, grant, or revoke access to database objects in a share using the following standard access control DDL:

General Data Sharing Considerations and Usage

Note the following important usage details for creating and maintaining shares:

  • Currently, consumer accounts must be in the same Snowflake Region as your account; i.e. you can only share with other accounts in your Snowflake Region.

  • Each share contains a single database, and all other objects included in the share must be from this same database. This is particularly important to note for views, which are defined by referencing one or more tables; the view and the referenced tables must all be in the same database.

    If any referenced objects (i.e. tables) are in a different database, the referencing object (i.e. view) will fail when a user in a consumer account attempts to query it.

  • For data security and privacy reasons, only secure views are supported in shares at this time. If a standard view is added to a share, Snowflake returns an error.

  • Adding accounts to a share immediately makes the share available to consume by the accounts.

  • New and modified rows in tables in a share (or in tables referenced by a view in a share) are available immediately to all consumers who have created a database from the share. Keep this in mind when updating these tables.

  • A new object created in a database in a share is not automatically available to consumers.

    To make the object available to consumers, you must use the GRANT <privilege> … TO SHARE command to explicitly add the object to the share.

    Note

    This also applies to objects that have been dropped from a database and then recreated with the same name in the database; the recreated object is treated as a new object and is, therefore, not accessible until the object has been explicitly granted the necessary privileges in the share.

Preparing Objects to Share

Before creating a share, Snowflake recommends identifying the database, tables, and secure views you plan to share. This may require some additional planning and administrative tasks, particularly if you decide to share only a subset of data in any of your tables.

Database and Tables

Little or no preparation is required to share a database. Similarly, if you choose to share entire tables in a database, no preparation is required.

However, if you decide to filter the data in a table (or set of tables), either based on certain conditions, or by consumer account, you will need to create one or more secure views on the table(s).

Secure Views

To control access to data in a shared database, use secure views. For example, you may choose to filter data by date or some other condition, or you may decide to use a single share to partition shared data for different consumer accounts. Secure views enable you to dictate the level of granularity you wish to apply to your data while ensuring that the base tables are protected from exposure.

Secure views are defined similar to standard views, using either the CREATE VIEW or ALTER VIEW commands. However, note the following important usage information:

  • Secure views that reference tables by their fully-qualified names (i.e. <db_name>.<schema_name>.<table_name>) can be included in a share; however, you must ensure that the referenced database name matches the database for the share.

  • Do not include secure views that use the CURRENT_USER or CURRENT_ROLE functions in their definition. The contextual values returned by these functions have no relevance in a consumer’s account and will cause the view to fail.

  • When defining a secure view to share with other accounts, a key/vital additional step to perform is validating that the view is configured correctly to display only the data you wish to display. This is particularly important if you wish to limit data access based on the account the data is shared with. To facilitate performing this validation, Snowflake provides the SIMULATED_DATA_SHARING_CONSUMER session parameter. Setting this parameter in a session enables you to simulate querying a secure view as a user in any of the consumer account(s) you plan to share the view with. For example, for a consumer account named xy12345:

    ALTER SESSION SET SIMULATED_DATA_SHARING_CONSUMER = xy12345;
    

For a detailed example of using secure views to control data access, see Using Secure Views to Control Consumer Access.

Note

In the output for SHOW GRANTS and DESCRIBE SHARE, secure views are displayed as tables.

Creating a Share

To create a share:

  1. Use the CREATE SHARE command to create an empty share.
  2. Use the GRANT <privilege> … TO SHARE command to add a database to the share and then selectively grant access to specific database objects (schemas, tables and secure views) to the share.
  3. Use the ALTER SHARE command to add one or more accounts access to the share.

Note

The following steps assume a provider account named prvdr1 is sharing data with two consumer accounts named xy12345 and yz23456.

Step 1: Create the Empty Share

The following example creates an empty share named sales_s:

CREATE SHARE sales_s;

Step 2: Grant Privileges for a Database and Objects to the Share

To include objects in the share, grant privileges on each object. When granting privileges, first grant usage on any container objects before granting usage on the objects in the container. For example, grant usage on a database before granting usage on any schemas contained in the database.

Note

Perform this task before adding accounts to the share. Attempting to add an account before granting usage on a database results in an error.

The following example illustrates granting privileges on the following objects to the sales_s share created in the previous step:

  • sales_db (database)
  • aggregates_eula (schema)
  • aggregate_1 (table)
GRANT USAGE ON DATABASE sales_db TO SHARE sales_s;

GRANT USAGE ON SCHEMA sales_db.aggregates_eula TO SHARE sales_s;

GRANT SELECT ON TABLE sales_db.aggregates_eula.aggregate_1 TO SHARE sales_s;

To confirm the contents of the share:

SHOW GRANTS TO SHARE sales_s;

+-------------------------------+-----------+------------+--------------------------------------+------------+----------------+--------------+--------------+
| created_on                    | privilege | granted_on | name                                 | granted_to | grantee_name   | grant_option | granted_by   |
|-------------------------------+-----------+------------+--------------------------------------+------------+----------------+--------------+--------------|
| 2017-06-15 16:45:07.307 -0700 | USAGE     | DATABASE   | SALES_DB                             | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |
| 2017-06-15 16:45:10.310 -0700 | USAGE     | SCHEMA     | SALES_DB.AGGREGATES_EULA             | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |
| 2017-06-15 16:45:12.312 -0700 | SELECT    | TABLE      | SALES_DB.AGGREGATES_EULA.AGGREGATE_1 | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |
+-------------------------------+-----------+------------+--------------------------------------+------------+----------------+--------------+--------------+

This ensures that the share is correctly configured before making it available to other accounts to consume.

Step 3: Add Accounts to the Share

Attention

If you have an ESD (Enterprise for Sensitive Data) account and are sharing data with consumer accounts:

  • Snowflake supports sharing sensitive data with non-ESD accounts (disabled by default), but does not encourage doing so.
  • To ensure compliance with HIPAA requirements, Snowflake does not allow HIPAA accounts to share data with non-HIPAA accounts.
  • If you are using Tri-Secret Secure data protection, note that Snowflake treats data access from consumer accounts as if the access occurred from within your own account.

For more information about these recommendations and restrictions, see Data Sharing and ESD (Enterprise for Sensitive Data) Accounts.

The following example adds two accounts to the sales_s share:

ALTER SHARE sales_s ADD ACCOUNTS=xy12345, yz23456;

Accounts xy12345 and yz23456 are now able to see the share and create a database from it.

Note

When adding accounts to a share, if the accounts do not exist, the command completes successfully, but no updates are made to the share. To ensure the share is properly updated, make sure the accounts exist and you’ve entered the names correctly.

Use SHOW SHARES to confirm the share. The output of the command lists the sales_s share. The kind column indicates that the share is OUTBOUND, meaning this share is sharing a database with other Snowflake accounts. The to column lists all accounts to which the share has been made available:

SHOW SHARES;

+-------------------------------+----------+-------------------------+-----------------------+------------------+--------------+----------------------------------------+
| created_on                    | kind     | name                    | database_name         | to               | owner        | comment                                |
|-------------------------------+----------+-------------------------+-----------------------+------------------+--------------+----------------------------------------|
| 2016-07-09 19:18:09.821 -0700 | INBOUND  | SFC_SAMPLES.SAMPLE_DATA | SNOWFLAKE_SAMPLE_DATA |                  |              | Sample data sets provided by Snowflake |
| 2017-06-15 17:02:29.625 -0700 | OUTBOUND | PRVDR1.SALES_S          | SALES_DB              | XY12345, YZ23456 | ACCOUNTADMIN |                                        |
+-------------------------------+----------+-------------------------+-----------------------+------------------+--------------+----------------------------------------+

Viewing Consumers Who Have Created Databases from Shares

To see the accounts that have created databases from a share, use the SHOW GRANTS OF SHARE command. This is different from the list of accounts returned by SHOW SHARES:

  • SHOW SHARES lists all shares that are available to accounts, as well as the accounts that are able to access each share.
  • SHOW GRANTS OF SHARE lists all accounts that have created a database from the share. If no accounts have created a database from the share, the results are empty.

For example, the following example shows:

  • Two shares, prvdr1.sales_s and prvdr1.sales_s2 have been made available to accounts xy12345 and yz23456.
  • Account xy12345 has created a database from the prvdr1.sales_s share.
  • No accounts have created databases from the prvdr1.sales_s2 share.
SHOW SHARES;

+-------------------------------+----------+-------------------------+-----------------------+------------------+--------------+----------------------------------------+
| created_on                    | kind     | name                    | database_name         | to               | owner        | comment                                |
|-------------------------------+----------+-------------------------+-----------------------+------------------+--------------+----------------------------------------|
| 2016-07-09 19:18:09.821 -0700 | INBOUND  | SFC_SAMPLES.SAMPLE_DATA | SNOWFLAKE_SAMPLE_DATA |                  |              | Sample data sets provided by Snowflake |
| 2017-06-15 17:02:29.625 -0700 | OUTBOUND | PRVDR1.SALES_S          | SALES_DB              | XY12345, YZ23456 | ACCOUNTADMIN |                                        |
| 2017-06-15 17:02:29.625 -0700 | OUTBOUND | PRVDR1.SALES_S2         | SALES_DB              | XY12345, YZ23456 | ACCOUNTADMIN |                                        |
+-------------------------------+----------+-------------------------+-----------------------+------------------+--------------+----------------------------------------+

SHOW GRANTS OF SHARE sales_s;

+-------------------------------+----------------+------------+----------+
| created_on                    | share          | granted_to | account  |
|-------------------------------+----------------+------------+----------|
| 2017-06-15 18:00:03.803 -0700 | PRVDR1.SALES_S | ACCOUNT    | XY12345  |
+-------------------------------+----------------+------------+----------+

SHOW GRANTS OF SHARE sales_s2;

+------------+-------+------------+---------+
| created_on | share | granted_to | account |
|------------+-------+------------+---------|
+------------+-------+------------+---------+

Maintaining Shares

Adding Objects to a Share

You can add objects to an existing share at any time using the GRANT <privilege> … TO SHARE command. Any objects that you add to a share are instantly available to the consumers accounts who have created databases from the share. For example, if you add a table to a share, users in consumer accounts can query the data in the table as soon as the table is added to the share.

Note

  • If the schema for the object is already in the share, you only need to add the object.
  • If the schema for the object is not already in the share, you need to first add the schema and then the object.

The following example adds a secure view named agg_secure in the aggregates_eula schema to the sales_s share:

SHOW GRANTS TO SHARE sales_s;

+-------------------------------+-----------+------------+--------------------------------------+------------+----------------+--------------+--------------+
| created_on                    | privilege | granted_on | name                                 | granted_to | grantee_name   | grant_option | granted_by   |
|-------------------------------+-----------+------------+--------------------------------------+------------+----------------+--------------+--------------|
| 2017-06-15 16:45:07.307 -0700 | USAGE     | DATABASE   | SALES_DB                             | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |
| 2017-06-15 16:45:10.310 -0700 | USAGE     | SCHEMA     | SALES_DB.AGGREGATES_EULA             | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |
| 2017-06-15 16:45:12.312 -0700 | SELECT    | TABLE      | SALES_DB.AGGREGATES_EULA.AGGREGATE_1 | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |
+-------------------------------+-----------+------------+--------------------------------------+------------+----------------+--------------+--------------+

GRANT SELECT ON VIEW sales_db.aggregates_eula.agg_secure TO SHARE sales_s;

SHOW GRANTS TO SHARE sales_s;

+-------------------------------+-----------+------------+--------------------------------------+------------+----------------+--------------+--------------+
| created_on                    | privilege | granted_on | name                                 | granted_to | grantee_name   | grant_option | granted_by   |
|-------------------------------+-----------+------------+--------------------------------------+------------+----------------+--------------+--------------|
| 2017-06-15 16:45:07.307 -0700 | USAGE     | DATABASE   | SALES_DB                             | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |
| 2017-06-15 16:45:10.310 -0700 | USAGE     | SCHEMA     | SALES_DB.AGGREGATES_EULA             | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |
| 2017-06-15 16:45:12.312 -0700 | SELECT    | TABLE      | SALES_DB.AGGREGATES_EULA.AGGREGATE_1 | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |
| 2017-06-17 12:33:15.310 -0700 | SELECT    | TABLE      | SALES_DB.AGGREGATES_EULA.AGG_SECURE  | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |
+-------------------------------+-----------+------------+--------------------------------------+------------+----------------+--------------+--------------+

Adding Accounts to a Share

You can add accounts to an existing share at any time using the ALTER SHARE command. Once an account is added to the share, the share is immediately “visible” to the account and the account can create a database from the share and start querying the tables and secure views in the database.

Removing Objects from a Share

You can remove objects from an existing share at any time using the REVOKE <privilege> … FROM SHARE command. Any objects that you remove from a share are instantly unavailable to the consumers accounts who have created databases from the share. For example, if you remove a table from a share, users in consumer accounts can no longer query the data in the table as soon as the table is removed from the share.

The following example removes the secure view named agg_secure in the aggregates_eula schema from the sales_s share:

SHOW GRANTS TO SHARE sales_s;

+-------------------------------+-----------+------------+--------------------------------------+------------+----------------+--------------+--------------+
| created_on                    | privilege | granted_on | name                                 | granted_to | grantee_name   | grant_option | granted_by   |
|-------------------------------+-----------+------------+--------------------------------------+------------+----------------+--------------+--------------|
| 2017-06-15 16:45:07.307 -0700 | USAGE     | DATABASE   | SALES_DB                             | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |
| 2017-06-15 16:45:10.310 -0700 | USAGE     | SCHEMA     | SALES_DB.AGGREGATES_EULA             | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |
| 2017-06-15 16:45:12.312 -0700 | SELECT    | TABLE      | SALES_DB.AGGREGATES_EULA.AGGREGATE_1 | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |
| 2017-06-17 12:33:15.310 -0700 | SELECT    | TABLE      | SALES_DB.AGGREGATES_EULA.AGG_SECURE  | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |
+-------------------------------+-----------+------------+--------------------------------------+------------+----------------+--------------+--------------+

REVOKE SELECT ON VIEW sales_db.aggregates_eula.agg_secure FROM SHARE sales_s;

+-------------------------------+-----------+------------+--------------------------------------+------------+----------------+--------------+--------------+
| created_on                    | privilege | granted_on | name                                 | granted_to | grantee_name   | grant_option | granted_by   |
|-------------------------------+-----------+------------+--------------------------------------+------------+----------------+--------------+--------------|
| 2017-06-15 16:45:07.307 -0700 | USAGE     | DATABASE   | SALES_DB                             | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |
| 2017-06-15 16:45:10.310 -0700 | USAGE     | SCHEMA     | SALES_DB.AGGREGATES_EULA             | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |
| 2017-06-15 16:45:12.312 -0700 | SELECT    | TABLE      | SALES_DB.AGGREGATES_EULA.AGGREGATE_1 | SHARE      | PRVDR1.SALES_S | false        | ACCOUNTADMIN |
+-------------------------------+-----------+------------+--------------------------------------+------------+----------------+--------------+--------------+

Removing Accounts from a Share

You can remove accounts from an existing share at any time using the ALTER SHARE command. Removing an account from a share instantly invalidates the database they created from the share. All queries and other operations that users in the account perform on the database will no longer work.

You remove an account from a share by setting a new list of accounts for the share and leaving the desired account off the list.

After removing an account from a share, you can add it back again to the share; however, this does not restore the database they created earlier from the share. They must create a new database from the share.

Note

Before removing an account from a share, consider the downstream impact it will have on the account. Because the database is instantly invalidated, all queries and other operations that users (in the account) perform on the database will stop working, which could have a significant impact on the business operations of the account.

Dropping a Share

You can drop a share at any time using the DROP SHARE command. Dropping a share instantly invalidates all databases created from the share by consumer accounts. All queries and other operations performed on these databases will no longer work.

After dropping a share, you can recreate it with the same name; however, this does not restore any of the databases created from the share by consumer accounts. The recreated share is treated as a new share and all consumer accounts must create a new database from the new share.

Note

Before dropping a share, consider the downstream impact it will have on all consumer accounts using the share.

Instead, you might want to consider removing individual objects from the share. Removed objects can be added back to a share without requiring any additional tasks on the part of the consumer accounts.