Snowflake Setup Document

Snowflake Data Source Registration

Step 1: Register Snowflake Data Source

  • Login to TrustLogix Console and Register a new Snowflake Datasource

Step 2: Configure Snowflake with prerequisite (Owner: Snowflake DBA )

TrustLogix supports Access Policies and Monitoring capabilities on Snowflake. Below are steps which are required to enable and function properly.

The below section describes the details of the Setup Script provided in the TrustLogix console

Each data source has a unique configuration file. TrustLogix recommends to download the script for every data source that is configured

snowflake-prereq

 

Configure user and role for control plane and TrustLet

  • ControlPlane connects to snowflake to query the data dictionary, we need a snowflake DB Role and User , a Warehouse.
  • Below are SQL statements to create user , role and warehouse used in TrustLogix.

-- 'Create warehouse and grant permission to service role...Done ';

-- CP: Create warehouse and grant to permissions to role;

create warehouse if not exists TLX_WAREHOUSE warehouse_size=XSMALL;

grant operate, usage on warehouse TLX_WAREHOUSE to role TLX_287DFF002E_CP_ROLE;

grant create role on account to role TLX_287DFF002E_CP_ROLE;

  • TrustLogix stores the column masking and row access policies in a separate DB and schema for easy identification of policies.
  • The role requires access to create and manage RLS, Masking and Grant statement
  • Below are the SQL statements that provision this access

-- CP : Create database and schema for tlx policies

create database if not exists tlx_policy_db;

create schema if not exists tlx_policy_db.tlx_row_policy;

create schema if not exists tlx_policy_db.tlx_column_policy;

use schema tlx_policy_db.tlx_row_policy;

-- Manage grants Privilege role to create and deploy policies

-- Grants to Manage RLS and Masking policies

grant manage grants on account to role TLX_287DFF002E_CP_ROLE;

grant apply masking policy on account to role TLX_287DFF002E_CP_ROLE;

-- CP: GRANT imported permissions to role for snowflake database

grant imported PRIVILEGES on database snowflake to role TLX_287DFF002E_CP_ROLE;

-- CP grant permissions on policy DB and schema to cp role

grant CREATE SCHEMA,MODIFY,MONITOR,REFERENCE_USAGE,USAGE on database tlx_policy_db to role TLX_287DFF002E_CP_ROLE;

grant CREATE MASKING POLICY,MODIFY,USAGE,CREATE VIEW on schema tlx_policy_db.tlx_row_policy to role TLX_287DFF002E_CP_ROLE;

grant CREATE MASKING POLICY,MODIFY,USAGE on schema tlx_policy_db.tlx_column_policy to role TLX_287DFF002E_CP_ROLE;

  • TrustLogix requires the roles to securely copy the meta data into Security Data Lake
  • A storage integration is required for this purpose

CREATE or replace STORAGE INTEGRATION TLX_287DFF002E_S3_INTEGRATION

TYPE = EXTERNAL_STAGE

STORAGE_PROVIDER = S3

ENABLED = TRUE

STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::657086339242:role/tlx-287dff002e-dp-snowflake-role'

STORAGE_ALLOWED_LOCATIONS = ('s3://snowflake-raw-audit-logs-9242');

grant usage on integration TLX_287DFF002E_S3_INTEGRATION to role TLX_287DFF002E_CP_ROLE;

CREATE or replace STAGE TLX_287DFF002E_S3_EXTERNAL_STAGE URL='s3://snowflake-raw-audit-logs-9242' storage_integration=TLX_287DFF002E_S3_INTEGRATION;

grant usage on stage TLX_287DFF002E_S3_EXTERNAL_STAGE to role TLX_287DFF002E_CP_ROLE