Overview

What You’ll Learn

What You’ll Need

What You’ll Build

Setup

Step 1: Create a Snowflake trial account

Step 2: Configure the first account and create two more accounts in the same org

-- Run this code in your PRIMARY Account
-- Make sure you update the four variables below (email_var, firstname_var, lastname_var, and pwd_var)

USE ROLE accountadmin;

-- Use the same name and email for all accounts
set email_var = 'FILL_IN_YOUR_EMAIL';
set firstname_var  = 'FILL_IN_YOUR_FIRST_NAME';
set lastname_var  = 'FILL_IN_YOUR_LAST_NAME';

-- Use the same password for users in all accounts
set pwd_var = 'FILL_IN_YOUR_PASSWORD';

CREATE OR REPLACE WAREHOUSE compute_wh WAREHOUSE_SIZE=xsmall INITIALLY_SUSPENDED=TRUE;
GRANT ALL ON WAREHOUSE compute_wh TO ROLE public;

--  Create a user and role for the sales domain:
USE ROLE accountadmin;
CREATE OR REPLACE ROLE sales_data_scientist_role;

SET my_user_var = CURRENT_USER();
ALTER USER identifier($my_user_var) SET DEFAULT_ROLE = sales_data_scientist_role;

CREATE OR REPLACE USER sales_admin
  PASSWORD = $pwd_var
  LOGIN_NAME = sales_admin
  DISPLAY_NAME = sales_admin
  FIRST_NAME = $firstname_var
  LAST_NAME = $lastname_var
  EMAIL = $email_var
  MUST_CHANGE_PASSWORD = FALSE
  DEFAULT_WAREHOUSE = compute_wh
  DEFAULT_ROLE = sales_data_scientist_role
  COMMENT = 'Sales domain admin';

GRANT ROLE sales_data_scientist_role TO USER sales_admin;
GRANT ROLE accountadmin              TO USER sales_admin;  -- for simplicity in this lab
GRANT CREATE SHARE ON ACCOUNT                    TO ROLE sales_data_scientist_role;
GRANT CREATE ORGANIZATION LISTING ON ACCOUNT     TO ROLE sales_data_scientist_role;


-- Next, create a user and role for the marketing domain:
USE ROLE accountadmin;
CREATE OR REPLACE ROLE marketing_analyst_role;

CREATE OR REPLACE USER marketing_admin
  PASSWORD = $pwd_var
  LOGIN_NAME = marketing_admin
  DISPLAY_NAME = marketing_admin
  FIRST_NAME = $firstname_var
  LAST_NAME = $lastname_var
  EMAIL = $email_var
  MUST_CHANGE_PASSWORD = FALSE
  DEFAULT_WAREHOUSE = compute_wh
  DEFAULT_ROLE = marketing_analyst_role
  COMMENT = 'Marketing domain admin';

GRANT ROLE marketing_analyst_role TO USER marketing_admin;
GRANT CREATE SHARE ON ACCOUNT                    TO ROLE marketing_analyst_role;
GRANT CREATE ORGANIZATION LISTING ON ACCOUNT     TO ROLE marketing_analyst_role;

USE ROLE orgadmin;
GRANT MANAGE LISTING AUTO FULFILLMENT ON ACCOUNT TO ROLE sales_data_scientist_role;
GRANT MANAGE LISTING AUTO FULFILLMENT ON ACCOUNT TO ROLE marketing_analyst_role;

aside negative IMPORTANT: You may receive an email asking for verification of your email address, if you have not done so before. Check your email inbox for a message from "Snowflake Computing" and validate the email for the marketing_admin user.

aside negative IMPORTANT: For the commands below, you have to use the same worksheet as above, as the variables created are reused (email_var, firstname_var, lastname_var, and pwd_var).

-- Run this code in your PRIMARY account
-- Create a secondary account in the same region (default!):
USE ROLE orgadmin;

CREATE ACCOUNT hol_account2
  admin_name = supply_chain_admin
  admin_password = $pwd_var
  first_name = $firstname_var
  last_name = $lastname_var 
  email = $email_var
  must_change_password = false
  edition = enterprise;

-- Create an organization account for admin purposes:
CREATE ORGANIZATION ACCOUNT hol_org_account
  admin_name = org_admin
  admin_password = $pwd_var
  first_name = $firstname_var
  last_name = $lastname_var 
  email = $email_var
  must_change_password = false
  edition = enterprise; 

-- Get an overview of all the accounts in the organization.
-- This SHOW command should return 3 rows:

SHOW ACCOUNTS;

Step 3: Configure the second account HOL_ACCOUNT2

-- Run this in hol_account2, logged in as supply_chain_admin user
-- Make sure you run this as ACCOUNTADMIN

USE ROLE accountadmin;

CREATE OR REPLACE WAREHOUSE compute_wh WAREHOUSE_SIZE=xsmall INITIALLY_SUSPENDED=TRUE;
GRANT ALL ON WAREHOUSE compute_wh TO ROLE public;

CREATE ROLE supply_chain_admin_role;
GRANT ROLE accountadmin TO ROLE supply_chain_admin_role; -- for simplicity in this lab
GRANT ROLE supply_chain_admin_role TO USER supply_chain_admin;

ALTER USER supply_chain_admin 
  SET DEFAULT_ROLE = supply_chain_admin_role;

