1
0
Enable Data Share with Business Critical Account : During this post we will discuss about a key important parameter SHARE_RESTRICTIONS. Consider a scenario where we have two Snowflake accounts belongs to Enterprise and Business Critical Editions respectively. As per the ask we are supposed to share some of the objects from the Business-Critical Accounts to lower edition. Initial it seems a very straight forward requirement and think to create a SHARE object in Business edition. Align the required objects to the SHARE and add the Enterprise Account (Locator) to SHARE object.
Here the problem comes, and Snowflake does not allow to do so.
By default, Snowflake does not allow sharing data from a Business Critical to a non-Business Critical account.
To emulate the scenario:
In Business-Critical edition, create a new User, role and assign the required privileges using ACCOUNTADMIN.
New User: SHARE_USER
Role Name: MANAGE_SHARE
Privileges: CREATE SHARE, DATABASE, MONITOR (for WH), SCHEMA
In business critical edition ,login with the new user and perform the below task:
Create or replace database CUSTOMER_DB;
create OR REPLACE table CUSTOMER_INFO (cust_info string);
INSERT INTO CUSTOMER_INFO(CUST_INFO) VALUES ('SACHIN')
create table CUSTOMER_DETAILS as
select * from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.CUSTOMER LIMIT 100
CREATE share CUSTOMER_OBJECT;
grant usage on database CUSTOMER_DB to share CUSTOMER_OBJECT;
grant usage on schema CUSTOMER_DB.PUBLIC to share CUSTOMER_OBJECT;
grant SELECT on table CUSTOMER_DB.PUBLIC.CUSTOMER_INFO to share CUSTOMER_OBJECT;
show grants to share CUSTOMER_OBJECT;
As we have notice Snowflake reported error while sharing the Objects from Business critical to Non-Business Critical.
To make it possible, below two task need to be performed at Business Critical side:
- Firstly, Grant OVERRIDE SHARE RESTRICTIONS global privilege to new Role
- Secondly, Disable SHARE RESTRICTIONS parameter
Use role ACCOUNTADMIN;
GRANT OVERRIDE SHARE RESTRICTIONS ON ACCOUNT TO ROLE MANAGE_SHARE;
Moreover, Now try to share the object using SHARE_RESTRICTION parameter
alter share CUSTOMER_OBJECT add accounts = ak65063 share_restrictions=false;
Now Login to the enterprise edition and import the Database from SHARE.
In Conclusion, As we can see using the SHARE_RESTRICTIONS parameter we can Enable Data Share with Business Critical Account to Non business Critical.
