Search
  • Tim Burns

Monitoring and Alerting on Snowflake

Updated: Aug 22, 2021


Photo by Author


Modern Database Administrators face significant security challenges on cloud-enabled databases like Snowflake. Whether setting up a new database or assessing user security on an existing database, you will want to monitor user authentication and access patterns to ensure security.


This article provides a quick startup template ensuring your access points are secure and a means to monitor user activity to keep your database safe.

  • Monitor and alert on Security

  • Give users the Minimum Privilege on the database

  • Enforce Separation by using Limited Role Access

Snowflake's Access Security Challenges

Snowflake is exposed to the Internet on HTTPS. This means many potential bad actors can attempt to access the data and we will need to apply all controls at our disposal to prevent unauthorized access.

Apply Principle of Least Privilege to All User Accounts

The service user accesses the database through ETL or a Business Analytics toolbar. By Principle of Least Privilege, here is a checklist to follow:

  • Discover what tables each user reads or writes

  • Create a role for each use case

  • Clear the roles on each user

  • Assign each user only the roles they need

Divide Users into Service and Person Users

Service and Person users access the system differently. Person users need to be tied to a single person or identity. Person users should not be able to share accounts. Service users are generally fixed on a single IP or range of IP and also have required credentials.


Snowflake solves security for Person users by allowing users to enroll in Multi-Factor Authentication. The user will download the Duo MFA tool. The user will provide credentials to Snowflake, and then a push notification will verify their identity on their phone. Thus, users cannot share passwords without sharing a single phone.


Snowflake solves for Service users by allowing the administrator to whitelist IP addresses and ranges on the individual Service account. Thus, any services that run on Snowflake will need to authenticate with a username and password and the network policy will also validate their IP address.


Challenge: Monitoring and Enforcement of Multi-Factor Authentication

A drawback of the user MFA enrollment is that the user must enroll themselves in MFA, so we need to monitor for compliance. A user may also stop using MFA and so we should also monitor for that event.


Snowflake has internal tables and procedures for user tracking, but those tables and procedures require the very powerful MANAGE GRANTS role, which is far outside the least privilege needed by those who monitor for compliance.


Additionally, the calls into the database metadata on Snowflake often have poor performance and do not take advantage of the Snowflake Result Cache.


Fortunately, Snowflake has a robust stored procedure and scheduling facility to support tables with the data needed for monitoring user and network access. We can then apply the least principles to an Analytics Service user and create a dashboard to monitor those tables.


The Core Tables to Monitor User Access

Create core tables for the Analytics Process to observe.


Stored Procedures to Separate Privileged Access

The user table requires the powerful MANAGE GRANTS permission and we don't want to expose that privilege outside of administrative accounts. Separate the permissions by driving the table updates using stored procedures and automated tasks.


Stored Procedures

Scheduled Tasks

Creating Dashboards for Analysis and Alerting

The core tables allow you to create dashboards for monitoring user access. Any BI tool will support access dashboards and most will integrate into alerts. Here, I have chosen Metabase, because it is open source and remarkably powerful.


A secure process to follow for building these dashboards is as follows

  1. Create a specific user for SELECTING tables (USER_ACCESS_VIEWER)

  2. Apply NETWORK security policies with a Whitelist on IP addresses

You can also run Metabase locally in Docker, and then you will want to whitelist your local IP address.

Create Views to Standardize Core Business Logic

Snowflake Views Encapsulate Business definitions and help build consistency of meaning across the system. A consistent catalog of views builds to a data dictionary for a unified security model across the organization. The User Authentication View will form the basis of the example security dashboard.


SNOWFLAKE_USER_AUTHENTICATION_VIEW

USER_NAME - User login name

EVENT_TIMESTAMP - Last user login event

FIRST_AUTHENTICATION_FACTOR - Configured first level authentication

SECOND_AUTHENTICATION_FACTOR - Configured second level authentication

POLICY_VALUE - Network policy value if defined

POLICY_DEFAULT - Network policy default value

POLICY_LEVEL - Policy level (USER or ACCOUNT)

POLICY_DESCRIPTION - An optional policy definition SERVICE_USER - User is flagged as a service user MFA_ENABLED - User with MFA


Create a Simple Dashboard for Alerting on Insecure Users

The dashboard example here is designed to give an idea about the basic starting place for a full monitoring solution. An administrator will set up the connection to the Snowflake data source and you will want to ask for the basic user authentication of the above view.


What users either do not have a SECOND_AUTHENCATION_FACTOR or a POLICY_VALUE for whitelisting IP addresses?

Build a dashboard focused on this question. Once we have the dashboard, then we define alerts and link them into our Slack account for notifications. With that end-to-end story, we have a good basis for scaling a Snowflake security operation.


In Metabase, link the Snowflake table with the service user assigned the role USER_ACCESS_VIEWER by connecting to the database.


Metabase calls a database query a "Question" with the idea that you build dashboards with a business goal in mind. Here is a simple dashboard that gets the counts of users who have IP whitelist policies and called them service users. I've called users with MFA Person users. Any other condition is a user alert.


Here is where I make build the alert to detect no MFA and no IP Whitelist. The dashboard and the Alert will use this alert table.

When I open the home page on my Security Dashboard I see any users out of compliance. Before resolving the condition, I will integrate the Metabase application with my Slack account following the documentation. After the Slack integration, I can specify an alert on my dashboard whenever any users are out of compliance.


In this case, DUMMY_USER is a Person user who did not set up MFA and METABASE_SNOWFLAKE_SECURITY doesn't have a network policy. When I resolve those items, then I have a clean bill for authentication and the basis for a robust alerting system on my Snowflake database.


Conclusions

Good security practices do not necessarily require large-scale projects or expensive software. Snowflake comes with solid security controls and judicious use of stored procedures and tasks that can open internal Snowflake data securely that can then be used for "actionable" dashboards.


Metabase is a BI tool that is free and can be used. It also scales to the enterprise, so it would certainly support a larger scale enterprise security initiative. However; nearly every popular BI tool can perform the simple alerting outlined in this article with a completely no-code solution.


Finally, If you have any user base beyond one or two users, not implementing the security practices here is very dangerous. With open databases on the HTTPS protocol, the endpoint is no longer secured in a corporate network, so the first and most important step in your Snowflake journey is setting up appropriate network access policies.

299 views0 comments

Recent Posts

See All

Downloading CMS Data is a bit tricky. The base site is here: https://data.cms.gov/provider-data/docs After beating my head against the wall, I discovered that the data key is embedded on the web page.