Collect Snowflake logs

Supported in:

Google secops SIEM

This document explains how to ingest Snowflake logs to Google Security Operations using AWS S3. The parser extracts fields from the log messages using a series of Grok and KV pattern matching rules, specifically designed to handle Snowflake log format. It then maps the extracted fields to the Unified Data Model (UDM), enriching the data with additional context and standardizing the representation for further analysis.

Before you begin

Make sure you have the following prerequisites:

• Google SecOps instance
• Privileged access to AWS
• Privileged access to Snowflake (ACCOUNTADMIN)

Configure an Amazon S3 Bucket

1. Create an Amazon S3 bucket following this user guide: Creating a bucket
2. Save the bucket Name and Region for future reference.

Configure the Snowflake AWS IAM Policy

1. Sign in to the AWS Management Console.
2. Search for and select IAM.
3. Select Account settings.
4. Under Security Token Service (STS) in the Endpoints list, find the Snowflake region where your account is located.
5. If the STS status is inactive, move the toggle to Active.
6. Select Policies.
7. Select Create Policy.
8. In Policy editor, select JSON.

9. Copy and paste the following policy (in JSON format) to provide Snowflake with the required permissions to load or unload data using a single bucket and folder path. You can also purge data files using the PURGE copy option.

   Note: Make sure to replace bucket and prefix with your actual bucket name and folder path prefix. The Amazon Resource Names (ARN) for buckets in government regions have a arn:aws-us-gov:s3::: prefix. The ARN for buckets in public AWS regions in China have a arn:aws-cn:s3::: prefix. If you're using an S3 access point, specify the access point ARN instead of a bucket ARN.

   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Effect": "Allow",
               "Action": [
                 "s3:PutObject",
                 "s3:GetObject",
                 "s3:GetObjectVersion",
                 "s3:DeleteObject",
                 "s3:DeleteObjectVersion"
               ],
               "Resource": "arn:aws:s3:::<bucket>/<prefix>/*"
           },
           {
               "Effect": "Allow",
               "Action": [
                   "s3:ListBucket",
                   "s3:GetBucketLocation"
               ],
               "Resource": "arn:aws:s3:::<bucket>",
               "Condition": {
                   "StringLike": {
                       "s3:prefix": [
                           "<prefix>/*"
                       ]
                   }
               }
           }
       ]
   }
   

10. Click Next.

11. Enter a Policy name (for example, snowflake_access) and an optional Description.

12. Click Create policy.

Configure Snowflake AWS IAM Role

1. In the AWS Identity and Access Management (IAM), select Roles.
2. Click Create role.
3. Select AWS account as the trusted entity type.
4. Select Another AWS account.
5. In the Account ID field, enter your own AWS account ID temporarily. Later, you modify the trust relationship and grant access to Snowflake.
6. Select the Require external ID option.
7. Enter a placeholder ID such as 0000. In a later step, you will modify the trust relationship for your IAM role and specify the external ID for your storage integration.
8. Click Next.
9. Select the IAM policy you created earlier.
10. Click Next.
11. Enter a name and description for the role.
12. Click Create role.
13. On the role summary page, copy and save the Role ARN value.

Configure Snowflake S3 Integration

1. Connect to the Snowflake db.

