Snowflake Native Alerts
Set alerts and get notified
When architecting complex solutions and performing POCs with our awesome clients, we sometimes have to use ‘AccountAdmin’ role on our demo accounts. Due to the temporary nature of POCs and sometimes urgent deadlines, I have, and I hate to admit it, missed out on implementing Multi-Factor Authentication or MFA as a best practice for enhanced security.
Our team at Snowflake has decided that we need to enable MFA on all users with AccountAdmin role on all our demo accounts. It is important to note that users cannot currently be automatically enrolled into MFA, but need to enable it via a self-service process. Across hundreds of users within a demo account, this can be a very tedious thing to track. So, how do we track this enrolment?
Introducing Snowflake Native Alerts
Snowflake users can now set up alerts and get notified when specific conditions are met. Please note this feature is currently in Private Preview. (Update: as of Feb 8, 2023 — it is Public Preview).
Let’s take a quick look at the syntax:
CREATE [ OR REPLACE ] ALERT [ IF NOT EXISTS ] <alert name>
WAREHOUSE = <warehouse name>
SCHEDULE = '{ <num> MINUTE | USING CRON <expr> <time zone> }'
-- Condition
IF( EXISTS(
<condition statement>
))
-- Action
THEN
<action statement>
;An alert is a schema-level object that specifies:
- A condition that triggers an alert, for instance, a query running over two hours or in this case, users that are not using MFA when signing into Snowflake.
- A schedule for how often a condition must be evaluated, for instance, every 24 hours or every minute, every Sunday at midnight, etc.
- An action for what to do when the condition is satisfied, for instance, capture data in a table or send an email notification.
Coming back to our use-case — we want to create an alert for users with AccountAdmin role who are not using MFA when logging into Snowflake. This condition needs to be run every day (however, for the purposes of this exercise, I will choose the schedule to be every minute). And basis the results, we need to take some action, which will be to send an email to the admin — me.
The Condition
First things first, let’s identify the condition required to be fulfilled.
SELECT
event_timestamp, user_name, client_IP, reported_client_type,
first_authentication_factor, second_authentication_factor
FROM snowflake.account_usage.login_history
WHERE second_authentication_factor IS NULL
AND (
reported_client_type = 'SNOWFLAKE_UI'
OR reported_client_type = 'OTHER'
)
;The above query has been sourced from:
SnowAlert/blob/master/packs/snowflake_security_monitoring.sql
This gives us all users who logged in without MFA. We need to make small changes to reflect users who have AccountAdmin privileges:
-- Sample script above, but updated for AccountAdmin roles only
SELECT
event_timestamp, user_name, client_IP, reported_client_type,
first_authentication_factor, second_authentication_factor
FROM snowflake.account_usage.login_history
WHERE second_authentication_factor IS NULL
AND (
reported_client_type = 'SNOWFLAKE_UI'
OR reported_client_type = 'OTHER'
)
-- Searching for AccountAdmin users
AND user_name IN (
SELECT grantee_name
FROM SNOWFLAKE.ACCOUNT_USAGE.grants_to_users
WHERE role = 'ACCOUNTADMIN'
AND deleted_on IS NULL)
;
As can be seen from the image output above, second_authentication_factor is empty, indicating that MFA was not used.
On the flip side, if we want to see users who are using MFA, we can simply change the WHERE condition to reflect second_authentication_factor IS NOT NULL.
Since we configured the Duo Mobile MFA, we can see it the second_authentication_factor column in the image above.
The Action
Since we plan to send out an email notification, we need to create a Notification Integration. Here’s a quick look at the syntax:
CREATE [ OR REPLACE ] NOTIFICATION INTEGRATION [ IF NOT EXISTS ] <name>
TYPE = EMAIL
ENABLED = { TRUE | FALSE }
ALLOWED_RECIPIENTS = ( '<email_address_1>' [ , ... '<email_address_N>' ] )
[ COMMENT = '<string_literal>' ]
;And the following would be for our specific requirement:
-- You will need AccountAdmin role to create a Notification Integration
CREATE NOTIFICATION INTEGRATION my_email_int
TYPE=email
ENABLED=true
ALLOWED_RECIPIENTS=('my_id@domain.com')
COMMENT='Created via Native Alerts App'
;
-- Required Grants
GRANT usage ON integration my_email_int to role <role_name>;The Alert
Putting the pieces together within the alert syntax as we learnt above:
-- If you wish to grant the privileges to another role
-- Perform the following steps using AccountAdmin role
CREATE ROLE <alert_role>;
GRANT ROLE <alert_role> TO USER <alert_user>;
GRANT EXECUTE ALERT ON ACCOUNT TO ROLE <alert_role>;
-- You may also require USAGE privileges on the database and warehouse
-- Using schema owner role, grant the following permissions to alert_role
GRANT CREATE ALERT ON SCHEMA <schema_name> TO ROLE <alert_role>;
-- or
GRANT OWNERSHIP ON SCHEMA <schema_name> TO ROLE <alert_role>;
-- if you have performed the above steps in this snippet
USE ROLE <alert_role>;
-- I am using AccountAdmin role, hence I will be ignoring the above steps
-- Let's create the alert now
CREATE OR REPLACE ALERT myalert
WAREHOUSE = compute_wh
SCHEDULE = '1 minute'
IF
(EXISTS(
SELECT
event_timestamp, user_name, client_IP, reported_client_type,
first_authentication_factor, second_authentication_factor
FROM snowflake.account_usage.login_history
WHERE second_authentication_factor IS NULL
AND (
reported_client_type = 'SNOWFLAKE_UI'
OR reported_client_type = 'OTHER'
)
AND user_name IN (
SELECT grantee_name
FROM SNOWFLAKE.ACCOUNT_USAGE.grants_to_users
WHERE role = 'ACCOUNTADMIN'
AND deleted_on IS NULL
)
))
THEN
CALL system$send_email(
'my_email_int',
'my_id@domain.com',
'Email Alert: MFA not used',
'Hi, Please note MFA is not being used in Snowflake account - abc12345.'
);For more details on CALL system$send_email() stored proc, please visit the documentation here.
Additionally, we need to resume this alert:
ALTER ALERT myalert RESUME;The Output
This is the output email that I received:
To prevent alerts from unnecessarily executing and incurring cost, please be a good samaritan and disable/drop your alerts:
-- Disable alerts
ALTER ALERT myalert SUSPEND;
-- Drop alerts
DROP ALERT myalert;Additionally:
- You can edit the action script to send emails directly to the end users instead of an admin. But their email addresses need to be verified in their Snowflake profile and added to the notification integration.
- Sending tabular data can be tricky, but you should consider using python tabulate.
Monitoring & Managing your alerts
Let’s see the show and describe commands syntax:
-- Show Alerts
SHOW [ TERSE ] ALERTS [ LIKE '<pattern>' ]
[ IN { ACCOUNT |
DATABASE [ <db_name> ] |
[ SCHEMA ] [ <schema_name> ]
}
]
[ STARTS WITH '<name_string>' ]
[ LIMIT <rows> [ FROM '<name_string>' ] ]
;
-- Describe Alerts
DESC[RIBE] ALERT <name>;These will help us understand the state and execution status of our alerts:
SHOW ALERTS LIKE 'myalert';
DESC ALERT myalert;+-------------------------------+---------+-------------------+-------------+--------------+---------+------------+----------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| created_on | name | database_name | schema_name | owner | comment | warehouse | schedule | state | condition | action |
+-------------------------------+---------+-------------------+-------------+--------------+---------+------------+----------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2023-01-25 20:24:03.143 -0800 | MYALERT | NATIVE_ALERTS_RND | PUBLIC | ACCOUNTADMIN | | COMPUTE_WH | 1 minute | suspended | SELECT event_timestamp AS event_time, user_name, client_IP, reported_client_type, first_authentication_factor, second_authentication_factor FROM snowflake.account_usage.login_history WHERE second_authentication_factor IS NULL AND (reported_client_type = 'SNOWFLAKE_UI' OR reported_client_type = 'OTHER') ORDER BY event_time DESC LIMIT 10 | CALL system$send_email('my_email_int', 'my_id@domain.com', 'Email Alert: MFA not used', 'Hi, Please note MFA is not being used in Snowflake account - abc12345.') |
+-------------------------------+---------+-------------------+-------------+--------------+---------+------------+----------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+I like how the alert parameters we defined earlier are neatly segregated into columns here, i.e., name, warehouse, and schedule. It is intelligent enough to also segregate the condition and the action.
Now, let’s check out the syntax for alert executions:
ALERT_HISTORY(
[ SCHEDULED_TIME_RANGE_START => <constant_expr> ]
[, SCHEDULED_TIME_RANGE_END => <constant_expr> ]
[, RESULT_LIMIT => <integer> ]
[, ALERT_NAME => '<string>' ] )SELECT *
FROM TABLE(INFORMATION_SCHEMA.alert_history(
scheduled_time_range_start=>DATEADD('day', -1, CURRENT_TIMESTAMP())))
ORDER BY scheduled_time DESC
;I don’t have too many alerts so I’ve skipped some of the parameter inputs to Alert History.
+---------+-------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+-----------+----------------+-------------------+-------------------------------+-------------------------------+
| NAME | DATABASE_NAME | SCHEMA_NAME | CONDITION | CONDITION_QUERY_ID | ACTION | ACTION_QUERY_ID | STATE | SQL_ERROR_CODE | SQL_ERROR_MESSAGE | SCHEDULED_TIME | COMPLETED_TIME |
+---------+-------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+-----------+----------------+-------------------+-------------------------------+-------------------------------+
| MYALERT | NATIVE_ALERTS_RND | PUBLIC | SELECT event_timestamp AS event_time, user_name, client_IP, reported_client_type, first_authentication_factor, second_authentication_factor FROM snowflake.account_usage.login_history WHERE second_authentication_factor IS NULL AND (reported_client_type = 'SNOWFLAKE_UI' OR reported_client_type = 'OTHER') ORDER BY event_time DESC LIMIT 10 | 01a9e66a-0402-7758-0059-bd030026751e | CALL system$send_email('my_email_int', 'my_id@domain.com', 'Email Alert: MFA not used', 'Hi, Please note MFA is not being used in Snowflake account - abc12345.') | 01a9e66a-0402-7758-0059-bd0300267526 | TRIGGERED | 0 | | 2023-01-25 20:26:06.014 -0800 | 2023-01-25 20:26:14.874 -0800 |
+---------+-------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+-----------+----------------+-------------------+-------------------------------+-------------------------------+This gives you information on the query ids for the condition and the action, execution state, errors, and timings.
Cost
Taking you back to the initial syntax we had:
CREATE [ OR REPLACE ] ALERT [ IF NOT EXISTS ] <alert name>
WAREHOUSE = <warehouse name>
SCHEDULE = '{ <num> MINUTE | USING CRON <expr> <time zone> }'
-- Condition
IF( EXISTS(
<condition statement>
))
-- Action
THEN
<action statement>
;- You are only charged based on the compute used through the virtual warehouse for the execution of your condition and action queries.
- You are not charged for sending the email.
Snowflake Native Alerts Streamlit App
So far we have seen a simple example to set up alerts for Admins to monitor users with AccountAdmin role who are not using MFA to access Snowflake.
Now let’s go ahead and extend that example with a Snowflake Alerting app on Streamlit. Here is what the basic workflow looks like:
- We are going to use a couple of more queries from the same Github repo — SnowAlert.
- The Home Page 🏠 will have two forms:
- Create a Snowflake connection 🔌
- Create a Notification Integration 🤝 - Then we will add two pages to our app:
- Security Alerts 🔑
- Auth Alerts ⚠️ - Finally, we will create a Monitoring Dashboard 🖥️ that includes:
- Show Alert Description 🛎️
- Show Alert History 📜
Here’s a nifty video of the app in action:
This brings me to the end of my post, I do hope you find this useful. Feel free to experiment with or extend this use-case. Or perhaps you have another use-case in mind, do keep us posted on what you come up with.
Since this feature is in Private Preview, there are on-going developments to be expected and so I would advise not to use in Production. (Update: as of Feb 8, 2023 — it is Public Preview, however, please don’t do Production deployments).
Last but not least, please do check out our careers page here. We’re hiring across the board in multiple geographies. Also, don’t hesitate to reach out to me on Medium here or LinkedIn here — my name is Prathamesh, and I’m a Data Cloud Architect at Snowflake, however, my opinions are personal and do not reflect those of Snowflake.
