Panther rules: snowflake

AnalysisType: scheduled_query
QueryName: "Query.Snowflake.AccountAdminGranted"
Enabled: false
Description: >
  Monitor and detect granting account admin role.
Query: |
  --return instances where active (not deleted) roles are granted within the last 24 hours

  --this was adapted from a Security Feature Checklist query

  SELECT
    created_on as p_event_time,
    role,
    grantee_name as granted_to,
    granted_by
  FROM snowflake.account_usage.grants_to_users
  WHERE
    p_event_time is NOT NULL
    AND grantee_name is NOT NULL
    AND granted_to is NOT NULL
    AND role ILIKE '%admin%'
    AND deleted_on is NULL
    AND p_occurs_since('1 day')
Schedule:
  RateMinutes: 1440
  TimeoutMinutes: 3

AnalysisType: scheduled_query
QueryName: "Query.Snowflake.BruteForceByIp"
Enabled: false
Description: >
  Detect brute force attempts by monitoring for failed logins to snowflake.
Query: |
  --return IPs with more than 5 failed logins in the previous 24 hours

  --this was adapted from a SnowAlert query

  SELECT
    client_ip,
    reported_client_type,
    ARRAY_AGG(DISTINCT error_code) as error_codes,
    ARRAY_AGG(DISTINCT error_message) as error_messages,
    COUNT(event_id) AS counts
  FROM snowflake.account_usage.login_history
  WHERE
    DATEDIFF(HOUR, event_timestamp, CURRENT_TIMESTAMP) < 24
    AND event_type = 'LOGIN'
    AND error_code is NOT NULL
    AND error_code != 394304 -- Ignore JWT Fingerprint Mismatch
  GROUP BY client_ip, reported_client_type
  HAVING counts >= 5;
Schedule:
  RateMinutes: 1440
  TimeoutMinutes: 2

AnalysisType: scheduled_query
QueryName: "Query.Snowflake.BruteForceByUsername"
Enabled: false
Description: >
  Detect brute force attempts by monitoring for failed logins to snowflake.
Query: |
  --return users with more than 5 failed logins in the previous 24 hours

  --this was adapted from a SnowAlert query

  SELECT
    user_name,
    reported_client_type,
    ARRAY_AGG(DISTINCT error_code) as error_codes,
    ARRAY_AGG(DISTINCT error_message) as error_messages,
    COUNT(event_id) AS counts
  FROM snowflake.account_usage.login_history
  WHERE
    DATEDIFF(HOUR, event_timestamp, CURRENT_TIMESTAMP) < 24
    AND event_type = 'LOGIN'
    AND error_code IS NOT NULL
    AND error_code != 394304 -- Ignore JWT Fingerprint Mismatch
  GROUP BY reported_client_type, user_name
  HAVING counts >=5;
Schedule:
  CronExpression: "0 0 * * *"
  TimeoutMinutes: 2

AnalysisType: scheduled_query
Enabled: false
QueryName: "Query.Snowflake.ClientIp"
Description: >
  Monitor for malicious IPs interacting with Snowflake as part of ongoing cyber threat activity reported May 31st, 2024
Query: |
  -- https://community.snowflake.com/s/article/Communication-ID-0108977-Additional-Information

  SELECT
    event_timestamp as p_event_time,
    *
  FROM
      snowflake.account_usage.login_history
  WHERE
      client_ip IN (
  '104.223.91.28',
  '198.54.135.99',
  '184.147.100.29',
  '146.70.117.210',
  '198.54.130.153',
  '169.150.203.22',
  '185.156.46.163',
  '146.70.171.99',
  '206.217.206.108',
  '45.86.221.146',
  '193.32.126.233',
  '87.249.134.11',
  '66.115.189.247',
  '104.129.24.124',
  '146.70.171.112',
  '198.54.135.67',
  '146.70.124.216',
  '45.134.142.200',
  '206.217.205.49',
  '146.70.117.56',
  '169.150.201.25',
  '66.63.167.147',
  '194.230.144.126',
  '146.70.165.227',
  '154.47.30.137',
  '154.47.30.150',
  '96.44.191.140',
  '146.70.166.176',
  '198.44.136.56',
  '176.123.6.193',
  '192.252.212.60',
  '173.44.63.112',
  '37.19.210.34',
  '37.19.210.21',
  '185.213.155.241',
  '198.44.136.82',
  '93.115.0.49',
  '204.152.216.105',
  '198.44.129.82',
  '185.248.85.59',
  '198.54.131.152',
  '102.165.16.161',
  '185.156.46.144',
  '45.134.140.144',
  '198.54.135.35',
  '176.123.3.132',
  '185.248.85.14',
  '169.150.223.208',
  '162.33.177.32',
  '194.230.145.67',
  '5.47.87.202',
  '194.230.160.5',
  '194.230.147.127',
  '176.220.186.152',
  '194.230.160.237',
  '194.230.158.178',
  '194.230.145.76',
  '45.155.91.99',
  '194.230.158.107',
  '194.230.148.99',
  '194.230.144.50',
  '185.204.1.178',
  '79.127.217.44',
  '104.129.24.115',
  '146.70.119.24',
  '138.199.34.144'
      )
  AND p_occurs_since('1 day')
  ORDER BY p_event_time
  LIMIT 100;
Schedule:
  RateMinutes: 1440
  TimeoutMinutes: 3

AnalysisType: scheduled_query
Enabled: false
QueryName: "Query.Snowflake.ConfigurationDrift"
Description: >
  Monitor for configuration drift made by malicious actors as part of ongoing cyber threat activity reported May 31st, 2024
Tags:
  - Configuration Required
SnowflakeQuery: |
  -- https://community.snowflake.com/s/article/Communication-ID-0108977-Additional-Information

  -- adjust query/limit to narrow as necessary

  SELECT
      query_text,
      user_name,
      role_name,
      start_time as p_event_time,
      end_time
  FROM snowflake.account_usage.query_history
    WHERE ((execution_status = 'SUCCESS'
      AND query_type NOT in ('SELECT', 'EXPLAIN')
      AND (
        user_name NOT in ('PANTHER_ADMIN', 'PANTHERACCOUNTADMIN')
        AND NOT (
          role_name LIKE 'PANTHER_READONLY%'
          AND query_text LIKE 'COPY INTO @panther_lookups.public.panther_lut_export_stage%'
        )
      )
      AND user_name NOT in ('PANTHER_ADMIN', 'PANTHERACCOUNTADMIN')
      AND (query_text ILIKE '%create role%'
          OR query_text ILIKE '%manage grants%'
          OR query_text ILIKE '%create integration%'
          OR query_text ILIKE '%alter integration%'
          OR query_text ILIKE '%create share%'
          OR query_text ILIKE '%create account%'
          OR query_text ILIKE '%monitor usage%'
          OR query_text ILIKE '%ownership%'
          OR query_text ILIKE '%drop table%'
          OR query_text ILIKE '%drop database%'
          OR query_text ILIKE '%create stage%'
          OR query_text ILIKE '%drop stage%'
          OR query_text ILIKE '%alter stage%'
          OR query_text ILIKE '%create user%'
          OR query_text ILIKE '%alter user%'
          OR query_text ILIKE '%drop user%'
          OR query_text ILIKE '%create_network_policy%'
          OR query_text ILIKE '%alter_network_policy%'
          OR query_text ILIKE '%drop_network_policy%'
          OR query_text ILIKE '%copy%'
          )
      ) OR (
        query_text ilike '%grant%accountadmin%to%'
        AND query_type = 'GRANT'
        AND execution_status = 'SUCCESS'
      ))
    AND p_occurs_since('1 day')
    ORDER BY end_time desc
    LIMIT 100;

