Integration Overview<\/strong><\/h3>\n\n\n\nOur integration with Snowflake follows a structured yet dynamic framework, beginning with the customer signup process. When a new customer joins our platform, they undergo a streamlined signup process that generates and stores OAuth credentials. These credentials are essential for establishing a secure connection to their Snowflake instance, allowing our system to authenticate and interact with Snowflake seamlessly.<\/p>\n\n\n\n
Setting Up a Security Integration in Snowflake<\/strong><\/h4>\n\n\n\nSetting up a security integration in Snowflake involves creating an OAuth-based connection. This process starts by configuring your Snowflake instance to recognize our application as a secure client.
This is done by first setting up a security integration within your Snowflake instance:<\/p>\n\n\n\n
CREATE SECURITY INTEGRATION MY_SNOWSQL_CLIENT<\/code><\/p>\n\n\n\nTYPE = OAUTH<\/code><\/p>\n\n\n\nENABLED = TRUE<\/code><\/p>\n\n\n\nOAUTH_CLIENT = CUSTOM<\/code><\/p>\n\n\n\nOAUTH_CLIENT_TYPE = 'CONFIDENTIAL'<\/code><\/p>\n\n\n\nOAUTH_REDIRECT_URI = 'https:\/\/example.com\/callback'<\/code><\/p>\n\n\n\nOAUTH_ISSUE_REFRESH_TOKENS = TRUE<\/code><\/p>\n\n\n\nOAUTH_REFRESH_TOKEN_VALIDITY = 86400;<\/code><\/p>\n\n\n\nThe OAUTH_REDIRECT_URI is a critical component, as it ensures that the authorization code grant is safely passed back to your application. For instance, during development, this URI might point to localhost:<yourport>, but in production, it should be set to a secure, publicly accessible URL.<\/p>\n\n\n\n
Handling an OAuth Callback<\/strong><\/h4>\n\n\n\nHere’s an example of handling an OAuth callback in Go:<\/p>\n\n\n\n
\/\/ add the handler function to the router for your golang server<\/code><\/p>\n\n\n\nmux.HandleFunc(\"\/callback\", callback<\/code>) <\/p>\n\n\n\nfunc callback(w http.ResponseWriter, r *http.Request) {<\/code><\/p>\n\n\n\n \/\/ Capture the auth code grant from the URL<\/code><\/p>\n\n\n\n AuthGrant := r.URL.Query().Get(\"code\")<\/code><\/p>\n\n\n\n oauthConfig := &oauth2.Config{<\/code><\/p>\n\n\n\n RedirectURL: \"http:\/\/example.com\/callback\",<\/code><\/p>\n\n\n\n ClientID: os.Getenv(\"SNOWFLAKE_CLIENT_ID\"),<\/code><\/p>\n\n\n\n ClientSecret: os.Getenv(\"SNOWFLAKE_CLIENT_SECRET\"),<\/code><\/p>\n\n\n\n Endpoint: os.Getenv(\"SNOWFLAKE_TOKEN_ENDPOINT\"),<\/code><\/p>\n\n\n\n }<\/code><\/p>\n\n\n\n token, err := oauthConfig.Exchange(r.Context(), AuthGrant)<\/code><\/p>\n\n\n\n if err != nil {<\/code><\/p>\n\n\n\n log.Println(err.Error())<\/code><\/p>\n\n\n\n http.Redirect(w, r, \"\/\", http.StatusTemporaryRedirect)<\/code><\/p>\n\n\n\n return<\/code><\/p>\n\n\n\n }<\/code><\/p>\n\n\n\n \/\/ Persist or use token here<\/code><\/p>\n\n\n\n PersistToken(token)<\/code><\/p>\n\n\n\n w.WriteHeader(http.StatusOK)<\/code><\/p>\n\n\n\n}<\/code><\/p>\n\n\n\nTo use the token later, it is retrieved from storage and used to construct an OAuth2-based HTTP client that can refresh the token as needed using:<\/p>\n\n\n\n
client := oauth2.NewClient(ctx, TokenSource)<\/code><\/p>\n\n\n\nMonitoring and Data Analysis<\/strong><\/h3>\n\n\n\nOnce authenticated, our system leverages the stored OAuth tokens to fetch data from several key Snowflake tables, including users, roles, shares, and usage_history. This data retrieval is facilitated through Snowflake’s SQL HTTP endpoint documented here: https:\/\/docs.snowflake.com\/en\/developer-guide\/sql-api\/intro<\/a>, enabling us to execute SQL queries and obtain the necessary information efficiently.<\/p>\n\n\n\nExample Query: Data Sharing Monitoring<\/strong><\/h4>\n\n\n\nSELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE REGEXP_LIKE(QUERY_TEXT, 'create\\\\s+share\\\\s.*','i') OR REGEXP_LIKE(QUERY_TEXT, '\\\\s+to\\\\s+share\\\\s.*','i') AND START_TIME >= DATEADD(HOUR, -24, CURRENT_TIMESTAMP());<\/code><\/p>\n\n\n\nSecurity Monitoring and Threat Detection<\/strong><\/h3>\n\n\n\nThe gathered data is meticulously analyzed to monitor database usage and identify potential security risks. Our analysis focuses on detecting unusual patterns, unauthorized access, and other indicators of insider threats. Some of our current detections for our Snowflake integration include MFA account status, data exfiltration, and data sharing. By continuously monitoring and scrutinizing this data, we provide our customers with actionable insights to help safeguard their data assets.<\/p>\n\n\n\n![\"MFA](\"https:\/\/www.anzenna.ai\/wp-content\/uploads\/2024\/08\/Screenshot-2024-08-06-at-10.43.48\u202fAM-redacted-edited.png\")
<\/figure>\n\n\n\nThis integrated approach ensures robust security monitoring and aligns with our mission at Anzenna to detect and prevent insider risks. By extending our monitoring capabilities to Snowflake, we continue to iterate on offering our customers a comprehensive solution to protect their sensitive information.<\/p>\n\n\n\n
Setting up a security integration in Snowflake involves creating an OAuth-based connection. This process starts by configuring your Snowflake instance to recognize our application as a secure client.
This is done by first setting up a security integration within your Snowflake instance:<\/p>\n\n\n\n
The OAUTH_REDIRECT_URI is a critical component, as it ensures that the authorization code grant is safely passed back to your application. For instance, during development, this URI might point to localhost:<yourport>, but in production, it should be set to a secure, publicly accessible URL.<\/p>\n\n\n\n Here’s an example of handling an OAuth callback in Go:<\/p>\n\n\n\n To use the token later, it is retrieved from storage and used to construct an OAuth2-based HTTP client that can refresh the token as needed using:<\/p>\n\n\n\n Once authenticated, our system leverages the stored OAuth tokens to fetch data from several key Snowflake tables, including users, roles, shares, and usage_history. This data retrieval is facilitated through Snowflake’s SQL HTTP endpoint documented here: https:\/\/docs.snowflake.com\/en\/developer-guide\/sql-api\/intro<\/a>, enabling us to execute SQL queries and obtain the necessary information efficiently.<\/p>\n\n\n\n The gathered data is meticulously analyzed to monitor database usage and identify potential security risks. Our analysis focuses on detecting unusual patterns, unauthorized access, and other indicators of insider threats. Some of our current detections for our Snowflake integration include MFA account status, data exfiltration, and data sharing. By continuously monitoring and scrutinizing this data, we provide our customers with actionable insights to help safeguard their data assets.<\/p>\n\n\n\n This integrated approach ensures robust security monitoring and aligns with our mission at Anzenna to detect and prevent insider risks. By extending our monitoring capabilities to Snowflake, we continue to iterate on offering our customers a comprehensive solution to protect their sensitive information.<\/p>\n\n\n\nCREATE SECURITY INTEGRATION MY_SNOWSQL_CLIENT<\/code><\/p>\n\n\n\nTYPE = OAUTH<\/code><\/p>\n\n\n\nENABLED = TRUE<\/code><\/p>\n\n\n\nOAUTH_CLIENT = CUSTOM<\/code><\/p>\n\n\n\nOAUTH_CLIENT_TYPE = 'CONFIDENTIAL'<\/code><\/p>\n\n\n\nOAUTH_REDIRECT_URI = 'https:\/\/example.com\/callback'<\/code><\/p>\n\n\n\nOAUTH_ISSUE_REFRESH_TOKENS = TRUE<\/code><\/p>\n\n\n\nOAUTH_REFRESH_TOKEN_VALIDITY = 86400;<\/code><\/p>\n\n\n\nHandling an OAuth Callback<\/strong><\/h4>\n\n\n\n
\/\/ add the handler function to the router for your golang server<\/code><\/p>\n\n\n\nmux.HandleFunc(\"\/callback\", callback<\/code>) <\/p>\n\n\n\nfunc callback(w http.ResponseWriter, r *http.Request) {<\/code><\/p>\n\n\n\n \/\/ Capture the auth code grant from the URL<\/code><\/p>\n\n\n\n AuthGrant := r.URL.Query().Get(\"code\")<\/code><\/p>\n\n\n\n oauthConfig := &oauth2.Config{<\/code><\/p>\n\n\n\n RedirectURL: \"http:\/\/example.com\/callback\",<\/code><\/p>\n\n\n\n ClientID: os.Getenv(\"SNOWFLAKE_CLIENT_ID\"),<\/code><\/p>\n\n\n\n ClientSecret: os.Getenv(\"SNOWFLAKE_CLIENT_SECRET\"),<\/code><\/p>\n\n\n\n Endpoint: os.Getenv(\"SNOWFLAKE_TOKEN_ENDPOINT\"),<\/code><\/p>\n\n\n\n }<\/code><\/p>\n\n\n\n token, err := oauthConfig.Exchange(r.Context(), AuthGrant)<\/code><\/p>\n\n\n\n if err != nil {<\/code><\/p>\n\n\n\n log.Println(err.Error())<\/code><\/p>\n\n\n\n http.Redirect(w, r, \"\/\", http.StatusTemporaryRedirect)<\/code><\/p>\n\n\n\n return<\/code><\/p>\n\n\n\n }<\/code><\/p>\n\n\n\n \/\/ Persist or use token here<\/code><\/p>\n\n\n\n PersistToken(token)<\/code><\/p>\n\n\n\n w.WriteHeader(http.StatusOK)<\/code><\/p>\n\n\n\n}<\/code><\/p>\n\n\n\nclient := oauth2.NewClient(ctx, TokenSource)<\/code><\/p>\n\n\n\nMonitoring and Data Analysis<\/strong><\/h3>\n\n\n\n
Example Query: Data Sharing Monitoring<\/strong><\/h4>\n\n\n\n
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE REGEXP_LIKE(QUERY_TEXT, 'create\\\\s+share\\\\s.*','i') OR REGEXP_LIKE(QUERY_TEXT, '\\\\s+to\\\\s+share\\\\s.*','i') AND START_TIME >= DATEADD(HOUR, -24, CURRENT_TIMESTAMP());<\/code><\/p>\n\n\n\nSecurity Monitoring and Threat Detection<\/strong><\/h3>\n\n\n\n
<\/figure>\n\n\n\n