In Snowflake, a Secret is a schema level object that allows you to store sensitive information such as API Keys or Passwords.
Recently, Snowflake has rolled out many features that enable more application and data pipeline development directly inside of Snowflake:
All of these use cases require storing and accessing secrets to authenticate to various applications and services.
grant usage on secret to role <role>
).There are 4 types of secrets that can be created in Snowflake and the DDL varies slightly by type.
Syntax
1CREATE [ OR REPLACE ] SECRET [ IF NOT EXISTS ] <name>
2 TYPE = OAUTH2
3 API_AUTHENTICATION = <security_integration_name>
4 OAUTH_SCOPES = ( '<scope_1>' [ , '<scope_2>' ... ] )
5 [ COMMENT = '<string_literal>' ]
Example
1-- Assume an integration exists:
2CREATE OR REPLACE SECURITY INTEGRATION my_oauth2_integration
3 TYPE = API_AUTHENTICATION
4 AUTH_TYPE = OAUTH2
5 ENABLED = TRUE
6 OAUTH_CLIENT_ID = 'your_client_id'
7 OAUTH_CLIENT_SECRET = 'your_client_secret'
8 OAUTH_TOKEN_ENDPOINT = 'https://oauth2.example.com/token'
9 OAUTH_ALLOWED_SCOPES = ('scope1', 'scope2');
10
11-- create the secret
12CREATE OR REPLACE SECRET my_oauth2_secret
13 TYPE = OAUTH2
14 API_AUTHENTICATION = my_oauth2_integration
15 OAUTH_SCOPES = ('scope1', 'scope2')
Syntax:
1CREATE [ OR REPLACE ] SECRET [ IF NOT EXISTS ] <name>
2 TYPE = CLOUD_PROVIDER_TOKEN
3 API_AUTHENTICATION = '<cloud_provider_security_integration>'
4 [ COMMENT = '<string_literal>' ]
Example:
1-- assume this integration exists called test_integration:
2CREATE OR REPLACE SECRET test_secret
3 TYPE = CLOUD_PROVIDER_TOKEN
4 API_AUTHENTICATION = 'TEST_INTEGRATION';
Syntax:
1CREATE [ OR REPLACE ] SECRET [ IF NOT EXISTS ] <name>
2 TYPE = PASSWORD
3 USERNAME = '<username>'
4 PASSWORD = '<password>'
5 [ COMMENT = '<string_literal>' ]
Example:
1CREATE OR REPLACE SECRET my_password_secret
2 TYPE = PASSWORD
3 USERNAME = 'db_user'
4 PASSWORD = 'MySecurePass123!'
5 COMMENT = 'Database credentials for application access';
Syntax:
1CREATE [ OR REPLACE ] SECRET [ IF NOT EXISTS ] <name>
2 TYPE = GENERIC_STRING
3 SECRET_STRING = '<string_literal>'
4 [ COMMENT = '<string_literal>' ]
Example:
1CREATE OR REPLACE SECRET my_string_secret
2 TYPE = GENERIC_STRING
3 SECRET_STRING = 'my-secret-api-key-123'
4 COMMENT = 'API key for service authentication';
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.
1CREATE OR REPLACE FUNCTION get_secret_by_name(secret_name STRING)
2RETURNS STRING
3LANGUAGE PYTHON
4RUNTIME_VERSION = 3.9
5HANDLER = 'get_secret_by_name'
6EXTERNAL_ACCESS_INTEGRATIONS = (external_access_integration)
7AS
8$$
9import _snowflake
10
11def get_secret_by_name(secret_name):
12 username_password_object = _snowflake.get_username_password(secret_name)
13
14 username_password_dictionary = {}
15 username_password_dictionary["Username"] = username_password_object.username
1SELECT get_secret_by_name('jeffs_credentials');
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:
1CREATE OR REPLACE NOTIFICATION INTEGRATION my_slack_webhook_int
2 TYPE=WEBHOOK
3 ENABLED=TRUE
4 WEBHOOK_URL='https://hooks.slack.com/services/SNOWFLAKE_WEBHOOK_SECRET'
5 WEBHOOK_SECRET=my_secrets_db.my_secrets_schema.my_slack_webhook_secret
6 WEBHOOK_BODY_TEMPLATE='{"text": "SNOWFLAKE_WEBHOOK_MESSAGE"}'
7 WEBHOOK_HEADERS=('Content-Type'='application/json');
You can show all secrets using the show secrets
command.
1show secrets; -- shows all secrets in account
2show secrets in account; -- verbose way of `show secrets`
3show secrets in database analytics; -- shows secrets for a database named analytics
You can delete a secret using the drop
command:
1drop secret my_oauth2_secret; -- this works if your worksheet or connection has a database and schema context set.
2drop secret <fully qualified secret name> -- this will always work
Alter secret is used to rotate keys or passwords, or alter any other aspect of the secret.
1alter secret <fully qualitfied secret name> set <parameter> = <value>;
Describing a secret using describe secret <fully qualified secret name>
will return the following fields:
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.
Jeff Skoldberg is a Sales Engineer at SELECT, helping customers get maximum value out of the SELECT app to reduce their Snowflake spend. Prior to joining SELECT, Jeff was a Data and Analytics Consultant with 15+ years experience in automating insights and using data to control business processes. From a technology standpoint, he specializes in Snowflake + dbt + Tableau. From a business topic standpoint, he has experience in Public Utility, Clinical Trials, Publishing, CPG, and Manufacturing.
Get up and running with SELECT in 15 minutes.
Gain visibility into Snowflake usage, optimize performance and automate savings with the click of a button.