DatabricksQuery: |
  -- https://community.snowflake.com/s/article/Communication-ID-0108977-Additional-Information

  -- adjust query/limit to narrow as necessary

  SELECT
      query_text,
      user_name,
      role_name,
      start_time as p_event_time,
      end_time
  FROM panther_logs.snowflake_queryhistory
    WHERE ((execution_status = 'SUCCESS'
      AND query_type NOT in ('SELECT', 'EXPLAIN')
      AND (
        user_name NOT in ('PANTHER_ADMIN', 'PANTHERACCOUNTADMIN')
        AND NOT (
          role_name LIKE 'PANTHER_READONLY%'
          AND query_text LIKE 'COPY INTO @panther_lookups.panther_lut_export_stage%'
        )
      )
      AND user_name NOT in ('PANTHER_ADMIN', 'PANTHERACCOUNTADMIN')
      AND (query_text ILIKE '%create role%'
          OR query_text ILIKE '%manage grants%'
          OR query_text ILIKE '%create integration%'
          OR query_text ILIKE '%alter integration%'
          OR query_text ILIKE '%create share%'
          OR query_text ILIKE '%create account%'
          OR query_text ILIKE '%monitor usage%'
          OR query_text ILIKE '%ownership%'
          OR query_text ILIKE '%drop table%'
          OR query_text ILIKE '%drop database%'
          OR query_text ILIKE '%create stage%'
          OR query_text ILIKE '%drop stage%'
          OR query_text ILIKE '%alter stage%'
          OR query_text ILIKE '%create user%'
          OR query_text ILIKE '%alter user%'
          OR query_text ILIKE '%drop user%'
          OR query_text ILIKE '%create_network_policy%'
          OR query_text ILIKE '%alter_network_policy%'
          OR query_text ILIKE '%drop_network_policy%'
          OR query_text ILIKE '%copy%'
          )
      ) OR (
        query_text ilike '%grant%accountadmin%to%'
        AND query_type = 'GRANT'
        AND execution_status = 'SUCCESS'
      ))
    AND p_occurs_since('1 day')
    ORDER BY end_time desc
    LIMIT 100
Schedule:
  RateMinutes: 1440
  TimeoutMinutes: 3

AnalysisType: scheduled_query
QueryName: "Query.Snowflake.CopyIntoStage"
Enabled: true
Description: https://cloud.google.com/blog/topics/threat-intelligence/unc5537-snowflake-data-theft-extortion/
SnowflakeQuery: |
    SELECT
    user_name,
    role_name,
    start_time AS p_event_time,
    query_type,
    execution_status,
    regexp_substr(query_text, 'COPY\\s+INTO\\s+(\\$\\$|\\\')?@([a-zA-Z0-9_\\.]+)', 1, 1, 'i', 2) as stage,
    regexp_substr(query_text, 'COPY\\s+INTO\\s+(\\$\\$|\\\')?@([a-zA-Z0-9_\\./]+)(\\$\\$|\\\')?\\s+FROM', 1, 1, 'i', 2) as path,
    query_text

    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE query_type = 'UNLOAD'
        AND stage IS NOT NULL
        AND p_occurs_since('1 day')
        AND execution_status = 'SUCCESS'
    LIMIT 100

DatabricksQuery: |
    SELECT
    user_name,
    role_name,
    start_time AS p_event_time,
    query_type,
    execution_status,
    regexp_extract(query_text, '(?i)COPY\\s+INTO\\s+(\\$\\$|\\\')?@([a-zA-Z0-9_\\.]+)', 2) as stage,
    regexp_extract(query_text, '(?i)COPY\\s+INTO\\s+(\\$\\$|\\\')?@([a-zA-Z0-9_\\./]+)(\\$\\$|\\\')?\\s+FROM', 2) as path,
    query_text

    FROM panther_logs.snowflake_queryhistory
    WHERE query_type = 'UNLOAD'
        AND regexp_extract(query_text, '(?i)COPY\\s+INTO\\s+(\\$\\$|\\\')?@([a-zA-Z0-9_\\.]+)', 2) != ''
        AND p_occurs_since('1 day')
        AND execution_status = 'SUCCESS'
    LIMIT 100
Schedule:
    RateMinutes: 1440
    TimeoutMinutes: 5
Tags:
    - data exfil

AnalysisType: scheduled_query
QueryName: "Query.Snowflake.External.Shares"
Enabled: false
Description: >
  Monitor for external shares from one cloud source to another.
Query: |
  --return external shares

  --this was adapted from a Security Feature Checklist query

  SELECT
    start_time as p_event_time,
    *
  FROM snowflake.account_usage.data_transfer_history
    WHERE
      p_occurs_since('1 day')
      AND p_event_time IS NOT NULL
      AND source_cloud IS NOT NULL
      AND target_cloud IS NOT NULL
      AND bytes_transferred > 0
  ORDER BY p_event_time desc
Schedule:
  CronExpression: "0 0 * * *"
  TimeoutMinutes: 2

AnalysisType: scheduled_query
QueryName: "Query.Snowflake.FailedLogins"
Enabled: false
Description: >
  Detect brute force attempts by monitoring for failed logins to snowflake.
Query: |
  --return all failed failed logins in the previous 24 hours

  --this was adapted from a SnowAlert query

  SELECT
    client_ip,
    user_name,
    reported_client_type,
    error_code,
    error_message,
    count(distinct event_id) over (partition by client_ip) as count_by_ip,
    count(distinct event_id) over (partition by user_name) as count_by_username
  FROM snowflake.account_usage.login_history
  WHERE
    p_occurs_since(1d, , event_timestamp)
    AND event_type = 'LOGIN'
    AND error_code is NOT NULL
Schedule:
  RateMinutes: 1440
  TimeoutMinutes: 2

AnalysisType: scheduled_query
QueryName: "Query.Snowflake.FileDownloaded"
Enabled: true
Description: https://cloud.google.com/blog/topics/threat-intelligence/unc5537-snowflake-data-theft-extortion/
SnowflakeQuery: |
    SELECT
    user_name,
    role_name,
    start_time AS p_event_time,
    query_type,
    execution_status,
    regexp_substr(query_text, 'GET\\s+(\\$\\$|\\\')?@([a-zA-Z0-9_\\.]+)', 1, 1, 'i', 2) as stage,
    regexp_substr(query_text, 'GET\\s+(\\$\\$|\\\')?@([a-zA-Z0-9_\\./]+)(\\$\\$|\\\')?\\s', 1, 1, 'i', 2) as path,
    query_text

    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE query_type = 'GET_FILES'
        AND path IS NOT NULL
        AND p_occurs_since('1 day')
        AND execution_status = 'SUCCESS'
    LIMIT 100

