def connect(connection_name, options): base_name = f'AWS_CONFIG_{connection_name}_EVENTS'.upper() stage = f'data.{base_name}_STAGE' staging_table = f'data.{base_name}_STAGING' landing_table = f'data.{base_name}_CONNECTION' bucket = options['bucket_name'] prefix = options.get('filter', 'AWSLogs/') role = options['aws_role'] comment = f""" --- module: aws_config """ db.create_stage( name=stage, url=f's3://{bucket}', prefix=prefix, cloud='aws', credentials=role, file_format=FILE_FORMAT ) db.create_table( name=staging_table, cols=[ ('v', 'VARIANT'), ('filename', 'STRING(200)') ] ) db.create_table( name=landing_table, cols=LANDING_TABLE_COLUMNS, comment=comment ) stage_props = db.fetch_props( f'DESC STAGE {stage}', filter=('AWS_EXTERNAL_ID', 'SNOWFLAKE_IAM_USER') ) prefix = prefix.rstrip('/') return { 'newStage': 'created', 'newMessage': CONNECT_RESPONSE_MESSAGE.format( role=role, role_trust_relationship=dumps({ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "AWS": stage_props['SNOWFLAKE_IAM_USER'] }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "sts:ExternalId": stage_props['AWS_EXTERNAL_ID'] } } } ] }, indent=4), role_policy=dumps({ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:GetObject", "s3:GetObjectVersion", ], "Resource": f"arn:aws:s3:::{bucket}/{prefix}/*" }, { "Effect": "Allow", "Action": "s3:ListBucket", "Resource": f"arn:aws:s3:::{bucket}", "Condition": { "StringLike": { "s3:prefix": [ f"{prefix}/*" ] } } } ] }, indent=4), ) }
def connect(connection_name, options): base_name = f'aws_vpc_flow_log_{connection_name}' stage = f'data.{base_name}_stage' landing_table = f'data.{base_name}_connection' bucket = options['bucket_name'] prefix = options.get('filter', 'AWSLogs/') role = options['aws_role'] comment = yaml_dump(module='aws_flow_log') db.create_stage( name=stage, url=f's3://{bucket}', prefix=prefix, cloud='aws', credentials=role, file_format=FILE_FORMAT, ) db.create_table(name=landing_table, cols=LANDING_TABLE_COLUMNS, comment=comment) db.create_table_and_upload_csv( name='data.network_protocol_mapping', columns=PROTOCOL_MAPPING_TABLE_COLUMNS, file_path=NETWORK_PROTOCOL_PATH, file_format=PROTOCOL_FILE_FORMAT, ifnotexists=True, ) stage_props = db.fetch_props( f'DESC STAGE {stage}', filter=('AWS_EXTERNAL_ID', 'SNOWFLAKE_IAM_USER') ) prefix = prefix.rstrip('/') return { 'newStage': 'created', 'newMessage': CONNECT_RESPONSE_MESSAGE.format( role=role, role_trust_relationship=dumps( { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": {"AWS": stage_props['SNOWFLAKE_IAM_USER']}, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "sts:ExternalId": stage_props['AWS_EXTERNAL_ID'] } }, } ], }, indent=4, ), role_policy=dumps( { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": ["s3:GetObject", "s3:GetObjectVersion"], "Resource": f"arn:aws:s3:::{bucket}/{prefix}/*", }, { "Effect": "Allow", "Action": "s3:ListBucket", "Resource": f"arn:aws:s3:::{bucket}", "Condition": {"StringLike": {"s3:prefix": [f"{prefix}/*"]}}, }, ], }, indent=4, ), ), }
def connect(connection_name, options): connection_type = options['connection_type'] base_name = f"azure_log_{connection_name}_{connection_type}" account_name = options['account_name'] container_name = options['container_name'] suffix = options['suffix'] cloud_type = options['cloud_type'] sas_token = options['sas_token'] comment = yaml_dump(module='azure_log') db.create_stage( name=f'data.{base_name}_stage', url=f"azure://{account_name}.blob.{suffix}/{container_name}", cloud='azure', prefix='', credentials=sas_token, file_format=FILE_FORMAT, ) db.execute( f'GRANT USAGE ON STAGE data.{base_name}_stage TO ROLE {SA_ROLE}') db.create_table( name=f'data.{base_name}_connection', cols=LANDING_TABLES_COLUMNS[connection_type], comment=comment, ifnotexists=True, ) db.execute( f'GRANT INSERT, SELECT ON data.{base_name}_connection TO ROLE {SA_ROLE}' ) external_table_columns = [( 'timestamp_part', 'TIMESTAMP_LTZ', GET_TIMESTAMP_FROM_FILENAME_SQL[connection_type], )] db.create_external_table( name=f'data.{base_name}_external', location=f'@data.{base_name}_stage', cols=external_table_columns, partition='timestamp_part', file_format=db.TypeOptions(type='JSON'), ) db.execute(f'GRANT SELECT ON data.{base_name}_external TO ROLE {SA_ROLE}') stored_proc_def = f""" var sql_command = "ALTER EXTERNAL TABLE data.{base_name}_external REFRESH"; try {{ snowflake.execute ({{sqlText: sql_command}}); return "Succeeded."; }} catch (err) {{ return "Failed: " + err; }} """ db.create_stored_procedure( name=f'data.{base_name}_procedure', args=[], return_type='string', executor='OWNER', definition=stored_proc_def, ) refresh_task_sql = f'CALL data.{base_name}_procedure()' db.create_task( name=f'data.{base_name}_refresh_task', warehouse=WAREHOUSE, schedule='5 minutes', sql=refresh_task_sql, ) select_statement_sql = { 'reg': (f"SELECT value " f"FROM data.{base_name}_external " f"WHERE timestamp_part >= DATEADD(HOUR, -2, CURRENT_TIMESTAMP())"), 'gov': (f"SELECT value FROM (" f" SELECT value AS a " f" FROM data.{base_name}_external" f" WHERE timestamp_part >= DATEADD(HOUR, -2, CURRENT_TIMESTAMP())" f"), LATERAL FLATTEN (INPUT => a:records)"), } insert_task_sql = { 'operation': f""" INSERT ( RAW, HASH_RAW, CALLER_IP_ADDRESS, CATEGORY, CORRELATION_ID, DURATION_MS, IDENTITY, IDENTITY_AUTHORIZATION, IDENTITY_CLAIMS, LEVEL, LOCATION, OPERATION_NAME, PROPERTIES, PROPERTIES_ANCESTORS, PROPERTIES_IS_COMPLIANCE_CHECK, PROPERTIES_POLICIES, PROPERTIES_RESOURCE_LOCATION, RESOURCE_ID, RESULT_SIGNATURE, RESULT_TYPE, EVENT_TIME, LOADED_ON ) VALUES ( VALUE, HASH(VALUE), VALUE:callerIpAddress::STRING, VALUE:category::STRING, VALUE:correlationId::STRING, VALUE:durationMs::NUMBER, VALUE:identity::VARIANT, VALUE:identity.authorization::VARIANT, VALUE:identity.claims::VARIANT, VALUE:level::STRING, VALUE:location::STRING, VALUE:operationName::STRING, VALUE:properties::VARIANT, VALUE:properties.ancestors::STRING, VALUE:properties.isComplianceCheck::STRING, PARSE_JSON(VALUE:properties.policies),VALUE:properties.resourceLocation::STRING, VALUE:resourceId::STRING, VALUE:resultSignature::STRING,VALUE:resultType::STRING, value:time::TIMESTAMP_LTZ, CURRENT_TIMESTAMP() ) """, 'audit': f""" INSERT ( RAW, HASH_RAW, CALLER_IP_ADDRESS, CATEGORY, CORRELATION_ID, DURATION_MS, LEVEL, OPERATION_NAME, OPERATION_VERSION, PROPERTIES, PROPERTIES_ACTIVITY_DATE_TIME, PROPERTIES_ACTIVITY_DISPLAY_NAME, PROPERTIES_ADDITIONAL_DETAILS, PROPERTIES_CATEGORY, PROPERTIES_ID, PROPERTIES_INITIATED_BY, PROPERTIES_LOGGED_BY_SERVICE, PROPERTIES_OPERATION_TYPE, PROPERTIES_RESULT, PROPERTIES_RESULT_REASON, PROPERTIES_TARGET_RESOURCES, RESOURCE_ID, RESULT_SIGNATURE, TENANT_ID, EVENT_TIME, LOADED_ON ) VALUES ( VALUE, HASH(VALUE), VALUE:callerIpAddress::STRING, VALUE:category::STRING, VALUE:correlationId::STRING, VALUE:durationMs::NUMBER, VALUE:level::STRING, VALUE:operationName::STRING, VALUE:operationVersion::STRING, VALUE:properties::VARIANT, VALUE:properties.activityDateTime::TIMESTAMP_LTZ, VALUE:properties.activityDisplayName::STRING, VALUE:properties.additionalDetails::VARIANT, VALUE:properties.category::STRING, VALUE:properties.id::STRING, VALUE:properties.initiatedBy::VARIANT, VALUE:properties.loggedByService::STRING, VALUE:properties.operationType::STRING, VALUE:properties.result::STRING, VALUE:resultReason::STRING, VALUE:properties.targetResources::VARIANT, VALUE:resourceId::STRING, VALUE:resultSignature::STRING, VALUE:tenantId::STRING, VALUE:time::TIMESTAMP_LTZ, CURRENT_TIMESTAMP() ) """, 'signin': f""" INSERT ( RAW, HASH_RAW, LEVEL, CALLER_IP_ADDRESS, CATEGORY, CORRELATION_ID, DURATION_MS, IDENTITY, LOCATION, OPERATION_NAME, OPERATION_VERSION, PROPERTIES, PROPERTIES_APP_DISPLAY_NAME, PROPERTIES_APP_ID, PROPERTIES_APPLIED_CONDITIONAL_ACESS_POLICIES, PROPERTIES_AUTHENTICATION_METHODS_USED, PROPERTIES_AUTHENTICATION_PROCESSING_DETAILS, PROPERTIES_CLIENT_APP_USED, PROPERTIES_CONDITIONAL_ACCESS_STATUS, PROPERTIES_CREATED_DATE_TIME, PROPERTIES_DEVICE_DETAIL, PROPERTIES_ID, PROPERTIES_IP_ADDRESS, PROPERTIES_IS_INTERACTIVE, PROPERTIES_LOCATION, PROPERTIES_MFA_DETAIL, PROPERTIES_NETWORK_LOCATION, PROPERTIES_PROCESSING_TIME_IN_MILLISECONDS, PROPERTIES_RESOURCE_DISPLAY_NAME, PROPERTIES_RESOURCE_ID, PROPERTIES_RISK_DETAIL, PROPERTIES_RISK_EVENT_TYPES, PROPERTIES_RISK_LEVEL_AGGREGATED, PROPERTIES_RISK_LEVEL_DURING_SIGNIN, PROPERTIES_RISK_STATE, PROPERTIES_STATUS, PROPERTIES_TOKEN_ISSUER_TYPE, PROPERTIES_USER_DISPLAY_NAME, PROPERTIES_USER_ID, PROPERTIES_USER_PRINCIPAL_NAME, RESOURCE_ID, RESULT_DESCRIPTION, RESULT_SIGNATURE, RESULT_TYPE, TENANT_ID, EVENT_TIME, LOADED_ON ) VALUES ( VALUE, HASH(VALUE), VALUE:Level::NUMBER, VALUE:callerIpAddress::STRING, VALUE:category::STRING, VALUE:correlationId::STRING, VALUE:durationMs, VALUE:identity::STRING, VALUE:location::STRING, VALUE:operationName::STRING, VALUE:operationVersion::STRING, VALUE:properties::VARIANT, VALUE:properties.appDisplayName::STRING, VALUE:properties.appId::STRING, VALUE:properties.appliedConditionalAccessPolicies::VARIANT, VALUE:properties.authenticationMethodsUsed::VARIANT, VALUE:properties.authenticationProcessingDetails::VARIANT, VALUE:properties.clientAppUsed::STRING, VALUE:properties.conditionalAccessStatus::STRING, VALUE:properties.createdDateTime::TIMESTAMP_LTZ, VALUE:properties.deviceDetail::VARIANT, VALUE:properties.id::STRING, VALUE:properties.ipAddress::STRING, VALUE:properties.isInteractive::BOOLEAN, VALUE:properties.location::VARIANT, VALUE:properties.mfaDetail::VARIANT, VALUE:properties.networkLocationDetails::VARIANT, VALUE:properties.processingTimeInMilliseconds::NUMBER, VALUE:properties.resourceDisplayName::STRING, VALUE:properties.resourceId::STRING, VALUE:properties.riskDetail::STRING, VALUE:properties.riskEventTypes::VARIANT, VALUE:properties.riskLevelAggregated::STRING, VALUE:properties.riskLevelDuringSignIn::STRING, VALUE:properties.riskState::VARIANT, VALUE:properties.status::VARIANT, VALUE:properties.tokenIssuerType::STRING, VALUE:properties.userDisplayName::STRING, VALUE:properties.userId::STRING, VALUE:properties.userPrincipalName::STRING, VALUE:resourceId::STRING, VALUE:resultDescription::STRING, VALUE:resultSignature::STRING, VALUE:resultType::STRING, VALUE:tenantId::STRING, VALUE:time::TIMESTAMP_LTZ, CURRENT_TIMESTAMP() ) """, } ingest_task_sql = f""" MERGE INTO data.{base_name}_connection a USING ( {select_statement_sql[cloud_type]} ) b ON a.raw = b.value WHEN NOT MATCHED THEN {insert_task_sql[connection_type]} """ db.create_task( name=f'data.{base_name}_ingest_task', warehouse=WAREHOUSE, schedule=f'AFTER data.{base_name}_refresh_task', sql=ingest_task_sql, ) return { 'newStage': 'finalized', 'newMessage': 'Created Stage, Tables, Stored Procedure, and Tasks.', }
def connect(connection_name, options): base_name = f'AWS_CLOUDTRAIL_{connection_name}_EVENTS'.upper() stage = f'data.{base_name}_STAGE' staging_table = f'data.{base_name}_STAGING' landing_table = f'data.{base_name}_CONNECTION' bucket = options['bucket_name'] prefix = options.get('filter', 'AWSLogs/') role = options['aws_role'] comment = f''' --- module: cloudtrail ''' db.create_stage( name=stage, url=f's3://{bucket}', prefix=prefix, cloud='aws', credentials=role, file_format=FILE_FORMAT ) db.create_table( name=staging_table, cols=[('v', 'variant')] ) db.create_table( name=landing_table, cols=LANDING_TABLE_COLUMNS, comment=comment ) stage_props = db.fetch_props( f'DESC STAGE {stage}', filter=('AWS_EXTERNAL_ID', 'SNOWFLAKE_IAM_USER') ) prefix = prefix.rstrip('/') return { 'newStage': 'created', 'newMessage': CONNECT_RESPONSE_MESSAGE.format( role=role, role_trust_relationship=dumps({ 'Version': '2012-10-17', 'Statement': [ { 'Effect': 'Allow', 'Principal': { 'AWS': stage_props['SNOWFLAKE_IAM_USER'] }, 'Action': 'sts:AssumeRole', 'Condition': { 'StringEquals': { 'sts:ExternalId': stage_props['AWS_EXTERNAL_ID'] } } } ] }, indent=4), role_policy=dumps({ 'Version': '2012-10-17', 'Statement': [ { 'Effect': 'Allow', 'Action': [ 's3:GetObject', 's3:GetObjectVersion', ], 'Resource': f'arn:aws:s3:::{bucket}/{prefix}/*' }, { 'Effect': 'Allow', 'Action': 's3:ListBucket', 'Resource': f'arn:aws:s3:::{bucket}', 'Condition': { 'StringLike': { 's3:prefix': [ f'{prefix}/*' ] } } } ] }, indent=4), ) }
def connect(connection_name, options): base_name = f'ldap_{connection_name}' stage = f'data.{base_name}_stage' landing_table = f'data.{base_name}_connection' comment = yaml_dump(module='ldap', **options) stage = options.get('existing_stage') if stage: prefix = '' aws_role = '' bucket_name = '' stage_name = stage else: stage_name = f'data.ldap_{connection_name}_stage' bucket_name = options['bucket_name'] prefix = options['prefix'] aws_role = options['aws_role'] db.create_stage( name=stage_name, url=f's3://{bucket_name}', prefix=prefix, cloud='aws', credentials=aws_role, file_format=FILE_FORMAT, ) db.create_table(name=landing_table, cols=LANDING_TABLE_COLUMNS, comment=comment) stage_props = db.fetch_props( f'DESC STAGE {stage_name}', filter=('AWS_EXTERNAL_ID', 'SNOWFLAKE_IAM_USER', 'AWS_ROLE', 'URL'), ) url_parts = stage_props['URL'].split('/') if prefix == '': prefix = '/'.join(url_parts[3:-1]) if bucket_name == '': bucket_name = url_parts[2] if aws_role == '': aws_role = stage_props['AWS_ROLE'] prefix = prefix.rstrip('/') return { 'newStage': 'created', 'newMessage': CONNECT_RESPONSE_MESSAGE.format( role=aws_role, role_trust_relationship=dumps( { "Version": "2012-10-17", "Statement": [{ "Effect": "Allow", "Principal": { "AWS": stage_props['SNOWFLAKE_IAM_USER'] }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "sts:ExternalId": stage_props['AWS_EXTERNAL_ID'] } }, }], }, indent=4, ), role_policy=dumps( { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": ["s3:GetObject", "s3:GetObjectVersion"], "Resource": f"arn:aws:s3:::{bucket_name}/{prefix}/*", }, { "Effect": "Allow", "Action": "s3:ListBucket", "Resource": f"arn:aws:s3:::{bucket_name}", "Condition": { "StringLike": { "s3:prefix": [f"{prefix}/*"] } }, }, ], }, indent=4, ), ), }
def connect(connection_name, options): connection_type = options['connection_type'] base_name = f"azure_{connection_name}_{connection_type}" account_name = options['account_name'] container_name = options['container_name'] suffix = options['suffix'] sas_token = options['sas_token'] sas_token_ct = vault.encrypt(sas_token) comment = f''' --- module: azure storage_account: {account_name} container_name: {container_name} suffix: {suffix} sas_token: {sas_token_ct} sa_user: {USER} snowflake_account: {ACCOUNT} database: {DATABASE} ''' db.create_stage( name=f'data.{base_name}_STAGE', url=f"azure://{account_name}.blob.{suffix}/{container_name}", cloud='azure', prefix='', credentials=sas_token, file_format=FILE_FORMAT) db.execute( f'GRANT USAGE ON STAGE data.{base_name}_STAGE TO ROLE {SA_ROLE}') db.create_table(name=f'data.{base_name}_CONNECTION', cols=LANDING_TABLES_COLUMNS[connection_type], comment=comment) db.execute( f'GRANT INSERT, SELECT ON data.{base_name}_CONNECTION TO ROLE {SA_ROLE}' ) pipe_sql = { 'operation': f''' COPY INTO DATA.{base_name}_CONNECTION(RAW, HASH_RAW, CALLER_IP_ADDRESS, CATEGORY, CORRELATION_ID, DURATION_MS, IDENTITY, IDENTITY_AUTHORIZATION, IDENTITY_CLAIMS, LEVEL, LOCATION, OPERATION_NAME, PROPERTIES, PROPERTIES_ANCESTORS, PROPERTIES_IS_COMPLIANCE_CHECK, PROPERTIES_POLICIES, PROPERTIES_RESOURCE_LOCAATION, RESOURCE_ID, RESULT_SIGNATURE, RESULT_TYPE, EVENT_TIME, LOADED_ON) FROM ( SELECT $1, HASH($1), $1:callerIpAddress::STRING, $1:category::STRING, $1:correlationId::STRING, $1:durationMs::NUMBER, $1:identity::VARIANT, $1:identity.authorization::VARIANT, $1:identity.claims::VARIANT, $1:level::STRING, $1:location::STRING, $1:operationName::STRING, $1:properties::VARIANT, $1:properties.ancestors::STRING, $1:properties.isComplianceCheck::STRING, PARSE_JSON($1:properties.policies), $1:properties.resourceLocation::STRING, $1:resourceId::STRING, $1:resultSignature::STRING, $1:resultType::STRING, $1:time::TIMESTAMP_LTZ, CURRENT_TIMESTAMP() FROM @DATA.{base_name}_STAGE) ''', 'audit': f''' COPY INTO data.{base_name}_CONNECTION (RAW, HASH_RAW, CALLER_IP_ADDRESS, CATEGORY, CORRELATION_ID, DURATION_MS, LEVEL, OPERATION_NAME, OPERATION_VERSION, PROPERTIES, PROPERTIES_ACTIVITY_DATE_TIME, PROPERTIES_ACTIVITY_DISPLAY_NAME, PROPERTIES_ADDITIONAL_DETAILS, PROPERTIES_CATEGORY, PROPERTIES_ID, PROPERTIES_INITIATED_BY, PROPERTIES_LOGGED_BY_SERVICE, PROPERTIES_OPERATION_TYPE, PROPERTIES_RESULT, PROPERTIES_RESULT_REASON, PROPERTIES_TARGET_RESOURCES, RESOURCE_ID, RESULT_SIGNATURE, TENANT_ID, EVENT_TIME, LOADED_ON) FROM ( SELECT $1, HASH($1), $1:callerIpAddress::STRING, $1:category::STRING, $1:correlationId::STRING, $1:durationMs::NUMBER, $1:level::STRING, $1:operationName::STRING, $1:operationVersion::STRING, $1:properties::VARIANT, $1:properties.activityDateTime::TIMESTAMP_LTZ, $1:properties.activityDisplayName::STRING, $1:properties.additionalDetails::VARIANT, $1:properties.category::STRING, $1:properties.id::STRING, $1:properties.initiatedBy::VARIANT, $1:properties.loggedByService::STRING, $1:properties.operationType::STRING, $1:properties.result::STRING, $1:resultReason::STRING, $1:properties.targetResources::VARIANT, $1:resourceId::STRING, $1:resultSignature::STRING, $1:tenantId::STRING, $1:time::TIMESTAMP_LTZ, CURRENT_TIMESTAMP() FROM @data.{base_name}_STAGE ) ''', 'signin': f''' COPY INTO DATA.{base_name}_CONNECTION ( RAW, HASH_RAW, LEVEL, CALLER_IP_ADDRESS, CATEGORY, CORRELATION_ID, DURATION_MS, IDENTITY, LOCATION, OPERATION_NAME, OPERATION_VERSION, PROPERTIES, PROPERTIES_APP_DISPLAY_NAME, PROPERTIES_APP_ID, PROPERTIES_APPLIED_CONDITIONAL_ACESS_POLICIES, PROPERTIES_AUTHENTICATION_METHODS_USED, PROPERTIES_AUTHENTICATION_PROCESSING_DETAILS, PROPERTIES_CLIENT_APP_USED, PROPERTIES_CONDITIONAL_ACCESS_STATUS, PROPERTIES_CREATED_DATE_TIME, PROPERTIES_DEVICE_DETAIL, PROPERTIES_ID, PROPERTIES_IP_ADDRESS, PROPERTIES_IS_INTERACTIVE, PROPERTIES_LOCATION, PROPERTIES_MFA_DETAIL, PROPERTIES_NETWORK_LOCATION, PROPERTIES_PROCESSING_TIME_IN_MILLISECONDS, PROPERTIES_RESOURCE_DISPLAY_NAME, PROPERTIES_RESOURCE_ID, PROPERTIES_RISK_DETAIL, PROPERTIES_RISK_EVENT_TYPES, PROPERTIES_RISK_LEVEL_AGGREGATED, PROPERTIES_RISK_LEVEL_DURING_SIGNIN, PROPERTIES_RISK_STATE, PROPERTIES_STATUS, PROPERTIES_TOKEN_ISSUER_TYPE, PROPERTIES_USER_DISPLAY_NAME, PROPERTIES_USER_ID, PROPERTIES_USER_PRINCIPAL_NAME, RESOURCE_ID, RESULT_DESCRIPTION, RESULT_SIGNATURE, RESULT_TYPE, TENANT_ID, EVENT_TIME, LOADED_ON ) FROM ( SELECT $1, HASH($1), $1:Level::NUMBER, $1:callerIpAddress::STRING, $1:category::STRING, $1:correlationId::STRING, $1:durationMs, $1:identity::STRING, $1:location::STRING, $1:operationName::STRING, $1:operationVersion::STRING, $1:properties::VARIANT, $1:properties.appDisplayName::STRING, $1:properties.appId::STRING, $1:properties.appliedConditionalAccessPolicies::VARIANT, $1:properties.authenticationMethodsUsed::VARIANT, $1:properties.authenticationProcessingDetails::VARIANT, $1:properties.clientAppUsed::STRING, $1:properties.conditionalAccessStatus::STRING, $1:properties.createdDateTime::TIMESTAMP_LTZ, $1:properties.deviceDetail::VARIANT, $1:properties.id::STRING, $1:properties.ipAddress::STRING, $1:properties.isInteractive::BOOLEAN, $1:properties.location::VARIANT, $1:properties.mfaDetail::VARIANT, $1:properties.networkLocationDetails::VARIANT, $1:properties.processingTimeInMilliseconds::NUMBER, $1:properties.resourceDisplayName::STRING, $1:properties.resourceId::STRING, $1:properties.riskDetail::STRING, $1:properties.riskEventTypes::VARIANT, $1:properties.riskLevelAggregated::STRING, $1:properties.riskLevelDuringSignIn::STRING, $1:properties.riskState::VARIANT, $1:properties.status::VARIANT, $1:properties.tokenIssuerType::STRING, $1:properties.userDisplayName::STRING, $1:properties.userId::STRING, $1:properties.userPrincipalName::STRING, $1:resourceId::STRING, $1:resultDescription::STRING, $1:resultSignature::STRING, $1:resultType::STRING, $1:tenantId::STRING, $1:time::TIMESTAMP_LTZ, CURRENT_TIMESTAMP() FROM @DATA.{base_name}_STAGE ) ''' } db.create_pipe(name=f"data.{base_name}_PIPE", sql=pipe_sql[options['connection_type']], replace=True) db.execute( f'ALTER PIPE data.{base_name}_PIPE SET PIPE_EXECUTION_PAUSED=true') db.execute( f'GRANT OWNERSHIP ON PIPE data.{base_name}_PIPE TO ROLE {SA_ROLE}') return { 'newStage': 'finalized', 'newMessage': 'Table, Stage, and Pipe created' }
def connect(connection_name, options): base_name = f'GITHUB_WEBHOOKS_S3_{connection_name}_EVENTS'.upper() stage = f'data.{base_name}_STAGE' landing_table = f'data.{base_name}_CONNECTION' bucket = options['bucket_name'] prefix = options.get('filter', S3_BUCKET_DEFAULT_PREFIX) role = options['aws_role'] comment = yaml_dump(module='github_webhooks_s3') db.create_stage( name=stage, url=f's3://{bucket}', prefix=prefix, cloud='aws', credentials=role, file_format=FILE_FORMAT, ) db.create_table(name=landing_table, cols=LANDING_TABLE_COLUMNS, comment=comment) stage_props = db.fetch_props(f'DESC STAGE {stage}', filter=('AWS_EXTERNAL_ID', 'SNOWFLAKE_IAM_USER')) prefix = prefix.rstrip('/') return { 'newStage': 'created', 'newMessage': CONNECT_RESPONSE_MESSAGE.format( role=role, role_trust_relationship=dumps( { "Version": "2012-10-17", "Statement": [{ "Effect": "Allow", "Principal": { "AWS": stage_props['SNOWFLAKE_IAM_USER'] }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "sts:ExternalId": stage_props['AWS_EXTERNAL_ID'] } }, }], }, indent=4, ), role_policy=dumps( { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": ["s3:GetObject", "s3:GetObjectVersion"], "Resource": f"arn:aws:s3:::{bucket}/{prefix}/*", }, { "Effect": "Allow", "Action": "s3:ListBucket", "Resource": f"arn:aws:s3:::{bucket}", "Condition": { "StringLike": { "s3:prefix": [f"{prefix}/*"] } }, }, ], }, indent=4, ), ), }
def connect(connection_name, options): table_name = f'osquery_log_{connection_name}_connection' landing_table = f'data.{table_name}' prefix = '' bucket_name = '' db.create_table( name=landing_table, cols=LANDING_TABLE_COLUMNS, comment=yaml_dump(module='osquery_log', **options), ) db.execute(f'GRANT INSERT, SELECT ON {landing_table} TO ROLE {SA_ROLE}') stage_name = options.get('existing_stage') if not stage_name: stage_name = f'data.osquery_log_{connection_name}_stage' bucket_name = options['bucket_name'] prefix = options['prefix'] aws_role = options['aws_role'] db.create_stage( name=stage_name, url=f's3://{bucket_name}', prefix=prefix, cloud='aws', credentials=aws_role, file_format=db.TypeOptions(type='JSON'), ) stage_props = db.fetch_props( f'DESC STAGE {stage_name}', filter=('AWS_EXTERNAL_ID', 'SNOWFLAKE_IAM_USER', 'AWS_ROLE', 'URL'), ) if not bucket_name or not prefix: m = re.match(r'^\["s3://([a-z-]*)/(.*)"\]$', stage_props['URL']) if m: bucket_name, prefix = m.groups() else: raise RuntimeError('cannot determine bucket name or prefix') prefix = prefix.rstrip('/') return { 'newStage': 'created', 'newMessage': CONNECT_RESPONSE_MESSAGE.format( role=stage_props[ 'AWS_ROLE'], # this seems better than what we do in other places? role_trust_relationship=dumps( { "Version": "2012-10-17", "Statement": [{ "Effect": "Allow", "Principal": { "AWS": stage_props['SNOWFLAKE_IAM_USER'] }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "sts:ExternalId": stage_props['AWS_EXTERNAL_ID'] } }, }], }, indent=4, ), role_policy=dumps( { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": ["s3:GetObject", "s3:GetObjectVersion"], "Resource": f"arn:aws:s3:::{bucket_name}/{prefix}/*", }, { "Effect": "Allow", "Action": "s3:ListBucket", "Resource": f"arn:aws:s3:::{bucket_name}", "Condition": { "StringLike": { "s3:prefix": [f"{prefix}/*"] } }, }, ], }, indent=4, ), ), }