Skip to main content

OAuth 2.0 for Snowflake

Configure the Trifacta Application to integrate with your Snowflake deployment using OAuth 2.0 to authenticate.

Prerequisites

OAuth 2.0 authentication must be enabled in theDesigner Cloud Powered by Trifacta platform. For more information, seeEnable OAuth 2.0 Authentication.

Create OAuth 2.0 Client App in Snowflake

In your Snowflake console, you must create the client app, which includes execution of several SQL statements.

Note

You must have the ACCOUNTADMIN role to create the client app.

In Snowflake, this object is called a security integration. For more information, see https://docs.snowflake.com/en/sql-reference/sql/create-security-integration.html.

Steps:

  1. Login to the Snowflake console as an account admin.

  2. Click Worksheets.

  3. For your role, select ACCOUNTADMIN.

  4. Paste the following command in the worksheet and modify its parameters:

    CREATE [ OR REPLACE ] SECURITY INTEGRATION [IF NOT EXISTS]
         <NAME>
         TYPE = OAUTH
         OAUTH_CLIENT = CUSTOM
         OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
         OAUTH_REDIRECT_URI = '<URI>'
         ENABLED = TRUE
         OAUTH_ALLOW_NON_TLS_REDIRECT_URI = FALSE
         [PRE_AUTHORIZED_ROLES_LIST = ( '<role_name_1>' [ , '<role_name_2>' , ... ]) ]
         [ BLOCKED_ROLES_LIST = ( '<role_name_3>' [ , '<role_name_4>' , ... ] ) ]
         OAUTH_ISSUE_REFRESH_TOKENS = TRUE
         OAUTH_REFRESH_TOKEN_VALIDITY = 7776000 (90 Days)
         [ NETWORK_POLICY = '<network_policy>']
         [ COMMENT = '<Description of your Integration>' ]

    Parameter

    Description

    <NAME>

    Name of the integration. Example: OAuth 2.0 Client

    <URI>

    Callback URI of the Designer Cloud Powered by Trifacta platform.

    https://<platform_url>:<port_number>/oauth2/callback

    PRE_AUTHORIZED_ROLES_LIST

    A comma-separated list of Snowflake roles that do not need user consent when accessing Snowflake. The roles SECURITYADMIN and ACCOUNTADMIN cannot be included in this list.

    Tip

    The roles in this list should match up with the roles that are scoped in the OAuth 2.0 client in the Trifacta Application. In the client, you can specify the Snowflake roles that are permitted to use the client for authentication. Roles that are scoped for access that are not in this list must consent to access Snowflake after login. In some use cases, such as API access or scheduled executions, this can be problematic.

    BLOCKED_ROLES_LIST

    A comma-separated list of Snowflake roles that cannot explicitly consent to use when accessing Snowflake. The roles SECURITYADMIN and ACCOUNTADMIN are included by default in this list. If you need to remove either of those roles, please contact Snowflake Support.

    <NETWORK_POLICY>

    (Optional) Provide the identifier for any applicable Snowflake network policy.

    <COMMENT>

    (Optional) Add a comment if needed.

  5. Run the above command. The security integration is created.

  6. Paste the following command and run it to acquire the following information: Client ID, Authorization URL, Token URL, and Refresh Token Expires In, where <NAME> Is the name you provided above:

    DESC integration <NAME>

    Retain the values for the following parameters. You must apply these parameters to the OAuth 2.0 client that you create in the Trifacta Application:

    Snowflake parameter

    Trifacta Application Client parameter

    OAUTH_CLIENT_ID

    Client Id

    OAUTH_AUTHORIZATION_ENDPOINT

    Authorization URL

    OAUTH_TOKEN_ENDPOINT

    Token URL

    OAUTH_REFRESH_TOKEN_VALIDITY

    Refresh Token Expires In

  7. Paste the following command and run it to acquire the client secret, where <NAME> Is the name you provided above:

    SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('<NAME>')

    Retain the values for the following. You must apply these parameters to the OAuth 2.0 client that you create in the Trifacta Application:

    Snowflake parameter

    Trifacta Application Client parameter

    OAUTH_CLIENT_SECRET

    Client Secret

  8. Save your changes.

Create OAuth 2.0 Client for Snowflake

After the Snowflake client app is created, you must create an OAuth 2.0 client in the Trifacta Application, which is used to integrate with the OAuth 2.0 Client app (security integration) that you created above.

Note

You must create one OAuth 2.0 client in the Trifacta Application for each Snowflake role that you wish to use. See "Scopes" below for more information.

Steps:

  1. Login to the Trifacta Application as a workspace administrator.

  2. In the lefthand menu, select User menu > Admin console > OAuth 2.0 Clients.

  3. In the OAuth 2.0 Clients page, click Register OAuth 2.0.0 Client.

  4. Specify the new client.

    1. For the Type value, select snowflake.

    2. You must apply the values listed in the previous section to your client object.

    3. For more information on Scopes, see "Scopes for Snowflake" below.

    4. Access Token Expires in: 600000

      Note

      The value of 600000 is required for Snowflake.

  5. To save your OAuth 2.0 client, click Save.

For more information, see Create OAuth2 Client.

Scopes for Snowflake

Scopes are space-delimited strings that are passed from the client to the client app as part of the authentication process.

The following scope must be specified as part of your Snowflake client definition:

refresh_token session:role:<role_name>

Scope

Description

refresh_token

(required) Snowflake session tokens have a short duration. By adding this scope, a refresh token is issued for the session. This token allows the OAuth 2.0 client to refresh the connection with Snowflake without user interaction.

role:<role_name>

(optional) The Snowflake role for which you wish to access its databases, schemas, and tables. If this value is not provided, then the default role is used.

Note

Only one role can be specified per client. This role must provide access to the databases, schemas, and objects that you wish to make accessible through this client.

Note

The value for <role_name> is case-sensitive, unless you specified the role in quotes when creating it. For more information, see https://docs.snowflake.com/en/user-guide/oauth-custom.html#scope.

Create Snowflake Connection

After you have created the two OAuth 2.0 client references, you can create a connection to your Snowflake databases.

Note

You must create a separate connection for each OAuth 2.0 client that is available in the Trifacta Application.

For more information, see Snowflake Connections.

Troubleshooting

The following may occur when trying to connect to Snowflake databases using OAuth 2.0.

"Invalid consent request" error

If you receive an invalid consent request error, then the user that is passed for OAuth 2.0 authorization does not have access to the role that is referenced in the corresponding OAuth 2.0 client that you created in the Trifacta Application.

You can do one of the following:

  • Specify a different user in the connection.

  • Create a new OAuth 2.0 client in the Trifacta Application which is scoped for a role that the database user has.

    Note

    This new role must also be authorized to use the security integration within Snowflake.

In this section: