Sharing data from multiple databases¶

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.

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.

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