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.
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;
Now that you are using the proper role, it is time to create a data share. This main consideration to make here is what to name your share. Similar to any object, it is best to name your share something that is representative of the object itself. To create a new data share, copy/paste the line of code below and replace <share_name> with your desired share name.
CREATE SHARE <share_name>;
To make sure your outbound share was created, click through the following path to make sure it is there.
- 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>'));
This query will return a table that includes the database, schema and object name you input plus the referenced database(s), schema(s) and object(s) names for your input object. Make note of each distinct referenced database name in this table as we will need to grant reference usage privileges to each of these databases for our data share.
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>;
At this point, your share should be configured and ready for distribution, but to be safe, let’s do a quick audit of the privileges granted to the share by running the line of code below:
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.
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:
NOTE 1: You will replace <provider_name> with the value for Shared By, and <share_name> with the value for Share.
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.