Consultas SQL para insights de segurança

Neste documento, descrevemos como analisar os registros de auditoria do Cloud Logging usando consultas SQL padrão do BigQuery na página Análise de registros. As consultas SQL permitem agregar e analisar os registros de auditoria, que fornecem informações sobre atividades administrativas e acesso nos recursos do Google Cloud.

Sobre os registros de auditoria

Há quatro tipos de registros de auditoria que podem ser gravados pelos serviços do Google Cloud:

  • Registros de auditoria de atividade do administrador: registram chamadas de API ou outras ações que modificam a configuração ou os metadados de recursos. Esses registros são sempre gravados. Não é possível configurá-los, excluí-los ou desativá-los.

  • Registros de auditoria de acesso a dados: eles registram chamadas de API que lêem a configuração ou os metadados dos recursos, além das chamadas de API orientadas pelo usuário que criam, modificam ou leem os dados dos recursos fornecidos pelo usuário. Como o acesso a dados é uma operação frequente da API, esses registros são desativados por padrão (exceto no BigQuery).

  • Registros de auditoria de eventos do sistema: contêm entradas de registro para ações administrativas do Google Cloud que modificam a configuração de recursos. Esses registros são gerados pelos sistemas do Google e não por ações do usuário. Não é possível configurar, excluir ou desativar os registros de auditoria de eventos do sistema.

  • Registros de auditoria de políticas negadas: são registrados quando um serviço do Google Cloud nega acesso a um usuário ou a uma conta de serviço devido a uma violação da política de segurança. Esses registros não podem ser desativados, mas é possível usar filtros de exclusão para impedir que eles sejam armazenados no Logging.

Para mais informações sobre os registros de auditoria, consulte Visão geral dos registros de auditoria. Para uma lista de serviços integrados com registros de auditoria, consulte Serviços do Google Cloud com registros de auditoria.

Usar registros de auditoria para identificar violações de políticas ou atividades suspeitas

Você pode usar os registros de auditoria para identificar violações de políticas ou atividades suspeitas:

  • Para identificar possíveis escalonamentos de privilégios usando o Identity and Access Management (IAM) ou a evasão de defesas desativando o registro, use os registros de auditoria de atividade do administrador. Para conferir um exemplo de consulta que identifica esse cenário, consulte Mudanças feitas nas configurações de geração de registros.

  • Para identificar possíveis usos indevidos de APIs ou dados hospedados em serviços como o Cloud Storage ou o BigQuery, use os registros de auditoria de acesso a dados. Para conferir uma consulta de exemplo que identifica esse cenário, consulte Identificar o uso intenso de uma API por um principal.

  • Para identificar a frequência de acesso aos dados e por quais usuários, consulte todos os registros de auditoria. Para conferir um exemplo de consulta que identifica esse cenário, consulte Determinar as ações mais comuns realizadas no último mês.

Antes de começar

  • Verifique se você tem um projeto, uma pasta ou uma organização do Google Cloud que gera registros de auditoria.

  • Verifique se você tem acesso a uma visualização no bucket de registros para onde os registros de auditoria são roteados. O bucket de registros precisa ser atualizado para usar a Análise de dados de registros. Para informações sobre como criar um bucket de registros que é atualizado para usar a Análise de dados de registros, consulte Configurar buckets de registros.

  • Para ter as permissões necessárias para criar sinks e visualizar logs, peça ao administrador para conceder a você os seguintes papéis do IAM:

    Para mais informações sobre a concessão de papéis, consulte Gerenciar o acesso a projetos, pastas e organizações.

    Também é possível conseguir as permissões necessárias por meio de papéis personalizados ou de outros papéis predefinidos.

    Dependendo dos registros de auditoria que você quer consultar, talvez seja necessário ter funções ou permissões separadas. Para informações sobre como definir papéis do IAM, consulte a documentação de Controle de acesso com o IAM do Logging.

  • Para usar as consultas deste documento na página Análise de dados de registros, faça o seguinte:

    1. No console do Google Cloud, acesse a página Análise de dados de registros:

      Acesse Análise de registros

      Se você usar a barra de pesquisa para encontrar essa página, selecione o resultado com o subtítulo Logging.

    2. Identifique o nome da tabela para a visualização de registro que você quer consultar.

      Para identificar esse nome, acesse a lista Visualizações de registro, localize a visualização de registro e selecione Consulta. O painel Consulta é preenchido com uma consulta padrão, que inclui o nome da tabela da visualização de registro consultada. O nome da tabela tem o formato project_ID.region.bucket_ID.view_ID.

      Para mais informações sobre como acessar a consulta padrão, consulte Consultar uma visualização de registro.

    3. Substitua TABLE_NAME_OF_LOG_VIEW pelo nome da tabela da visualização de registro que você quer consultar e copie a consulta.

    4. Cole a consulta no painel Consulta e clique em Executar consulta.

Amostras de consultas

Esta seção mostra exemplos de consultas SQL para consultar registros de auditoria.

Mudanças nas configurações de geração de registros