DatabricksQuery: |
    SELECT
    user_name,
    role_name,
    start_time AS p_event_time,
    query_type,
    execution_status,
    regexp_extract(query_text, '(?i)GET\\s+(\\$\\$|\\\')?@([a-zA-Z0-9_\\.]+)', 2) as stage,
    regexp_extract(query_text, '(?i)GET\\s+(\\$\\$|\\\')?@([a-zA-Z0-9_\\./]+)(\\$\\$|\\\')?\\s', 2) as path,
    query_text

    FROM panther_logs.snowflake_queryhistory
    WHERE query_type = 'GET_FILES'
        AND regexp_extract(query_text, '(?i)GET\\s+(\\$\\$|\\\')?@([a-zA-Z0-9_\\./]+)(\\$\\$|\\\')?\\s', 2) != ''
        AND p_occurs_since('1 day')
        AND execution_status = 'SUCCESS'
    LIMIT 100
Schedule:
    RateMinutes: 1440
    TimeoutMinutes: 5
Tags:
    - data exfil

AnalysisType: scheduled_query
QueryName: "Query.Snowflake.KeyUserPasswordLogin"
Enabled: false
Description: >
  Detects when a user with a configured RSA key logs in with a password
Query: |
  --return instances where a user who has key-based login configured logs in with a password
  --this was adapted from a Security Feature Checklist query

   SELECT
     u.name,
     first_authentication_factor,
     second_authentication_factor,
     count(*) as counts
  FROM snowflake.account_usage.login_history as l
  JOIN snowflake.account_usage.users u on l.user_name = u.name and has_rsa_public_key = 'true'
   WHERE is_success = 'YES'
   AND first_authentication_factor != 'RSA_KEYPAIR'
   AND DATEDIFF(HOUR, event_timestamp, CURRENT_TIMESTAMP) < 24
   GROUP BY name, first_authentication_factor, second_authentication_factor
   ORDER BY count(*) desc
Schedule:
  RateMinutes: 1440
  TimeoutMinutes: 3

AnalysisType: scheduled_query
QueryName: "Query.Snowflake.MFALogin"
Enabled: false
Description: >
  Monitor logins that are not using MFA.
Query: |
  --return instances where a user logs in without MFA

  --this was adapted from a Security Feature Checklist query

  SELECT
    event_timestamp as p_event_time,
    user_name,
    client_ip,
    reported_client_type,
    reported_client_version,
    first_authentication_factor,
    second_authentication_factor
  FROM snowflake.account_usage.login_history
    WHERE
      p_occurs_since('1 day')
      AND event_type = 'LOGIN'
      AND first_authentication_factor = 'PASSWORD'
      AND second_authentication_factor IS null
  ORDER BY p_event_time desc
Schedule:
  RateMinutes: 1440
  TimeoutMinutes: 3

AnalysisType: scheduled_query
QueryName: "Query.Snowflake.Multiple.Logins.Followed.By.Success"
Enabled: false
Description: >
  Monitor for brute force user activity.
Query: |
  --return multiple failed logins followed by a success

  WITH login_attempts as (
  SELECT
      user_name,
      client_ip,
      reported_client_type,
      error_code,
      error_message,
      event_id,
      event_timestamp as p_event_time
    FROM snowflake.account_usage.login_history
    WHERE
      p_occurs_since('1 day')
      AND event_type = 'LOGIN'
      AND client_ip != '0.0.0.0' -- filtering out unnecessary 'elided' snowflake entries
  )
  SELECT
  *
  FROM login_attempts
  MATCH_RECOGNIZE(
      PARTITION BY client_ip, user_name
      ORDER BY p_event_time DESC -- backwards in time
      MEASURES
        match_number() as match_number,
        first(p_event_time) as successful_login_time,
        last(p_event_time) as start_of_unsuccessful_logins_time,
        count(*) as rows_in_sequence,
        count(row_with_success.*) as num_successes,
        count(row_with_fail.*) as num_fails,
        ARRAY_AGG(DISTINCT error_code) as error_codes,
        ARRAY_AGG(DISTINCT error_message) as error_messages
      ONE ROW PER MATCH
      AFTER MATCH SKIP TO LAST row_with_fail
      -- a success with fails following
      PATTERN(row_with_success row_with_fail+)
      DEFINE
        row_with_success AS error_message is null,
        row_with_fail AS error_message is not null
    )
  HAVING num_fails >= 5 -- changeable per environment
Schedule:
  CronExpression: "0 0 * * *"
  TimeoutMinutes: 3

AnalysisType: scheduled_query
Enabled: false
QueryName: "Query.Snowflake.SuspectedUserAccess"
Description: >
  Return sessions of suspected clients as part of ongoing cyber threat activity reported May 31st, 2024
SnowflakeQuery: |
  -- https://community.snowflake.com/s/article/Communication-ID-0108977-Additional-Information

  SELECT
    CREATED_ON as p_event_time,
    *
  FROM
      snowflake.account_usage.sessions
  WHERE
      PARSE_JSON(CLIENT_ENVIRONMENT):APPLICATION = 'rapeflake'
      OR
      (
          PARSE_JSON(CLIENT_ENVIRONMENT):APPLICATION = 'DBeaver_DBeaverUltimate'
          AND
          PARSE_JSON(CLIENT_ENVIRONMENT):OS = 'Windows Server 2022'
      )
  AND p_occurs_since('1 day')
  ORDER BY p_event_time
  LIMIT 100;

DatabricksQuery: |
  -- https://community.snowflake.com/s/article/Communication-ID-0108977-Additional-Information

  SELECT
    CREATED_ON as p_event_time,
    *
  FROM
      panther_logs.snowflake_sessions
  WHERE
      (
          CLIENT_ENVIRONMENT.APPLICATION = 'rapeflake'
          OR
          (
              CLIENT_ENVIRONMENT.APPLICATION = 'DBeaver_DBeaverUltimate'
              AND
              CLIENT_ENVIRONMENT.OS = 'Windows Server 2022'
          )
      )
  AND p_occurs_since('1 day')
  ORDER BY p_event_time
  LIMIT 100
Schedule:
  RateMinutes: 1440
  TimeoutMinutes: 3

AnalysisType: scheduled_query
QueryName: "Query.Snowflake.TempStageCreated"
Enabled: true
Description: https://cloud.google.com/blog/topics/threat-intelligence/unc5537-snowflake-data-theft-extortion/
SnowflakeQuery: |
    SELECT
    user_name,
    role_name,
    start_time AS p_event_time,
    query_type,
    execution_status,
    regexp_substr(query_text, 'CREATE\\s+(OR\\s+REPLACE\\s+)?(TEMPORARY\\s+|TEMP\\s+)STAGE\\s+(IF\\s+NOT\\s+EXISTS\\s+)?([a-zA-Z0-9_\\.]+)', 1, 1, 'i', 4) as stage,
    query_text

    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE query_type = 'CREATE'
        AND stage IS NOT NULL
        AND p_occurs_since('1 day')
        AND execution_status = 'SUCCESS'
    LIMIT 100

