Real-Time Brute Force and Credential Stuffing Detection with SQL

SQL window aggregations detect brute force attacks by counting failed authentication attempts per account within a fixed time window, triggering alerts when the count exceeds a threshold. Credential stuffing is detected at the campaign level by aggregating failed logins across many accounts grouped by shared source IP or user-agent, revealing automated attacks that spread across targets.

What is the difference between brute force and credential stuffing?

These two attack types are often confused, but they target different weaknesses and require different detection strategies.

Brute force attacks focus on a single account. An attacker repeatedly tries different passwords against one username, hoping to guess the correct credential. The signal is concentrated: many failures on one account in a short window. A naive rate limiter on a per-account basis catches most brute force attempts.

Credential stuffing attacks use lists of stolen username/password pairs from previous data breaches. Because the attacker already has plausible credentials, each attempt on each account is typically just one or two tries. The attacker spreads attempts across thousands of accounts, often using rotating proxies or botnets to blend into normal traffic. Per-account rate limiting is ineffective here. You need to detect the campaign, not the individual attempt.

The technical signatures differ precisely:

Why real-time detection matters for authentication attacks

The fraud window for authentication attacks is measured in minutes, not hours. Research from fraud prevention firms consistently shows that attackers who successfully validate stolen credentials begin exploiting accounts within 15 minutes of a positive login. By the time a batch job runs at midnight, the attacker has already changed account email addresses, drained loyalty points, or initiated wire transfers.

Account lockout policies help with brute force, but they create a denial-of-service vector against your own users if misconfigured. More importantly, lockout only addresses the targeted account after it has already been hammered. A streaming detection system lets you respond earlier, at the IP or session level, before a threshold is reached.

For credential stuffing, batch detection arrives too late almost by definition. The attacker completes a campaign in bursts, validating a batch of credentials and moving on. A streaming aggregation that sees the cross-account pattern within 30-60 seconds can block the source infrastructure before the campaign completes.

Real-time detection also enables graduated responses. Instead of a binary block, you can trigger step-up authentication (MFA challenge) as soon as suspicious patterns emerge, preserving the experience for legitimate users while stopping attackers.

Detecting brute force with SQL COUNT windows

Brute force detection is a COUNT aggregation over a tumbling time window, partitioned by account. The query counts failed authentication events per account in a rolling window and flags accounts where the count exceeds a threshold.

With RisingWave, you express this as a materialized view over a streaming source. The view continuously maintains counts and surfaces any account that has crossed the threshold. No batch job, no polling.

-- Brute force detection: flag accounts with > 5 failed logins in 5 minutes
CREATE MATERIALIZED VIEW brute_force_alerts AS
SELECT
    account_id,
    COUNT(*) AS failed_attempts,
    window_start,
    window_end
FROM TUMBLE(auth_events, event_time, INTERVAL '5 minutes')
WHERE event_type = 'AUTH_FAILURE'
GROUP BY account_id, window_start, window_end
HAVING COUNT(*) > 5;

The TUMBLE function creates non-overlapping 5-minute windows aligned to the clock. For overlapping windows (to catch attacks that straddle window boundaries), use HOP instead:

-- Sliding window version: 5-minute window, advancing every 1 minute
CREATE MATERIALIZED VIEW brute_force_alerts_sliding AS
SELECT
    account_id,
    COUNT(*) AS failed_attempts,
    window_start,
    window_end
FROM HOP(auth_events, event_time, INTERVAL '1 minute', INTERVAL '5 minutes')
WHERE event_type = 'AUTH_FAILURE'
GROUP BY account_id, window_start, window_end
HAVING COUNT(*) > 5;

Downstream systems query brute_force_alerts directly. Because it is a materialized view, the results are pre-computed and already stored. A read is a simple SELECT, not a re-scan of the event stream.

Detecting credential stuffing campaigns with cross-account aggregation

Credential stuffing requires grouping across accounts. Instead of "how many failures did account X have?", the question is "how many distinct accounts did source IP Y fail against in the last 10 minutes?"

The SQL structure is similar, but the GROUP BY clause changes from account to the attacker's shared infrastructure signal: source IP, user-agent string, or IP subnet (using network prefix masking).

-- Credential stuffing campaign detection: flag source IPs targeting many accounts
CREATE MATERIALIZED VIEW credential_stuffing_alerts AS
SELECT
    source_ip,
    user_agent,
    COUNT(DISTINCT account_id) AS accounts_targeted,
    COUNT(*) AS total_failures,
    window_start,
    window_end
FROM TUMBLE(auth_events, event_time, INTERVAL '10 minutes')
WHERE event_type = 'AUTH_FAILURE'
GROUP BY source_ip, user_agent, window_start, window_end
HAVING COUNT(DISTINCT account_id) > 10;

For attackers using IP rotation, the user-agent or a fingerprint derived from TLS JA3 hashes becomes the grouping key. You can also group by IP subnet to catch campaigns that spread across a /24 block:

-- Subnet-level campaign detection (IPv4 /24 prefix)
CREATE MATERIALIZED VIEW subnet_stuffing_alerts AS
SELECT
    SUBSTRING(source_ip, 1, POSITION('.' IN REVERSE(source_ip)) * -1 + LENGTH(source_ip)) AS ip_subnet,
    COUNT(DISTINCT account_id) AS accounts_targeted,
    COUNT(*) AS total_failures,
    window_start,
    window_end
FROM TUMBLE(auth_events, event_time, INTERVAL '10 minutes')
WHERE event_type = 'AUTH_FAILURE'
GROUP BY ip_subnet, window_start, window_end
HAVING COUNT(DISTINCT account_id) > 20;

Both views update continuously. A downstream alert service or SIEM queries them and receives fresh results reflecting events from the last few seconds.

