> For the complete documentation index, see [llms.txt](https://blog.pepperclipp.com/pepperclipp-public/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://blog.pepperclipp.com/pepperclipp-public/others/introducing-yetihunter-an-open-source-tool-to-detect-and-hunt-for-suspicious-activity-in-snowflake.md). # Introducing YetiHunter: An open-source tool to detect and hunt for suspicious activity in Snowflake ## Summary On May 30, 2024 Snowflake confirmed many clients were affected by an attacker leveraging compromised NHI credentials to perform data theft. In [their notice](https://community.snowflake.com/s/question/0D5VI00000Emyl00AB/detecting-and-preventing-unauthorized-user-access), Snowflake included [some indicators](https://community.snowflake.com/s/article/Communication-ID-0108977-Additional-Information) and suggested hunts. Our good friends at Mandiant also provided insight into the attacker (UNC5537) and more hunting opportunities in their [recent blog post](https://cloud.google.com/blog/topics/threat-intelligence/unc5537-snowflake-data-theft-extortion) as well. To summarize the high level facts of the case: * An attacker gained access to over 150 credentials (non-mfa) to client organizations of Snowflake * Mandiant believes the attacker gained access to these credentials through a variety of infostealers * The attacker leveraged these credentials to perform data theft for the purpose of extortion * The earliest evidence of the attackers unauthorized access to a victim organization was on April 14, 2024 To see if your Snowflake instance has been compromised, Permiso has released YetiHunter – an open source tool combining several Indicators of Compromise published by the community. Reach out if you need any assistance in scanning your Snowflake environment. ## Snowflake Hunting with YetiHunter In order to ease the burden of hunting through Snowflake data for indicators, the Permiso team has created YetiHunter. YetiHunter combines the Indicators that [Snowflake](https://community.snowflake.com/s/article/Communication-ID-0108977-Additional-Information), [Mandiant](https://cloud.google.com/blog/topics/threat-intelligence/unc5537-snowflake-data-theft-extortion), [DataDog](https://securitylabs.datadoghq.com/articles/a-guide-to-threat-hunting-and-monitoring-in-snowflake/) and Permiso have into one easy to run script for triage. YetiHunter performs the following queries: | **Query Name** | **Description** | **Query** | | | | | | | | | | | | | | | | --------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | - | --------------- | - | ---------- | - | ----------- | - | --------- | - | ----- | - | ----------- | - | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | select\_all\_without\_where |

select \* queries that do not contain a WHERE clause.


Attackers often use simple select statements without extra filtering via WHERE clauses.

|

SELECT query\_id, user\_name, query\_text
FROM snowflake.account\_usage.query\_history
WHERE query\_text ILIKE 'SELECT \*'
AND query\_text NOT ILIKE '%WHERE%';

| | | | | | | | | | | | | | | | copy\_into\_select\_all |

COPY INTO and select \* in a single query.

Attackers often attempt to combine these two commands to export all records to a temp location.

|

SELECT query\_id, user\_name, query\_text
FROM snowflake.account\_usage.query\_history
WHERE query\_text ILIKE 'COPY INTO%'
AND query\_text ILIKE 'SELECT \*%';

| | | | | | | | | | | | | | | | show\_tables |

Performing a show tables query

show tables is a common recon command that attackers may use to understand the environment they are in.

|

SELECT query\_id, user\_name, query\_text
FROM snowflake.account\_usage.query\_history
WHERE query\_text ILIKE 'SHOW TABLES';

| | | | | | | | | | | | | | | | create\_temp\_storage |

Creating temp storage

Attackers will often create a temporary storage location (usually with a generic name like test) as a staging location.

|

SELECT query\_id, user\_name, query\_text
FROM snowflake.account\_usage.query\_history
WHERE query\_text ILIKE 'CREATE TEMP%’

| | | | | | | | | | | | | | | | 10\_largest\_queries |

Top 10 largest queries by rows\_produced

Review queries that have produced a large amount of rows.

|

SELECT query\_id, user\_name, query\_text, rows\_produced
FROM snowflake.account\_usage.query\_history
WHERE rows\_produced > 2000
ORDER BY rows\_produced DESC
LIMIT 10;

| | | | | | | | | | | | | | | | dbeaver\_used | DBEAVER is a common utility that is used by administrators and attackers alike to interact with many database types to include SNOWFLAKE |

SELECT
created\_on,
user\_name,
authentication\_method,
PARSE\_JSON(client\_environment) :APPLICATION :: STRING AS client\_application,
PARSE\_JSON(client\_environment) :OS :: STRING AS client\_os,
PARSE\_JSON(client\_environment) :OS\_VERSION :: STRING AS client\_os\_version,
session\_id
FROM
snowflake.account\_usage.sessions,
WHERE
PARSE\_JSON(CLIENT\_ENVIRONMENT):APPLICATION = 'DBeaver\_DBeaverUltimate'
ORDER BY CREATED\_ON;

| | | | | | | | | | | | | | | | accountadmin\_changes | Review recent ADMIN role grants |

select user\_name | | ' granted the ' | | role\_name | | ' role on ' | | end\_time | | ' \[' | | query\_text | | ']' as Grants
from snowflake.account\_usage.query\_history where execution\_status = 'SUCCESS'
and query\_type = 'GRANT' and
query\_text ilike '%grant%accountadmin%to%'
order by end\_time desc;

| | impactful\_modifications | This query will list many impactful modifications that occurred in snowflake, review for any suspicious modifications |

SELECT
start\_time,
user\_name,
role\_name,
query\_type,
query\_text
FROM snowflake.account\_usage.query\_history
WHERE execution\_status = 'SUCCESS'
AND query\_type NOT in ('SELECT')
AND query\_type NOT in ('SHOW')
AND query\_type NOT in ('DESCRIBE')
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%'
)
ORDER BY end\_time desc;