USE ROLE supply_chain_admin_role;
CREATE DATABASE supply_chain_db;

Step 4: Configure the organization account and rename your primary account

-- Login to the Organization Account HOL_ORG_ACCOUNT and execute the following commands in a worksheet.

USE ROLE accountadmin;

CREATE OR REPLACE WAREHOUSE compute_wh WAREHOUSE_SIZE=xsmall INITIALLY_SUSPENDED=TRUE;
GRANT ALL ON WAREHOUSE compute_wh TO ROLE public;

-- Rename the Primary Account:
USE ROLE globalorgadmin;


-- execute the following two commands together, 
-- no other commands in between:

  show accounts;
  SET my_curr_account = (SELECT "account_name" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) order by "created_on" ASC LIMIT 1);

-- View and rename the account:
SELECT $my_curr_account;

ALTER ACCOUNT identifier($my_curr_account) 
  RENAME TO hol_account1 SAVE_OLD_URL = true;

-- Enable users with the ACCOUNTADMIN role to set up Cross-Cloud Auto-Fulfillment
SELECT SYSTEM$ENABLE_GLOBAL_DATA_SHARING_FOR_ACCOUNT('hol_account1');
SELECT SYSTEM$ENABLE_GLOBAL_DATA_SHARING_FOR_ACCOUNT('hol_account2');

SHOW ACCOUNTS;

-- You should see 3 rows similar to the image below.
-- Make a note of your account names, URLs, and passwords!

Step 5: Create profiles for the Sales, Marketing, and Supply Chain domains

Step 6: Setup of a TPC-H sample database

Step 7: Pre-populate the Internal Marketplace with Sample Listings

---

Create / Publish Org Listing

Publishing Flow (Step 1 of 5): Listing Title and Ownership

aside positive IMPORTANT: some code later in this lab will reference the listing by the name Order Insights.

Publishing Flow Step (2 of 5): Selecting Data Objects to Share

Publishing Flow (Step 3 of 5): Configure Access Control and the Approval Process

Publishing Flow (Step 4 of 5): Add Optional Metadata and SLOs

Publishing Flow (Step 5 of 5): Publish your listing to the internal marketplace

Request and Grant Access

Request Access

Review and Grant Access

---

Consume Org Listing

---

Live Data Sharing

---

Data Governance Policies

Row-level Access Control across Domains

use schema tpch.sf1;
use role sales_data_scientist_role;

CREATE OR REPLACE ROW ACCESS POLICY country_filter AS (country INTEGER) 
RETURNS boolean ->
  CASE
    WHEN current_account_name() = 'HOL_ACCOUNT1' 
     AND current_role()         = 'SALES_DATA_SCIENTIST_ROLE'
     THEN true
    WHEN current_account_name() = 'HOL_ACCOUNT1' 
     AND current_role()         = 'MARKETING_ANALYST_ROLE'
     AND country                = 3 /* Canada */
     THEN true
    WHEN current_account_name() = 'HOL_ACCOUNT2' 
     AND country                = 24 /* USA */
     THEN true
   ELSE false
  END;

 ALTER TABLE nation ADD ROW ACCESS POLICY country_filter ON (n_nationkey); 

Data Masking across Domains

 CREATE OR REPLACE MASKING POLICY order_mask AS (value INT, cutoff_date DATE) RETURNS INT ->
  CASE
    WHEN current_account_name() = 'HOL_ACCOUNT1' 
     AND current_role()         = 'SALES_DATA_SCIENTIST_ROLE'
     THEN value
    WHEN current_account_name() = 'HOL_ACCOUNT1' 
     AND current_role()         = 'MARKETING_ANALYST_ROLE'
     AND cutoff_date            >= '1996-01-01'
     THEN value
    WHEN current_account_name() = 'HOL_ACCOUNT2' 
     AND cutoff_date            >= '1996-01-01'
     THEN value
   ELSE null
  END;

ALTER VIEW order_summary ALTER COLUMN order_amount 
  SET MASKING POLICY order_mask USING (order_amount, o_orderdate); 

ALTER TABLE orders       ALTER COLUMN o_totalprice 
  SET MASKING POLICY order_mask USING (o_totalprice, o_orderdate);

ALTER TABLE lineitem     ALTER COLUMN l_extendedprice 
  SET MASKING POLICY order_mask USING (l_extendedprice, l_commitdate);

Effect of the Policies on Data Consumers

Basic Listing Management

Change/Revoke Access or Discoverability for a Listing

---

How to Grant Listing Management Privileges

---

How to Audit Access to Organizational Listing

use role globalorgadmin;
use warehouse compute_wh;

select q.account_name, 
    q.user_name, 
    q.role_name, 
    q.query_text, 
    q.start_time, 
    q.end_time, 
    a.direct_objects_accessed, 
    a.provider_base_objects_accessed,
    a.provider_policies_referenced
from SNOWFLAKE.ORGANIZATION_USAGE.ACCESS_HISTORY a, 
    SNOWFLAKE.ORGANIZATION_USAGE.QUERY_HISTORY q
where a.query_id = q.query_id
and q.query_text ilike '%ORDER_INSIGHTS%'
order by query_start_time desc;

---

Programmatic Listings

As a Listing Owner

As a Listing Consumer

---

Conclusion And Resources

What you Learned

Related Resources