Dev.to Security 🔐 Cybersecurity 👁 0 📖 6 min read

CloudTrail no Athena: as 10 queries que todo SecOps devia ter salvas

Quando dá problema, ninguém tem tempo de lembrar o nome da coluna do CloudTrail. Esse post é pra salvar nos favoritos. Contas, buckets e IDs são placeholders. A tabela Para trilha organizacional (a estrutura

Quando dá problema, ninguém tem tempo de lembrar o nome da coluna do CloudTrail. Esse post é pra salvar nos favoritos. Contas, buckets e IDs são placeholders.

A tabela

Para trilha organizacional (a estrutura de pastas tem o ID da organização). Com partition projection você não precisa rodar MSCK REPAIR nem criar partição na mão.

CREATE EXTERNAL TABLE cloudtrail_logs (
  eventversion STRING,
  useridentity STRUCT<
    type: STRING,
    principalid: STRING,
    arn: STRING,
    accountid: STRING,
    invokedby: STRING,
    accesskeyid: STRING,
    username: STRING,
    sessioncontext: STRUCT<
      attributes: STRUCT<mfaauthenticated: STRING, creationdate: STRING>,
      sessionissuer: STRUCT<type: STRING, principalid: STRING, arn: STRING, accountid: STRING, username: STRING>
    >
  >,
  eventtime STRING,
  eventsource STRING,
  eventname STRING,
  awsregion STRING,
  sourceipaddress STRING,
  useragent STRING,
  errorcode STRING,
  errormessage STRING,
  requestparameters STRING,
  responseelements STRING,
  additionaleventdata STRING,
  requestid STRING,
  eventid STRING,
  readonly STRING,
  resources ARRAY<STRUCT<arn: STRING, accountid: STRING, type: STRING>>,
  eventtype STRING,
  apiversion STRING,
  recipientaccountid STRING,
  sharedeventid STRING,
  vpcendpointid STRING
)
PARTITIONED BY (account STRING, region STRING, `timestamp` STRING)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://<BUCKET_CLOUDTRAIL>/AWSLogs/<ORG_ID>/'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.account.type' = 'enum',
  'projection.account.values' = '<ACCOUNT_ID_1>,<ACCOUNT_ID_2>,<ACCOUNT_ID_3>',
  'projection.region.type' = 'enum',
  'projection.region.values' = 'sa-east-1,us-east-1,us-east-2,us-west-2',
  'projection.timestamp.type' = 'date',
  'projection.timestamp.format' = 'yyyy/MM/dd',
  'projection.timestamp.interval' = '1',
  'projection.timestamp.interval.unit' = 'DAYS',
  'projection.timestamp.range' = '2025/01/01,NOW',
  'storage.location.template' = 's3://<BUCKET_CLOUDTRAIL>/AWSLogs/<ORG_ID>/${account}/CloudTrail/${region}/${timestamp}'
);

Três dicas que economizam dinheiro e dor de cabeça:

  1. Sempre filtre pela partição "timestamp" (com aspas duplas, porque é palavra reservada). Sem isso o Athena lê o bucket inteiro.
  2. Coloque em projection.region.values todas as regiões, não só as que você usa. Atacante adora região que ninguém olha. Lista completa: aws ec2 describe-regions --all-regions --query 'Regions[].RegionName' --output text.
  3. Trilha de conta única (sem Organization) tem o caminho AWSLogs/<ACCOUNT_ID>/CloudTrail/.... Ajuste o LOCATION e o template removendo o nível do org.

Todas as queries abaixo olham os últimos 7 dias. Troque o intervalo conforme a necessidade.

1. Uso da conta root

Root não deveria fazer nada no dia a dia. Qualquer linha aqui merece explicação.

SELECT eventtime, account, awsregion, eventname, sourceipaddress, useragent, errorcode
FROM cloudtrail_logs
WHERE useridentity.type = 'Root'
  AND useridentity.invokedby IS NULL
  AND eventtype <> 'AwsServiceEvent'
  AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d')
ORDER BY eventtime DESC;

2. Login no console sem MFA

SELECT eventtime, account, useridentity.arn, sourceipaddress,
       json_extract_scalar(responseelements, '$.ConsoleLogin') AS resultado
FROM cloudtrail_logs
WHERE eventname = 'ConsoleLogin'
  AND json_extract_scalar(additionaleventdata, '$.MFAUsed') = 'No'
  AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d')
ORDER BY eventtime DESC;

Login via IAM Identity Center (SSO) aparece com MFAUsed = No porque o MFA acontece no provedor de identidade. Filtre por useridentity.type = 'IAMUser' se quiser ver só usuários IAM.

3. Rajada de AccessDenied por identidade

Quem está tentando fazer o que não pode? Típico de credencial roubada fazendo reconhecimento.

SELECT useridentity.arn,
       sourceipaddress,
       count(*) AS negados,
       count(DISTINCT eventname) AS acoes_distintas,
       min(eventtime) AS inicio,
       max(eventtime) AS fim
FROM cloudtrail_logs
WHERE errorcode IN ('AccessDenied', 'AccessDeniedException', 'UnauthorizedOperation', 'Client.UnauthorizedOperation')
  AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d')
GROUP BY useridentity.arn, sourceipaddress
HAVING count(*) > 20
ORDER BY negados DESC;

4. Access keys e usuários criados

SELECT eventtime, account, eventname, useridentity.arn AS quem,
       json_extract_scalar(requestparameters, '$.userName') AS usuario_alvo,
       sourceipaddress
FROM cloudtrail_logs
WHERE eventsource = 'iam.amazonaws.com'
  AND eventname IN ('CreateUser', 'CreateAccessKey', 'CreateLoginProfile', 'UpdateLoginProfile')
  AND errorcode IS NULL
  AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d')
