Using OAuth 2.0 with Snowflake

Author: Tyler Jones | Contributing Authors: Harsha Kapre

How to Use Snowflake, Snowflake Technology

Snowflake is pleased to announce general availability of OAuth 2.0 support. This feature is available to all accounts across all service levels.

What is OAuth 2.0?

OAuth 2.0 is an industry-standard protocol for securing the authorization of web APIs. It is a mechanism for allowing users to grant web services, third parties, or applications (e.g. a BI tool) access to their data. Because Snowflake is a cloud-built web service, it uses internet protocols for both network communication and security. OAuth is a natural fit for such services and enables support for user-centric access controls and token persistence options regardless of whether the user is present or a connection is made in the background on behalf of the user.

Prior to OAuth support in Snowflake, third-party services and applications needed to store user credentials in order to access Snowflake on a user’s behalf. This requirement often resulted in scenarios in which customers would configure applications with a generic, shared, or system user, and then grant roles and resources to that user. This process resulted in weakened security from both a credentials and role-based access control (RBAC) point of view because the rights of the generic user governed data access. Furthermore, it was difficult to accurately audit activity, because all activity was tied to that single user.

With OAuth as implemented by Snowflake, a client no longer needs to know or store database credentials because a token is used for all processes and actions that access Snowflake. Access tokens are issued tied to a user and the tokens are fixed to a role that has resources and privileges granted to it. Administrators can view these grants and revoke access to them at any point, thereby cutting off access immediately without changing anything specific to the user in Snowflake.

Snowflake’s OAuth 2.0 Implementation

Although the OAuth 2.0 specification describes a few protocol flows, Snowflake implements only the Authorization Code flow, which is intended primarily for server-to-server applications or services. At a high level, the OAuth 2.0 Authorization Code flow specifies that the client (e.g. a BI tool) requesting access to resources (e.g. Snowflake Table) be able to direct users to an authorization endpoint (Snowflake Authorization Server). After authenticating either through a username and password or through federated authentication, the user authorizes access to the specified resources. The client then provides an endpoint known as the redirect URI where users are directed after authorization. The client then receives  a short lived authorization code which can be exchanged for an access token and, optionally, a refresh token by making a request to a token endpoint. A refresh token can be used to obtain additional access tokens to facilitate long-term and offline access.

Setting Up an OAuth 2.0 Security Integration

To start using OAuth, an administrator needs to first configure an OAuth security integration. Integrations are a new type of account-level object in Snowflake administrators can use to extend functionality between Snowflake and other systems. Only the ACCOUNTADMIN role can create and manage integrations, so an administrator must assume that role when creating a security integration for OAuth.

An administrator can configure OAuth security integrations with a number of options, such as whether to issue refresh tokens, the time to live of the refresh tokens, what roles cannot be authorized, IP whitelisting via network policies to limit IP addresses that can perform token requests, and Proof Key for Code Exchange (PKCE) support. Minimally, the administrator must configure the OAuth security integration with the following:

  • The redirect URI, which is the URI that Snowflake redirects the user to after they perform authorization with a short-lived authorization code.
  • The client type. Snowflake supports both confidential and public clients. Confidential clients are those that maintain secrets, whereas public clients cannot. Each client type has different behavior with respect to authorizations; the key difference is that confidential clients will not prompt the user to authorize a role if a previous authorization exists, whereas a public client will always prompt for authorization.

The command for creating a simple OAuth security integration looks like this:

CREATE SECURITY INTEGRATION my_oauth_integration

    TYPE=OAUTH

    OAUTH_CLIENT= OAUTH_CUSTOM

    OAUTH_REDIRECT_URI=’https://myredirecturi.com/oauth_callback’

    OAUTH_CLIENT_TYPE=’CONFIDENTIAL’

    ENABLED=true;

 

In this example, the command above creates an active OAuth security integration, which is intended to be a confidential client, with the redirect URI “https://mydirecturi.com/oauth_callback. The ENABLED option indicates that this is an active integration; the administrator can disable the integration at any point by setting it to FALSE, as follows:

ALTER SECURITY INTEGRATION my_oauth_integration SET ENABLED=FALSE;

Configuring the Client Application or Service

After an administrator creates the security integration, as the ACCOUNTADMIN user, the administrator needs to configure the client application or service with the client ID and client secret(s) tied to that security integration.

