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:
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.
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.
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.
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.