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

Snowflake Metadata Collection

MSSQL

Oracle