Consumers — Using Shared Databases

This topic describes the tasks associated with creating databases from shares made available by data providers and then using the databases for queries and other operations.

In this Topic:

Note

You must use the ACCOUNTADMIN role to perform these tasks.

Usage Notes for Shared Databases

Shared databases have the following limitations for consumers:

  • Shared databases are read-only. Users in a consumer account can view/query data, but cannot insert or update data, or create any objects in the database.

  • The following features are not supported:

    • Creating a clone of a shared database or any schemas/tables in the database.
    • Time Travel for a shared database or any schemas/tables in the database.
  • Shared databases and all the objects in the database cannot be forwarded (i.e. re-shared with other accounts). If you are acting as both a data provider and consumer, you can only grant database objects explicitly owned by your account to any shares that you create; you cannot grant databases/objects shared by other providers.

Viewing Available Shares

The following example shows the shares that are available to consume in your account:

SHOW SHARES;

+---------------------------------+---------+-------------------------+-----------------------+----+-------+---------------------------------------------+
| created_on                      | kind    | name                    | database_name         | to | owner | comment                                     |
|---------------------------------+---------+-------------------------+-----------------------+----+-------+---------------------------------------------|
| Thu, 15 Jun 2017 17:02:29 -0700 | INBOUND | PRVDR1.SALES_S          |                       |    |       |                                             |
| Thu, 15 Jun 2017 17:32:44 -0700 | INBOUND | PRVDR1.SALES_S2         |                       |    |       |                                             |
| Sat, 09 Jul 2016 19:18:09 -0700 | INBOUND | SFC_SAMPLES.SAMPLE_DATA | SNOWFLAKE_SAMPLE_DATA |    |       | Sample data sets provided by Snowflake      |
+---------------------------------+---------+-------------------------+-----------------------+----+-------+---------------------------------------------+

The output shows:

  • Three shares are available. A value of INBOUND in the kind column specifies that a data provider included your account in the share.
  • The name column displays the fully-qualified name of each share, in the form of <provider_account>.<share_name>, e.g. prvdr1.sales_s.
  • If the database_name column is empty, this indicates that a database has not been created yet in your account for the share.

The next example show the objects (database, schemas, and tables) that are being shared in the prvdr1.sales_s share:

DESC SHARE prvdr1.sales_s;

+----------+----------------------------------+---------------------------------+
| kind     | name                             | shared_on                       |
|----------+----------------------------------+---------------------------------|
| DATABASE | <DB>                             | Thu, 15 Jun 2017 17:03:16 -0700 |
| SCHEMA   | <DB>.AGGREGATES_EULA             | Thu, 15 Jun 2017 17:03:16 -0700 |
| TABLE    | <DB>.AGGREGATES_EULA.AGGREGATE_1 | Thu, 15 Jun 2017 17:03:16 -0700 |
+----------+----------------------------------+---------------------------------+

The share consists of one schema, aggregates_eula , and one table, aggregate_1, in the schema. Note that each object name is prefixed with <DB>, indicating a database has not yet been created for the share.

Creating a Database from a Share

To use a shared database, simply use the CREATE DATABASE command with the following Data Sharing-specific syntax to create a new database from the corresponding share:

CREATE DATABASE <name> FROM SHARE <provider_account>.<share_name>

Where provider_account is the name of the account that provided the share and share_name is the name of the share from which to create the database.

Note

  • A share can only be consumed once per account.
  • To see the objects that are being shared before creating a database, use the DESC SHARE command.
  • After creating a database from a share, to grant other roles access to the database, use the GRANT privilege command with the IMPORTED PRIVILEGES keyword (see Granting Privileges for a Shared Database in this topic).

Examples

The following example creates a new database named snow_sales in your account from the prvdr1.sales_s share:

CREATE DATABASE snow_sales FROM SHARE prvdr1.sales_s;

List the new snow_sales database:

SHOW DATABASES LIKE 'snow%';

+---------------------------------+-----------------------+------------+------------+-------------------------+--------------+---------+---------+----------------+
| created_on                      | name                  | is_default | is_current | origin                  | owner        | comment | options | retention_time |
|---------------------------------+-----------------------+------------+------------+-------------------------+--------------+---------+---------+----------------|
| Sun, 10 Jul 2016 23:28:50 -0700 | SNOWFLAKE_SAMPLE_DATA | N          | N          | SFC_SAMPLES.SAMPLE_DATA | ACCOUNTADMIN |         |         | 1              |
| Thu, 15 Jun 2017 18:30:08 -0700 | SNOW_SALES            | N          | Y          | PRVDR1.SALES_S          | ACCOUNTADMIN |         |         | 1              |
+---------------------------------+-----------------------+------------+------------+-------------------------+--------------+---------+---------+----------------+

In this example, the origin column indicates the fully-qualified name of the share from which the database was created.

Similarly, the output of SHOW SHARES and DESC SHARE now includes the name of the database that was created from the share:

SHOW SHARES;

