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 a provider can share a database and
its objects with one or more accounts. It also describes how a consumer can access 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 only the minimum set of tasks required for providers and consumers to start using Data Sharing. For more detailed information, including more detailed data sharing
scenarios, see:
Providing a Shared Database
As a data provider, all you need to do to share a database is:
Use CREATE SHARE to create a share.
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
Providers — Using Secure Views to Control Access to Shared Data.
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 Data with Consumers.
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.
- The database, schema, and table will be shared with two accounts named
consumer_account1 and consumer_acount2.
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:
- Use the SHOW SHARES command to view the shares that have been made available to you.
- Use the CREATE DATABASE command to create a database from one of the shares.
- 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 shared objects (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 Data.
Consumer Example
The following example illustrates the entire consumer process as described above.
Note that this example assumes:
- A share named
sales_s is available in your account.
- A role named
finance_dept exists in your account.
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;
New Feature