Secure Data Sharing

It is necessary to understand that in the process of sharing there is no real copying of data. Therefore, the data consumer pays only for the computing service but does not pay for the storage of this data, since physically the data remains stored with the data provider. Since the information is not actually transferred, consumers get an instant update when the provider changes the data. A single data provider may have multiple data consumers, both within the company and with external consumers. Similarly, data consumers may have access to multiple providers, thereby forming a network of providers and consumers.

Let’s see how it works. The data sharing feature provides the ability to share database objects between Snowflake’s accounts within a region by using a specific share object. Such objects can be tables, secure views, and secure UDFs.³ The data provider creates a share object, and the data consumer uses this object for access.

Often there is a situation where you have a base table, and you need to organize access to only part of the records of this table. The best practice is to use secure views.

The data sharing feature in Snowflake works only between Snowflake accounts. If you want to grant access to the outside world, you will need to use a reader account.⁴

A provider account can create reader accounts for those consumers who are not customers of Snowflake. See Figure 10-1.

Secure Table Sharing

Let’s do an example.

Example with Sharing Table

Let’s look at how to share a table in practice:

1. 1.

   Log into your Snowflake account (the provider account).

    
2. 2.

   Switch to a worksheet and run the code in Listing 10-1. See Figure 10-2.

    

use role sysadmin;

create database samples;

create schema samples.finance;

create or replace table samples.finance.stocks_data (

    id int,

    symbol string,

    date date,

    time time(9),

    bid_price float,

       ask_price float,

       bid_cnt int,

       ask_cnt int

    );

insert into samples.finance.stocks_data

 values(1,'TDC',dateadd(day,  -1,current_date()), '10:15:00', 36.3, 36.0, 10, 10),

(2,'TDC', dateadd(month,-2,current_date()), '11:14:00', 36.5, 36.2, 10, 10),

(3,'ORCL', dateadd(day, -1,current_date()), '11:15:00',57.8, 59.9, 13, 13),

(4,'ORCL', dateadd(month,-2,current_date()), '09:11:00',57.3, 57.9, 12, 12),

(5,'TSLA', dateadd(day, -1,current_date()), '11:01:00', 255.2, 256.4, 22, 22),

(6,'TSLA', dateadd(month, -2,current_date()), '11:13:00', 255.2, 255.7, 23, 23);

select * from samples.finance.stocks_data;

Listing 10-1

Creating Sample Data

In Listing 10-1 we did the following:

• We created a new database called samples and a schema called samples.finance.

• We created a sample table called samples.finance.stocks_data and filled it with values.

• We created a share object and provided access to another account.

Now see Listing 10-2.

use role accountadmin;

create or replace share stocks_share;

show shares;

grant usage on database samples to share stocks_share;

grant usage on schema samples.finance to share stocks_share;

grant select on table samples.finance.stocks_data to share stocks_share;

  show grants to share stocks_share;

alter share stocks_share add accounts=<consumer_account>;

Listing 10-2

Creating a Share and Granting Permissions to a New Account

In Listing 10-2 we did the following:

• We created a shared object called stocks_share and a schema called samples.finance. You can see the metadata of the share object in Figure 10-3.

  

• We granted privileges by using the statement GRANT <privilege> .... TO SHARE on the database samples, the schema finance, or the concrete table stocks_share to the consumer account locator <consumer _account>.

• We checked privileges using SHOW GRANTS TO SHARE <share_name>. See Figure 10-4.

  

• We added a new account to a share using ALTER SHARE <share_name> ADD ACCOUNTS=<consumer _account>;.

1. 3.

   Log into your consumer account called <consumer_account>. Check access to the table via the consumer account.

    
2. 4.

   Switch to the Worksheets tab and execute SQL. See Figure 10-5.

   Now see Listing 10-3.

   use role accountadmin;

   show shares;

   desc share <consumer_account>.STOCKS_SHARE;

   Listing 10-3

   Showing the Available Share

   

    
3. 5.

   Let’s create a database based on the share. See Figure 10-6.

   create database shared_db from share <provider_account>.STOCKS_SHARE;

   

    
4. 6.

   Query the shared table. See Figure 10-7.

    

Data Sharing Using a Secure View

Let’s do an example.

Sharing a Table Using Secure View

Let’s look at how to provide access row-level sharing using a secure view.

1. 1.

   Log into your Snowflake account.

    
2. 2.

   Switch to a worksheet and execute the code in Listing 10-4.

    

