OAuth 2.0 for Snowflake

Prerequisites

OAuth 2.0 authentication must be enabled in the Designer Cloud Powered by Trifacta platform. For more information, see Enable 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.

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.
   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 Designer Cloud 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 Designer Cloud application:

   Snowflake parameter	Designer Cloud 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 Designer Cloud application:

   Snowflake parameter	Designer Cloud 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 Designer Cloud application, which is used to integrate with the OAuth 2.0 Client app (security integration) that you created above.

Steps:

1. Login to the Designer Cloud 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>

Create Snowflake Connection

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

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 Designer Cloud application.

You can do one of the following:

• Specify a different user in the connection.

• Create a new OAuth 2.0 client in the Designer Cloud 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.