Search

Snowflake Read-Only User Setup

In order to connect your Snowflake account to Magnify, you will need to create a read-only user that Magnify will use to ingest all data into our systems on your behalf. This document provides  instructions for creating the read-only user.

To create a dedicated read-only database user, run the following commands against your database:

-- set variables (these need to be uppercase)

SET MAGNIFY_ROLE = 'ROLE_NAME';
SET MAGNIFY_USERNAME = 'USER_NAME';

-- set user password

SET MAGNIFY_PASSWORD = 'PASSWORD';
SET MAGNIFY_WAREHOUSE = 'WAREHOUSE_NAME';
SET DATABASE_NAME = 'NAME_OF_THE_DATABASE';

BEGIN;

-- create Magnify role

CREATE ROLE IF NOT EXISTS IDENTIFIER($MAGNIFY_ROLE);
CREATE USER IF NOT EXISTS IDENTIFIER($MAGNIFY_USERNAME)
PASSWORD = $MAGNIFY_PASSWORD
DEFAULT_ROLE = $MAGNIFY_ROLE
DEFAULT_WAREHOUSE = $MAGNIFY_WAREHOUSE;
GRANT ROLE IDENTIFIER($MAGNIFY_ROLE) TO USER IDENTIFIER($MAGNIFY_USERNAME);

-- grant schema access

GRANT USAGE ON DATABASE IDENTIFIER($DATABASE_NAME) to role IDENTIFIER($MAGNIFY_ROLE);
GRANT USAGE ON DATABASE IDENTIFIER($DATABASE_NAME) to role IDENTIFIER($MAGNIFY_ROLE);
GRANT USAGE ON SCHEMA <DATABASE_NAME>.<SCHEMA_NAME> to role IDENTIFIER($MAGNIFY_ROLE);
GRANT SELECT ON ALL TABLES IN DATABASE IDENTIFIER($DATABASE_NAME) to role IDENTIFIER($MAGNIFY_ROLE);
GRANT SELECT ON FUTURE TABLES IN DATABASE IDENTIFIER($DATABASE_NAME) to role IDENTIFIER($MAGNIFY_ROLE);

COMMIT;

You will need to replace the following fields in the command above:

  • ROLE_NAME
  • USER_NAME
  • PASSWORD
  • WAREHOUSE_NAME
  • DATABASE_NAME
  • SCHEMA_NAME
  • ENTER_DATABASE_NAME_HERE
  • ENTER_SCHEMA_NAME_HERE
If you have a firewall in place, you may need to grant access to Magnify's IP addresses used to sync data: 
  • 44.228.211.46
  • 54.201.164.178
  • 34.210.165.51

 

Restricting Permissions

If you'd like to restrict permissions to a subset of tables/views within Snowflake for your Magnify integration, run the following command for each table/view:

GRANT USAGE ON DATABASE <DATABASE_NAME> TO ROLE <ROLE_NAME>;
GRANT USAGE ON SCHEMA <DATABASE_NAME>.<SCHEMA_NAME> TO ROLE <ROLE_NAME>;

-- The below query needs to be ran for each table/view to integrate

GRANT SELECT ON TABLE<DATABASE_NAME>.<SCHEMA_NAME>.<TABLE/VIEW NAME> TO ROLE <ROLE_NAME>;

Related to

Updated