Para identificar quando os registros de auditoria estão desativados ou quando as configurações padrão de geração de registros são alteradas, consulte os registros de auditoria de atividade do administrador:

SELECT
  receive_timestamp, timestamp AS eventTimestamp,
  proto_payload.audit_log.request_metadata.caller_ip,
  proto_payload.audit_log.authentication_info.principal_email,
  proto_payload.audit_log.resource_name,
  proto_payload.audit_log.method_name
FROM
  `TABLE_NAME_OF_LOG_VIEW`
WHERE
  proto_payload.audit_log.service_name = "logging.googleapis.com"
  AND log_id = "cloudaudit.googleapis.com/activity"

Determinar as ações mais comuns realizadas no último mês

Para identificar quais ações são realizadas com mais frequência nos últimos 30 dias, consulte todos os registros de auditoria:

SELECT
  proto_payload.audit_log.method_name,
  proto_payload.audit_log.service_name,
  resource.type,
  COUNT(*) AS counter
FROM
  `TABLE_NAME_OF_LOG_VIEW`
WHERE
  timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND log_id="cloudaudit.googleapis.com/data_access"
GROUP BY
  proto_payload.audit_log.method_name,
  proto_payload.audit_log.service_name,
  resource.type
ORDER BY
  counter DESC
LIMIT 100

A consulta anterior pesquisa todos os registros de auditoria dos últimos 30 dias e retorna as 100 ações mais realizadas com informações sobre o method_name, o service_name, o tipo de recurso e um contador das ações realizadas.

Detectar papéis concedidos em uma conta de serviço

Para identificar a representação de uma conta de serviço ou os papéis concedidos a contas de serviço, consulte os registros de auditoria de atividade do administrador:

SELECT
  timestamp,
  proto_payload.audit_log.authentication_info.principal_email as grantor,
  JSON_VALUE(bindingDelta.member) as grantee,
  JSON_VALUE(bindingDelta.role) as role,
  proto_payload.audit_log.resource_name,
  proto_payload.audit_log.method_name
FROM
  `TABLE_NAME_OF_LOG_VIEW`,
  UNNEST(JSON_QUERY_ARRAY(proto_payload.audit_log.service_data.policyDelta.bindingDeltas)) AS bindingDelta
WHERE
  timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND log_id = "cloudaudit.googleapis.com/activity"
  AND (
    (resource.type = "service_account"
    AND proto_payload.audit_log.method_name LIKE "google.iam.admin.%.SetIAMPolicy")
    OR
    (resource.type IN ("project", "folder", "organization")
    AND proto_payload.audit_log.method_name = "SetIamPolicy"
    AND JSON_VALUE(bindingDelta.role) LIKE "roles/iam.serviceAccount%")
  )
  AND JSON_VALUE(bindingDelta.action) = "ADD"
  -- Principal (grantee) exclusions
  AND JSON_VALUE(bindingDelta.member) NOT LIKE "%@example.com"
ORDER BY
  timestamp DESC

A consulta anterior procura registros de auditoria que capturam papéis concedidos a um principal em uma conta de serviço. O papel de Criador de token da conta de serviço permite que o principal imite a conta de serviço. A consulta também especifica um período de sete dias e exclui beneficiários aprovados (%@example.com).

Identificar o uso elevado de uma API por um principal

Para identificar um uso excepcionalmente alto da API por um principal, consulte todos os registros de auditoria:

SELECT
  *
FROM (
  SELECT
    *,
    AVG(counter) OVER (
      PARTITION BY principal_email
      ORDER BY day
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS avg,
    STDDEV(counter) OVER (
      PARTITION BY principal_email
      ORDER BY day
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS stddev,
    COUNT(*) OVER (
      PARTITION BY principal_email
      RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS numSamples
  FROM (
    SELECT
      proto_payload.audit_log.authentication_info.principal_email,
      EXTRACT(DATE FROM timestamp) AS day,
      ARRAY_AGG(DISTINCT proto_payload.audit_log.method_name IGNORE NULLS) AS actions,
      COUNT(*) AS counter
    FROM `TABLE_NAME_OF_LOG_VIEW`
    WHERE
      timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 60 DAY)
      AND proto_payload.audit_log.authentication_info.principal_email IS NOT NULL
      AND proto_payload.audit_log.method_name NOT LIKE "storage.%.get"
      AND proto_payload.audit_log.method_name NOT LIKE "v1.compute.%.list"
      AND proto_payload.audit_log.method_name NOT LIKE "beta.compute.%.list"
    GROUP BY
      proto_payload.audit_log.authentication_info.principal_email,
      day
  )
)
WHERE
  counter > avg + 3 * stddev
  AND day >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
ORDER BY
  counter DESC

Para o principal especificado, principal_email, a consulta calcula o número médio de chamadas de API por dia e a variação padrão dessas chamadas. Quando o número médio de chamadas de API é maior que a média em execução mais três vezes o desvio padrão, a consulta mostra as seguintes informações:

  • Um contador das ações realizadas.
  • A média calculada de ações realizadas por dia.
  • As ações específicas que foram realizadas.

A seguir