Skip to main content
All CollectionsForward & Reverse Engineering
Snowflake - Direct Connect and Reverse Engineer
Snowflake - Direct Connect and Reverse Engineer

Role and account settings for using direct connect to Snowflake

S
Written by Serge Gershkovich
Updated over a week ago

This article describes the minimum privileges that must be granted to the user to perform Reverse Engineering. If connecting via Direct Connect, some user/account settings must also be considered.

Direct Connect supports the following login methods:

  • User / Pass

  • User / Pass with multi-factor authentication (MFA)

  • SSO

  • Key pair - coming soon

When connecting with any of these methods, please consider the IP policy for your Snowflake account.

IP Allowlisting

To ensure that the SqlDBM application can reach your cloud data platform, ask an admin with ACCOUNTADMIN access to check if IP Allowlisting is enabled.

Alternatively, attempt to access your organization's Snowflake login page from a home (non-work) computer which is not connected to a VPN.

Ask the admin to add the SqlDBM IP address to the policy:

34.217.200.121

Application Allowlisting

If your organization has a network policy in place with allowlist blocking, you will need to add SqlDBM to the list.

sqldbm-lb-1868119251.us-west-2.elb.amazonaws.com

Private Networking (Private Link)

Private Networking (a.k.a PrivateLink/Private Link, offered by various CDPs) is a security feature that is only available as an add-on product. Please contact sales@sqldbm.com to find out more.

Private networking example using AWS:

Required Database Privileges

SqlDBM uses DDL to draw and maintain its projects and diagrams - no data is ever queried, and no objects are created or modified. As such, only minimal privileges are required for the role used to generate the DDL (manually or via Direct Connect).

Note, access to the container object (database, schema) must be granted as well as having access to the underlying objects (tables, views, functions, etc.) Otherwise, underlying objects will not be visible.

Also note, that for tables and views, "REFERENCES" is the minimum required privilege to access the DDL. Having "SELECT" privilege and above will also allow a role to view the object structure.

Also also note, that new objects must be explicitly granted to the role unless that role is the owner or has "FUTURE" grants.

The minimum privileges below are required to generate DDL for objects:

Object

Privilege

Note

Database

usage

Grants the ability to execute a USE <object> command on the object. Also grants the ability to execute a SHOW <objects> command on the object.

Schema

usage

Grants the ability to execute a USE <object> command on the object. Also grants the ability to execute a SHOW <objects> command on the object.

Table/View

REFERENCES

Grants the ability to view the structure of an object (but not the data).

For tables, the privilege also grants the ability to reference the object as the unique/primary key table for a foreign key constraint.

File Format, Sequence, Stored Procedure, User-Defined Function

usage

Grants the ability to execute a USE <object> command on the object. Also grants the ability to execute a SHOW <objects> command on the object.

Note that this will also allow to call or select the object.

Sample script for creating a new role, and granting access to schema objects for Reverse Engineering:

Note, you must perform these grants using a role that already has access to the required objects. Preferably SECURITYADMIN or USERADMIN.

create role MYROLE;

grant usage on database MYDB to role MYROLE;

grant usage on schema MYDB.MYSCHEMA to role MYROLE;

grant references on all tables in schema MYDB.MYSCHEMA to role MYROLE;

grant references on all views in schema MYDB.MYSCHEMA to role MYROLE;

grant usage on all functions in schema MYDB.MYSCHEMA to role MYROLE;

create user METADATA_READ_USER password='abc123' must_change_password = true;

grant role MYROLE to user METADATA_READ_USER;

Direct Connect

Using the service account created above (or your own personal user), you can connect directly to your Snowflake account and see the objects allowed by the role-based access control. To use direct connect with a role other than the user's default role, you must specify it along with the user name and password when connecting.

Required parameters (non-SSO-enabled connections):

  • Server - account name in myOrg.snowflakecomputing.com format

  • User - personal or service account user name

  • Password - associated password

  • Role (optional) - role to use once connected. To use your Snowflake default role, leave the Role field empty.

Note that the direct connection and its parameters will only persist in the browser session. These credentials are not stored or recorded by SqlDBM.

(For more information on direct connect and using it with Snowflake SSO, see the related articles at the end of this post for more info.)

Troubleshooting connectivity issues

Corporate networking and security policies may interfere with connectivity between SqlDBM and Snowflake. If you are unable to connect despite following the guidelines in this article, please make sure that IP blocking or private networking is not interfering.

An easy way to diagnose this type of issue is by attempting to connect to your Snowflake account from a home computer without using a VNP or any corporate software.

For instructions on allowing SqlDBM through the firewall or private network, please see the related article at the bottom of this page.


See also:

Did this answer your question?