2. Replace the following fields and run the command:

   • <integration_name> is the name of the new integration (for example, s3_integration).
   • <iam_role> is the Amazon Resource Name (ARN) of the role you created earlier.
   • <aws_s3_bucket_path> is the path to the bucket you created earlier (for example, s3://your-log-bucket-name/).

   CREATE OR REPLACE STORAGE INTEGRATION <integration_name>
     TYPE = EXTERNAL_STAGE
     STORAGE_PROVIDER = 'S3'
     ENABLED = TRUE
     STORAGE_AWS_ROLE_ARN = '<iam_role>'
     STORAGE_ALLOWED_LOCATIONS = ('<aws_s3_bucket_path>')
   

Configure AWS IAM User Permissions to Access Bucket

1. Retrieve the ARN for the IAM user that was created automatically for your Snowflake account, replace <integration_name> with the actual name of the integration you created earlier: none DESC INTEGRATION <integration_name>;

• For example: none DESC INTEGRATION s3_integration; +---------------------------+---------------+--------------------------------------------------------------------------------+------------------+ | property | property_type | property_value | property_default | +---------------------------+---------------+--------------------------------------------------------------------------------+------------------| | ENABLED | Boolean | true | false | | STORAGE_ALLOWED_LOCATIONS | List | s3://mybucket1/mypath1/,s3://mybucket2/mypath2/ | [] | | STORAGE_BLOCKED_LOCATIONS | List | s3://mybucket1/mypath1/sensitivedata/,s3://mybucket2/mypath2/sensitivedata/ | [] | | STORAGE_AWS_IAM_USER_ARN | String | arn:aws:iam::123456789001:user/abc1-b-self1234 | | | STORAGE_AWS_ROLE_ARN | String | arn:aws:iam::001234567890:role/myrole | | | STORAGE_AWS_EXTERNAL_ID | String | MYACCOUNT_SFCRole=2_a123456/s0aBCDEfGHIJklmNoPq= | | +---------------------------+---------------+--------------------------------------------------------------------------------+------------------+

1. Copy and save the values for the following properties:
   • STORAGE_AWS_IAM_USER_ARN
   • STORAGE_AWS_EXTERNAL_ID
2. Go to the AWS Management Console.
3. Select IAM > Roles.
4. Select the role you created earlier.
5. Select the Trust relationships tab.
6. Click Edit trust policy.

7. Update the policy document with the DESC INTEGRATION output values:

   {
     "Version": "2012-10-17",
     "Statement": [
       {
         "Sid": "",
         "Effect": "Allow",
         "Principal": {
           "AWS": "<snowflake_user_arn>"
         },
         "Action": "sts:AssumeRole",
         "Condition": {
           "StringEquals": {
             "sts:ExternalId": "<snowflake_external_id>"
           }
         }
       }
     ]
   }
   

8. Replace:

   • snowflake_user_arn is the STORAGE_AWS_IAM_USER_ARN value you recorded.
   • snowflake_external_id is the STORAGE_AWS_EXTERNAL_ID value you recorded.

9. Click Update policy.

Configure JSON File Format in Snowflake

1. In Snowflake, enter the following command:

   CREATE OR REPLACE FILE FORMAT my_json_format
     type = json
     COMPRESSION = 'gzip'
     null_if = ('NULL', 'null');
   

Create S3 Stage in Snowflake

1. In Snowflake, replace the following fields and enter the command:

   • <DB_NAME>
   • <DB_SCHEMA_NAME>
   • <AWS_S3_BUCKET_PATH>

   use database '<DB_NAME>';
   use schema '<DB_SCHEMA_NAME>';
   CREATE OR REPLACE STAGE my_s3_stage
   storage_integration = s3_integration
   url = '<AWS_S3_BUCKET_PATH>'
   file_format = my_json_format;
   

Configure Snowflake to export data

1. Run the unload command to export data from tables to stage and in turn to AWS S3:

   use database '<DB_NAME>';
   use WAREHOUSE '<WAREHOUSE_NAME>';
   
   copy into @my_s3_stage/login_history from (SELECT OBJECT_CONSTRUCT('application', 'snowflake' ,'environment', '<PUT_HERE_ENV_NAME>', 'log_type', 'login_history', 'EVENT_TIMESTAMP', EVENT_TIMESTAMP, 'EVENT_TYPE', EVENT_TYPE, 'USER_NAME', USER_NAME, 'CLIENT_IP', CLIENT_IP, 'REPORTED_CLIENT_TYPE', REPORTED_CLIENT_TYPE, 'FIRST_AUTHENTICATION_FACTOR',FIRST_AUTHENTICATION_FACTOR, 'IS_SUCCESS', IS_SUCCESS, 'ERROR_CODE', ERROR_CODE, 'ERROR_MESSAGE', ERROR_MESSAGE) from snowflake.account_usage.Login_history) FILE_FORMAT = (TYPE = JSON) ;
   
   copy into @my_s3_stage/access_history from (SELECT OBJECT_CONSTRUCT('application', 'snowflake' ,'environment', '<PUT_HERE_DB_NAME>', 'log_type', 'access_history', 'QUERY_START_TIME',QUERY_START_TIME, 'USER_NAME', USER_NAME, 'DIRECT_OBJECTS_ACCESSED',DIRECT_OBJECTS_ACCESSED, 'BASE_OBJECTS_ACCESSED', BASE_OBJECTS_ACCESSED, 'OBJECTS_MODIFIED', OBJECTS_MODIFIED) from snowflake.account_usage.Access_History ) FILE_FORMAT = (TYPE = JSON);
   

2. Repeat the export process for all the following tables in which Snowflake stores logs and audit related data:

   Databases ;
   WAREHOUSE_EVENTS_HISTORY ;
   WAREHOUSE_LOAD_HISTORY ;
   WAREHOUSE_METERING_HISTORY ;
   DATABASE_STORAGE_USAGE_HISTORY ;
   DATA_TRANSFER_HISTORY ;
   GRANTS_TO_ROLES ;
   GRANTS_TO_USERS ;
   METERING_DAILY_HISTORY ;
   PIPE_USAGE_HISTORY ;
   REPLICATION_USAGE_HISTORY ;
   STAGE_STORAGE_USAGE_HISTORY ;
   STORAGE_USAGE ;
   TASK_HISTORY ;
   COPY_HISTORY ;
   

   Note: All these tables are present in snowflake.account_usage schema.

Configure AWS IAM for Google SecOps

1. Sign in to the AWS Management Console.
2. Create a User following this user guide: Creating an IAM user.
3. Select the created User.
4. Select Security credentials tab.
5. Click Create Access Key in section Access Keys.
6. Select Third-party service as Use case.
7. Click Next.
8. Optional: Add description tag.
9. Click Create access key.
10. Click Download CSV file for save the Access Key and Secret Access Key for future reference.
11. Click Done.
12. Select Permissions tab.
13. Click Add permissions in section Permissions policies.
14. Select Add permissions.
15. Select Attach policies directly.
16. Search for and select the AmazonS3FullAccess policy.
17. Click Next.
18. Click Add permissions.

Set up feeds

To configure a feed, follow these steps:

1. Go to SIEM Settings > Feeds.
2. Click Add New Feed.
3. On the next page, click Configure a single feed.
4. In the Feed name field, enter a name for the feed (for example, Snowflake Logs).
5. Select Amazon S3 V2 as the Source type.
6. Select Snowflake as the Log type.
7. Click Next.

8. Specify values for the following input parameters:

   • S3 URI: The bucket URI (the format should be: s3://your-log-bucket-name/). Replace the following:
     • your-log-bucket-name: the name of the bucket.
   • Source deletion options: select deletion option according to your preference.
   Note: If you select the Delete transferred files or Delete transferred files and empty directories option, make sure that you granted appropriate permissions to the service account. * Maximum File Age: Includes files modified in the last number of days. Default is 180 days. * Access Key ID: the User access key with access to the S3 bucket. * Secret Access Key: the User secret key with access to the S3 bucket.

9. Click Next.

10. Review your new feed configuration in the Finalize screen, and then click Submit.

UDM mapping table

Log field	UDM mapping	Logic
column4_label	additional.fields	Merged
column7_label	additional.fields	Merged
first_authentication_factor_label	additional.fields	Merged
host_list	additional.fields	Merged
query_text_label	additional.fields	Merged
query_type_label	additional.fields	Merged
roleIds_list	additional.fields	Merged
roleNames_list	additional.fields	Merged
rolecount_label	additional.fields	Merged
user_count_label	additional.fields	Merged
has_principal	extensions.auth.type	Mapped: true → AUTHTYPE_UNSPECIFIED
START_TIME	metadata.event_timestamp	Parsed as yyyy-MM-dd HH:mm:ss.SSS Z
column2	metadata.event_timestamp	Parsed as yyyy-MM-dd HH:mm:ss.SSS Z
column20	metadata.event_timestamp	Parsed as yyyy-MM-dd HH:mm:ss.SSS Z
ts	metadata.event_timestamp	Parsed as ISO8601
event_type	metadata.event_type	Directly mapped
has_principal	metadata.event_type	Mapped: true → NETWORK_CONNECTION, true → STATUS_UPDATE, true → USER_LOGIN
has_principal_user	metadata.event_type	Mapped: true → USER_UNCATEGORIZED
column10	metadata.product_event_type	Directly mapped
QUERY_ID	metadata.product_log_id	Directly mapped
column1	metadata.product_log_id	Directly mapped
column6	metadata.product_version	Directly mapped
SESSION_ID	network.http.session_id	Directly mapped
RECEIVED_BYTES	network.received_bytes	Renamed/mapped
BYTES_SENT_OVER_THE_NETWORK	network.sent_bytes	Renamed/mapped
SENT_BYTES	network.sent_bytes	Renamed/mapped
APPLICATION	principal.application	Directly mapped
data.user_name	principal.asset.hostname	Directly mapped
CLIENT_IP	principal.asset.ip	Merged
IP	principal.asset.ip	Merged
column5	principal.asset.ip	Merged
SOURCE_REGION	principal.cloud.availability_zone	Directly mapped
SOURCE_CLOUD	principal.cloud.environment	Mapped: (?i)azure → MICROSOFT_AZURE, (?i)amazon → AMAZON_WEB_SERVICES, (?i)google ...
data.user_name	principal.hostname	Directly mapped
CLIENT_IP	principal.ip	Merged
IP	principal.ip	Merged
column5	principal.ip	Merged
OS	principal.platform	Mapped: (?i)Linux → LINUX, (?i)windows → WINDOWS, (?i)mac/ios → MAC
OS_VERSION	principal.platform_version	Directly mapped
SOURCE_CLOUD_label	principal.resource.attribute.labels	Merged
roles	principal.user.attribute.roles	Merged
data.role_name	principal.user.role_name	Directly mapped
column9	principal.user.user_display_name	Directly mapped
USER_NAME	principal.user.userid	Directly mapped
column10	principal.user.userid	Directly mapped
column3	principal.user.userid	Directly mapped
EXECUTION_STATUS	security_result.action	Mapped: (?i)success → action, (?i)fail → security_result_action_block
STATUS	security_result.action	Mapped: (?i)success → action, (?i)fail → action
action	security_result.action	Merged
security_result_action_block	security_result.action	Merged
OCSP_MODE	security_result.action_details	Directly mapped
column11	security_result.action_details	Directly mapped
column3	security_result.action_details	Directly mapped
BYTES_DELETED_label	security_result.detection_fields	Merged
BYTES_READ_FROM_RESULT_label	security_result.detection_fields	Merged
BYTES_SCANNED_label	security_result.detection_fields	Merged
BYTES_SPILLED_TO_LOCAL_STORAGE_label	security_result.detection_fields	Merged
BYTES_SPILLED_TO_REMOTE_STORAGE_label	security_result.detection_fields	Merged
BYTES_WRITTEN_TO_RESULT_label	security_result.detection_fields	Merged
BYTES_WRITTEN_label	security_result.detection_fields	Merged
CHILD_QUERIES_WAIT_TIME_label	security_result.detection_fields	Merged
CLUSTER_NUMBER_label	security_result.detection_fields	Merged
COMPILATION_TIME_label	security_result.detection_fields	Merged
CREDITS_USED_CLOUD_SERVICES_label	security_result.detection_fields	Merged
DATABASE_ID_label	security_result.detection_fields	Merged
DATABASE_NAME_label	security_result.detection_fields	Merged
END_TIME_label	security_result.detection_fields	Merged
ERROR_MESSAGE_label	security_result.detection_fields	Merged
EXECUTION_TIME_label	security_result.detection_fields	Merged
EXTERNAL_FUNCTION_TOTAL_INVOCATIONS_label	security_result.detection_fields	Merged
EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES_label	security_result.detection_fields	Merged
EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS_label	security_result.detection_fields	Merged
EXTERNAL_FUNCTION_TOTAL_SENT_BYTES_label	security_result.detection_fields	Merged
EXTERNAL_FUNCTION_TOTAL_SENT_ROWS_label	security_result.detection_fields	Merged
INBOUND_DATA_TRANSFER_BYTES_label	security_result.detection_fields	Merged
IS_CLIENT_GENERATED_STATEMENT_label	security_result.detection_fields	Merged
LIST_EXTERNAL_FILES_TIME_label	security_result.detection_fields	Merged
OUTBOUND_DATA_TRANSFER_BYTES_label	security_result.detection_fields	Merged
PARTITIONS_SCANNED_label	security_result.detection_fields	Merged
PARTITIONS_TOTAL_label	security_result.detection_fields	Merged
PERCENTAGE_SCANNED_FROM_CACHE_label	security_result.detection_fields	Merged
QUERY_ACCELERATION_BYTES_SCANNED_label	security_result.detection_fields	Merged
QUERY_ACCELERATION_PARTITIONS_SCANNED_label	security_result.detection_fields	Merged
QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR_label	security_result.detection_fields	Merged
QUERY_HASH_VERSION_label	security_result.detection_fields	Merged
QUERY_HASH_label	security_result.detection_fields	Merged
QUERY_LOAD_PERCENT_label	security_result.detection_fields	Merged
QUERY_PARAMETERIZED_HASH_VERSION_label	security_result.detection_fields	Merged
QUERY_TAG_label	security_result.detection_fields	Merged
QUERY_TYPE_label	security_result.detection_fields	Merged
QUEUED_OVERLOAD_TIME_label	security_result.detection_fields	Merged
QUEUED_PROVISIONING_TIME_label	security_result.detection_fields	Merged
QUEUED_REPAIR_TIME_label	security_result.detection_fields	Merged
RELEASE_VERSION_label	security_result.detection_fields	Merged
ROLE_TYPE_label	security_result.detection_fields	Merged
ROWS_DELETED_label	security_result.detection_fields	Merged
ROWS_INSERTED_label	security_result.detection_fields	Merged
ROWS_PRODUCED_label	security_result.detection_fields	Merged
ROWS_UNLOADED_label	security_result.detection_fields	Merged
ROWS_UPDATED_label	security_result.detection_fields	Merged
ROWS_WRITTEN_TO_RESULT_label	security_result.detection_fields	Merged
SCHEMA_ID_label	security_result.detection_fields	Merged
SCHEMA_NAME_label	security_result.detection_fields	Merged
TOTAL_ELAPSED_TIME_label	security_result.detection_fields	Merged
TRANSACTION_BLOCKED_TIME_label	security_result.detection_fields	Merged
TRANSACTION_ID_label	security_result.detection_fields	Merged
WAREHOUSE_ID_label	security_result.detection_fields	Merged
WAREHOUSE_NAME_label	security_result.detection_fields	Merged
WAREHOUSE_SIZE_label	security_result.detection_fields	Merged
WAREHOUSE_TYPE_label	security_result.detection_fields	Merged
authentication_factor_label	security_result.detection_fields	Merged
column4_label	security_result.detection_fields	Merged
column6_label	security_result.detection_fields	Merged
column7_label	security_result.detection_fields	Merged
column8_label	security_result.detection_fields	Merged
event_id_label	security_result.detection_fields	Merged
event_type_label	security_result.detection_fields	Merged
is_success_label	security_result.detection_fields	Merged
python_compiler_label	security_result.detection_fields	Merged
python_runtime_label	security_result.detection_fields	Merged
python_version_label	security_result.detection_fields	Merged
reported_client_type_label	security_result.detection_fields	Merged
reported_client_version_label	security_result.detection_fields	Merged
tracing_label	security_result.detection_fields	Merged
EXECUTION_STATUS	security_result.summary	Directly mapped
STATUS	security_result.summary	Directly mapped
column17	security_result.summary	Directly mapped
TARGET_REGION	target.cloud.availability_zone	Directly mapped
TARGET_CLOUD	target.cloud.environment	Mapped: (?i)azure → MICROSOFT_AZURE, (?i)amazon → AMAZON_WEB_SERVICES, (?i)google ...
TARGET_CLOUD_label	target.resource.attribute.labels	Merged
column2_label	target.resource.attribute.labels	Merged
column7_label	target.resource.attribute.labels	Merged
USER_NAME	target.user.userid	Directly mapped
column4	target.user.userid	Directly mapped
N/A	extensions.auth.type	Constant: AUTHTYPE_UNSPECIFIED
N/A	metadata.event_type	Constant: NETWORK_CONNECTION
N/A	metadata.product_name	Constant: SNOWFLAKE
N/A	metadata.vendor_name	Constant: SNOWFLAKE
N/A	principal.cloud.environment	Constant: MICROSOFT_AZURE
N/A	principal.platform	Constant: LINUX
N/A	target.cloud.environment	Constant: MICROSOFT_AZURE

Change Log

View the Change Log for this parser

Need more help? Get answers from Community members and Google SecOps professionals.