How to Manage Secrets in Snowflake
- Date
Jeff SkoldbergPrincipal Consultant at Green Mountain Data Solutions
What are Snowflake Secrets?
In Snowflake, a Secret is a schema level object that allows you to store sensitive information such as API Keys or Passwords.
Why does this feature exist?
Recently, Snowflake has rolled out many features that enable more application and data pipeline development directly inside of Snowflake:
- Snowpark Container Services enables users to run custom applications in any programming language.
- Snowpark for Python is becoming a popular choice for data engineering tasks, including entire ELT/ETL pipelines.
- We can now send alerts via email, Slack, or Microsoft Teams (via webhook).
- Snowflake now allows API calls to the public internet; many users are excited to use OpenAI APIs in their Snowflake account.
All of these use cases require storing and accessing secrets to authenticate to various applications and services.
Advantages to Managing Secrets in Snowflake.
- Secrets created in Snowflake use the same Snowflake RBAC process we are already comfortable with (i.e.
grant usage on secret to role <role>). - The secret lives along side your application or pipeline code; no additional system to manage.
- It would be challenging to use an external secrets manager (such as AWS Secrets Manager), without first having a streamlined way to authenticate to that secrets manager.
How to create a Snowflake Secret
There are 4 types of secrets that can be created in Snowflake and the DDL varies slightly by type.
OAuth:
Syntax
CREATE [ OR REPLACE ] SECRET [ IF NOT EXISTS ] <name>
TYPE = OAUTH2
API_AUTHENTICATION = <security_integration_name>
OAUTH_SCOPES = ( '<scope_1>' [ , '<scope_2>' ... ] )
[ COMMENT = '<string_literal>' ]
Example
-- Assume an integration exists:
CREATE OR REPLACE SECURITY INTEGRATION my_oauth2_integration
TYPE = API_AUTHENTICATION
AUTH_TYPE = OAUTH2
ENABLED = TRUE
OAUTH_CLIENT_ID = 'your_client_id'
OAUTH_CLIENT_SECRET = 'your_client_secret'
OAUTH_TOKEN_ENDPOINT = 'https://oauth2.example.com/token'
OAUTH_ALLOWED_SCOPES = ('scope1', 'scope2');
-- create the secret
CREATE OR REPLACE SECRET my_oauth2_secret
TYPE = OAUTH2
API_AUTHENTICATION = my_oauth2_integration
OAUTH_SCOPES = ('scope1', 'scope2')
COMMENT = 'This is a test secret for OAuth2 integration';
Cloud Provider:
Syntax:
CREATE [ OR REPLACE ] SECRET [ IF NOT EXISTS ] <name>
TYPE = CLOUD_PROVIDER_TOKEN
API_AUTHENTICATION = '<cloud_provider_security_integration>'
[ COMMENT = '<string_literal>' ]
Example:
-- assume this integration exists called test_integration:
CREATE OR REPLACE SECRET test_secret
TYPE = CLOUD_PROVIDER_TOKEN
API_AUTHENTICATION = 'TEST_INTEGRATION';
Basic Auth:
Syntax:
CREATE [ OR REPLACE ] SECRET [ IF NOT EXISTS ] <name>
TYPE = PASSWORD
USERNAME = '<username>'
PASSWORD = '<password>'
[ COMMENT = '<string_literal>' ]
Example:
CREATE OR REPLACE SECRET my_password_secret
TYPE = PASSWORD
USERNAME = 'db_user'
PASSWORD = 'MySecurePass123!'
COMMENT = 'Database credentials for application access';
Generic String:
Syntax:
CREATE [ OR REPLACE ] SECRET [ IF NOT EXISTS ] <name>
TYPE = GENERIC_STRING
SECRET_STRING = '<string_literal>'
[ COMMENT = '<string_literal>' ]
Example:
CREATE OR REPLACE SECRET my_string_secret
TYPE = GENERIC_STRING
SECRET_STRING = 'my-secret-api-key-123'
COMMENT = 'API key for service authentication';
How to use a Snowflake Secret in your code?
Secrets can be fetched using Java or Python only. If you are hosting an application in Snowpark Container Services using another programming language, you will need a small custom module in Java or Python to fetch the secret, then pass the value back to your application.
Here is an example to fetch a secret using Python.
Step 1: Create a function to fetch secrets by name
CREATE OR REPLACE FUNCTION get_secret_by_name(secret_name STRING)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = 3.9
HANDLER = 'get_secret_by_name'
EXTERNAL_ACCESS_INTEGRATIONS = (external_access_integration)
AS
$$
import _snowflake
def get_secret_by_name(secret_name):
username_password_object = _snowflake.get_username_password(secret_name)
username_password_dictionary = {}
username_password_dictionary["Username"] = username_password_object.username
username_password_dictionary["Password"] = username_password_object.password
return username_password_dictionary
$$;
Step 2: Call the function:
SELECT get_secret_by_name('jeffs_credentials');
Using Secrets in Notification Integrations / Webhooks:
Snowflake provides a user friendly way to pass a secret to Notification Integrations for purposes of sending alerts to webhooks (Slack, Microsoft Teams, etc.)
Simply pass the fully qualified database.schema.<secret_name> to the WEBHOOK_SECRET argument of the create notification integration statement. For example:
CREATE OR REPLACE NOTIFICATION INTEGRATION my_slack_webhook_int
TYPE=WEBHOOK
ENABLED=TRUE
WEBHOOK_URL='https://hooks.slack.com/services/SNOWFLAKE_WEBHOOK_SECRET'
WEBHOOK_SECRET=my_secrets_db.my_secrets_schema.my_slack_webhook_secret
WEBHOOK_BODY_TEMPLATE='{"text": "SNOWFLAKE_WEBHOOK_MESSAGE"}'
WEBHOOK_HEADERS=('Content-Type'='application/json');
Managing Secrets in Snowflake
Show All Secrets
You can show all secrets using the show secrets command.
show secrets; -- shows all secrets in account
show secrets in account; -- verbose way of `show secrets`
show secrets in database analytics; -- shows secrets for a database named analytics
Delete a Secret
You can delete a secret using the drop command:
drop secret my_oauth2_secret; -- this works if your worksheet or connection has a database and schema context set.
drop secret <fully qualified secret name> -- this will always work
Alter a Secret
Alter secret is used to rotate keys or passwords, or alter any other aspect of the secret.
alter secret <fully qualitfied secret name> set <parameter> = <value>;
Describe a Secret
Describing a secret using describe secret <fully qualified secret name> will return the following fields:
- created on
- name
- schema_name
- database_name
- owner
- comment
- secret_type
- username
- oauth_access_token_expiry_time
- oauth_refresh_token_expiry_time
- oauth_scopes
- integration_name
Wrapping up
Managing secrets directly in Snowflake is a powerful way to streamline the use of sensitive information when developing applications, pipelines, or alerts in Snowflake.
In the next article, we will put this knowledge into action by creating a Slack alert in Snowflake.