Skip to main content

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_HISTORY for real-time investigation (e.g., An analyst in Chennai just called saying they can't log in right now).
  • Use SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY for 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:

  1. Lock the user account: ALTER USER ARJUN_ADMIN SET DISABLED = TRUE;
  2. 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_HISTORY tracks all authentication attempts.
  • Use INFORMATION_SCHEMA for real-time debugging and ACCOUNT_USAGE for 365-day auditing.
  • Monitor the IS_SUCCESS and ERROR_MESSAGE columns to detect locked accounts or bad passwords.
  • Group by CLIENT_IP to detect potential Brute-Force attacks.
  • Use Snowflake Alerts to automatically notify the SecOps team when thresholds are breached.