Configure OAuth with write access

Sigma allows you to leverage the benefits of OAuth permission management with write-access features like input tables, warehouse views, workbook materializations, and CSV uploads.

This document explains how to configure an OAuth connection to enable secure and efficient write-back workflows for Sigma objects (created or edited using write-access features). For more information about OAuth and general usage, see OAuth with Snowflake.

System and user requirements

The ability to configure an OAuth connection with write access requires the following:

OAuth must be configured as an authentication method for your organization.
You must be assigned the Admin account type.
You must be able to provide the schema paths of all write destinations to be used by Sigma’s write-access features (including the schema path for the input table edit log destination, if utilizing input tables).
If your organization utilizes input tables, you must also be able to provide credentials for a Snowflake service account with permission to write to the edit log destination.

About OAuth with write-access features

To use an OAuth connection with write-access features, Sigma requires you to designate one or more Snowflake schemas as write destinations for Sigma object data. Since OAuth enables Sigma to leverage data permissions defined in Snowflake, individual users can only create and edit Sigma objects written to destinations they're authorized to write to in Snowflake.

To determine whether or not a user is authorized to write to a particular destination, Sigma utilizes the user's corresponding Snowflake user account (not a service account) to perform a backend validation process that attempts to create a table in each write destination schema. If a table is successfully created, write access is confirmed for the schema and the validation table is deleted. Sigma initiates the validation process when the user logs in, and the authorized write destinations are cached for the duration of the session to reduce the frequency of Snowflake queries.

🚧
Tables created by the write access validation process can be easily identified by object names prepended with SIGDS. To ensure proper functionality of write-access features, avoid modifying any Snowflake table with the SIGDS prefix.

When a user is only authorized to write to one Snowflake schema designated as a write destination for the OAuth connection, the Sigma objects they create are written to that destination by default. Otherwise, Sigma allows the user to select from multiple destinations when creating the object.

💡
Write permissions can be granted to users through their primary or secondary roles in Snowflake. However, the ability to create objects must be granted through the primary role.

Write-back architecture for warehouse views, materializations, and CSV uploads

The following steps explain how an OAuth connection enables Sigma to write workbook warehouse views, workbook materializations, and CSV uploads to Snowflake (for information about writing input tables, see About OAuth with input tables in this document):

In the Sigma UI, a user creates or edits a Sigma object with a specified write destination.
The Sigma UI sends the object data to Sigma’s web service.
Sigma’s web service retrieves the user’s OAuth credentials from the Sigma database.
Sigma’s web service applies the user’s OAuth credentials to authorize Snowflake access and write the object data to the specified write destination schema.

About OAuth with input tables

When your organization utilizes input tables, enabling OAuth with write access requires you to designate a Snowflake schema as the input table edit log destination.

The edit log (also known as a write-ahead log or WAL) is a sequential record of input table changes that stores information related to user activity and resulting system operations (including input table data stored as edit records). As an internal database mechanism, the edit log ensures data durability, consistency, and recovery.

Sigma also requires you to provide credentials for a service account granted CREATE privilege on the edit log destination schema in Snowflake. For data governance purposes, ensure the service account is the only Snowflake user account with permission to write to the edit log destination.

🚧
The edit log's corresponding Snowflake table and all input tables can be easily identified by object names prepended with SIGDS. To ensure proper input table functionality, avoid modifying any table with the SIGDS prefix.

For more information about enabling and using a service account with an OAuth connection, see OAuth with Snowflake.

Write-back architecture for input tables

The following steps explain how an OAuth connection enables Sigma to write input tables to Snowflake:

In the Sigma UI, a user creates or edits an input table with a specified write destination.
The Sigma UI sends the object data to Sigma’s web service.
Sigma’s web service retrieves the connection’s service account credentials from the Sigma database.
Sigma’s web service applies the service account credentials to authorize Snowflake access and write input table change information to the edit log destination schema.
Sigma’s web service retrieves the user OAuth credentials from the Sigma database.
Sigma’s web service applies the user OAuth credentials to authorize Snowflake access and write the object data to the specified write destination schema.

Best practices when upgrading to OAuth with input tables

If your organization utilizes input tables, it's important to note the following information and best practices when you upgrade a non-OAuth connection to use OAuth.

Input tables written to the connection before the upgrade can still be viewed in Sigma, regardless of whether the previous destination schema is configured as a new write destination. However, users can only continue writing to existing input tables if they're granted write permission to the previous destination schema in Snowflake.
For a seamless transition using OAuth, the edit log destination must be the same schema path that was configured as the connection's write-back destination before the upgrade. If you need to configure a different schema path as the edit log destination, you must also move the edit log's corresponding Snowflake table to the new destination.
- To move the edit log table to a new destination, use the following SnowSQL:
```
ALTER TABLE {original_db}.{original_schema}.{original_table} RENAME TO {new_db}.{new_schema}.{new_table};
```
- Each connection has a single edit log table, named as follows: SIGDS. If multiple connections use the same edit log destination, that destination schema can contain multiple tables with the SIGDS_WAL prefix. Ensure you move the correct edit log table for the specific connection.