The administrator can find the client ID by describing the integration, that is, by using the DESC SECURITY INTEGRATION command. The administrator can find the client secrets by using the system function SHOW_OAUTH_CLIENT_SECRETS. This function reveals two client secrets, as well as the client ID again for verification purposes.

The function supplies two client secrets so the application or service being configured can rotate client secrets without any downtime. Although Snowflake does not enforce client secret rotation or automatically rotate client secrets, periodically rotating these secrets is a best practice.

To rotate a client secret, the administrator can use the ALTER SECURITY INTEGRATION data definition language (DDL):

ALTER SECURITY INTEGRATION my_oauth_integration REFRESH OAUTH_CLIENT_SECRET;

ALTER SECURITY INTEGRATION my_oauth_integration REFRESH OAUTH_CLIENT_SECRET_2;

Specifying Additional Parameters

After the administrator has properly configured the client, the client can start directing users to the security integration’s authorization URL so that authorization grants can be obtained after a user successfully authorizes the client with Snowflake. For convenience, the output of the DESC SECURITY INTEGRATION provides this URL, but the URL needs to be amended to contain the following parameters:

  • client_id: The client ID that the client was configured with.
  • response_type: The type of response expected. Because Snowflake supports only the Authorization Code flow, this must be set to code.
  • redirect_uri: The URI the user should be directed to after successfully authorizing the client. This needs to be the same URI the administrator configured the integration with.

Additionally, Snowflake highly recommends the following optional parameters:

  • state: A string no more than 2,048 ASCII characters, which is intended to be used for preventing cross-site request forgery (CSRF) attacks.
  • code_challenge: If the administrator enabled PKCE for the security integration, this is the challenge for PKCE.
  • code_challenge_method: If the administrator enabled PKCE for the security integration, this is a string indicating the method used to derive the code challenge.
  • scope: A string used to limit the operations and role permitted by the access token issued.

The scope parameter allows the client to request a role, which controls access to a set of resources within Snowflake, as well as to specify whether a refresh token should be issued so the client can perform operations offline on behalf of the user for that role.

If no role is specified in the scope parameter, the user’s default role will be used by the application or service when it connects to the user’s Snowflake account. If a default role does not exist, the PUBLIC role, which every user has access to, will be used. If the client requests a role that the user does not have access to, no tokens will be issued and the user will not be able to establish a connection to their Snowflake account.

If the refresh token scope is not requested, only an access token is issued, which is ideal for clients initializing a Snowflake session in which the user is present.

For example, after the user successfully authenticates, if the requesting client wants access to the PUBLIC role and wants refresh tokens to be issued, the user sees an authorization page similar to the following:

After the User Authorizes the Application or Service

After the user authorizes the application or service, Snowflake returns an authorization code as well as the state the application or service supplied (if they chose to do so) to the security integration’s redirect URI. The client is then responsible for exchanging this authorization code with the Snowflake resource server by sending a request to the token endpoint containing the code, and in response the client receives an access token and, optionally, a refresh token.

The access token can then be passed to one of Snowflake’s client drivers by using the OAuth authenticator. Currently, client driver support is limited to JDBC, ODBC, Python, and Go. The client driver then creates a session that is fixed to the user and the role that was authorized. The role cannot be changed by the application or service after a connection is established, and attempting to establish a connection with a role other than what was authorized will result in an error. After the database session is created, you can see via the Login History View that a session was created via OAuth, which allows for greater auditability.

You can view the grants that have been issued for different users and roles using the SHOW DELEGATED AUTHORIZATIONS DDL. This DDL can be further broken down by integrations and users, providing insight into what users are authorizing what roles. The administrator can revoke authorizations at any time via the ALTER USER DDL commands. Doing so will disallow new sessions to be created with any outstanding access and refresh tokens; the client then must direct the user through the authorization flow to obtain new tokens. Administrators can also disable an integration or drop it entirely.

Summary

OAuth 2.0 is a powerful security feature with a number of benefits, including allowing a client to access Snowflake on a user’s behalf without passing explicit user credentials, enforcing fine-grained access controls specific to that user and role, full auditability of client actions, and allowing long-lived access by the client to act on the user’s behalf while giving administrators clear insight into how authorizations are being used. This feature can be used in a number of scenarios and allows cloud-native applications to more seamlessly integrate with Snowflake.

Acknowledgements

A number of individuals have contributed to the OAuth project. Specifically, we want to thank Jason Benterou, Johnston Chu, Vikas Jain, Gillian Maducdoc, Peter Provinec, and Qin Yang for their countless contributions and guidance for this project.