Snowflake

Overview

When exposing your data to mediarithmics we recommend the following structure :

• A Snowflake database that contains :

  • One or multiple schemas with data you want to expose to mediarithmics with read-only access

  • 2 schemas specifically created for mediarithmics usage with read and write access.

You do not have to expose all tables/views in a given schema. Refer to this section for more information

This page describes how to set up a service account with such access.

Steps

1. Service account and permissions setup

2. Create dedicated schemas for mediarithmics usage

3. Configure key-pair authentication

4. Create and format a credentials JSON file

For more information on Snowflake key-pair authentication please refer to Snowflake documentation https://docs.snowflake.com/en/user-guide/key-pair-auth

1. Service account and permissions setup

For the initial set up, start with the SQL script template below. The script will :

• Create a dedicated service account, role, and warehouse for mediarithmics to use

• Give the service account read only access on a database of your choice and all its content

Grant limited access

If you wish to restrict access to specific tables or views the minimum permissions required are :

• USAGE on DATABASE

• USAGE on SCHEMA

• SELECT on TABLES (or VIEWS)

-- =============================================================
-- Setup variables - update these values for your environment
-- =============================================================
SET mics_username          = 'MICS_USER';
SET mics_default_warehouse = 'MICS_WAREHOUSE';
SET mics_database          = '<database>';
SET mics_default_namespace = '<database.schema>';
SET mics_default_role      = 'MICS_USER_ROLE';

-- =============================================================
-- Switch to admin role
-- =============================================================
USE ROLE ACCOUNTADMIN;

-- =============================================================
-- Provision resources: warehouse, role and service user
-- =============================================================
CREATE WAREHOUSE IF NOT EXISTS identifier($mics_default_warehouse);
CREATE ROLE IF NOT EXISTS identifier($mics_default_role);

CREATE USER IF NOT EXISTS identifier($mics_username)
    TYPE              = SERVICE
    DEFAULT_WAREHOUSE = $mics_default_warehouse
    DEFAULT_NAMESPACE = $mics_default_namespace
    DEFAULT_ROLE      = $mics_default_role;

-- =============================================================
-- Role assignments
-- =============================================================
GRANT ROLE identifier($mics_default_role) TO ROLE SYSADMIN;
GRANT ROLE identifier($mics_default_role) TO USER identifier($mics_username);

-- =============================================================
-- Warehouse access
-- =============================================================
GRANT USAGE ON WAREHOUSE identifier($mics_default_warehouse) TO ROLE identifier($mics_default_role);

-- =============================================================
-- Read-only access on the database, schemas, tables and views
-- (includes future objects to cover tables/views created later)
-- =============================================================
GRANT USAGE  ON DATABASE identifier($mics_database)                       TO ROLE identifier($mics_default_role);
GRANT USAGE  ON ALL SCHEMAS       IN DATABASE identifier($mics_database)  TO ROLE identifier($mics_default_role);
GRANT SELECT ON ALL TABLES        IN DATABASE identifier($mics_database)  TO ROLE identifier($mics_default_role);
GRANT SELECT ON ALL VIEWS         IN DATABASE identifier($mics_database)  TO ROLE identifier($mics_default_role);
GRANT SELECT ON FUTURE TABLES     IN DATABASE identifier($mics_database)  TO ROLE identifier($mics_default_role);
GRANT SELECT ON FUTURE VIEWS      IN DATABASE identifier($mics_database)  TO ROLE identifier($mics_default_role);

2. Create dedicated schemas for mediarithmics usage

We recommend to create two dedicated schemas for mediarithmics usage.

• mics_workspace schema that holds all temporary tables created while syncing with your warehouse

• mics_output schema that holds all output tables — the clean results of daily processing, ready for consumption

Note that the tables in mics_output are not meant to be interface tables : their schema could change with successive releases. If you need a specific stable table schema to expose it to other tools, please contact your account manager.

The service account you created should have full access (read/write) on both schemas. Use this code to do so :

-- =============================================================
-- Dedicated schemas with full ownership
-- =============================================================
SET mics_database = '<database>';

USE ROLE ACCOUNTADMIN;
USE DATABASE IDENTIFIER($mics_database);

CREATE SCHEMA IF NOT EXISTS mics_workspace;
GRANT OWNERSHIP ON SCHEMA mics_workspace TO ROLE MICS_USER_ROLE;

CREATE SCHEMA IF NOT EXISTS mics_output;
GRANT OWNERSHIP ON SCHEMA mics_output TO ROLE MICS_USER_ROLE;

Nota bene : If you wish to use a synchronization strategy that handles updates and deletes of data, you will need a working schema (i.e. the mics_workspace schema)

3. Configure key-pair authentication

Use the following command to generate a private key (called rsa_key.p8)

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

Then use the following command to generate a public key (called rsa_key.pub linked to the private key called rsa_key.pub stored in the current directory)

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Then, from the Snowflake interface or the Snowflake CLI run the following SQL query to assign the public key generated at step 1 (rsa_key.pub) to the service account created for mediarithmics to use :

ALTER USER <MICS_USER> SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

Nota bene :

• Exclude the public key delimiters in the SQL statement

• To run this query you will need one of the following role/privilege

  • The MODIFY PROGRAMMATIC AUTHENTICATION METHODS or OWNERSHIP privilege on the user

  • The SECURITYADMIN role or higher

4. Edit a credentials file

Now you need to generate a JSON file with the following format :

{
"user" : "MICS_USER",
"account" : "ORGANIZATION_NAME-ACCOUNT_NAME",
"database" : "DATABASE",
"private_key" : "-----BEGIN PRIVATE KEY-----\nMII..."
}

• "user" : the user you associated the public key with

• "account" : the "account" property is what is called "Account identifier" in Snowflake. It's usually the organization name and the account name hyphenated

• "database" : the database containing the data you want to make available to mediarithmics

• "private key" : the private key generated in step 3 and associated to the user public key. Be ware of the formatting of the private key : the key must be in on a single line, this means you should use a "\n" separator at each return to line of the private key.