# 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](https://docs.snowflake.com/en/sql-reference/sql/use-role.html#usage-notes).

## User permissions on the source database <a href="#snowflake-source-db-permissions" id="snowflake-source-db-permissions"></a>

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.

{% code overflow="wrap" %}

```sql
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;
```

{% endcode %}

## User permissions on the destination database <a href="#snowflake-destination-permissions" id="snowflake-destination-permissions"></a>

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](https://docs.tonic.ai/app/setting-up-your-database/snowflake/before-you-create-a-snowflake-workspace/snowflake-init-skip-db).

* 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.

{% code overflow="wrap" %}

```sql
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;
```

{% endcode %}

### 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.

{% code overflow="wrap" %}

```sql
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;
```

{% endcode %}

### 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.

{% code overflow="wrap" %}

```sql
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;
```

{% endcode %}

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.

{% code overflow="wrap" %}

```sql
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;
```

{% endcode %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.tonic.ai/app/setting-up-your-database/snowflake/before-you-create-a-snowflake-workspace/snowflake-database-permissions.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.