Getting Started with Data Sharing

Data Sharing is an extremely powerful, yet easy-to-use feature. You can get started as a data provider or consumer in just a few simple steps. This topic describes how to share a database and its objects with one or more accounts. It also describes how to consume a shared database for use in queries.

In this Topic:

Note

To perform the tasks described in this topic, either as a provider or consumer, you must use the ACCOUNTADMIN role.

Also, this topic describes the minimum tasks required for providers and consumers to get starting using Data Sharing. For more detailed information, see:

Providing a Shared Database

As a data provider, all you need to do to share a database is:

  1. Use CREATE SHARE to create a share.

  2. Use GRANT privilege to grant the following privileges to the share:

    • USAGE privilege on the database you wish to share.
    • USAGE privilege on each database schema containing the objects you wish to share.
    • SELECT privilege for sharing specific objects (tables and secure views) in each shared schema.

    Optionally use SHOW GRANTS to view the object grants for the share.

    Tip

    We recommend doing this minimal amount of validation of the share before completing the next step because, once the next step is completed, the share is “visible” to all the accounts that are added to the share.

    If you wish to perform a more in-depth validation of the share, Snowflake provides a mechanism for simulating a consumer account in your account. For more details, see Using Secure Views to Control Access to Shared Data.

  3. Use ALTER SHARE to add one or more accounts to the share.

    To review the accounts added to the share, use SHOW GRANTS.

That’s it! The share is now ready to be consumed by the specified accounts.

For more detailed instructions for performing these and other data provider tasks, see Providers — Sharing Databases.

Provider Example

The following example illustrates the entire provider process as described above.

Note that this example assumes an existing database named sales_db that contains a schema named aggregates_eula with a table named aggregate_1:

USE ROLE accountadmin;

CREATE SHARE sales_s;

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;

SHOW GRANTS TO SHARE sales_s;

ALTER SHARE sales_s ADD ACCOUNTS=consumer_account1, consumer_account2;

SHOW GRANTS OF SHARE sales_s;

Consuming a Shared Database

As a data consumer, all you need to do to consume a shared database is:

  1. Use the SHOW SHARES command to view the shares that have been made available to you.
  2. Use the CREATE DATABASE command to create a database from one of the shares.
  3. Use the GRANT privilege command with the IMPORTED PRIVILEGES keyword to grant access, as needed/desired, on the database you create to other roles. This allows users with those roles to use/query the database.

That’s it! Once you’ve completed these tasks, you and your users can perform queries on the tables and secure views in the database just as you would any other database in your account.

For more detailed instructions for performing these and other data consumer tasks, see Consumers — Using Shared Databases.

Consumer Example

The following example illustrates the entire consumer process as described above.

Note that this example assumes an existing share named sales_s and a role named finance_dept:

USE ROLE accountadmin;

SHOW SHARES;

CREATE DATABASE snow_sales FROM SHARE snowflake.sales_s;

GRANT IMPORTED PRIVILEGES ON DATABASE snow_sales TO ROLE finance_dept;

USE ROLE finance_dept;

SELECT * FROM snow_sales.aggregate_1;