DatabricksQuery: |
    SELECT
    user_name,
    role_name,
    start_time AS p_event_time,
    query_type,
    execution_status,
    regexp_extract(query_text, '(?i)CREATE\\s+(OR\\s+REPLACE\\s+)?(TEMPORARY\\s+|TEMP\\s+)STAGE\\s+(IF\\s+NOT\\s+EXISTS\\s+)?([a-zA-Z0-9_\\.]+)', 4) as stage,
    query_text

    FROM panther_logs.snowflake_queryhistory
    WHERE query_type = 'CREATE'
        AND regexp_extract(query_text, '(?i)CREATE\\s+(OR\\s+REPLACE\\s+)?(TEMPORARY\\s+|TEMP\\s+)STAGE\\s+(IF\\s+NOT\\s+EXISTS\\s+)?([a-zA-Z0-9_\\.]+)', 4) != ''
        AND p_occurs_since('1 day')
        AND execution_status = 'SUCCESS'
    LIMIT 100
Schedule:
    RateMinutes: 1440
    TimeoutMinutes: 5
Tags:
    - data exfil

AnalysisType: saved_query
QueryName: "Query.Snowflake.ThreatHunting.ClientIp"
Description: >
  Monitor for malicious IPs interacting with Snowflake as part of ongoing cyber threat activity reported May 31st, 2024
Query: |
  -- https://community.snowflake.com/s/article/Communication-ID-0108977-Additional-Information

  SELECT
    event_timestamp as p_event_time,
    *
  FROM
      snowflake.account_usage.login_history
  WHERE
      client_ip IN (
  '104.223.91.28',
  '198.54.135.99',
  '184.147.100.29',
  '146.70.117.210',
  '198.54.130.153',
  '169.150.203.22',
  '185.156.46.163',
  '146.70.171.99',
  '206.217.206.108',
  '45.86.221.146',
  '193.32.126.233',
  '87.249.134.11',
  '66.115.189.247',
  '104.129.24.124',
  '146.70.171.112',
  '198.54.135.67',
  '146.70.124.216',
  '45.134.142.200',
  '206.217.205.49',
  '146.70.117.56',
  '169.150.201.25',
  '66.63.167.147',
  '194.230.144.126',
  '146.70.165.227',
  '154.47.30.137',
  '154.47.30.150',
  '96.44.191.140',
  '146.70.166.176',
  '198.44.136.56',
  '176.123.6.193',
  '192.252.212.60',
  '173.44.63.112',
  '37.19.210.34',
  '37.19.210.21',
  '185.213.155.241',
  '198.44.136.82',
  '93.115.0.49',
  '204.152.216.105',
  '198.44.129.82',
  '185.248.85.59',
  '198.54.131.152',
  '102.165.16.161',
  '185.156.46.144',
  '45.134.140.144',
  '198.54.135.35',
  '176.123.3.132',
  '185.248.85.14',
  '169.150.223.208',
  '162.33.177.32',
  '194.230.145.67',
  '5.47.87.202',
  '194.230.160.5',
  '194.230.147.127',
  '176.220.186.152',
  '194.230.160.237',
  '194.230.158.178',
  '194.230.145.76',
  '45.155.91.99',
  '194.230.158.107',
  '194.230.148.99',
  '194.230.144.50',
  '185.204.1.178',
  '79.127.217.44',
  '104.129.24.115',
  '146.70.119.24',
  '138.199.34.144'
      )
  ORDER BY p_event_time
  LIMIT 100;

AnalysisType: saved_query
QueryName: "Query.Snowflake.ThreatHunting.ConfigurationDrift"
Description: >
  Monitor for configuration drift made by malicious actors as part of ongoing cyber threat activity reported May 31st, 2024
SnowflakeQuery: |
  -- https://community.snowflake.com/s/article/Communication-ID-0108977-Additional-Information

  -- adjust query/limit to narrow as necessary

  SELECT
      query_text,
      user_name,
      role_name,
      start_time as p_event_time,
      end_time
  FROM snowflake.account_usage.query_history
    WHERE ((execution_status = 'SUCCESS'
      AND query_type NOT in ('SELECT')
      AND user_name NOT in ('PANTHER_ADMIN', 'PANTHERACCOUNTADMIN')
      AND (query_text ILIKE '%create role%'
          OR query_text ILIKE '%manage grants%'
          OR query_text ILIKE '%create integration%'
          OR query_text ILIKE '%alter integration%'
          OR query_text ILIKE '%create share%'
          OR query_text ILIKE '%create account%'
          OR query_text ILIKE '%monitor usage%'
          OR query_text ILIKE '%ownership%'
          OR query_text ILIKE '%drop table%'
          OR query_text ILIKE '%drop database%'
          OR query_text ILIKE '%create stage%'
          OR query_text ILIKE '%drop stage%'
          OR query_text ILIKE '%alter stage%'
          OR query_text ILIKE '%create user%'
          OR query_text ILIKE '%alter user%'
          OR query_text ILIKE '%drop user%'
          OR query_text ILIKE '%create_network_policy%'
          OR query_text ILIKE '%alter_network_policy%'
          OR query_text ILIKE '%drop_network_policy%'
          OR query_text ILIKE '%copy%'
          )
      ) OR (
        query_text ilike '%grant%accountadmin%to%'
        AND query_type = 'GRANT'
        AND execution_status = 'SUCCESS'
      ))
    ORDER BY end_time desc
    LIMIT 100;

DatabricksQuery: |
  -- https://community.snowflake.com/s/article/Communication-ID-0108977-Additional-Information

  -- adjust query/limit to narrow as necessary

  SELECT
      query_text,
      user_name,
      role_name,
      start_time as p_event_time,
      end_time
  FROM panther_logs.snowflake_queryhistory
    WHERE ((execution_status = 'SUCCESS'
      AND query_type NOT in ('SELECT')
      AND user_name NOT in ('PANTHER_ADMIN', 'PANTHERACCOUNTADMIN')
      AND (query_text ILIKE '%create role%'
          OR query_text ILIKE '%manage grants%'
          OR query_text ILIKE '%create integration%'
          OR query_text ILIKE '%alter integration%'
          OR query_text ILIKE '%create share%'
          OR query_text ILIKE '%create account%'
          OR query_text ILIKE '%monitor usage%'
          OR query_text ILIKE '%ownership%'
          OR query_text ILIKE '%drop table%'
          OR query_text ILIKE '%drop database%'
          OR query_text ILIKE '%create stage%'
          OR query_text ILIKE '%drop stage%'
          OR query_text ILIKE '%alter stage%'
          OR query_text ILIKE '%create user%'
          OR query_text ILIKE '%alter user%'
          OR query_text ILIKE '%drop user%'
          OR query_text ILIKE '%create_network_policy%'
          OR query_text ILIKE '%alter_network_policy%'
          OR query_text ILIKE '%drop_network_policy%'
          OR query_text ILIKE '%copy%'
          )
      ) OR (
        query_text ilike '%grant%accountadmin%to%'
        AND query_type = 'GRANT'
        AND execution_status = 'SUCCESS'
      ))
    ORDER BY end_time desc
    LIMIT 100