| | | | | | | | | | | | | | | | least\_common\_applications\_used | Applications in SNOWFLAKE are much like user-agents. This query will show the least common applications used in your environment. Review the list for any abnormal applications |

SELECT
COUNT(\*) AS client\_app\_count,
PARSE\_JSON(client\_environment) :APPLICATION :: STRING AS client\_application,
PARSE\_JSON(client\_environment) :OS :: STRING AS client\_os,
PARSE\_JSON(client\_environment) :OS\_VERSION :: STRING AS client\_os\_version
FROM
snowflake.account\_usage.sessions sessions
WHERE
1 = 1
AND sessions.created\_on >= '2024-04-01'
GROUP BY
ALL
ORDER BY
1 DESC;

| | | | | | | | | | | | | | | | malicious\_ips | This will check all of the known observed malicious IPs that SNOWFLAKE and Mandiant have released associated with the UNC5537 campaign. | | | | | | | | | | | | | | | | | frostbite\_used | Frostbite is the Mandiant name for one of the applications known used by UNC5537. This query looks for it’s usage. |

SELECT
created\_on,
user\_name,
authentication\_method,
PARSE\_JSON(client\_environment) :APPLICATION :: STRING AS client\_application,
PARSE\_JSON(client\_environment) :OS :: STRING AS client\_os,
PARSE\_JSON(client\_environment) :OS\_VERSION :: STRING AS client\_os\_version,
session\_id
FROM
snowflake.account\_usage.sessions,
WHERE
PARSE\_JSON(CLIENT\_ENVIRONMENT):APPLICATION = 'rapeflake'
ORDER BY CREATED\_ON;

| | | | | | | | | | | | | | | | copy\_http | This query will identify any usage of `COPY INTO` with `HTTP` in the `query_text` as well |

SELECT \*,
FROM
snowflake.account\_usage.query\_history
WHERE
CONTAINS(QUERY\_TEXT, 'COPY INTO') AND CONTAINS(QUERY\_TEXT, 'http')

| | | | | | | | | | | | | | | | get\_file |

