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.
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
Create a specific user for SELECTING tables (USER_ACCESS_VIEWER)
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.
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.
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.