2
0
SIMULATED_DATA_SHARING_CONSUMER: During this post we will discuss an important and interesting Session parameter. When defining a secure object to share with consumer accounts, it is recommended to perform the validations. Validations ensure that whether the SHARE object is configure correctly. Also confirms if SHARE object display only the relevant data we wish to display. This is particularly important if you wish to limit data access base on the account the data is share with.
To facilitate performing this validation, Snowflake provides the SIMULATED_DATA_SHARING_CONSUMER session parameter.
Syntax:
alter session set simulated_data_sharing_consumer=<Consumer account>;
Specifies the name of a consumer account to simulate for testing/validating SHARE data, particularly secure views.
When this parameter is set in a session, shared views will return rows as if executed in the specified consumer account rather than the provider account. In other words, you can then query the view and see the results that a user in the consumer account will see.
Technical details:
We have CUSTOMER_DETAILS table in our provider account i.e. VG73771
Also having INVOICE_DETAILS table having reference to the CUSTOMER_DETAILS table via CUSTOMER_ID column.
Secure View
We have created a secure view, which is filtering out the data based on CURRENT_ACCOUNT().
create or replace secure view DEMO_DB.public.CUST_INV_DATA as
select CUSTOMER_NAME,CD.CUSTOMER_ID,CRID,ASSET,INVOICE_NO,INV_AMT,SNOW_ACCOUNT
from CUSTOMER_DETAILS CD,INVOICE_DETAILS ID
WHERE CD.CUSTOMER_ID = ID.CUSTOMER_ID
and CD.SNOW_ACCOUNT = current_account();
As part of the requirement, we need to share this VIEW to consumer Account, but before sharing want to Confirm the contents of the share in Provider Account itself.
To simulate the Shared data that a user in the consumer account will see. Run the below statement inside the Provider Account.
alter session set simulated_data_sharing_consumer=WH10521;
Now Query the Secure View in Provider Account:
To validate the results, we will share this view to the Consumer Account.
Now login to the Consumer Account and verify the data.
As we can confirm the contents of SHARED VIEW are same in Consumer Account as well in Provider Account via simulated_data_sharing_consumer parameter.
Note: At this time, the SIMULATED_DATA_SHARING_CONSUMER session parameter only supports secure views and secure materialized views, but does not support secure UDFs.
