SnowAlert: Security Analytics on Snowflake

SnowAlert is a security analytics framework that uses the Snowflake Cloud Data Platform to identify security incidents across diverse data sources and time ranges.

About SnowAlert

SnowAlert is a security analytics framework that uses the Snowflake Cloud Data Platform to identify security incidents across diverse data sources and time ranges.

Overview

As Snowflake’s security team, we need to know quickly and reliably when we’re under attack. We use the Snowflake Cloud Data Platform to store system, network and application logs and we analyze those logs for indications of active threats.

Some very large enterprise teams have developed in-house security analytics solutions on top of Snowflake but we want to make it easier for any team to use Snowflake for their security analytics. That’s where SnowAlert comes in.

SnowAlert is how we run scheduled queries on machine data in Snowflake and use the results to drive action on the part of the security team. We built it to be easy to use and extend, without any servers required and Snowflake as the only data store.

How It Works

SnowAlert queries Snowflake data on a regular schedule. We provide a Docker container and orchestration templates, so that you can deploy it anywhere: on your laptop or on a server, or orchestrated with AWS Fargate or Google Kubernetes.

The queries that drive the alerts are all defined as views that get stored in Snowflake; if you can write SQL, you can write queries for SnowAlert. We also provide some prebuilt packs of queries to help you get started and to serve as a model for what informative alerts should look like.

Alerts are generated when Query Rules match Data in your database and are saved into Snowflake. There, they are de-duplicated, passed through Suppression Rules for sorting into expected and unexpected behavior, correlated by action or object, and dispatched to Handlers (e.g. Slack, Jira) for review by users or analysts, who then circle back to correct Data (e.g. update vulnerable software) or refine the Rules (e.g. whitelist access of a certaint type) that power SnowAlert.

SnowAlert can also manage Violations, which are like alerts that require a less immediate response: a server not following best practices, a user without MFA enabled, and so on. These gets stored in a table similar to alerts, but are intended to be visualized with a tool like Sigma or Looker for ease of processing.

Getting Started

tl;dr Usage

The SnowAlert Docker container exposes two commands —

docker run -it snowsec/snowalert ./install

which configures your database, and

docker run -it --env-file snowalert-{account}.envs snowsec/snowalert ./run

which runs the SnowAlert functions, using auth environment variables {SA_ROLE SA_USER PRIVATE_KEY and PRIVATE_KEY_PASSWORD} to authenticate and operational envars {SNOWFLAKE_ACCOUNT SA_REGION SA_DATABASE SA_WAREHOUSE} to create the alerts.

Finally, we have a SnowAlert Management WebUI, which you can use to edit your rules —

docker run -it -p 8000:8000 --env-file snowalert-{account}.envs snowsec/snowalert-webui

Snowflake OAuth is the recommended authentication method for the WebUI. Storing credentials in the following environment variables will enable anyone with web access to manage the rules and data connectors. Please don’t use these environment variables in production. While we recommend using OAuth for prod auth, locally or in an otherwise secured environment, you can configure the application to use the same credentials stored in environment variables as the runners do: SNOWFLAKE_ACCOUNT, SA_USER, SA_ROLE, SA_DATABASE, SA_WAREHOUSE, PRIVATE_KEY, PRIVATE_KEY_PASSWORD. When deploying to prod, please follow the instructions in SnowAlert WebUI Authentication.

The WebUI can use OAuth with Snowflake serving as an “OAuth provider”, which is the same protocol, but a different authenticity delegation than the SSO OAuth in the Snowflake product. There, a provider like Okta can provide authentication services to Snowflake instead of a username and password. The two OAuth implementations can be used together to chain authentication from Okta, through Snowflake, and into SnowAlert.

Requirements

To use SnowAlert, you will need

  1. Administrator access to a Snowflake account
    • the SnowAlert installer needs Snowflake account administrator (ACCOUNTADMIN) privileges.
    • after installation is complete, SnowAlert accesses Snowflake using a dedicated, non-admin user.
  2. Docker container orchestration in the cloud, e.g. —
    • SnowAlert runners are published in a Docker container to run on AWS Fargate, Azure Container, or Kubernetes,
    • SnowAlert WebUI is published in a separate Docker container hosting a web application which needs to be network accessible by SnowAlert administrators.

Additionally, if you would like to use KMS as an extra layer of encryption and audit, please run the installer with AWS credentials that are allowed to manage and/or use KMS keys, e.g. —

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "kms:CreateKey",
                "kms:Encrypt",
                "kms:Decrypt"
            ],
            "Resource": "*"
        }
    ]
}