+---------------------------------+---------+-------------------------+-----------------------+----+-------+---------------------------------------------+
| created_on                      | kind    | name                    | database_name         | to | owner | comment                                     |
|---------------------------------+---------+-------------------------+-----------------------+----+-------+---------------------------------------------|
| Thu, 15 Jun 2017 17:02:29 -0700 | INBOUND | PRVDR1.SALES_S          | SNOW_SALES            |    |       |                                             |
| Thu, 15 Jun 2017 17:32:44 -0700 | INBOUND | PRVDR1.SALES_S2         |                       |    |       |                                             |
| Sat, 09 Jul 2016 19:18:09 -0700 | INBOUND | SFC_SAMPLES.SAMPLE_DATA | SNOWFLAKE_SAMPLE_DATA |    |       | Sample data sets provided by Snowflake      |
+---------------------------------+---------+-------------------------+-----------------------+----+-------+---------------------------------------------+

DESC SHARE prvdr1.sales_s;

+----------+----------------------------------------+---------------------------------+
| kind     | name                                   | shared_on                       |
|----------+----------------------------------------+---------------------------------|
| DATABASE | SNOW_SALES                             | Thu, 15 Jun 2017 17:03:16 -0700 |
| SCHEMA   | SNOW_SALES.AGGREGATES_EULA             | Thu, 15 Jun 2017 17:03:16 -0700 |
| TABLE    | SNOW_SALES.AGGREGATES_EULA.AGGREGATE_1 | Thu, 15 Jun 2017 17:03:16 -0700 |
+----------+----------------------------------------+---------------------------------+

Granting Privileges for a Shared Database

Only account administrators (i.e. users with the ACCOUNTADMIN role) can create databases from shares and, therefore, use the database initially.

To then grant access to the database to other roles, account administrators can use the GRANT privilege command. For example:

GRANT IMPORTED PRIVILEGES ON DATABASE snow_sales TO ROLE sysadmin;

USE ROLE sysadmin;

GRANT IMPORTED PRIVILEGES ON DATABASE snow_sales TO ROLE public;

This example illustrates how account administrators can grant all imported privileges on the database to the SYSADMIN role.

To see the roles that have USAGE privileges on a shared database, use the SHOW GRANTS command to view privileges on the database or the share from which the database was created:

SHOW GRANTS ON DATABASE snow_sales;

+---------------------------------+-----------+------------+------------+------------+---------------+--------------+---------------+
| created_on                      | privilege | granted_on | name       | granted_to | grantee_name  | grant_option | granted_by    |
|---------------------------------+-----------+------------+------------+------------+---------------+--------------+---------------|
| Thu, 15 Jun 2017 18:30:09 -0700 | OWNERSHIP | DATABASE   | SNOW_SALES | ROLE       | ACCOUNTADMIN  | true         | ACCOUNTADMIN  |
| Thu, 15 Jun 2017 18:36:46 -0700 | USAGE     | DATABASE   | SNOW_SALES | ROLE       | PUBLIC        | false        | SECURITYADMIN |
| Thu, 15 Jun 2017 18:30:09 -0700 | USAGE     | DATABASE   | SNOW_SALES | ROLE       | SECURITYADMIN | true         | ACCOUNTADMIN  |
+---------------------------------+-----------+------------+------------+------------+---------------+--------------+---------------+

SHOW GRANTS ON SCHEMA snow_sales.aggregates_eula;

+---------------------------------+-----------+------------+----------------------------+------------+---------------+--------------+---------------+
| created_on                      | privilege | granted_on | name                       | granted_to | grantee_name  | grant_option | granted_by    |
|---------------------------------+-----------+------------+----------------------------+------------+---------------+--------------+---------------|
| Thu, 15 Jun 2017 16:41:40 -0700 | OWNERSHIP | SCHEMA     | SNOW_SALES.AGGREGATES_EULA |            |               | true         |               |
| Thu, 15 Jun 2017 18:30:09 -0700 | USAGE     | SCHEMA     | SNOW_SALES.AGGREGATES_EULA | ROLE       | ACCOUNTADMIN  | false        | ACCOUNTADMIN  |
| Thu, 15 Jun 2017 18:36:46 -0700 | USAGE     | SCHEMA     | SNOW_SALES.AGGREGATES_EULA | ROLE       | PUBLIC        | false        | SECURITYADMIN |
| Thu, 15 Jun 2017 18:30:09 -0700 | USAGE     | SCHEMA     | SNOW_SALES.AGGREGATES_EULA | ROLE       | SECURITYADMIN | true         | ACCOUNTADMIN  |
+---------------------------------+-----------+------------+----------------------------+------------+---------------+--------------+---------------+

SHOW GRANTS OF SHARE prvdr1.sales_s;

+---------------------------------+-------+------------+---------------+---------------+
| created_on                      | share | granted_to | grantee_name  | granted_by    |
|---------------------------------+-------+------------+---------------+---------------|
| Thu, 15 Jun 2017 18:30:09 -0700 |       | ROLE       | ACCOUNTADMIN  | ACCOUNTADMIN  |
| Thu, 15 Jun 2017 18:36:46 -0700 |       | ROLE       | PUBLIC        | SECURITYADMIN |
| Thu, 15 Jun 2017 18:36:46 -0700 |       | ROLE       | SECURITYADMIN | ACCOUNTADMIN  |
+---------------------------------+-------+------------+---------------+---------------+

Querying a Shared Database

Querying a shared database is the same as querying any other database. For example:

USE ROLE public;

USE DATABASE snow_sales;

SELECT * FROM aggregates_1;