AnalysisType: saved_query
QueryName: "Query.Snowflake.ThreatHunting.SuspectedUserAccess"
Description: >
  Return sessions of suspected clients as part of ongoing cyber threat activity reported May 31st, 2024
SnowflakeQuery: |
  -- https://community.snowflake.com/s/article/Communication-ID-0108977-Additional-Information

  SELECT
    CREATED_ON as p_event_time,
    *
  FROM
      snowflake.account_usage.sessions
  WHERE
      PARSE_JSON(CLIENT_ENVIRONMENT):APPLICATION = 'rapeflake'
      OR
      (
          PARSE_JSON(CLIENT_ENVIRONMENT):APPLICATION = 'DBeaver_DBeaverUltimate'
          AND
          PARSE_JSON(CLIENT_ENVIRONMENT):OS = 'Windows Server 2022'
      )
  ORDER BY p_event_time
  LIMIT 100;

DatabricksQuery: |
  -- https://community.snowflake.com/s/article/Communication-ID-0108977-Additional-Information

  SELECT
    CREATED_ON as p_event_time,
    *
  FROM
      panther_logs.snowflake_sessions
  WHERE
      CLIENT_ENVIRONMENT.APPLICATION = 'rapeflake'
      OR
      (
          CLIENT_ENVIRONMENT.APPLICATION = 'DBeaver_DBeaverUltimate'
          AND
          CLIENT_ENVIRONMENT.OS = 'Windows Server 2022'
      )
  ORDER BY p_event_time
  LIMIT 100

AnalysisType: saved_query
QueryName: "Query.Snowflake.ThreatHunting.SuspectedUserActivity"
Description: >
  Return actions/queries made by suspected users as part of ongoing cyber threat activity reported May 31st, 2024
SnowflakeQuery: |
  -- https://community.snowflake.com/s/article/Communication-ID-0108977-Additional-Information

  -- replace <SUSPECTED_USER> with actual user name

  SELECT
    *
  FROM
      snowflake.account_usage.query_history
  WHERE
      user_name = '<SUSPECTED_USER>'
      AND start_time BETWEEN '2024-04-01'
      AND CURRENT_TIMESTAMP
  ORDER BY
  start_time
  LIMIT 100;

DatabricksQuery: |
  -- https://community.snowflake.com/s/article/Communication-ID-0108977-Additional-Information

  -- replace <SUSPECTED_USER> with actual user name

  SELECT
    *
  FROM
      panther_logs.snowflake_queryhistory
  WHERE
      user_name = '<SUSPECTED_USER>'
      AND start_time BETWEEN '2024-04-01'
      AND CURRENT_TIMESTAMP
  ORDER BY
  start_time
  LIMIT 100

AnalysisType: scheduled_query
QueryName: "Query.Snowflake.UserCreated"
Enabled: false
Description: >
  Monitor for new users.
SnowflakeQuery: |
  --return create user events

  --this was adapted from a Security Feature Checklist query

  SELECT
    start_time as p_event_time,
    end_time,
    query_type,
    query_text,
    user_name,
    role_name
  FROM snowflake.account_usage.query_history
    WHERE
      p_occurs_since('1 day')
      AND execution_status = 'SUCCESS'
      AND query_type = 'CREATE_USER'
      AND query_text ILIKE '%create%user%'
  ORDER BY end_time desc

DatabricksQuery: |
  --return create user events

  --this was adapted from a Security Feature Checklist query

  SELECT
    start_time as p_event_time,
    end_time,
    query_type,
    query_text,
    user_name,
    role_name
  FROM panther_logs.snowflake_queryhistory
    WHERE
      p_occurs_since('1 day')
      AND execution_status = 'SUCCESS'
      AND query_type = 'CREATE_USER'
      AND query_text ILIKE '%create%user%'
  ORDER BY end_time desc
Schedule:
  CronExpression: "0 0 * * *"
  TimeoutMinutes: 3

AnalysisType: scheduled_query
QueryName: "Query.Snowflake.UserEnabled"
Enabled: false
Description: >
  Monitor for users that are being re-enabled.
SnowflakeQuery: |
  --return enable user events

  --this was adapted from a Security Feature Checklist query

  SELECT
    start_time as p_event_time,
    end_time,
    query_type,
    query_text,
    user_name,
    role_name
  FROM snowflake.account_usage.query_history
    WHERE
      p_occurs_since('1 day')
      AND execution_status = 'SUCCESS'
      AND query_type = 'ALTER_USER'
      AND (query_text ILIKE '%alter user%set disabled = false%'
          OR query_text ILIKE '%alter user%set disabled= false%'
          OR query_text ILIKE '%alter user%set disabled =false%'
          OR query_text ILIKE '%alter user%set disabled=false%')
  ORDER BY end_time desc

DatabricksQuery: |
  --return enable user events

  --this was adapted from a Security Feature Checklist query

  SELECT
    start_time as p_event_time,
    end_time,
    query_type,
    query_text,
    user_name,
    role_name
  FROM panther_logs.snowflake_queryhistory
    WHERE
      p_occurs_since('1 day')
      AND execution_status = 'SUCCESS'
      AND query_type = 'ALTER_USER'
      AND (query_text ILIKE '%alter user%set disabled = false%'
          OR query_text ILIKE '%alter user%set disabled= false%'
          OR query_text ILIKE '%alter user%set disabled =false%'
          OR query_text ILIKE '%alter user%set disabled=false%')
  ORDER BY end_time desc
Schedule:
  CronExpression: "0 0 * * *"
  TimeoutMinutes: 3

def rule(_):
    return True


def title(event):
    target = " ".join(event.get("query_text", "").split(" ")[-2:])
    return f"Snowflake AccountAdmin granted to {target}"

AnalysisType: scheduled_rule
Filename: snowflake_account_admin_assigned.py
RuleID: "Snowflake.AccountAdminGranted"
Description: >
  Detect when account admin is granted.
DisplayName: "Snowflake Account Admin Granted"
Enabled: false
Tags:
  - Snowflake
  - Privilege Escalation:Valid Accounts
Reports:
  MITRE ATT&CK:
    - TA0004:T1078
ScheduledQueries:
  - Query.Snowflake.AccountAdminGranted
Severity: Medium

def rule(event):
    if event.get("DELETED_ON"):
        return False
    return "admin" in event.get("GRANTEE_NAME", "").lower()


def title(event):
    source_name = event.get("p_source_label", "<UNKNOWN SNOWFLAKE SOURCE>")
    target = event.get("GRANTED_TO", "<UNKNOWN TARGET>")
    actor = event.get("GRANTED_BY", "<UNKNOWN ACTOR>")
    role = event.get("GRANTEE_NAME", "<UNKNOWN ROLE>")
    return f"{source_name}: {actor} granted role {role} to {target}"