Step-by-step implementation with RisingWave

Here is the complete setup from Kafka source to live detection views.

Step 1: Create the source

Authentication events flow from your application into a Kafka topic. RisingWave connects to Kafka with a CREATE SOURCE statement:

CREATE SOURCE auth_events (
    event_id        VARCHAR,
    event_time      TIMESTAMPTZ,
    event_type      VARCHAR,        -- 'AUTH_SUCCESS' | 'AUTH_FAILURE'
    account_id      VARCHAR,
    source_ip       VARCHAR,
    user_agent      VARCHAR,
    failure_reason  VARCHAR         -- 'INVALID_PASSWORD' | 'ACCOUNT_LOCKED' | etc.
)
WITH (
    connector = 'kafka',
    topic = 'auth-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

Step 2: Brute force materialized view

CREATE MATERIALIZED VIEW brute_force_alerts AS
SELECT
    account_id,
    COUNT(*) AS failed_attempts,
    MIN(event_time) AS first_attempt,
    MAX(event_time) AS last_attempt,
    window_start,
    window_end
FROM TUMBLE(auth_events, event_time, INTERVAL '5 minutes')
WHERE event_type = 'AUTH_FAILURE'
GROUP BY account_id, window_start, window_end
HAVING COUNT(*) > 5;

Step 3: Credential stuffing campaign view

CREATE MATERIALIZED VIEW credential_stuffing_alerts AS
SELECT
    source_ip,
    user_agent,
    COUNT(DISTINCT account_id) AS accounts_targeted,
    COUNT(*) AS total_failures,
    ARRAY_AGG(DISTINCT account_id) AS affected_accounts,
    window_start,
    window_end
FROM TUMBLE(auth_events, event_time, INTERVAL '10 minutes')
WHERE event_type = 'AUTH_FAILURE'
GROUP BY source_ip, user_agent, window_start, window_end
HAVING COUNT(DISTINCT account_id) > 10;

Step 4: Query the views

Your application or SIEM polls these views on a short interval:

SELECT account_id, failed_attempts, window_start
FROM brute_force_alerts
WHERE window_end > NOW() - INTERVAL '1 minute'
ORDER BY failed_attempts DESC;

Because the views are materialized, this query returns instantly from pre-computed state. There is no re-aggregation at query time.

Step 5: Sink to alerting infrastructure

RisingWave can also push alerts downstream via a sink connector to Kafka, PostgreSQL, or a webhook, so your alerting pipeline receives events without polling:

CREATE SINK brute_force_sink
FROM brute_force_alerts
WITH (
    connector = 'kafka',
    topic = 'security-alerts',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

How does this compare to Flink for authentication attack detection?

Apache Flink is the other common choice for streaming security analytics. The functional capabilities are comparable, but the development experience differs significantly.

SQL vs DataStream API: Flink's SQL layer can express these same window aggregations, but production Flink deployments at security-critical companies often rely on the DataStream API for fine-grained control over state management and exactly-once semantics. The DataStream API requires Java or Scala and a build toolchain. With RisingWave, the entire detection pipeline is SQL, the same language your analysts already use. There is no separate compilation step, no JAR to deploy.

Operational overhead: Flink requires a cluster with a JobManager and TaskManagers, separate from your storage layer. RisingWave is a single system that handles both stream processing and storage, reducing the number of moving parts in your security stack.

Incremental view maintenance: RisingWave's materialized views use incremental computation: when new auth events arrive, only the affected window aggregates are updated. This is equivalent to Flink's incremental aggregation mode, but exposed as a declarative SQL construct rather than a stateful operator you configure manually.

Latency: Both systems achieve sub-second detection latency on high-throughput auth streams. For brute force detection, where you are counting within a window, both are well within the requirements. The operational simplicity advantage favors RisingWave for teams that do not already have Flink expertise.

Ecosystem: Flink has a larger ecosystem and more connectors. RisingWave supports Kafka, Kinesis, Pulsar, and major databases as sources and sinks, which covers the authentication analytics use case completely.

Frequently Asked Questions

Q: What threshold should I use for brute force detection?

The right threshold depends on your user base and application. A common starting point is 5 failed attempts in 5 minutes. For high-value accounts (admin, financial), lower the threshold to 3. Monitor your false-positive rate and adjust. RisingWave lets you update the HAVING clause threshold by recreating the materialized view without downtime.

Q: Can RisingWave handle high-volume auth event streams?

Yes. RisingWave is designed for high-throughput streaming workloads. Authentication event streams for large platforms can exceed millions of events per minute. RisingWave scales horizontally, and its incremental aggregation approach means compute cost scales with the number of distinct accounts and IPs being tracked, not raw event volume.

Q: How does RisingWave handle late-arriving events from distributed services?

RisingWave supports watermark-based late event handling. You define a watermark on the event_time column, and the system uses it to determine when a window is closed. Events arriving after the watermark deadline are handled according to your configured policy (discard or apply to the late window). For authentication analytics, a small watermark delay (30 seconds) is typically sufficient.

Q: Can I join auth failure data with account risk scores in real time?

Yes. RisingWave supports stream-table joins. You can maintain a risk_scores table updated from your risk engine and join it with the streaming auth events in your materialized view. Accounts already flagged as high-risk can use a lower failure threshold, or the join result can be filtered to focus alerting on accounts with existing suspicious activity.

Get started with RisingWave

Real-time authentication attack detection does not require a complex streaming infrastructure. With RisingWave, three SQL statements (a source and two materialized views) give you continuous brute force and credential stuffing detection over live Kafka streams.

Try it at risingwave.com or explore the documentation to connect your authentication event stream and start detecting attacks in minutes.