Detecting and Preventing Unauthorized User Access

Created 3 new monitors to help detect and prevent unathorised user access based off the article:
https://community.snowflake.com/s/article/Communication-ID-0108977-Additional-Information

• login_attempts_suspect_clients
• login_attempts_suspect_ip_addresses
• login_attempts_unseen_ip_address_password

Like for ILike replacement

Updated the following monitors to use ilike instead of like to capture caseing variations

1. Long Running Queries
2. SCIM token expiry

Failed Logins

• added new monitor login_failures_by_username_detailed which shows the username, ip addresses used, authentication method and exception thrown
• updated monitor blocked_ip_address_events to refer to current_timestamp instead of current_time when doing a time_filter
• updated monitor login_failures_by_ip_address to refer to current_timestamp instead of current_time when doing a time_filter

Monitor Details:

Security Administration

name	description
login_failures_by_username_detailed	Count of login failures by username

Data Quality Monitors

Added Data Quality Monitors

name	description
pipe_channel_error	Checks to see if an error message has been produced when attempting to read messages from the associated Google Cloud Pub/Sub queue or Microsoft Azure Event Grid storage queue. If there is an error then a notification will be sent
pipe_freshness	Checks to see when the most recent file was loaded successfully by Snowpipe into the destination table. If the file is older than the freshness_threshold then a notification will be sent
pipe_outstanding_messages	Checks the number of messages in the queue that have been queued but not received yet and number of files queued for loading by the pipe. If either of these values exceed the threshold then a notification will be sent
pipe_status	Checks the current execution state of a pipe. Any status except those in the exception list will trigger an notification to be sent
streams_gone_invalid	Checks to see if there are any streams cannot be queried successfully for the given databases. If any are found, a notification will be sent.
streams_with_invalid_tables	Checks to see if there are any streams have invalid base tables for the given databases. If any are found, a notification will be sent.
streams_gone_stale	Checks to see if there are any streams have gone stale for the given databases. If any are found, a notification will be sent.
streams_going_stale	Checks to see if there are any streams which may become stale if they aren't consumed from for the given databases. If any are found, a notification will be sent.
not_null	Checks to see the presence of a null value. If the results contains a null then a notification will be sent
source_freshness	Checks to see when data was retrieved last, if the time exeeds the specified expectation then a notification will be sent
expect_column_values_to_be_between	Checks to see if the column has a value between those specified, if any rows exceed the limits then a notification will be sent
expect_column_value_lengths_to_be_between	Checks to see if column entries to be strings with length between a min_length value and a max_length value (inclusive). If any rows falls outside this range then a notification will be sent
expect_column_value_lengths_to_equal	Checks to see if column entries to be strings with a specific length. If any rows don't match outside this range then a notification will be sent

RBAR Detection

Release of the following Monitors

name	description
rbar_detection	Detect row-by-row processing queries repeatedly executed within the same session as these are a high priority for tuning. Ref: https://www.analytics.today/blog/top-3-snowflake-performance-tuning-tactics

rbar_detection

New monitor

Monitoral.io Monitors

First release of monitors which includes the following macros:

Performance

name	description
long_running_queries	Returns a list of queries that have been running for more than the specified timeframe

Security Administration

name	description
orphaned_roles	Returns a list of orphaned roles
scim_token_expiry	Alerts a configurable numbers of days has past since the last token creation

Snowflake Security Practices

name	description
blocked_ip_address_events	Blocked ip address login failures (this requires Network Policies to be configured)
login_failures_by_ip_address	Count of login failures by ip address
blocked_ip_address_aggregate	Blocked ip address login failures aggregated by username, ip address, driver and authentication type (this requires Network Policies to be configured)
changes_to_network_policies	Monitor changes to Network Policies and associated objects
network_policy_exists	Monitors for the presence of network policy
altered_client_sessions	Monitor for client applications that are keeping sessions open longer than desired by policy
public_role_grants	The public role should have the fewest possible grants (read none). Every user in a Snowflake account has the public role granted to them. Monitor QUERY_HISTORY for alterations or grants to the public role
unauthorized_privilege_grants	Snowflake recommends using a designated role for all user management tasks. Monitor that all user and role grants originate from this role, and that this role is only granted to appropriate users
admin_roles_query_check	Monitor for all instances of a user using the default Snowflake admin roles to ensure their use is appropriate
user_creation	Monitors for the creation of users
user_creation_non_admin	Monitors for user creation by non admin roles
user_altered	Monitors occurrences of altered users
user_altered_key_pair	Monitors occurrences of altered users key pair auth removal
user_altered_mfa_bypass	Monitors occurrences of altered users mfa bypass time period
enabled_user_previously_disabled	Monitors instances where a previously disabled user has been enabled
user_altered_to_plaintext_password	Monitor for the enablement of plaintext user passwords
scim_api_calls	"Applicable if SCIM user-provisioning via the REST API is configured. Monitor SCIM API calls to ensure API requests comply with policy https://docs.snowflake.com/en/user-guide/scim-intro#auditing-with-scim"
high_privilege_grants	Monitors high privilege query activity that involves elevated privileges in your Snowflake Account
accountadmin_role_grants	The Snowflake role ACCOUNTADMIN should be closely monitored for granting to new users
authentication_method_by_user	Monitors the number of times each user authenticated and the authentication method they used
not_using_sso_auth	Monitor if users who have used SSO before are using other authentication methods instead After users successfully authenticate using SSO, they should not be using other methods
by_key_pair_auth	Monitor the use of key pair authentication by querying login attempt
has_key_pair_and_password	Monitor if exclusive Key Pair authentication users are configured to use other authentication methods. Users who have key ...