AnalysisType: rule
Filename: snowflake_stream_account_admin_assigned.py
RuleID: "Snowflake.Stream.AccountAdminGranted"
DisplayName: Snowflake Account Admin Granted
Enabled: true
LogTypes:
  - Snowflake.GrantsToUsers
Severity: Medium
Reports:
  MITRE ATT&CK:
    - TA0004:T1078
Description: Detect when account admin is granted.
Tags:
  - Snowflake
  - '[MITRE] Privilege Escalation'
  - '[MITRE] Valid Accounts'

def rule(_):
    return True


def title(event):
    return (
        f"Snowflake: {event.get('count_by_ip', 'many')} failed login attempts from IP "
        f"[{event.get('client_ip','<UNKNOWN_USER>')}]"
    )


def severity(event):
    # If the error appears to be caused by an automation issue, downgrade to INFO
    common_errors = {"JWT_TOKEN_INVALID_PUBLIC_KEY_FINGERPRINT_MISMATCH"}
    if event.get("ERROR_MESSAGE") in common_errors:
        return "INFO"
    return "DEFAULT"


def dedup(event):
    # Dedup on title and severity
    return f"[{severity(event)}] {title(event)}"

AnalysisType: scheduled_rule
Filename: snowflake_brute_force_ip.py
RuleID: "Snowflake.BruteForceByIp"
DisplayName: "Snowflake Brute Force Attacks by IP"
Enabled: false
ScheduledQueries:
  - Query.Snowflake.FailedLogins
Severity: Medium
Reports:
  MITRE ATT&CK:
    - TA0006:T1110
Description: >
  Detect brute force attacks by monitoring for failed logins from the same IP address
Threshold: 5
SummaryAttributes:
  - error_message
  - error_code
  - reported_client_type
  - user_name
Tags:
  - Snowflake
  - Credential Access:Brute Force

def rule(event):
    # Return true for any login attempt; Let Panther's dedup and threshold handle the brute force
    #   detection.
    return (
        event.get("EVENT_TYPE") == "LOGIN"
        and event.get("IS_SUCCESS") == "NO"
        and event.get("ERROR_MESSAGE") != "OVERFLOW_FAILURE_EVENTS_ELIDED"
        # ^^ OVERFLOW_FAILURE_EVENTS_ELIDED are placeholder logs -> no point in alerting
    )


def title(event):
    return (
        "Login attempts from IP "
        f"{event.get('CLIENT_IP', '<UNKNOWN IP>')} "
        "have exceeded the failed logins threshold"
    )


def severity(event):
    # If the error appears to be caused by an automation issue, downgrade to INFO
    common_errors = {"JWT_TOKEN_INVALID_PUBLIC_KEY_FINGERPRINT_MISMATCH"}
    if event.get("ERROR_MESSAGE") in common_errors:
        return "INFO"
    return "DEFAULT"


def dedup(event):
    return event.get("CLIENT_IP", "<UNKNOWN IP>") + event.get(
        "REPORTED_CLIENT_TYPE", "<UNKNOWN CLIENT TYPE>"
    )

AnalysisType: rule
Filename: snowflake_stream_brute_force_by_ip.py
RuleID: "Snowflake.Stream.BruteForceByIp"
DisplayName: Snowflake Brute Force Attacks by IP
Enabled: true
LogTypes:
  - Snowflake.LoginHistory
Severity: Medium
Reports:
  MITRE ATT&CK:
    - TA0006:T1110
Description: Detect brute force attacks by monitorign failed logins from the same
  IP address
DedupPeriodMinutes: 60
Threshold: 5
Tags:
  - Snowflake
  - '[MITRE] Credential Access'
  - '[MITRE] Brute Force'

def rule(event):
    # Return true for any login attempt; Let Panther's dedup and threshold handle the brute force
    #   detection.
    return (
        event.get("EVENT_TYPE") == "LOGIN"
        and event.get("IS_SUCCESS") == "NO"
        and event.get("ERROR_MESSAGE") != "OVERFLOW_FAILURE_EVENTS_ELIDED"
        # ^^ OVERFLOW_FAILURE_EVENTS_ELIDED are placeholder logs -> no point in alerting
    )


def title(event):
    return (
        f"User {event.get('USER_NAME', '<UNKNOWN USER>')} has exceeded the failed logins threshold"
    )


def severity(event):
    # If the error appears to be caused by an automation issue, downgrade to INFO
    common_errors = {"JWT_TOKEN_INVALID_PUBLIC_KEY_FINGERPRINT_MISMATCH"}
    if event.get("ERROR_MESSAGE") in common_errors:
        return "INFO"
    return "DEFAULT"


def dedup(event):
    return event.get("USER_NAME", "<UNKNOWN USER>") + event.get(
        "REPORTED_CLIENT_TYPE", "<UNKNOWN CLIENT TYPE>"
    )

AnalysisType: rule
Filename: snowflake_stream_brute_force_by_username.py
RuleID: "Snowflake.Stream.BruteForceByUsername"
DisplayName: Snowflake Brute Force Attacks by User
Enabled: true
LogTypes:
  - Snowflake.LoginHistory
Severity: Medium
Reports:
  MITRE ATT&CK:
    - TA0006:T1110
Description: Detect brute force attacks by monitorign failed logins from the same
  IP address
DedupPeriodMinutes: 60
Threshold: 5
Tags:
  - Snowflake
  - '[MITRE] Credential Access'
  - '[MITRE] Brute Force'

def rule(_):
    return True


def title(event):
    return (
        f"Snowflake: {event.get('counts_by_user', 'many')} failed login attempts by user "
        f"[{event.get('user_name','<UNKNOWN_USER>')}]"
    )


def severity(event):
    # If the error appears to be caused by an automation issue, downgrade to INFO
    common_errors = {"JWT_TOKEN_INVALID_PUBLIC_KEY_FINGERPRINT_MISMATCH"}
    if event.get("ERROR_MESSAGE") in common_errors:
        return "INFO"
    return "DEFAULT"


def dedup(event):
    # Dedup on title and severity
    return f"[{severity(event)}] {title(event)}"

AnalysisType: scheduled_rule
Filename: snowflake_brute_force_username.py
RuleID: "Snowflake.BruteForceByUsername"
Description: >
  Detect brute force attacks by monitoring for failed logins by the same username
DisplayName: "Snowflake Brute Force Attacks by Username"
Enabled: false
ScheduledQueries:
  - Query.Snowflake.FailedLogins
Tags:
  - Snowflake
  - Credential Access:Brute Force
Reports:
  MITRE ATT&CK:
    - TA0006:T1110
Severity: Medium
Threshold: 5
SummaryAttributes:
  - client_ip
  - error_message
  - error_code
  - reported_client_type

