Sharing data from multiple databases

Snowflake data providers can share data that resides in different databases by using secure views. A secure view can reference objects such as schemas, tables, and other views from one or more databases, as long as these databases belong to the same account.

The process of sharing a secure view that references objects from multiple databases is different from sharing data residing in a single database.

In addition to performing all the standard steps to share data, you must grant the REFERENCE_USAGE privilege on each database referenced by a secure view you wish to share. However, you do not need to grant REFERENCE_USAGE on the database where the secure view is created.

Important

The REFERENCE_USAGE privilege cannot be granted to a database role to include objects from multiple databases in a share. This limitation also applies to objects such as secure views that have one or more underlying tables in a separate database.

To include multiple databases in a share, you must grant privileges on the objects directly to the share. For more information on this option, see Option 2: Granting privileges directly to a share.

You must grant the privilege separately on each database referenced in a view, before adding the view to a share (i.e. granting the SELECT privilege on the view to a share).

To share a secure view that references objects from multiple databases:

  1. Connect to your Snowflake account as a user with the ACCOUNTADMIN role or a role granted the CREATE SHARE global privilege. For more details about the CREATE SHARE privilege, see Enabling non-ACCOUNTADMIN roles to perform data sharing tasks.

  2. Create a share using CREATE SHARE.

  3. Grant the USAGE privilege on the database you wish to share using GRANT <privilege> … TO SHARE.

    Note

    If you are sharing a secure view that references objects belonging to multiple databases, you need to grant the USAGE privilege only to the database where the secure view is created. You can only grant USAGE to one database per share.

  4. Grant the USAGE privilege on each schema in the database you wish to share using GRANT <privilege> … TO SHARE.

  5. Grant the REFERENCE_USAGE privilege on each additional database that contains objects referenced by the view you wish to share using GRANT <privilege> … TO SHARE.

  6. Grant the SELECT privilege on the view you wish to share using GRANT <privilege> … TO SHARE.

  7. Add one or more consumer accounts to the share using ALTER SHARE.

The share is now ready to be consumed by the specified accounts.

Note

To share a secure view that references a UDF in a different database, you must make the UDF secure. For more details about creating a secure UDF, see Creating a Secure UDF or Stored Procedure.

Examples

Refer to the following examples for creating secure views.

Example 1: Create and share a secure view in an existing database

A provider who organizes data into different databases based on the characteristics of data and business needs wants to share a secure view in one database that joins data in the database with objects (e.g. schema, table, view) in other databases.

Relationship between databases, database objects, shares, and accounts
  1. Create database database1 and data:

    CREATE DATABASE database1;
    CREATE SCHEMA database1.sch;
    CREATE TABLE database1.sch.table1 (id INT);
    CREATE VIEW database1.sch.view1 AS SELECT * FROM database1.sch.table1;
    
    Copy
  2. Create database database2 and data:

    CREATE DATABASE database2;
    CREATE SCHEMA database2.sch;
    CREATE TABLE database2.sch.table2 (id INT);
    
    Copy
  3. Create database database3 and data:

    CREATE DATABASE database3;
    CREATE SCHEMA database3.sch;
    CREATE TABLE database3.sch.table3 (id INT);
    
    Copy
  4. Create the secure view with the data to be shared in database3:

    CREATE SECURE VIEW database3.sch.view3 AS
      SELECT view1.id AS View1Id,
             table2.id AS table2id,
             table3.id AS table3id
      FROM database1.sch.view1 view1,
           database2.sch.table2 table2,
           database3.sch.table3 table3;
    
    Copy
  5. Create the share and grant all required privileges.

    The data referenced in additional databases by secure view view3 requires granting the REFERENCE_USAGE privilege on database1 and database2 to the share:

    CREATE SHARE share1;
    GRANT USAGE ON DATABASE database3 TO SHARE share1;
    GRANT USAGE ON SCHEMA database3.sch TO SHARE share1;
    
    GRANT REFERENCE_USAGE ON DATABASE database1 TO SHARE share1;
    GRANT REFERENCE_USAGE ON DATABASE database2 TO SHARE share1;
    
    GRANT SELECT ON VIEW database3.sch.view3 TO SHARE share1;
    
    Copy

You can share this data with consumers in other regions by using a replication group to replicate data to an account in another region. For instructions, see Example 3: Share data from multiple databases.

Example 2: Create and share a secure view in a separate database

A provider stores customer data in separate databases and does not want to create new objects in those databases. To share data, the provider creates a new database with a secure view. The secure view references objects (schema, table, view) in the databases with customer data.

Relationship between databases, database objects, shares, and accounts

Sample Code:

  1. Create the customer database customer1_db and data:

    CREATE DATABASE customer1_db;
    CREATE SCHEMA customer1_db.sch;
    CREATE TABLE customer1_db.sch.table1 (id INT);
    CREATE VIEW customer1_db.sch.view1 AS SELECT * FROM customer1_db.sch.table1;
    
    Copy
  2. Create the customer database customer2_db and data:

    CREATE DATABASE customer2_db;
    CREATE SCHEMA customer2_db.sch;
    CREATE TABLE customer2_db.sch.table2 (id INT);
    
    Copy
  3. Create the new database new_db and schema sch:

    CREATE DATABASE new_db;
    CREATE SCHEMA new_db.sch;
    
    Copy
  4. Create the secure view in new_db that references objects in customer1_db and customer2_db:

    CREATE SECURE VIEW new_db.sch.view3 AS
      SELECT view1.id AS view1Id,
             table2.id AS table2ID
      FROM customer1_db.sch.view1 view1,
           customer2_db.sch.table2 table2;
    
    Copy
  5. Create the share and grant all required privileges.

    The data referenced in additional databases by secure view view3 requires granting the REFERENCE_USAGE privilege on customer1_db and customer2_db to the share:

    CREATE SHARE share1;
    
    GRANT USAGE ON DATABASE new_db TO SHARE share1;
    GRANT USAGE ON SCHEMA new_db.sch TO SHARE share1;
    
    GRANT REFERENCE_USAGE ON DATABASE customer1_db TO SHARE share1;
    GRANT REFERENCE_USAGE ON DATABASE customer2_db TO SHARE share1;
    
    GRANT SELECT ON VIEW new_db.sch.view3 TO SHARE share1;
    
    Copy

Sharing data from multiple database with consumers in other regions

You can share data from multiple databases with consumer accounts in other regions and cloud platforms by using a replication group. Include the share and each database the share references in the group to replicate data to a Snowflake account in another region. You can then add consumer accounts to the replicated share. For detailed instructions, see Sharing data securely across regions and cloud platforms.