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.

Account Settings

If connecting via Direct Connect, the user account not be enrolled in multi-factor authentication (MFA), and the SqlDBM IP address must not be blocked. You must be using an account with a named username/password login method.

Check if MFA is enabled:

Run the command below and ensure that the EXT_AUTHN_DUO property is set to "false."

DESCRIBE USER  <user>;

Disable MFA:

To use Direct Connect, create a user without MFA enabled or disable it for a given user with the following command:

ALTER USER <user> DISABLE_MFA = TRUE

To disable MFA temporarily for a number of minutes:

ALTER USER <user> MINS_TO_BYPASS_MFA = <integer>

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

AWS Private Link

AWS private link is a security feature that is only available as an add-on product. Please contact sales@sqldbm.com to find out more.

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 <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 with a non-default role

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.

To use your default role, leave the Role field empty.