ADA-C01 Question 115
Single answerSecure and Integrate the Snowflake SQL APIA company is building an internal web application that must submit ad hoc SQL statements to Snowflake from a middle-tier service using the Snowflake SQL API. The security team requires that no Snowflake passwords be stored in the application and that access tokens be short-lived and centrally managed by the identity provider. The application must also ensure that users cannot switch to unauthorized roles when submitting requests. Which approach best meets these requirements?
- A
Use OAuth with the Snowflake SQL API, have the identity provider issue short-lived access tokens for approved users, and configure role scoping so the token can only be used with authorized Snowflake roles.
- B
Use basic authentication with a Snowflake username and password stored in the middle-tier service, and restrict role changes by setting a default role on the user account.
- C
Use key pair authentication directly with the Snowflake SQL API from the web application, because key pair authentication eliminates the need for OAuth tokens and prevents role switching automatically.
- D
Use a long-lived OAuth refresh token in the browser and let the client application request any Snowflake role dynamically at runtime, because role enforcement occurs only after SQL execution.
Show answer and explanation
Correct answer: A
Explanation
For the Snowflake SQL API, the most secure and operationally appropriate pattern in this scenario is OAuth-based authentication with short-lived access tokens issued by an external identity provider or security integration. This avoids embedded Snowflake passwords, supports centralized identity lifecycle management, and aligns with modern zero-trust and least-privilege practices. Snowflake supports OAuth with the SQL API, and administrators can configure security integrations and role restrictions so that tokens are valid only for approved roles. That is the key control for preventing unauthorized role switching.
Why the other choices fail:
- Basic authentication conflicts with the requirement to avoid stored passwords and is weaker operationally for web applications.
- Key pair authentication is not the best answer for the SQL API security pattern described, especially given the explicit requirement for centrally managed short-lived tokens from the identity provider.
- Long-lived browser-held refresh tokens and unrestricted role requests weaken security and violate least-privilege design.
Relevant Snowflake best-practice areas include SQL API authentication patterns, OAuth security integrations, and role-based access control. Administrators should prefer token-based delegated auth for application integration, limit token scope, and ensure the roles available through the token are explicitly controlled.
- A. Correct.
Correct. The Snowflake SQL API supports OAuth for authentication, which is the best fit when the requirement is to avoid storing Snowflake passwords and to use short-lived, centrally managed tokens from an identity provider. In addition, OAuth integrations in Snowflake can restrict which roles may be used, helping prevent users or applications from switching to unauthorized roles. This aligns with enterprise security practices for delegated authentication and least privilege.
- B. Incorrect.
Incorrect. Basic authentication requires storing and transmitting a Snowflake username and password, which directly violates the requirement to avoid stored Snowflake passwords. Also, relying only on a user's default role does not adequately prevent role switching if the session has access to additional roles. This option reflects a common misconception that setting a default role is sufficient for role governance.
- C. Incorrect.
Incorrect. Key pair authentication is supported for Snowflake clients and programmatic access in some contexts, but the Snowflake SQL API is designed to use token-based authentication such as OAuth or programmatic access tokens rather than direct username/password or assuming key pair authentication is the preferred security control here. More importantly, this option incorrectly claims that key pair authentication automatically prevents role switching; role authorization is still governed by Snowflake role grants and token or session constraints.
- D. Incorrect.
Incorrect. Storing a long-lived refresh token in a browser is generally a poor security practice for sensitive enterprise data access because it increases the risk of token theft. In addition, allowing the client to request any role dynamically conflicts with the requirement to prevent unauthorized role switching. The statement that role enforcement occurs only after SQL execution is false; role authorization is evaluated before statements are executed.