AnalysisType: correlation_rule
RuleID: "Snowflake.PotentialBruteForceSuccess.Group"
DisplayName: "Snowflake Brute Force Login Success"
Enabled: false
Severity: High
Description: Detecting brute force activity and reporting when a user has incorrectly logged in multiple times and then had a successful login.
Detection:
  - Group:
      - ID: Multiple Failed Logins
        RuleID: Snowflake.Stream.BruteForceByIp
        MinMatchCount: 5
      - ID: Successful Login
        RuleID: Snowflake.Stream.LoginSuccess
    MatchCriteria:
      field_name:
        - GroupID: Multiple Failed Logins
          Match: CLIENT_IP
        - GroupID: Successful Login
          Match: CLIENT_IP
    Schedule:
      RateMinutes: 1440
      TimeoutMinutes: 15
    LookbackWindowMinutes: 1800

def rule(_):
    return True


def title(event):
    client_ip = event.get("client_ip", "<NO_IP_FOUND>")
    user_name = event.get("user_name", "<NO USERNAME FOUND>")
    return f"{user_name} accessed Snowflake from {client_ip}"

AnalysisType: scheduled_rule
Filename: snowflake_0108977_ip.py
RuleID: "Snowflake.Client.IP"
Description: >
  Monitor for malicious IPs interacting with Snowflake as part of ongoing cyber threat activity reported May 31st, 2024
DisplayName: "Snowflake Client IP"
Enabled: false
Runbook: Determine if this occurred as a result of a valid business request.
ScheduledQueries:
  - Query.Snowflake.ClientIp
Severity: High

def rule(_):
    return True


def title(event):
    user_name = event.get("user_name", "<NO USERNAME FOUND>")
    action = event.get("query_text", "<NO QUERY FOUND>").split(" ")[:2]
    target = " ".join(event.get("query_text", "").split(" ")[-2:])
    return f"{user_name} performed {action} on {target}"

AnalysisType: scheduled_rule
Filename: snowflake_0108977_configuration_drift.py
RuleID: "Snowflake.Configuration.Drift"
Description: >
  Monitor for configuration drift made by malicious actors as part of ongoing cyber threat activity reported May 31st, 2024
DisplayName: "Snowflake Configuration Drift"
Enabled: false
Runbook: Determine if this occurred as a result of a valid business request.
ScheduledQueries:
  - Query.Snowflake.ConfigurationDrift
Tags:
  - Configuration Required
Severity: Medium

AnalysisType: correlation_rule
RuleID: "Snowflake.Data.Exfiltration.Group"
DisplayName: "Snowflake Data Exfiltration"
Enabled: false
Tags:
  - Snowflake
  - Data Exfiltration
  - Database
  - Cloud Security
Severity: Critical
Description: >
  Detects multi-step Snowflake data exfiltration by identifying temporary stage creation, table data copied to stage, and file downloads. This technique was used in the April 2024 Snowflake breach (UNC5537) targeting accounts without MFA. The correlation of all three steps provides high-confidence evidence of active data theft beyond legitimate ETL operations.
Runbook: |
  1. Query Snowflake's QUERY_HISTORY and ACCESS_HISTORY views for the stage name to identify the user account, session ID, source IP addresses, client application, and all tables that were copied into the stage
  2. Review the specific tables copied into the stage to determine data sensitivity (PII, financial records, intellectual property) and estimate the volume of data exfiltrated, then check Snowflake's LOGIN_HISTORY to verify if the user account had MFA enabled
  3. Analyze the source IP addresses used during the exfiltration sequence against threat intelligence feeds to determine if they are corporate IPs, suspicious cloud providers, or known malicious infrastructure, and check for impossible travel patterns
Reference: https://cloud.google.com/blog/topics/threat-intelligence/unc5537-snowflake-data-theft-extortion/
Reports:
  MITRE ATT&CK:
    - TA0010:T1041 # Exfiltration: Exfiltration Over C2 Channel
    - TA0010:T1530 # Exfiltration: Data from Cloud Storage
    - TA0009:T1213 # Collection: Data from Information Repositories
Detection:
  - Group:
      - ID: SnowflakeTempStageCreated
        RuleID: Snowflake.TempStageCreated
      - ID: SnowflakeCopyIntoStage
        RuleID: Snowflake.CopyIntoStage
      - ID: SnowflakeFileDownloaded
        RuleID: Snowflake.FileDownloaded
    MatchCriteria:
      field_name:
        - GroupID: SnowflakeTempStageCreated
          Match: stage
        - GroupID: SnowflakeCopyIntoStage
          Match: stage
        - GroupID: SnowflakeFileDownloaded
          Match: stage
    Schedule:
      RateMinutes: 1440
      TimeoutMinutes: 15
    LookbackWindowMinutes: 1800

AnalysisType: correlation_rule
RuleID: "Snowflake.Stream.DataExfiltration.Group"
DisplayName: "Snowflake Data Exfiltration"
Enabled: false
Tags:
  - Snowflake
  - Data Exfiltration
  - Database
  - Cloud Security
Severity: Critical
Description: >
  Detects multi-step Snowflake data exfiltration by identifying temporary stage creation, table data copied to stage, and file downloads. This technique was used in the April 2024 Snowflake breach (UNC5537) targeting accounts without MFA. The correlation of all three steps provides high-confidence evidence of active data theft beyond legitimate ETL operations.
Runbook: |
  1. Query Snowflake's QUERY_HISTORY and ACCESS_HISTORY views for the stage name in p_alert_context.stage to identify the user account, session ID, source IP addresses, client application, and all tables that were copied into the stage
  2. Review the specific tables copied into the stage to determine data sensitivity (PII, financial records, intellectual property) and estimate the volume of data exfiltrated, then check Snowflake's LOGIN_HISTORY to verify if the user account had MFA enabled
  3. Analyze the source IP addresses used during the exfiltration sequence against threat intelligence feeds to determine if they are corporate IPs, suspicious cloud providers, or known malicious infrastructure, and check for impossible travel patterns
Reference: https://cloud.google.com/blog/topics/threat-intelligence/unc5537-snowflake-data-theft-extortion/
Reports:
  MITRE ATT&CK:
    - TA0010:T1041 # Exfiltration: Exfiltration Over C2 Channel
    - TA0010:T1530 # Exfiltration: Data from Cloud Storage
    - TA0009:T1213 # Collection: Data from Information Repositories
Detection:
  - Group:
      - ID: SnowflakeTempStageCreated
        RuleID: Snowflake.Stream.TempStageCreated
      - ID: SnowflakeCopyIntoStage
        RuleID: Snowflake.Stream.TableCopiedIntoStage
      - ID: SnowflakeFileDownloaded
        RuleID: Snowflake.Stream.FileDownloaded
    MatchCriteria:
      field_name:
        - GroupID: SnowflakeTempStageCreated
          Match: p_alert_context.stage
        - GroupID: SnowflakeCopyIntoStage
          Match: p_alert_context.stage
        - GroupID: SnowflakeFileDownloaded
          Match: p_alert_context.stage
    Schedule:
      RateMinutes: 1440
      TimeoutMinutes: 15
    LookbackWindowMinutes: 1800

