Snowflake Secrets Explained

Learn how to use Snowflake Secrets to Improve Security.

Photo by Kristina Flour on Unsplash

Secrets, secrets, and secrets are always the question. How do we manage secrets for a platform? Typically, in SaaS, we will give them our secrets into other platforms, and often, the more security-focused one will create two object types, configuration, and secrets, which are usually not readable on the screen, etc. Sometimes, secrets are a one-time save.

Snowflake has spent the last few years, but particularly the last year, introducing many features related to application development and external access to other APIs, so how do we handle secrets in those cases? All those passwords and API keys are not in a public cloud, and Snowflake has robust integrations that are super safe through the use of Cloud AIM.

Snowflake just released a neat new feature that allows saving Secrets as an object as part of the External API Authentication feature.

Snowflake Secrets

Let’s learn what Secrets in Snowflake are and how to use them first.

What is a Secret in Snowflake?

• A secret is an object at the schema level that stores sensitive information
• Limits access to the sensitive information using role-based access control (RBAC)
• Encrypted using Snowflake’s key encryption hierarchy
• When a secret object is created, its information is encrypted using a key from the key hierarchy
• Only specific Snowflake components, such as integrations and external functions, have access to read the sensitive data
• If a user runs DESCRIBE SECRET, the secret is not exposed

Things to keep in mind:

• CREATE command creates the secret
• USAGE command provides access to the secret and enables its use
• OWNERSHIP transfers the secret to a new owner and provides full control. This role is required if you need to change properties to it

Secrets support four authentication types with examples:

• OAuth with client credentials flow

CREATE OR REPLACE SECRET oauth_token
    TYPE = OAUTH2
    API_AUTHENTICATION = buffer_oauth
    OAUTH_SCOPES = ( '<scope_1>' [, '<scope_2>' ...] )
    [COMMENT = '<string_literal>']

• OAuth with authorization code grant flow

CREATE OR REPLACE SECRET oauth_token
  TYPE = OAUTH2
  API_AUTHENTICATION = google_oauth
  OAUTH_REFRESH_TOKEN = 'Refresh Token from Google OAUTH Developers Playarea';

GRANT READ ON SECRET oauth_token TO ROLE DEV_ROLE;

• Basic authentication

CREATE OR REPLACE SECRET secret_officeapi
    TYPE = PASSWORD
    USERNAME = 'myusername'
    PASSWORD = 'mypassword'
    [COMMENT = 'Secret username and password used to access our office API']

• Generic string

CREATE OR REPLACE SECRET secret_officeapi
    TYPE = GENERIC_STRING
    SECRET_STRING = 'XXHHXXHHXXXJJHAKK'
    [COMMENT = 'used to connect to our secret office API that only uses a Generic String']

How and Where Are Secrets Used in Snowflake

You can use secrets when creating external access integration or a UDF or procedure handler. These are all ways to access external-facing services outside Snowflake.

The point here is that a Developer does not need to hard code secrets into the code when deploying as a Snowflake application or creating a UDF, for example.

Secrets cannot be viewed anymore, but let’s try a few things:

-- Let's look inside
USE ROLE ACCOUNTADMIN;
SHOW SECRETS IN ACCOUNT;
DESC SECRET OAUTH_TOKEN;

USE ROLE ICEBERG_LAB;
SHOW SECRETS IN ACCOUNT;
DESC SECRET OAUTH_TOKEN;

You can see that the owner ROLE can see SECRETS listed, but it cannot open the SECRET’s contents.

Then, if I use any other role that does not have access, it seems nothing, no lists of SECRETS or content inside the SECRET.

Conclusion

Snowflake secrets are used so that we are not hard coding external passwords inside Snowflake, for example, when creating a UDF that accesses external services like an API or a serverless service.

From a security risk perspective, it is a great implementation as you can use the Secret without ever having the ability to read it in the clear when using across the platform.

The latest use case would be Snowflake Applications, as many of the initial use cases are around Data Integration and require connections to external services.

I’m Augusto Rosa, Snowflake Data Super Hero, Snowflake SME, and VP of Engineering for Infostrux Solutions. Thanks for reading my blog post. You can follow me on LinkedIn or my Blog Posts.

Subscribe to Infostrux Medium Blogs https://medium.com/infostrux-solutions for the most interesting Data Engineering and Snowflake news.

Sources:

• https://docs.snowflake.com/en/user-guide/api-authentication
• https://docs.snowflake.com/en/sql-reference/sql/create-secret
• https://docs.snowflake.com/en/developer-guide/external-network-access/external-network-access-examples

---