0
0
SHARE Restrictions: Imagine a situation in a state-of-the-art healthcare facility where Snowflake is employed to meet its varied data analytics needs. The organization is divided into two key segments: the Research Division, operating on a Business Edition Snowflake account, and the Clinical Division, constrained to a Snowflake Enterprise Edition due to budget limitations. The Research Division has a lot of patient records, data from clinical trials, and important medical research. They want to work together with the Clinical Division, but they can’t share data on an account with a lower Snowflake Edition than the Provider account due to data security and rules.
To tackle this challenge, Snowflake’s Edition-Based Sharing Restrictions step in as the solution. Snowflake offers the OVERRIDE SHARE RESTRICTIONS global privilege, initially bestowed upon the ACCOUNTADMIN role by default. As a result, people with these roles can control the SHARE_RESTRICTIONS setting for their provider accounts. Once the parameter is disabled, the Business-Critical provider account gains the flexibility to include a consumer account with a Non-Business Critical edition in the share.
Implementation
Step1: Create two Accounts with Business-Critical Edition and Enterprise Edition respectively.
Step2: Login to the Business-Critical Snowflake Account 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
/* Create Role, Users and Assign the privileges */
CREATE or REPLACE user SHARE_USER password = 'Thakurji@2020' default_role = MANAGE_SHARE must_change_password = false;
CREATE or replace ROLE MANAGE_SHARE;
GRANT CREATE DATABASE ON ACCOUNT TO role MANAGE_SHARE;
GRANT USAGE on warehouse COMPUTE_WH to role MANAGE_SHARE;
GRANT CREATE SHARE ON ACCOUNT TO role MANAGE_SHARE;
GRANT ROLE MANAGE_SHARE TO USER SHARE_USER;
Step3: Login to the Business-Critical Snowflake Account with new User.
/* create the DB, Tables and Share object */
Create or replace database RESEARCH_DB;
create table CUSTOMER_DETAILS as select * from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.CUSTOMER LIMIT 100;
CREATE share CUSTOMER_OBJECT;
grant usage on database RESEARCH_DB to share CUSTOMER_OBJECT;
grant usage on schema RESEARCH_DB.PUBLIC to share CUSTOMER_OBJECT;
grant SELECT on table RESEARCH_DB.PUBLIC.CUSTOMER_DETAILS to share CUSTOMER_OBJECT;
show grants to share CUSTOMER_OBJECT;
Step4: Try to add Enterprise account to SHARE Object
ALTER SHARE CUSTOMER_OBJECT ADD ACCOUNTS = NW11551;
As we have noticed Snowflake reported error while sharing the Objects from Business Critical to Non-Business Critical.
Below are two task that needs to be performed at the Business-Critical side:
- Grant OVERRIDE SHARE RESTRICTIONS global privilege to new Role.
- Secondly, Disable SHARE RESTRICTIONS parameter.
Step5: Login to Business Critical with ACCOUNTADMIN and run the below grant command.
GRANT OVERRIDE SHARE RESTRICTIONS ON ACCOUNT TO ROLE MANAGE_SHARE;
Step6: Login to Business Critical with a new user i.e. SHARE_USER and run the command with SHARE_RESTRICTION parameter.
ALTER SHARE CUSTOMER_OBJECT ADD ACCOUNTS = NW11551 SHARE_RESTRICTIONS=FALSE;
Result outcomes: Verify the data inside the Enterprise account.
We can see using the SHARE_RESTRICTIONS parameter, we can Enable Data Share with Business-Critical Account to Non business Critical.
/* Query the shared table in Enterprise */
With Edition-Based Sharing Restrictions and the OVERRIDE SHARE RESTRICTIONS privilege, Snowflake empowers organizations to share data securely while upholding the highest standards of data integrity and security.
