[go: up one dir, main page]
Scribd
Upload
38 views3 pages

Data Sharing

Uploaded by

vr.sf99

This document shows how to securely share data between Snowflake accounts by creating a share object and granting access to specific views and tables. It demonstrates creating a share, granting privileges to the share, creating a reader account, sharing the data to the reader account by adding it to the share, and having the reader account create a database from the shared objects.

Copyright:

© All Rights Reserved
Download as TXT, PDF, TXT or read online from Scribd
Download as txt, pdf, or txt

Data Sharing

Uploaded by

vr.sf99
38 views3 pages
This document shows how to securely share data between Snowflake accounts by creating a share object and granting access to specific views and tables. It demonstrates creating a share, granting privileges to the share, creating a reader account, sharing the data to the reader account by adding it to the share, and having the reader account create a database from the shared objects.

Original Title

DataSharing

Copyright

© © All Rights Reserved

Available Formats

TXT, PDF, TXT or read online from Scribd

Did you find this document useful?

Is this content inappropriate?

This document shows how to securely share data between Snowflake accounts by creating a share object and granting access to specific views and tables. It demonstrates creating a share, granting privileges to the share, creating a reader account, sharing the data to the reader account by adding it to the share, and having the reader account create a database from the shared objects.

Copyright:

© All Rights Reserved
Download as TXT, PDF, TXT or read online from Scribd
Download as txt, pdf, or txt
38 views3 pages

Data Sharing

Uploaded by

vr.sf99
This document shows how to securely share data between Snowflake accounts by creating a share object and granting access to specific views and tables. It demonstrates creating a share, granting privileges to the share, creating a reader account, sharing the data to the reader account by adding it to the share, and having the reader account create a database from the shared objects.

Copyright:

© All Rights Reserved
Download as TXT, PDF, TXT or read online from Scribd
Download as txt, pdf, or txt
You are on page 1/ 3

//// PREPARATION ////

CREATE OR REPLACE DATABASE DATA_S;

CREATE OR REPLACE STAGE aws_stage


url='s3://bucketsnowflakes3';

// List files in stage


LIST @aws_stage;

// Create table
CREATE OR REPLACE TABLE ORDERS (
ORDER_ID VARCHAR(30)
,AMOUNT NUMBER(38,0)
,PROFIT NUMBER(38,0)
,QUANTITY NUMBER(38,0)
,CATEGORY VARCHAR(30)
,SUBCATEGORY VARCHAR(30));

// Load data using copy command


COPY INTO ORDERS
FROM @MANAGE_DB.external_stages.aws_stage
file_format= (type = csv field_delimiter=',' skip_header=1)
pattern='.*OrderDetails.*';

SELECT * FROM ORDERS;

--- Why Secure view is recommended? ---


CREATE OR REPLACE VIEW ORDERS_VIEW AS
SELECT
ORDER_ID,
AMOUNT,
QUANTITY
FROM ORDERS
WHERE CATEGORY != 'Furniture';

SHOW VIEWS LIKE '%ORDER%';

-- Create Secure View


CREATE OR REPLACE SECURE VIEW ORDERS_VIEW_SECURE AS
SELECT
ORDER_ID,
AMOUNT,
QUANTITY
FROM ORDERS
WHERE CATEGORY != 'Furniture';

//// STEP 1: Create a share object

-- You need the ACCOUNTADMIN role


USE ROLE ACCOUNTADMIN;

-- Create Share
CREATE OR REPLACE SHARE ORDERS_SHARE;
//// STEP 2: Setup Grants

// Grant usage on database


GRANT USAGE ON DATABASE DATA_S TO SHARE ORDERS_SHARE;

// Grant usage on schema


GRANT USAGE ON SCHEMA DATA_S.PUBLIC TO SHARE ORDERS_SHARE;

// Grant SELECT on table


--GRANT SELECT ON TABLE DATA_S.PUBLIC.ORDERS TO SHARE ORDERS_SHARE;

// Grant select on view


GRANT SELECT ON VIEW DATA_S.PUBLIC.ORDERS_VIEW TO SHARE ORDERS_SHARE;
GRANT SELECT ON VIEW DATA_S.PUBLIC.ORDERS_VIEW_SECURE TO SHARE ORDERS_SHARE;

// Validate Grants
SHOW GRANTS TO SHARE ORDERS_SHARE;

-- Create Reader Account --

CREATE MANAGED ACCOUNT reader_account


ADMIN_NAME = read_acc_admin,
ADMIN_PASSWORD = 'Pwd-12456',
TYPE = READER;

--- To drop the account again: DROP MANAGED ACCOUNT reader_account;

// Show accounts
SHOW MANAGED ACCOUNTS;

-- Share the data --

ALTER SHARE ORDERS_SHARE


ADD ACCOUNT = <consumer_locator>;

-- Sharing to a lower edition


ALTER SHARE ORDERS_SHARE
ADD ACCOUNT = <consumer_locator>
SHARE_RESTRICTIONS=false;

//// STEP 4:Create database from share ////


--- By using reader account ---

// Show all shares (consumer & producers)


SHOW SHARES;

// See details on share


DESC SHARE <consumer_account>.ORDERS_SHARE;

// Create a database in consumer account using the share


CREATE DATABASE DATA_SHARE_DB FROM SHARE <account_producer>.ORDERS_SHARE;

// Validate table access


SELECT * FROM DATA_SHARE_DB.PUBLIC.ORDERS;

// Setup virtual warehouse


CREATE WAREHOUSE READ_WH WITH
WAREHOUSE_SIZE='X-SMALL'
AUTO_SUSPEND = 180
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE;

//// STEP 5: Grant privileges optionally

-- Create and set up users --

// Create user
CREATE USER MYRIAM PASSWORD = 'difficult_passw@ord=123';

// Grant usage on warehouse


GRANT USAGE ON WAREHOUSE READ_WH TO ROLE PUBLIC;

// Grating privileges on a Shared Database for other users


GRANT IMPORTED PRIVILEGES ON DATABASE DATA_SHARE_DB TO REOLE PUBLIC;

You might also like