Database permissions for Snowflake

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 source database user only needs USAGE access to the database.

The below example:

1. Creates a role.

2. Grants the role USAGE access to a specified warehouse and to an external stage (if one is used).

3. Grants the role USAGE data access to:

   1. The database.

   2. All current and future schemas, tables, and sequences.

4. Creates a user and assigns it to that role.

5. Sets QUOTED_IDENTIFIERS_IGNORE_CASE = false. This must be set at either the account or user level.

CREATE ROLE TONIC_SOURCE_DATABASE_ROLE;

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

-- If using a source external stage
GRANT USAGE ON STAGE <source stage 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>;

-- You must set this parameter at either the account or user level.
ALTER USER <user name> set QUOTED_IDENTIFIERS_IGNORE_CASE = false;

User permissions on the destination database

The destination database must exist before Structural can connect to it.

For the user that you provide to Structural for the destination database connection, the required role permissions depend on who creates the destination database schema.

• If Structural creates the destination database schema, then the destination database user must have OWNERSHIP privileges on all of the destination database objects.

• If you create the destination database schema, then the destination database user requires:

  • USAGE privileges on the destination database and schemas.

  • SELECT, TRUNCATE, and INSERT on the destination database tables.

Creating the role and user

The following example:

1. Creates the destination database role.

2. Grants read-only (USAGE) access to the warehouse and to an external stage (if one is used).

3. Creates the destination database user and assigns it to the role.

4. Sets QUOTED_IDENTIFIERS_IGNORE_CASE = false. This must be set at either the account or user level.

CREATE ROLE TONIC_DESTINATION_DATABASE_ROLE;

GRANT USAGE ON WAREHOUSE <warehouse name> TO TONIC_DESTINATION_DATABASE_ROLE;
-- If using a destination external stage
GRANT USAGE ON STAGE <destination stage name> TO ROLE 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>;

-- You must set this parameter at either the account or user level.
ALTER USER <user name> set QUOTED_IDENTIFIERS_IGNORE_CASE = false;

Grant permissions - Structural creates the destination database schema

If Structural creatse the destination database schema, then during a Structural data generation job, the destination database user must be able to:

• DROP and then create schemas on the destination database.

• Copy data from the temporary storage into tables in the destination database.

Because of this, it must have ownership of the destination database.

We suggest that you create the destination database from the destination database 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 destination database user account.

The following example grants to the destination database role:

• OWNERSHIP of the destination database.

• OWNERSHIP of the database schemas. This is to accommodate the case where a different user created the database.

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

-- If the destination database was already created with another user, 
-- you must transfer ownership of existing schemas (including default PUBLIC)
-- 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;

Grant permissions - You create the destination database schema

If you create the destination database schema, then during data generation, Structural:

1. Truncates the destination database tables.

2. Copies data from the temporary storage into the destination database tables.

The following example grants to the destination role:

• USAGE access to the destination database.

• USAGE access to the destination database schemas.

GRANT USAGE ON DATABASE <destination database name> TO ROLE TONIC_DESTINATION_DATABASE_ROLE;

GRANT USAGE ON ALL SCHEMAS IN DATABASE <destination database name> TO ROLE TONIC_DESTINATION_DATABASE_ROLE;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE <destination database name> TO ROLE TONIC_DESTINATION_DATABASE_ROLE;

The destination database user also requires SELECT, TRUNCATE, and INSERT on the destination database tables.

The following example grants this required access to the role.

GRANT SELECT ON ALL TABLES IN DATABASE <destination database name> TO ROLE TONIC_DESTINATION_DATABASE_ROLE;
GRANT SELECT ON FUTURE TABLES IN DATABASE <destination database name> TO ROLE TONIC_DESTINATION_DATABASE_ROLE;

GRANT TRUNCATE ON ALL TABLES IN DATABASE <destination database name> TO ROLE TONIC_DESTINATION_DATABASE_ROLE;
GRANT TRUNCATE ON FUTURE TABLES IN DATABASE <destination database name> TO ROLE TONIC_DESTINATION_DATABASE_ROLE;

GRANT INSERT ON ALL TABLES IN DATABASE <destination database name> TO ROLE TONIC_DESTINATION_DATABASE_ROLE;
GRANT INSERT ON FUTURE TABLES IN DATABASE <destination database name> TO ROLE TONIC_DESTINATION_DATABASE_ROLE;