Login History & Failed Authentication Tracking
A critical responsibility of a Snowflake Security Administrator (SecOps) is ensuring that malicious actors are not attempting to breach the platform.
The primary tool for this is the LOGIN_HISTORY view, which records every single attempt (both successful and failed) to access your Snowflake account.
Topics Covered
- Understanding
LOGIN_HISTORY - Key Authentication Metrics
- Detecting Brute-Force Attacks
- Compliance Reporting
1. Understanding LOGIN_HISTORY
The LOGIN_HISTORY view resides in both INFORMATION_SCHEMA and ACCOUNT_USAGE.
- Use
INFORMATION_SCHEMA.LOGIN_HISTORYfor real-time investigation (e.g., An analyst in Chennai just called saying they can't log in right now). - Use
SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORYfor long-term historical auditing and compliance reporting (holds data for 365 days, but has up to a 10-minute delay).
(To query these views, you typically need the ACCOUNTADMIN or SECURITYADMIN role. Verify your role: SELECT CURRENT_ROLE();)
2. Key Authentication Metrics
Let's look at a basic query to understand the shape of the data.
USE ROLE ACCOUNTADMIN;
SELECT
EVENT_TIMESTAMP,
USER_NAME,
CLIENT_IP,
REPORTED_CLIENT_TYPE, -- e.g., 'SNOWFLAKE_UI', 'PYTHON_DRIVER', 'TABLEAU'
IS_SUCCESS, -- 'YES' or 'NO'
ERROR_CODE,
ERROR_MESSAGE
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE EVENT_TIMESTAMP >= DATEADD('day', -1, CURRENT_TIMESTAMP())
ORDER BY EVENT_TIMESTAMP DESC
LIMIT 20;
If IS_SUCCESS is NO, the ERROR_MESSAGE column will tell you exactly why (e.g., "Incorrect username or password", "User account is locked", "MFA authentication failed").
3. Detecting Brute-Force Attacks
A "Brute Force" attack is when an attacker uses an automated script to try thousands of different passwords against a single username in rapid succession.
If you see a high volume of failed logins for a single user from the same IP address, that is a massive red flag.
Hands-On: The Brute Force Detection Query
We can write a query to flag any IP address that has failed to log in more than 10 times in the last hour.
SELECT
CLIENT_IP,
USER_NAME,
COUNT(*) AS FAILED_ATTEMPTS,
MIN(EVENT_TIMESTAMP) AS FIRST_ATTEMPT,
MAX(EVENT_TIMESTAMP) AS LAST_ATTEMPT
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE
IS_SUCCESS = 'NO'
-- Look only at the last 60 minutes
AND EVENT_TIMESTAMP >= DATEADD('minute', -60, CURRENT_TIMESTAMP())
GROUP BY
CLIENT_IP,
USER_NAME
HAVING COUNT(*) > 10
ORDER BY FAILED_ATTEMPTS DESC;
If an IP address shows up here (e.g., 192.168.1.55 generating 500 failed attempts for the user ARJUN_ADMIN), you should immediately:
- Lock the user account:
ALTER USER ARJUN_ADMIN SET DISABLED = TRUE; - Update your Network Policy to explicitly block that IP address from accessing Snowflake.
4. Compliance Reporting (Auditing)
Regulated industries (like banking and healthcare in India governed by RBI or HIPAA) require strict auditing of who accessed the system and from where.
Auditors frequently ask for a report of all successful logins grouped by location or client tool to ensure no unauthorized third-party software is connecting to the database.
Hands-On: Client Connection Audit
SELECT
REPORTED_CLIENT_TYPE,
REPORTED_CLIENT_VERSION,
COUNT(DISTINCT USER_NAME) AS UNIQUE_USERS,
COUNT(*) AS TOTAL_LOGINS
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE
IS_SUCCESS = 'YES'
AND EVENT_TIMESTAMP >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY
REPORTED_CLIENT_TYPE,
REPORTED_CLIENT_VERSION
ORDER BY TOTAL_LOGINS DESC;
If your company policy dictates that all BI analysts must use Tableau, and this query suddenly shows 50 successful logins using the DBeaver or Python drivers, you have identified a compliance violation.
5. Setting up Alerts
You should not run the Brute Force query manually every day. You should wrap it in a Snowflake Alert.
-- Create an alert that runs every 15 minutes
CREATE OR REPLACE ALERT SECURITY_BRUTE_FORCE_ALERT
WAREHOUSE = COMPUTE_WH
SCHEDULE = '15 MINUTE'
IF (EXISTS (
SELECT CLIENT_IP
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE IS_SUCCESS = 'NO'
AND EVENT_TIMESTAMP >= DATEADD('minute', -15, CURRENT_TIMESTAMP())
GROUP BY CLIENT_IP
HAVING COUNT(*) > 10
))
THEN
-- Trigger an email via an integration
CALL SYSTEM$SEND_EMAIL(
'MY_EMAIL_INT',
'security-team@beems.tech',
'URGENT: Brute Force Attack Detected',
'Multiple failed logins detected from a single IP. Check LOGIN_HISTORY immediately.'
);
-- Start the alert
ALTER ALERT SECURITY_BRUTE_FORCE_ALERT RESUME;
Summary
LOGIN_HISTORYtracks all authentication attempts.- Use
INFORMATION_SCHEMAfor real-time debugging andACCOUNT_USAGEfor 365-day auditing. - Monitor the
IS_SUCCESSandERROR_MESSAGEcolumns to detect locked accounts or bad passwords. - Group by
CLIENT_IPto detect potential Brute-Force attacks. - Use Snowflake Alerts to automatically notify the SecOps team when thresholds are breached.