Over the past few weeks, we’ve embarked on an exciting project to enhance Anzenna’s insider security monitoring capabilities by integrating with Snowflake, a leading cloud-based data warehousing platform. With this new integration, our customers can now enjoy seamless monitoring of their Snowflake environments, with up to date information on data sharing and use. This means quicker identification of potential threats and more effective risk management. In this update, we delve into the technical details of this integration, highlighting our use of OAuth for authentication and Snowflake’s SQL HTTP endpoint to monitor and analyze database usage.
You might wonder what a Snowflake integration has to do with insider risk. While recent attention has focused on enabling MFA for Snowflake, the real security risk lies in how securely insiders use it, given the vast amount of sensitive information stored within. For more details, refer to our Insider Risk 101 blog post: https://www.anzenna.ai/holistic-insider-risk-management-the-time-is-now/.
Integration Overview
Our 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.
Setting Up a Security Integration in Snowflake
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:
CREATE SECURITY INTEGRATION MY_SNOWSQL_CLIENT
TYPE = OAUTH
ENABLED = TRUE
OAUTH_CLIENT = CUSTOM
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = 'https://example.com/callback'
OAUTH_ISSUE_REFRESH_TOKENS = TRUE
OAUTH_REFRESH_TOKEN_VALIDITY = 86400;
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.
Handling an OAuth Callback
Here’s an example of handling an OAuth callback in Go:
// add the handler function to the router for your golang server
mux.HandleFunc("/callback", callback)
func callback(w http.ResponseWriter, r *http.Request) {
// Capture the auth code grant from the URL
AuthGrant := r.URL.Query().Get("code")
oauthConfig := &oauth2.Config{
RedirectURL: "http://example.com/callback",
ClientID: os.Getenv("SNOWFLAKE_CLIENT_ID"),
ClientSecret: os.Getenv("SNOWFLAKE_CLIENT_SECRET"),
Endpoint: os.Getenv("SNOWFLAKE_TOKEN_ENDPOINT"),
}
token, err := oauthConfig.Exchange(r.Context(), AuthGrant)
if err != nil {
log.Println(err.Error())
http.Redirect(w, r, "/", http.StatusTemporaryRedirect)
return
}
// Persist or use token here
PersistToken(token)
w.WriteHeader(http.StatusOK)
}
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:
client := oauth2.NewClient(ctx, TokenSource)
Monitoring and Data Analysis
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, enabling us to execute SQL queries and obtain the necessary information efficiently.
Example Query: Data Sharing Monitoring
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());
Security Monitoring and Threat Detection
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.
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.
Contact Anzenna to learn more and get a free Insider Risk Assessment today!
