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 slightly 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. Note that 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 SELECT on the view to a share).

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

Connect to your Snowflake account as a user with the ACCOUNTADMIN role or a role granted the CREATE SHARES global privilege. For more details about the CREATE SHARES privilege, see Enabling non-ACCOUNTADMIN Roles to Perform Data Sharing Tasks.
Create a share using CREATE SHARE.
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.
Grant the USAGE privilege on each schema in the database you wish to share using GRANT <privilege> … TO SHARE.
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.
Grant the SELECT privilege on the view you wish to share using GRANT <privilege> … TO SHARE.
Add one or more consumer accounts to the share using ALTER SHARE.

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

Examples¶

Example 1¶

A provider who organized 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 that database with objects (e.g. schema, table, view) from other databases.

Relationship between databases, database objects, shares, and accounts

Sample code:

-- Sample database database1
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;

-- Sample database database2
CREATE DATABASE database2;
CREATE SCHEMA database2.sch;
CREATE TABLE database2.sch.table2 (id int);

-- Sample database to be shared
CREATE DATABASE database3;
CREATE SCHEMA database3.sch;

-- Create a table in the database to be shared.
CREATE TABLE database3.sch.table3 (id int);

-- Sample view to be shared.
-- This will require granting REFERENCE_USAGE on database1 and database2
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;

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;

Example 2¶

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.

Sample Code:

-- Sample database customer1DB
CREATE DATABASE customer1DB;
CREATE SCHEMA customer1DB.sch;
CREATE TABLE customer1DB.sch.table1 (id int);
CREATE VIEW customer1DB.sch.view1 AS SELECT * FROM customer1DB.sch.table1;

-- Sample database customer2DB
CREATE DATABASE customer2DB;
CREATE SCHEMA customer2DB.sch;
CREATE TABLE customer2DB.sch.table2 (id int);

-- Sample database to be shared
CREATE DATABASE newDB;
CREATE SCHEMA newDB.sch;
-- Sample view to be shared
-- This will require granting REFERENCE_USAGE on customer1DB and customer2DB
CREATE SECURE VIEW newDB.sch.view3 AS
SELECT view1.id AS view1Id, table2.id AS table2ID
FROM customer1DB.sch.view1 view1,
   customer2DB.sch.table2 table2;

CREATE SHARE share1;
GRANT USAGE ON DATABASE newDB TO SHARE share1;
GRANT USAGE ON SCHEMA newDB.sch TO SHARE share1;

GRANT REFERENCE_USAGE ON DATABASE customer1DB TO SHARE share1;
GRANT REFERENCE_USAGE ON DATABASE customer2DB TO SHARE share1;

GRANT SELECT ON VIEW newDB.sch.view3 TO SHARE share1;