GCP Destructive Queries

Description

Detect any destructive BigQuery queries or jobs such as update, delete, drop, alter or truncate.

Query · python

DESTRUCTIVE_STATEMENTS = ["UPDATE", "DELETE", "DROP_TABLE", "ALTER_TABLE", "TRUNCATE_TABLE"]


def rule(event):
    if all(
        [
            event.deep_get("resource", "type", default="<RESOURCE_NOT_FOUND>").startswith(
                "bigquery"
            ),
            event.deep_get("protoPayload", "metadata", "jobChange", "job", "jobConfig", "type")
            == "QUERY",
            event.deep_get(
                "protoPayload",
                "metadata",
                "jobChange",
                "job",
                "jobConfig",
                "queryConfig",
                "statementType",
                default="<STATEMENT_NOT_FOUND>",
            )
            in DESTRUCTIVE_STATEMENTS,
        ]
    ):
        return True

    if event.deep_get("protoPayload", "metadata", "tableDeletion"):
        return True

    if event.deep_get("protoPayload", "metadata", "datasetDeletion"):
        return True

    return False


def title(event):
    actor = event.deep_get(
        "protoPayload", "authenticationInfo", "principalEmail", default="<ACTOR_NOT_FOUND>"
    )
    statement = event.deep_get(
        "protoPayload",
        "metadata",
        "jobChange",
        "job",
        "jobConfig",
        "queryConfig",
        "statementType",
        default="<STATEMENT_NOT_FOUND>",
    )
    if (
        event.deep_get("protoPayload", "metadata", "jobChange", "job", "jobConfig", "type")
        == "QUERY"
    ):
        return f"GCP: [{actor}] performed a destructive BigQuery [{statement}] query"

    if event.deep_get("protoPayload", "metadata", "tableDeletion"):
        return f"GCP: [{actor}] deleted a table in BigQuery"

    if event.deep_get("protoPayload", "metadata", "datasetDeletion"):
        return f"GCP: [{actor}] deleted a dataset in BigQuery"

    # Default return value
    return f"GCP: [{actor}] performed a destructive BigQuery query"


def severity(event):
    statement = event.deep_get(
        "protoPayload",
        "metadata",
        "jobChange",
        "job",
        "jobConfig",
        "queryConfig",
        "statementType",
        default="<STATEMENT_NOT_FOUND>",
    )
    if statement in ("UPDATE", "DELETE"):
        return "INFO"
    return "DEFAULT"


def alert_context(event):
    return {
        "query": event.deep_get(
            "protoPayload",
            "metadata",
            "jobChange",
            "job",
            "jobConfig",
            "queryConfig",
            "query",
            default="<QUERY_NOT_FOUND>",
        ),
        "actor": event.deep_get(
            "protoPayload",
            "authenticationInfo",
            "principalEmail",
            default="<ACTOR_NOT_FOUND>",
        ),
        "statement": event.deep_get(
            "protoPayload",
            "metadata",
            "jobChange",
            "job",
            "jobConfig",
            "queryConfig",
            "statementType",
            default="<STATEMENT_NOT_FOUND>",
        ),
        "table": event.deep_get(
            "protoPayload",
            "metadata",
            "jobChange",
            "job",
            "jobConfig",
            "queryConfig",
            "destinationTable",
        )
        or event.deep_get("protoPayload", "metadata", "resourceName", default="<TABLE_NOT_FOUND>"),
    }

Detection tier

Atomic IOCs
Network & Host Artifacts
Tools
TTPs
Pending classification

Metadata

Source: panther-labs/panther-analysis
License: Apache 2.0
Severity: Informational
Status: Stable
Query type: python
Created: 2023-04-05
Modified: 2025-04-22
Authored by: panther-labs
Source ID
Upstream: rules/gcp_audit_rules/gcp_destructive_queries.yml
Type: rule
Version: 1
References: https://cloud.google.com/bigquery/docs/managing-tables
Data sources: GCP.AuditLog

Detection rules belong to the projects that publish them and remain under their own licenses. This site indexes and links to them; it claims no rights in them.