use role sysadmin;

alter table samples.finance.stocks_data

  add column access_id string;

update finance.stocks_data

    set access_id = 'GRP_1'

where id in (1,2,3,4);

update finance.stocks_data

    set access_id = 'GRP_2'

where id in (5,6);

commit;

  select * from samples.finance.stocks_data;

Listing 10-4

Modifying the Table and Adding Values for Grouping Data

In Listing 10-4, we did the following:

• We changed the table from the previous example by adding a new column called access_id.

• We divided the stock data into two groups.

  • IT companies: GRP_1

  • Auto companies: GRP_2

    Figure 10-8 shows some summary data of the table.

1. 3.

   To provide public access based on a secure view, execute the code in Listing 10-5.

    

use role sysadmin;

create or replace table samples.finance.access_map (

  access_id string,

  account string

);

# add access to tech companies for my account

insert into samples.finance.access_map values('GRP_1', current_account());

# add access to tech companies for my account

insert into samples.finance.access_map values('GRP_2', '<consumer_account>');

commit;

select * from samples.finance.access_map;

Listing 10-5

Creating a Mapping Table

In Listing 10-5, we did the following:

• We created a mapping table called access_map;.

• We filled the table with values:

  • Group #1 of stocks for our account

  • Group #2 of stocks for <consumer_account>

1. 4.

   To provide public access based on a secure view, execute the code in Listing 10-6.

    

create or replace schema samples.public;

create or replace secure view samples.public.stocks as

    select sd.symbol, sd.date, sd.time, sd.bid_price, sd.ask_price, sd.bid_cnt, sd.ask_cnt

    from samples.finance.stocks_data sd

    join samples.finance.access_map  am on sd.access_id = am.access_id

     and am.account = current_account();

grant select on samples.public.stocks  to public;

Listing 10-6

Creating the Secure View on the Table

In Listing 10-6, we did the following:

• We created a new public schema.

• We created a secure view called samples.public.stocks; based on the table and the mapping table.

• We used the function current_account() for dynamically identifying the user account.

• We granted privileges to access the secure view.

1. 5.

   We tested the access to the table and the secure view.

   Now see Listing 10-7.

    

select count(*) from samples.finance.stocks_data;

select * from samples.finance.stocks_data;

select count(*) from samples.public.stocks;

select * from samples.public.stocks;

select * from samples.public.stocks

where symbol = 'TDC';

Listing 10-7

Checking Access to Tables

1. 6.

   Test the access to the table and secure view by using the session parameter simulated_data_sharing_consumer. See Figure 10-9.

   Now see Listing 10-8.

    

alter session set simulated_data_sharing_consumer=<consumer_name>;

select * from samples.public.stocks;

Listing 10-8

Checking Access to the Table Using a Session Parameter

1. 7.

   Create a share object, add the secure view to the share, and grant privileges.

   Now see Listing 10-9.

    

alter session set simulated_data_sharing_consumer='<provider_account>';

use role accountadmin;

create or replace share share_sv;

grant usage on database samples to share share_sv;

grant usage on schema samples.public to share share_sv;

grant select on samples.public.stocks to share share_sv;

show grants to share share_sv;

alter share share_sv set accounts = <consumer_accounts>;

show shares;

Listing 10-9

Adding the Secure View in the Share Object and Grant Privileges

In Listing 10-9, we did the following:

• We turned back to the session of the producer account.

• We created a new share object called share_sv.

• We added the secure view to the share.

• We granted privileges to access the secure view for the consumer account.

1. 8.

   Execute the script in Listing 10-10 on the consumer side. See Figure 10-10.

    

use role accountadmin;

show shares;

create database shared_views_db from share <provider_account>.share_sv;

grant imported privileges on database shared_views_db to sysadmin;

use role sysadmin;

show views;

use warehouse <warehouse_name>;

select * from stocks;

Listing 10-10

Consumer’s Script

In Listing 10-10, we did the following:

• We created a database from the share object called share_sv.

• We granted imported privileges from the share object to the sysadmin user.

• We got access to the secure view called stocks.

Summary

In this chapter, we covered the Snowflake data sharing feature that provides an easy, fast, and secure way to distribute data. Moreover, you learned about share objects and considered several basic options for using these features.

Finally, we walked through two examples: a simple way to share a table and an advanced way to share one by using a secure view.

In the next chapter, you will learn about how to design modern analytical solutions based on Snowflake services.