After copying into temp storage, attackers will often use the GET command to retrieve that file. This looks for GET and File: in a single query


|

SELECT \*,
FROM
snowflake.account\_usage.query\_history
WHERE
query\_text ilike '%GET' AND query\_text ilike '%file:%’

| | | | | | | | | | | | | | | ### YetiHunter Details ### Installation To install, the only thing needed, is to install the required libraries. > ``` > python3 -m venv ./venv > source venv/bin/activate > python3 -m pip install -r requirements.txt > > ``` If you prefer to run in Docker check for installation instructions in our [README file](https://github.com/Permiso-io-tools/YetiHunter). Then, just run the tool by running **yetihunter.py**: > ``` > python3 yetihunter.py -h > usage: yetihunter [-h] [-u USER] [-p PASSWORD] [-w WAREHOUSE] [-a ACCOUNT] [-d DATABASE] [-s SCHEMA] [-am {SSO,USERPASS}] [-cf CONFIG_FILE_PATH] [-gcf] [-o OUTPUT_DIRECTORY] > > Permiso Security has created a tool to query snowflake environments for evidence of compromise, based on indicators from Permiso and the community. > > options: > -h, --help show this help message and exit > > -u USER, --user USER The Snowflake user to authenticate as. Even if the authentication is SSO, the email of the identity authenticating should be added. > > -p PASSWORD, --password PASSWORD > This field is not required if the authentication is SSO, but required if the authentication is USERPASS. If needed and the field is empty, an imput will popup to ask for the password. > > -w WAREHOUSE, --warehouse WAREHOUSE > The Snowflake warehouse to connect to. > > -a ACCOUNT, --account ACCOUNT > The Snowflake Account to connect to. It should be in the format of - > > -d DATABASE, --database DATABASE > The database name to connect to. > > -s SCHEMA, --schema SCHEMA > The name of the schema inside the database. This can be empty. > > -am {SSO,USERPASS}, --authentication-method {SSO,USERPASS} > > -cf CONFIG_FILE_PATH, --config-file-path CONFIG_FILE_PATH > If a config file is used, this option will have the path of the config file. No other flag is needed besides this. > > -gcf, --generate-config-file > If put, the tool will create an empty config file that can be filled by the user. The fields with null value can be left as null as they are not required. > > -o OUTPUT_DIRECTORY, --output-directory OUTPUT_DIRECTORY > The directory inside ./output/, where the query output files will be saved in. > > ``` ### YetiHunter Usage #### Authenticating YetiHunter supports two (2) forms of authentication. `-am {SSO,USERPASS}, --authentication-method {SSO,USERPASS}` * Username + password * SSO Authentication and database information can be passed via command line parameters or stored in a config. To make this process seamless, YetiHunter includes a config generator: > ``` > -cf CONFIG_FILE_PATH, --config-file-path CONFIG_FILE_PATH >                         If a config file is used, this option will have the path of the config file. No other flag is needed besides this. > > -gcf, --generate-config-file >                         If put, the tool will create an empty config file that can be filled by the user. The fields with null value can be left as null as they are not required.! > > ``` #### YetiHunter Queries The rest of the execution is done by the tool itself, based on the queries provided. YetiHunter has a list of queries on `queries/queries.json`, which the user can extend or update.
At the end of the execution, a list of CSV files are created with the output of each query. ### YetiHunter Custom Queries As mentioned, YetiHunter stores its queries on `queries/queries.json`. Each query has a `name`, a `description` and the `query` itself. Queries can be added, removed or modified according to the users’ needs and they will automatically get executed by the tool. ### YetiHunter Custom Malicious IPs A list of known malicious IPs is provided to the users. That list resides in `queries/known_bad_ips` . Users can add new IPs to the list or swap the current ones for their list and the tool will continue to run. The list will be ingested on a rule that checks for logins coming from these IPs. When there is a hit, a file called `login_from_malicious_ips.csv` will be created with the list of identities and their login info.