Viswamitra's blog

Snowflake has an interesting feature – Secure data sharing, which share the data(tables) with other members(clients). Secure data sharing enables users to share the data between account to account. The provider of data shares the data with consumer using shares.

Example: Snowflake by default share the account usage information and sample databases with all snowflake accounts.

NOTE: Database objects shared to other accounts are read only (they cannot modify or delete the objects). Also sharing is possible only if the consumer is in same region.

How secure data sharing works

With secure data sharing, no actual data is copied or transferred between accounts. All sharing is done by the services layer of the snowflake. The data sharing does not consume any storage at consumer side so consumer do not need to pay for storage he need pau only for the virtual warehouses.

What is share ?

Share is a snowflake database object which has information about the objects shared and with which accounts the objects are shared.

What is Reader Account ?

Sometimes the clients with whom we want to share the data might not have a snowflake account. To share the data with such customers we will use the reader account. The provider creates the reader account, share the database objects to reader account same as full snowflake account using shares.

But the reader account can consume objects only from the provider who created it. To create a reader account, user should have granted the “CREATE ACCOUNT” permissions.

use role accountadmin;

create managed account viswamitra_reader admin_name = viswamitra, admin_password = 'Sdfed43da!44', type = reader;

status
{"accountName":"DCA80445","loginUrl":"https://dca80445.us-east-1.snowflakecomputing.com"}

NOTES:

• By default, the total number of reader accounts a provider can create is 20. If you reach the limit and require creating additional accounts with snowflake support.
• The reader account utilizes the same Snowflake Edition as the provider account and is created in the same region.

How to create share ?

To share the database objects with other accounts you need accountadmin privileges, else the user should have “CREATE SHARE” global permissions which makes managing shares more flexible.

NOTE:

Granting CREATE SHARE to other roles makes managing shares more flexible, but also allows users with these roles to expose any objects they own (or on which they have the necessary privileges) to other accounts. This is particularly important to note if you are sharing data from an account that contains sensitive or proprietary data.

grant create share on account to employee_owner;
create share client_share;

How to grant access on tables to share ?

To grant access of objects to a share user should have

• OWNERSHIP of the share, and
• OWNERSHIP or USAGE/SELECT WITH GRANT OPTION on each of the objects to be granted/revoked

Grant usage on database employee to share client_share;
Grant usage on schema emp_schema to share client_share;
Grant select on table emp_schema.employee to share client_share;
---- to share all objects in a schema
Grant select on all tables in schema emp_schema to share client_share;

Grant access on share to client account

alter share client_share add accounts=era83997;

How to share tables of multiple databases ?

We can share one database using a share if we want to share objects from multiple databases, we need to use the secure views. The secure view can refer the objects from different databases.

To grant the permission on the secure view to the share grant the “reference_usage” permission on the databases from which the view referring the objects.

grant reference_usage on database employee to share client_share;
grant select on view secure_data to share client_share;

Before we actually share the data with other accounts we can do verification the data shared with them using “simulated_data_sharing_consumer” parameter.

alter session set simulated_data_sharing_consumer = xy12345;

How to access shared data ?

To access the shares shared within an account the user should have “import share” privileges. With this privilege he can see both inbound and outbound shares and create database from inbound share.

Create database from the share (inbound) given by provider.

CREATE DATABASE <name> FROM SHARE <provider_account>.<share_name>;
CREATE DATABASE client_data FROM SHARE xy23456.client_share;

user who created the data from share will be the owner of the database and he can grant the permissions to other users.