ORDER BY eventtime DESC;

5. Alguém tentou cegar você

Desligar trilha, detector do GuardDuty ou gravador do Config é o primeiro passo de um atacante cuidadoso.

SELECT eventtime, account, awsregion, eventsource, eventname, useridentity.arn, sourceipaddress, errorcode
FROM cloudtrail_logs
WHERE eventname IN (
    'StopLogging', 'DeleteTrail', 'UpdateTrail', 'PutEventSelectors',
    'DeleteDetector', 'UpdateDetector', 'DisassociateFromAdministratorAccount',
    'StopConfigurationRecorder', 'DeleteConfigurationRecorder', 'DeleteDeliveryChannel',
    'DisableSecurityHub', 'DeleteAnalyzer', 'DeleteFlowLogs'
  )
  AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d')
ORDER BY eventtime DESC;

6. Bucket S3 ficando público

SELECT eventtime, account, eventname, useridentity.arn,
       json_extract_scalar(requestparameters, '$.bucketName') AS bucket,
       sourceipaddress
FROM cloudtrail_logs
WHERE eventsource = 's3.amazonaws.com'
  AND eventname IN ('PutBucketPolicy', 'PutBucketAcl', 'DeleteBucketPublicAccessBlock', 'PutBucketPublicAccessBlock', 'DeleteBucketPolicy')
  AND errorcode IS NULL
  AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d')
ORDER BY eventtime DESC;

7. Security group aberto para a internet

SELECT eventtime, account, awsregion, useridentity.arn,
       json_extract_scalar(requestparameters, '$.groupId') AS sg,
       requestparameters
FROM cloudtrail_logs
WHERE eventname IN ('AuthorizeSecurityGroupIngress', 'ModifySecurityGroupRules')
  AND (requestparameters LIKE '%0.0.0.0/0%' OR requestparameters LIKE '%::/0%')
  AND errorcode IS NULL
  AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d')
ORDER BY eventtime DESC;

8. AssumeRole vindo de outra conta

SELECT eventtime,
       recipientaccountid AS conta_destino,
       useridentity.accountid AS conta_origem,
       useridentity.arn AS quem,
       json_extract_scalar(requestparameters, '$.roleArn') AS role,
       sourceipaddress
FROM cloudtrail_logs
WHERE eventsource = 'sts.amazonaws.com'
  AND eventname = 'AssumeRole'
  AND useridentity.accountid IS NOT NULL
  AND useridentity.accountid <> recipientaccountid
  AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d')
ORDER BY eventtime DESC;

Cruze o resultado com a lista de contas da sua Organization. Conta que não é sua é alerta vermelho.

9. Instâncias lançadas em região incomum

SELECT eventtime, account, awsregion, useridentity.arn,
       json_extract_scalar(requestparameters, '$.instanceType') AS tipo,
       sourceipaddress
FROM cloudtrail_logs
WHERE eventname = 'RunInstances'
  AND errorcode IS NULL
  AND awsregion NOT IN ('sa-east-1', 'us-east-1')
  AND "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d')
ORDER BY eventtime DESC;

Troque a lista do NOT IN pelas regiões que você realmente usa. Pegar instância GPU (p*, g*) em região estranha é minerador quase com certeza.

10. IP novo para uma identidade

O CloudTrail não traz geolocalização, mas dá pra achar IP que nunca tinha aparecido para aquela identidade nos 30 dias anteriores:

WITH historico AS (
  SELECT DISTINCT useridentity.arn AS arn, sourceipaddress AS ip
  FROM cloudtrail_logs
  WHERE "timestamp" BETWEEN date_format(current_date - INTERVAL '37' DAY, '%Y/%m/%d')
                        AND date_format(current_date - INTERVAL '8' DAY, '%Y/%m/%d')
    AND useridentity.arn IS NOT NULL
),
recente AS (
  SELECT useridentity.arn AS arn, sourceipaddress AS ip,
         min(eventtime) AS primeiro, count(*) AS chamadas
  FROM cloudtrail_logs
  WHERE "timestamp" >= date_format(current_date - INTERVAL '7' DAY, '%Y/%m/%d')
    AND useridentity.arn IS NOT NULL
    AND sourceipaddress NOT LIKE '%.amazonaws.com'
  GROUP BY useridentity.arn, sourceipaddress
)
SELECT r.arn, r.ip, r.primeiro, r.chamadas
FROM recente r
LEFT JOIN historico h ON r.arn = h.arn AND r.ip = h.ip
WHERE h.ip IS NULL
ORDER BY r.primeiro DESC;

Essa é mais pesada (lê 37 dias). Rode de vez em quando, não todo dia.

Deixando salvas

No Athena, salve cada uma como named query pra todo o time ter acesso:

aws athena create-named-query \
  --name "secops-01-uso-root" \
  --database default \
  --work-group primary \
  --query-string file://01-uso-root.sql

E se quiser rodar via CLI e já pegar o resultado:

QID=$(aws athena start-query-execution \
  --work-group primary \
  --query-string file://01-uso-root.sql \
  --query QueryExecutionId --output text)

aws athena get-query-execution --query-execution-id "$QID" \
  --query QueryExecution.Status.State --output text

aws athena get-query-results --query-execution-id "$QID" --output table

O workgroup precisa ter um local de resultado configurado no S3. Se não tiver, passe --result-configuration OutputLocation=s3://<BUCKET_RESULTADOS>/.

O próximo passo natural é transformar as queries 1, 5 e 6 em alerta em tempo real com EventBridge, que é o assunto de outro post da série de segurança aqui do blog.

📰 Read the original article on Dev.to Security

Originally published by Dev.to Security. Aggregated on AIWithGhost for educational purposes — full credit and traffic to the original publisher.