Source and destination database permissions for Snowflake on AWS

Tonic.ai recommends that you create separate accounts for the source and destination databases, even if the databases are in the same Snowflake account. This allows each account to have the minimum permissions needed in the source and destination databases.

If you use the same account, then you must combine the relevant permissions for the source and destination databases into a single role. Snowflake only allows each account to have a single active primary role.

User permissions on the source database

The below permissions create a role with the necessary permissions to act as the source database user. A user is then created and assigned into that role.

The below permissions give read-only data access to a specific database on all current and future schemas, tables, and sequences.

It also grants the role access to a specified warehouse.

CREATE ROLE TONIC_SOURCE_DATABASE_ROLE;

GRANT USAGE ON WAREHOUSE <warehouse name> TO ROLE TONIC_SOURCE_DATABASE_ROLE;

GRANT USAGE ON DATABASE <source database name> TO ROLE TONIC_SOURCE_DATABASE_ROLE;

GRANT USAGE ON ALL SCHEMAS IN DATABASE <source database name> TO ROLE TONIC_SOURCE_DATABASE_ROLE;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE <source database name> TO ROLE TONIC_SOURCE_DATABASE_ROLE;

GRANT SELECT ON FUTURE TABLES IN DATABASE <source database name> TO ROLE TONIC_SOURCE_DATABASE_ROLE;
GRANT SELECT ON ALL TABLES IN DATABASE <source database name> TO ROLE TONIC_SOURCE_DATABASE_ROLE;

GRANT USAGE ON FUTURE SEQUENCES IN DATABASE <source database name> TO ROLE TONIC_SOURCE_DATABASE_ROLE;
GRANT USAGE ON ALL SEQUENCES IN DATABASE <source database name> TO ROLE TONIC_SOURCE_DATABASE_ROLE;

CREATE USER <user name> password='<password>' default_role = TONIC_SOURCE_DATABASE_ROLE default_warehouse = <warehouse name>;

GRANT ROLE TONIC_SOURCE_DATABASE_ROLE TO USER <user name>;


-- Structural requires this parameter to be set at either the account or user level.
ALTER USER <user name> set QUOTED_IDENTIFIERS_IGNORE_CASE = false;

-- If using a source external stage
GRANT USAGE ON STAGE <source stage name> TO ROLE TONIC_SOURCE_DATABASE_ROLE;

User permissions on the destination database

The destination database must exist before Structural can connect to it. The user that you provide to Structural for connecting to the destination database should be the owner of the database and of all of the objects in the database.

We suggest that you first create a specific Structural destination database user. Then create the destination database from that user's account. If you create the database with another account such as ACCOUNTADMIN, then you must transfer ownership of the database and all of its objects to the new account.

As part of a Structural data generation job, this user must be able to:

• DROP and then create schemas on the output database

• Copy data from Amazon S3 into tables in the database

The below permissions create a role with the necessary permissions to act as the destination database user. A user is then created and assigned into that role.

The following permissions gives full access to the destination database. They also grant the role access to a specified warehouse. To accommodate a situation where the database was created by another user such as ACCOUNTADMIN, this includes the required transfer of ownership to the role.

CREATE ROLE TONIC_DESTINATION_DATABASE_ROLE;

GRANT OWNERSHIP ON DATABASE <destination database name> TO TONIC_DESTINATION_DATABASE_ROLE;
GRANT USAGE ON WAREHOUSE <warehouse name> TO TONIC_DESTINATION_DATABASE_ROLE;

CREATE USER <user name> password='<password>' default_role = TONIC_DESTINATION_DATABASE_ROLE default_warehouse = <warehouse name>;
GRANT ROLE TONIC_DESTINATION_DATABASE_ROLE TO USER <user name>;

-- If the destination database was already created with another user, 
-- existing schemas (including default PUBLIC) must have ownership transferred
-- to the Structural user role.
GRANT OWNERSHIP on SCHEMA <destination database name>.PUBLIC to TONIC_DESTINATION_DATABASE_ROLE;
GRANT OWNERSHIP on SCHEMA <destination database name>.<additional schemas> to TONIC_DESTINATION_DATABASE_ROLE;


-- Structural requires this parameter to be set at either the account or user level.
ALTER USER <user name> set QUOTED_IDENTIFIERS_IGNORE_CASE = false;

-- If using a destination external stage
GRANT USAGE ON STAGE <destination stage name> TO ROLE TONIC_DESTINATION_DATABASE_ROLE;

Additional user configuration

Structural requires that the parameter QUOTED_IDENTIFIERS_IGNORE_CASE = false at either the account or user level.

To set it at the user level, run:

ALTER USER <user name> set QUOTED_IDENTIFIERS_IGNORE_CASE = false;