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
When connecting with any of these methods, please consider the IP policy for your Snowflake account.
MFA
MFA token caching can help reduce the number of prompts that must be acknowledged while connecting and authenticating to Snowflake, especially when multiple connection attempts are made. A cached MFA token is valid for up to four hours.
This feature can be enabled in the Snowflake account using the following command:alter account set ALLOW_CLIENT_MFA_CACHING = true;
IP Allowlisting and Network Policies
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
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.
Documentation: Defining Future Grants on Database or Schema Objects
The minimum privileges below are required to generate DDL for objects:
|
Object |
Privilege |
Note |
|
Database |
usage |
Grants the ability to execute a USE command on the object. Also grants the ability to execute a SHOW command on the object. |
|
Schema |
usage |
Grants the ability to execute a USE command on the object. Also grants the ability to execute a SHOW 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 command on the object. Also grants the ability to execute a SHOW 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. There are several ways to connect to Snowflake using Direct connect.
User connection. A user connection can be created from this screen.
Username/password.
Key pair - (if using an encrypted key, include the passphrase)
SSO
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.)
Generating key pair credentials
To generate a key pair, please follow Snowflake documentation guidelines to generate them:
Encrypted (with passphrase)
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
Unencrypted (no passphrase)
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocryptSubmitting Private Key
When using your private key to Direct Connect, please enter the whole file's value, for example:
-----BEGIN PRIVATE KEY-----
MIIE***********redacted*********
*************************AwCI=
-----END PRIVATE KEY-----
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 VPN 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.