Connection

x

Snowflake

x

x

Prerequisites

As a superuser, execute the following SQL commands to create a read-only role, a user (e.g. SVC_PDQ) assigned to that role, and a warehouse for that role. PDQ fetches the metadata from the .INFORMATION_SCHEMA, usage grant on the database should provide permissions to read INFORMATION_SCHEMA for the service account role.

We also need access to Snowflake Metadata to Fetch Query Counts on the data assets and this is pulled from SNOWFLAKE.ACCOUNT_USAGE. Provide the following access for that “READ ONLY” service account role that is assigned to the PDQ service account:

● grant "USAGE" access on the database

● grant "USAGE" access on schemas

● grant "USAGE" access on the warehouse

● grant "Select" access on all tables in the Schema/Database that is defined in PDQ Snowflake Connection

● grant "Select" access on Snowflake metadata for usage information.

In the below query, replace the names accordingly and run for each Schema:

-- Read-only access to specific schemas and warehouse (CHANGE THIS) 
set schema_name = 'DATABASE_NAME.SCHEMA_NAME'; 
set warehouse_name = 'WAREHOUSE_NAME';

grant USAGE on database identifier($database_name) to role identifier($PDQ_ROLE);
grant USAGE on schema identifier($schema_name) to role identifier($PDQ_ROLE);

grant USAGE on warehouse identifier($warehouse_name) to role identifier($PDQ_ROLE); 
grant SELECT on all tables in schema identifier($schema_name) to role identifier($PDQ_ROLE); 
grant SELECT on future tables in schema identifier($schema_name) to role identifier($PDQ_ROLE); 
grant SELECT on all views in schema identifier($schema_name) to role identifier($PDQ_ROLE); 
grant SELECT on future views in schema identifier($schema_name) to role identifier($PDQ_ROLE);
grant imported privileges on database snowflake to $PDQ_ROLE

Key-based Authentication

The user can generate either an encrypted or an unencrypted version of the private key. PDQ suggests using an encrypted version for security.

1. Generate the private key.

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

1. Generate a Public key (reference the private key).

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

x

x

Snowflake Metadata Collection

Snowflake Collects Metadata in each Database instance inside INFORMATION_SCHEMA and exposes the necessary metadata for the following objects.

Snowflake also exposes a shared database called “SNOWFLAKE” that also presents the following schemas.

x

x

PDQ depends on both INFORMATION_SCHEMA for data profiling and ACCOUNT_USAGE in the Snowflake shared database for usage information to provide insights into queries that are getting kicked off on the data assets, extraction of semantic terms like TAGS and POLICIES.

PDQ has also added Snowflake Pipeline observability into the product and will require additional access to Tasks, Snowpipe along with Warehouse metering history for Cost Ops.

x

MSSQL

Oracle