def rule(event):
    return all(
        [
            event.get("SOURCE_CLOUD"),
            event.get("TARGET_CLOUD"),
            event.get("BYTES_TRANSFERRED", 0) > 0,
        ]
    )


def title(event):
    return (
        f"A data export has been initiated from source cloud "
        f"{event.get('SOURCE_CLOUD', '<UNKNOWN SOURCE CLOUD>')} "
        f"in source region {event.get('SOURCE_REGION', '<UNKNOWN SOURCE REGION>')} "
        f"to target cloud {event.get('TARGET_CLOUD', '<UNKNOWN TARGET CLOUD>')} "
        f"in target region {event.get('TARGET_REGION', '<UNKNOWN TARGET REGION>')} "
        f"with transfer type {event.get('TRANSFER_TYPE', '<UNKNOWN TRANSFER TYPE>')} "
        f"for {event.get('BYTES_TRANSFERRED', '<UNKNOWN VOLUME>')} bytes"
    )

AnalysisType: rule
Filename: snowflake_stream_external_shares.py
RuleID: Snowflake.Stream.ExternalShares
DisplayName: Snowflake External Data Share
Enabled: true
LogTypes:
  - Snowflake.DataTransferHistory
Severity: Medium
Reports:
  MITRE ATT&CK:
    - TA0010:T1537
Description: Detect when an external share has been initiated from one source cloud
  to another target cloud.
Runbook: Determine if this occurred as a result of a valid business request.
Tags:
  - Snowflake
  - '[MITRE] Exfiltration'
  - '[MITRE] Transfer Data to Cloud Account'

def rule(_):
    return True


def title(event):
    return (
        "A data export has been initiated from source cloud "
        f"[{event.get('source_cloud','<SOURCE_CLOUD_NOT_FOUND>')}] "
        f"in source region [{event.get('source_region','<SOURCE_REGION_NOT_FOUND>')}] "
        f"to target cloud [{event.get('target_cloud','<TARGET_CLOUD_NOT_FOUND>')}] "
        f"in target region [{event.get('target_region','<TARGET_REGION_NOT_FOUND>')}] "
        f"with transfer type [{event.get('transfer_type','<TRANSFER_TYPE_NOT_FOUND>')}] "
        f"for [{event.get('bytes_transferred','<BYTES_NOT_FOUND>')}] bytes."
    )

AnalysisType: scheduled_rule
Filename: snowflake_external_shares.py
RuleID: "Snowflake.External.Shares"
Description: >
  Detect when an external share has been initiated from one source cloud to another target cloud.
DisplayName: "Snowflake External Share"
Enabled: false
Runbook: Determine if this occurred as a result of a valid business request.
ScheduledQueries:
  - Query.Snowflake.External.Shares
Severity: Medium

def rule(_):
    return True

AnalysisType: scheduled_rule
Filename: snowflake_file_downloaded_signal.py
RuleID: "Snowflake.FileDownloaded"
Description: >
  A file was downloaded from a stage
DisplayName: "Snowflake File Downloaded"
Enabled: true
CreateAlert: false
Reference: https://cloud.google.com/blog/topics/threat-intelligence/unc5537-snowflake-data-theft-extortion/
Reports:
    MITRE ATT&CK:
        - TA0010:T1041  # Exfiltration Over C2 Channel
ScheduledQueries:
  - Query.Snowflake.FileDownloaded
Severity: Info

import re

from panther_snowflake_helpers import query_history_alert_context

PATH_EXPR = re.compile(r"GET\s+(?:\$\$|')?@([a-zA-Z0-9_\./]+)(?:\$\$|')?\s", flags=re.I)
STAGE_EXPR = re.compile(r"GET\s+(?:\$\$|')?@([a-zA-Z0-9_\.]+)", flags=re.I)

PATH = ""
STAGE = ""


def rule(event):
    # pylint: disable=global-statement
    # Check these conditions first to avoid running an expensive regex on every log
    if not all(
        (
            event.get("QUERY_TYPE") == "GET_FILES",
            event.get("EXECUTION_STATUS") == "SUCCESS",
            # Avoid alerting for fetching worksheets:
            event.get("QUERY_TEXT") != "GET '@~/worksheet_data/metadata' 'file:///'",
        )
    ):
        return False

    global PATH
    PATH = PATH_EXPR.search(event.get("QUERY_TEXT", ""))

    return PATH is not None


def alert_context(event):
    # pylint: disable=global-statement
    global PATH
    global STAGE
    STAGE = STAGE_EXPR.match(event.get("QUERY_TEXT", ""))
    return query_history_alert_context(event) | {
        "path": PATH.group(1),
        "stage": None if not STAGE else STAGE.group(1).lower(),
    }

AnalysisType: rule
Filename: snowflake_stream_file_downloaded.py
RuleID: Snowflake.Stream.FileDownloaded
DisplayName: Snowflake File Downloaded
Enabled: true
LogTypes:
  - Snowflake.QueryHistory
Severity: Info
CreateAlert: false
Reports:
  MITRE ATT&CK:
    - TA0010:T1041 # Exfiltration Over C2 Channel
Description: A file was downloaded from a stage.
Reference: 
  https://cloud.google.com/blog/topics/threat-intelligence/unc5537-snowflake-data-theft-extortion/
Tags:
  - Snowflake
  - '[MITRE] Exfiltration'
  - '[MITRE] Exfiltration Over C2 Channel'

def rule(event):
    return event.get("GRANTEE_NAME").lower() == "public"


def title(event):
    return (
        f"{event.get('p_source_label', '<UNKNOWN LOG SOURCE>')}: "
        f"{event.get('GRANTED_BY', '<UNKNOWN ACTOR>')} made changes to the PUBLIC role"
    )

AnalysisType: rule
Filename: snowflake_stream_public_role_grant.py
RuleID: Snowflake.Stream.PublicRoleGrant
DisplayName: Snowflake Grant to Public Role
Enabled: true
LogTypes:
  - Snowflake.GrantsToRoles
Severity: Medium
Reports:
  MITRE ATT&CK:
    - TA0004:T1078.001
Description: Detect additional grants to the public role.
Runbook: Determine if this is a necessary grant for the public role, which should
  be kept to the fewest possible.
Tags:
  - Snowflake
  - '[MITRE] Privilege Escalation'
  - '[MITRE] Valid Accounts'
  - '[MITRE] Valid Accounts: Default Accounts'

MFA_EXCEPTIONS = {"PANTHER_READONLY", "PANTHER_ADMIN", "PANTHERACCOUNTADMIN"}


def rule(event):
    return event.get("user_name", "") not in MFA_EXCEPTIONS

AnalysisType: scheduled_rule
Filename: snowflake_login_without_mfa.py
RuleID: "Snowflake.LoginWithoutMFA"
Description: >
  Detect snowflake logins without multifactor authentication
DisplayName: "Snowflake Login Without MFA"
Enabled: false
ScheduledQueries:
  - Query.Snowflake.MFALogin
Tags:
  - Snowflake
  - Defense Evasion:Modify Authentication Process
Reports:
  MITRE ATT&CK:
    - TA0005:T1556
Severity: Medium