To ensure sensitive data in a shared database is not exposed to users in consumer accounts, Snowflake strongly recommends sharing secure views instead of directly
sharing tables. In addition, for optimal performance, especially when sharing data in extremely large tables, we recommend defining clustering keys on the base
table(s) for your secure views.

This topic describes using clustering keys in base tables for shared secure views and also provides step-by-step instructions for sharing a secure view with a consumer account. It provides sample scripts
for both data providers and consumers.

The instructions for sharing a secure view are essentially the same as sharing a table, with the addition of the following objects:

A “private” schema containing the base table and a “public” schema containing the secure view. Only the public schema and secure view are shared.

A “mapping table” (also in the “private” schema), which is only required if you wish to share the data in the base table with multiple consumer accounts and share specific rows in the table with
specific accounts.

On very large (i.e. multi-terabyte) tables, clustering keys provide significant query performance benefits. By defining one or more clustering keys on the base tables used in shared
secure views, you ensure users in your consumer accounts are not negatively impacted when querying these views.

After defining clustering keys on a table, you must run the ALTER TABLE … RECLUSTER command to recluster the table. You may need to run the command
multiple times to achieve the optimal clustering. Also, after performing significant DML on a table with clustering keys, you should recluster the table.

These sample instructions assume a database named mydb exists in the data provider account and has two schemas, private and public. If the database and schemas do not exist, you should create
them before proceeding.

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.

Add privileges for the database (mydb), schema (public), and secure view (paid_sensitive_data) to the share. Note that these are the only objects added to the share, which ensures
no users in the consumer accounts can access the private schema 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_data is displayed in the command output as a table.

The following script illustrates performing 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. */userolesysadmin;createorreplacetablemydb.private.sensitive_data(namestring,datedate,timetime(9),bid_pricefloat,ask_pricefloat,bid_sizeint,ask_sizeint,access_idstring/* granularity for access */)clusterby(date);insertintomydb.private.sensitive_datavalues('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');createorreplacetablemydb.private.sharing_access(access_idstring,snowflake_accountstring);/* In the first insert, CURRENT_ACCOUNT() gives your account access to the AAPL and MSFT data. */insertintomydb.private.sharing_accessvalues('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. */insertintomydb.private.sharing_accessvalues('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. */createorreplacesecureviewmydb.public.paid_sensitive_dataasselectname,date,time,bid_price,ask_price,bid_size,ask_sizefrommydb.private.sensitive_datasdjoinmydb.private.sharing_accesssaonsd.access_id=sa.access_idandsa.snowflake_account=current_account();grantselectonmydb.public.paid_sensitive_datatopublic;/* Test the table and secure view by first querying the data as the provider account. */selectcount(*)frommydb.private.sensitive_data;select*frommydb.private.sensitive_data;selectcount(*)frommydb.public.paid_sensitive_data;select*frommydb.public.paid_sensitive_data;select*frommydb.public.paid_sensitive_datawherename='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; */altersessionsetsimulated_data_sharing_consumer=<account_name>;select*frommydb.public.paid_sensitive_data;/* Create a share using the ACCOUNTADMIN role. */useroleaccountadmin;createorreplacesharemydb_sharedcomment='Example of using Data Sharing with secure views';showshares;/* Grant privileges on the database objects to include in the share. */grantusageondatabasemydbtosharemydb_shared;grantusageonschemamydb.publictosharemydb_shared;grantselectonmydb.public.paid_sensitive_datatosharemydb_shared;/* Confirm the contents of the share. */showgrantstosharemydb_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; */altersharemydb_sharedsetaccounts=<consumer_accounts>;

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; */useroleaccountadmin;showshares;desc<provider_account>.mydb_shared;createdatabasemydb_shared1fromshare<provider_account>.mydb_shared;/* Grant privileges on the database to other roles (e.g. SYSADMIN) in your account. */grantimportedprivilegesondatabasemydb_shared1tosysadmin;/* 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. */userolesysadmin;showviews;usewarehouse<warehouse_name>;select*frompaid_sensitive_data;