- Release Notes
- Getting Started
- Tutorials, Videos & Other Resources
- Introduction to Snowflake
- Connecting to Snowflake
- Managing Snowflake
- Using Snowflake
- Using the Web Interface
- Virtual Warehouses
- Databases, Tables & Views
- Data Loading
- Data Unloading
- Binary Data
- Dates & Timestamps
- Semi-structured Data
- Snowflake Data Sharing
- Snowflake Time Travel & Fail-safe
- Sample Data Sets
- General Reference
- SQL Command Reference
- Function Reference
Using Secure Views to Control Access to Shared Data¶
Data Sharing is not enabled by default. To request this feature, please contact Snowflake Support.
This topic provides step-by-step instructions for using secure views to ensure sensitive data in a shared database is not exposed to users in accounts that consume the database. It provides instructions for both data providers and consumers.
In this Topic:
The instructions in this topic require at least two accounts (one provider account and one or more consumer accounts) and users in each account with the SYSADMIN and ACCOUNTADMIN roles to perform the various tasks.
Data Provider Setup and Tasks¶
These instructions assume a database named
mydb exists in the data provider account and has two schemas,
public. If the database and schemas do not exist, you should create them
before proceeding with these instructions.
As a provider, perform the following tasks:
Create two tables in the
mydband populate them with data:
sensitive_data— contains the data to share, and an
access_idcolumn for controlling data access by account.
sharing_access— uses the
access_idto maps the shared data and the accounts that can access the data.
Create a secure view in the
paid_sensitive_data— displays data based on account.
Note that the
account_idcolumn from the base table (
sensitive_data) does not need to be included in the view.
Validate the tables and secure view to ensure the data is filtered properly by account.
To enable validating secure views that will be shared with other accounts, Snowflake provides a session parameter, SIMULATED_DATA_SHARING_CONSUMER. Set this session parameter to the name of the consumer account you wish to simulate access for. You can then query the view and see the results that a user in the consumer account will see.
Create a share using the ACCOUNTADMIN role.
Add privileges for the database (
mydb), schema (
public), and secure view (
paid_sensitive_data) to the share. These are the only objects added to the view, which ensures no users in the consumer account(s) can access the
privateschema or any of the tables in the schema.
Confirm the contents of the share. At the most basic level, you should use the SHOW GRANTS command to confirm the objects in the share have the necessary privileges.
Note that the secure view
paid_sensitive_datais displayed in the command output as a table.
Add one or more accounts to the share.
Data Provider Sample Script¶
The following script can be used to perform all the tasks described in the previous section:
/* Create two tables in the 'private' schema and populate the first one with stock data from three */ /* different companies (Apple, Microsoft, and IBM). You will then populate the second one with */ /* data that maps the stock data to individual accounts. */ use role sysadmin; create or replace table mydb.private.sensitive_data ( name string, date date, time time(9), bid_price float, ask_price float, bid_size int, ask_size int, access_id string /* granularity for access */ ); insert into mydb.private.sensitive_data values('AAPL',dateadd(day, -1,current_date()), '10:00:00', 116.5, 116.6, 10, 10, 'STOCK_GROUP_1'), ('AAPL',dateadd(month,-2,current_date()), '10:00:00', 116.5, 116.6, 10, 10, 'STOCK_GROUP_1'), ('MSFT',dateadd(day, -1,current_date()), '10:00:00', 58.0, 58.9, 20, 25, 'STOCK_GROUP_1'), ('MSFT',dateadd(month,-2,current_date()), '10:00:00', 58.0, 58.9, 20, 25, 'STOCK_GROUP_1'), ('IBM', dateadd(day, -1,current_date()), '11:00:00', 175.2, 175.4, 30, 15, 'STOCK_GROUP_2'), ('IBM', dateadd(month,-2,current_date()), '11:00:00', 175.2, 175.4, 30, 15, 'STOCK_GROUP_2'); create or replace table mydb.private.sharing_access ( access_id string, snowflake_account string ); /* In the first insert, CURRENT_ACCOUNT() gives your account access to the AAPL and MSFT data. */ insert into mydb.private.sharing_access values('STOCK_GROUP_1', CURRENT_ACCOUNT()); /* In the second insert, replace <consumer_account> with an account name; this account will have */ /* access to IBM data only. Note that account names are case-sensitive and must be enclosed in */ /* single-quotes, e.g. */ /* */ /* insert into into mydb.private.sharing_access values('STOCK_GROUP_2', 'ACCT1') */ /* */ /* To share the IBM data with multiple accounts, repeat the second insert for each account. */ insert into mydb.private.sharing_access values('STOCK_GROUP_2', '<consumer_account>'); /* Create a secure view in the 'public' schema. This view filters the stock data from the first */ /* table by account, using the mapping information in the second table. */ create or replace secure view mydb.public.paid_sensitive_data as select name, date, time, bid_price, ask_price, bid_size, ask_size from mydb.private.sensitive_data sd join mydb.private.sharing_access sa on sd.access_id = sa.access_id and sa.snowflake_account = current_account(); grant select on mydb.public.paid_sensitive_data to public; /* Test the table and secure view by first querying the data as the provider account. */ select count(*) from mydb.private.sensitive_data; select * from mydb.private.sensitive_data; select count(*) from mydb.public.paid_sensitive_data; select * from mydb.public.paid_sensitive_data; select * from mydb.public.paid_sensitive_data where name = 'AAPL';} /* Next, test the secure view by querying the data as a simulated consumer account. You specify the */ /* account to simulate using the SIMULATED_DATA_SHARING_CONSUMER session parameter. */ /* */ /* In the ALTER command, replace <consumer_account> with one of the accounts you specified in the */ /* mapping table. Note that the account name is not case-sensitive and does not need to be enclosed */ /* in single-quotes, e.g. */ /* */ /* alter session set simulated_data_sharing_consumer=acct1; */ alter session set simulated_data_sharing_consumer=<account_name>; select * from mydb.public.paid_sensitive_data; /* Create a share using the ACCOUNTADMIN role. */ use role accountadmin; create or replace share mydb_shared comment = 'Example of using Data Sharing with secure views'; show shares; /* Grant privileges on the database objects to include in the share. */ grant usage on database mydb to share mydb_shared; grant usage on schema mydb.public to share mydb_shared; grant select on mydb.public.paid_sensitive_data to share mydb_shared; /* Confirm the contents of the share. */ show grants to share mydb_shared; /* Add accounts to the share. */ /* */ /* In the alter statement, replace <consumer_accounts> with the */ /* consumer account(s) you assigned to STOCK_GROUP2 earlier, */ /* with each account name separated by commas, e.g. */ /* */ /* alter share mydb_shared set accounts = acct1, acct2; */ alter share mydb_shared set accounts = <consumer_accounts>;
Data Consumer Sample Script¶
The following script can be used by consumers to create a database from the share created in the above script and query the secure view in the resulting database:
/* Bring the shared database into your account by creating a database from the share. */ /* */ /* In the following commands, the share name must be fully qualified by replacing */ /* <provider_account> with the name of the account that provided the share, e.g. */ /* */ /* desc prvdr1.mydb_shared; */ use role accountadmin; show shares; desc <provider_account>.mydb_shared; create database mydb_shared1 from share <provider_account>.mydb_shared; /* Grant privileges on the database to other roles (e.g. SYSADMIN) in your account. */ grant imported privileges on database mydb_shared1 to sysadmin; /* Now you can use the SYSADMIN role to query the view in the database you created. */ /* */ /* Note that there must be a warehouse in use in the session to perform queries. In the */ /* USE WAREHOUSE command, replace <warehouse_name> with the name of one of the warehouses */ /* in your account. */ use role sysadmin; show views; use warehouse <warehouse_name>; select * from paid_sensitive_data;