Snowflake Secure Data Sharing enables users to provide specific data points to consumers using the same cloud provider within the same region. Providers can share tables, external tables, secure views, secure materialized views or secure user defined functions with consumers. Data sharing is customizable, and in this post I will do my best to cover all of the pieces to build a Direct Share. So, without further ado, let’s begin.

In essence, data shares are only possible if providers would like to share data with consumers. The architecture of shares empowers providers to select which database objects they would like to make available to consumers on a read-only basis through Direct Shares, the Snowflake Marketplace or the Data Exchange. In this example, we will cover Direct Shares that are configured using role-based access control (RBAC) that is offered by Snowflake for all objects within the system.

Since Direct Shares leverage RBAC, providers have the ability to share data with multiple consumers, and consumers have the ability to access data from multiple providers. This is accomplished by granting privileges on objects to a share via a database role. Configuring these privileges is important because providers only want consumers to have access to specific data. If the consumer does have access to sensitive data, then the provider has made a mistake in the configuration of their Direct Share.

While consumers do have access to provider’s data, it is important to note that this data is not stored in the consumer’s account, so the onus of storage costs falls on the provider. This is accomplished through Snowflake’s services layer and metadata store to ensure there is no physical data copied or transferred between accounts. The only charges that are incurred by the consumer are for compute resources that are used to query the shared data.

One last piece I’d like to cover before getting into the setup guide is in regards to third-party accounts. There are going to be situations where, as a provider, you’d like to share data with someone who does not have a Snowflake account. Thankfully, Snowflake thought of this possibility and created the reader account. Reader accounts belong to the provider account that created it, and can only consume data from the provider account that created it. In this solution, the cost of storage and compute both fall on the provider.

Creating the Share

In order to create a Direct Share, the intended provider will login to their Snowflake instance using credentials that have access to use the ACCOUNTADMIN role. By default, the ACCOUNTADMIN role is the only one that has the privileges to create, alter and drop a data share. Once logged into your Snowflake instance, copy/paste the line of code below to set ACCOUNTADMIN as the role for your current session:

USE ROLE ACCOUNTADMIN;

CREATE SHARE <share_name>;

To make sure your outbound share was created, click through the following path to make sure it is there.

• Select Data in the left side pane:

• Select Private Sharing in the left side pane:

• Select Shared By By Account and check to see your <share_name> is listed in the table:

Once you’ve confirmed that your outbound share is created, it’s time to start adding some privileges to the share. In this example, I will walk you through how to add select privileges for a secure view to a share, and how to check which objects are referenced in that secure view.

Adding Privileges to the Share

The first thing you will want to do is check to see what objects your secure view references. To do this you will utilize Snowflake’s GET_OBJECT_REFERENCES table function. Copy/paste the code below and replace database/schema/object names for your applicable situation:

SELECT * FROM TABLE(get_object_references(database_name=>'<database_name>', schema_name=>'<schema_name>', object_name=>'<object_name>'));

Now that we have the object references, it is time to grant usage privileges on the database and schema that are housing your secure view, reference usage privileges on any referenced objects and select privileges on the desired object(s). Copy/paste the lines of code below into your worksheet accomplish each of these and be sure to alter pieces where applicable to your given situation:

GRANT USAGE ON DATABASE <database_name> TO SHARE <share_name>;
GRANT USAGE ON SCHEMA <database_name>.<schema_name> TO SHARE <share_name>;
REFERENCE_USAGE ON DATABASE <referenced_database_name> TO SHARE <share_name>;
GRANT SELECT ON VIEW <database_name>.<schema_name>.<secure_view_1> TO SHARE <share_name>;
GRANT SELECT ON VIEW <database_name>.<schema_name>.<secure_view_2> TO SHARE <share_name>;
GRANT SELECT ON VIEW <database_name>.<schema_name>.<secure_view_n> TO SHARE <share_name>;

SHOW GRANTS TO SHARE <share_name>;

After confirming that the share is configured as intended, it is time to setup a reader account (if applicable) and add accounts to the share. If you do not need to set up a reader account for your data share, then skip ahead to adding accounts to a data share.

Setting up the Reader Account

To create a managed account to share data with, run the code below. Be sure to make a note of the <admin_name> and <admin_password> you input for the next step.

CREATE MANAGED ACCOUNT <reader_account_name>
    ADMIN_NAME = <admin_name>, ADMIN_PASSWORD = '<admin_password>',
    TYPE = READER;

The reader account has been created and you have the admin credentials to login. However, before you go ahead and login to check that the data share is accessible by the reader account, you will need to add this new reader account to the data share. In order to accomplish this, your first step is to click on Data and then Private Sharing in the left side pane once again:

Once inside of the Private Sharing page, select the Reader Accounts tab and make note of the Account Locator:

Adding an Account to the Share

NOTE -> Obtain the account locator for the consumer account. This page will give you a more in-depth look on how to find an account identifier.

With your account locator noted, navigate back to your Snowflake worksheet and copy/paste the line of code below to add your reader account to the share:

ALTER SHARE <share_name> ADD ACCOUNTS = <account_locator>;

You can audit this alteration by navigating back to the Shared By My Account menu and checking to see if your desired account is listed under the SHARED_WITH column.

Creating a Database with Shared Data

To check that the reader account has access to the desired objects, navigate back to the Account page and click on the Account URL. Here you will be prompted to log in using the admin credentials you setup earlier. Once you input those credentials and log into the reader account, it is time for the final step of creating a database with the inbound share.

First, you will navigate back to the Data section, but this time you will remain on Shared With Me. On this page you will click on the share you created:

Once you click into the Direct Share, make a note of the Source Details. Specifically you will be making note of the Share and Shared by:

Navigate back to the Worksheets tab and input the following line of code to create a database from your inbound data share:

CREATE DATABASE <database_name> FROM SHARE <provider_name>.<share_name>;

NOTE 1: You will replace <provider_name> with the value for Shared By, and <share_name> with the value for Share.

NOTE 2: A share can only be consumed once per account, so if you try to create multiple databases from the same share you will receive and error.

And there it is! You have successfully created a database from an inbound share that you configured using a provider account. If you made any mistakes, or would like to make updates to your share in the future, then you always have the ability to alter your share after the fact. If this is a solution you’d like to learn more about, or believe your business could benefit from, then please feel free to reach out to us here at InterWorks so we can help.