Downloading

The installer and runner both are distributed via DockerHub. To download it, run:

docker pull snowsec/snowalert

You can also use git to download the code that builds this container by running:

git clone git@github.com:snowflakedb/SnowAlert.git
cd SnowAlert
docker build -t snowsec/snowalert .

Installing

Snowflake provides an installer script in the home dir of the docker container, as well as the git repository, which will configure your Snowflake workspace and use KMS to encrypt secrets in the runner’s environment:

If you have ~/.aws and ~/.snowsql credentials in your environment, you can run it with:

docker run -it \
  --mount type=bind,source="$HOME/.aws",target=/root/.aws \
  --mount type=bind,source="$HOME/.snowsql",target=/root/.snowsql \
    snowsec/snowalert ./install

For a simpler installation which skips the KMS encryption layer, you can run:

$ docker run -it snowsec/snowalert ./install

Results

During the installation, the installer will create:

  1. A warehouse, user, role, database, and schema in your Snowflake instance that SA will use to manage alerts.
  2. A private key with an optional passphrase for authenticating to Snowflake as the SA user. This passphrase can be stored as an environment variable and can be optionally encrypted via Amazon KMS.

Finally, the installer will provide you with a list of environment variables which must be given to the Docker container in order to run SnowAlert functions. The functions should be run regularly (we recommend at least once an hour). If you need help setting up a framework for scheduling the functions, email snowalert@snowflake.com and we will help you get the automation set up properly.

The installer will also provide you with commands that will let you run a sample alert and violation definition SnowAlert immediately. Since the SnowAlert user authenticated to Snowflake during installation and does not have MFA configured, it will result in an alert appearing in your alerts table. If Jira is configured, then the Jira alert handler will run, creating a ticket in the Jira project for the alert.

Feedback

Any issues? Please reach out to us at snowalert@snowflake.com.

Managing Alerts

SnowAlert Rules are a set of views defined in your Snowflake account that manage the lifecycle of Alerts. Query Rules create alerts and Suppression Rules automatically handle alerts. The containers published on DockerHub use these rules to create alerts. Since these rules are Snowflake views, they can be managed using snowsql command line client or your Snowflake Web UI.

We are also actively working on a snowsec/snowalert-webui service container which will help you manage your rules in SnowAlert.

Query Rules

Before creating new alerts, take a look at the Snowflake Query Pack to see examples of useful Queries.

Note that each Query Rule defines a set set of columns, of which some are static, e.g. 'Medium' AS severity and 'SnowAlert' AS detector, while others are based on the data queries, such as user_name AS actor and start_time AS event_time. Together, these define alerts that are saved in the results schema in your Snowflake account.

Creating New Alert Queries

Since a Query Rule is a view over data in Snowflake, they can be managed by writing SQL statements to create those views and grant the SnowAlert role SELECT privileges on them. Those statements can be saved in a .sql file and either pasted into the SnowFlake worksheet UI or executed via snowsql. A single .sql file can contain multiple rules.

Viewing Alert Results

When alert queries return data from your warehouse, the results will be added to the alerts table in your SnowAlert database. You can select them in the database to confirm that they are being generated as expected.

If you’ve configured the alert handlers to notify you on alerts, e.g. through Jira, you can expect to see those notifications as soon as the Dispatch step is done.

Adding Suppressions

SnowAlert supports Suppression Queries to prevent known behavior from being dispatched for review. Suppression Queries are configured similarly to Alert Queries, but end with _ALERT_SUPPRESSION. Suppressions can suppress Alerts from multiple Queries or from a specific query, with a suppression for, e.g. AWS_ACCESS_DENIED_ALERT_QUERY, being called AWS_ACCESS_DENIED_ALERT_SUPPRESSION.

Suppressions are views over the Alerts table, like Queries are views over data, and so can be managed in the same way: by writing .sql files with statements to create the view and make the appropriate grants.

When the suppression step runs, it marks Alerts as suppressed or not. Alerts that have been through the suppression step and were not suppressed are then dispatched to handlers (e.g. Jira, Slack) by the Alert Dispatcher.

SnowAlert Query Packs

SnowAlert is shipped with some sample queries, categorized by the type of monitoring it provides and grouped into query packs.

To enable a query pack, copy the query pack file from packs/ into the Snowflake Worksheet UI and run the SQL statements to create the appropriate views and enable the appropriate grants.

The current query packs and queries are documented below:

  • Snowflake Query Pack

    1. Snowflake Admin Role Grants - This query generates an alert whenever a new user or role is granted the SECURITYADMIN or ACCOUNTADMIN role.
    2. Snowflake Authorization Failures - This query generates an alert whenever a user runs a query that returns an authorization error, indicating that the user does not have the appropriate grants to query the data.
    3. Snowflake Authentication Failures - This query generates an alert whenever a user fails to authenticate with Snowflake, indicating misconfigured scripts attempting to authenticate to your Snowflake account or potentially malicious brute force attempts.

Note: Using the Snowflake query pack will require you to grant imported privileges on the snowflake database to the SnowAlert role in your Snowflake account.

SnowAlert Violations

Sometimes there are events you want to track and resolve, but which don’t require immediate action; for example, if you want all of your Snowflake users to require MFA before authenticating, you want to know about a user who doesn’t have that turned on, but it’s not something that requires your Security team to track down the offending user that instant.

For cases like this, SnowAlert can manage violations, which are similar to alerts but can run less often (for example. daily instead of hourly). Violation queries are managed and suppressed identically to to Alert queries (so you might have USER_NO_MFA_VIOLATION_QUERY and USER_NO_MFA_VIOLATION_SUPPRESSION), but the results are stored in a Violations table, which you can visualize and process using a BI tool like Sigma, Looker, or Superset.

SnowAlert WebUI Authentication

Environment Variables

If a single team is using the SnowAlert WebUI, the following environment variables are recommended for the server:

  • OAUTH_CONNECTION_ROLE to specify the role used to see Rules
  • OAUTH_CONNECTION_DATABASE to specify the db where Rules will be managed
  • OAUTH_CONNECTION_WAREHOUSE to specify the warehouse the WebUI will use to load data
  • SA_REGION (default “us-west-2”) specifies the default region used by connectors
  • SA_KMS_KEY (optional) ARN of a key which allows your WebUI to encrypt secrets, e.g. connection passwords
  • OAUTH_{{type}}_{{account}} (see below) allows server to authenticate

OAuth Setup

There are a few steps to complete to enable OAuth authentication from the SnowAlert WebUI.

  1. Create a Security Integration in the Snowflake account you are connecting to:
CREATE OR REPLACE SECURITY INTEGRATION
  SNOWALERT_INTERNAL
    TYPE = OAUTH
    ENABLED = TRUE
    OAUTH_CLIENT = CUSTOM
    OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
    OAUTH_REDIRECT_URI = 'http://localhost:8080/login'
    OAUTH_ALLOW_NON_TLS_REDIRECT_URI = TRUE
;

Change the URI to the location where the Snowalert WebUI is running: e.g. if it is running at http://192.168.1.123:8000 then you should set the OAUTH_REDIRECT_URI to be http://192.168.1.123:8000/login

  1. Get the Client ID and the Client Secret
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('SNOWALERT_INTERNAL');

This will output a JSON document with the Client ID and 2 Client Secrets (two are useful in case you need to rotate them, choose the first for the next step)

  1. Wherever the SnowAlert WebUI is running set 2 environment variables

    1. "OAUTH_CLIENT_" + account.toUpperCase()
    2. "OAUTH_SECRET_" + account.toUpperCase()

Where account is the account name of your Snowflake instance for example:

https://mydemo.us-east-1.snowflakecomputing.com

would be DEMO (note the upper-case). i.e.:

  1. OAUTH_CLIENT_MYDEMO
  2. OAUTH_SECRET_MYDEMO

4. (Optional) By default, OAuth tokens are granted to the user’s default role, and the SnowAlert WebUI backend will use the user’s default database and warehouse when running queries. To override this behaviour, there are three additional environment variables you can set:

  • OAUTH_CONNECTION_ROLE specifies the role to assume during all OAuth authorization flows (case sensitive).
  • OAUTH_CONNECTION_DATABASE specifies the database to use for all SQL queries.
  • OAUTH_CONNECTION_WAREHOUSE specifies the warehouse to use for all SQL queries.

Server-side authentication

Snowflake OAuth is the recommended authentication method for the WebUI. Storing credentials in the following environment variables will enable anyone with web access to manage the rules and data connectors. Please don’t use these environment variables in production. While we recommend using OAuth for prod auth, locally or in an otherwise secured environment, you can configure the application to use the same credentials stored in environment variables as the runners do: SNOWFLAKE_ACCOUNT, SA_USER, SA_ROLE, SA_DATABASE, SA_WAREHOUSE, PRIVATE_KEY, PRIVATE_KEY_PASSWORD.