Consultas en SQL para estadísticas de seguridad

Puedes ejecutar consultas de SQL estándar de BigQuery en la página Estadísticas de observabilidad para agregar y analizar tus registros de auditoría de Cloud Logging. Analizar los registros de auditoría te ayuda a supervisar las actividades administrativas, hacer un seguimiento del acceso a los datos y, además, identificar los incumplimientos de política o la actividad sospechosa en tus recursos deGoogle Cloud .

Acerca de los registros de auditoría

Existen cuatro tipos de registros de auditoría que pueden escribir los servicios de Google Cloud:

  • Registros de auditoría de actividad del administrador: Estos registros registran las llamadas a la API o las otras acciones que modifican la configuración o los metadatos de los recursos. Estos registros siempre se escriben; no puedes configurarlos, excluirlos ni inhabilitarlos.

  • Registros de auditoría de acceso a los datos: Estos registros graban llamadas a la API mediante las cuales se leen los metadatos o la configuración de los recursos, así como llamadas a la API controladas por el usuario con las que se crean, modifican o leen datos de los recursos que proporciona el usuario. Debido a que el acceso a los datos es una operación de la API frecuente, estos registros están inhabilitados de forma predeterminada (excepto para BigQuery).

  • Registros de auditoría de eventos del sistema: Los registros de auditoría de eventos del sistema contienen entradas de registro para las acciones deGoogle Cloud que modifican la configuración de los recursos. Los sistemas de Google generan estos registros, no las acciones de los usuarios. No puedes configurar, excluir ni inhabilitar los registros de auditoría de eventos del sistema.

  • Registros de auditoría de política denegada: Estos registros se crean cuando un servicio de Google Cloud deniega el acceso a un usuario o a una cuenta de servicio debido a un incumplimiento de política de seguridad. Estos registros no se pueden inhabilitar, pero puedes usar filtros de exclusión para evitar que se almacenen en Logging.

Para obtener más información sobre los registros de auditoría, consulta Descripción general de los registros de auditoría. Para obtener una lista de los servicios integrados en los registros de auditoría, consulta Servicios deGoogle Cloud con registros de auditoría.

Usa los registros de auditoría para identificar incumplimientos de políticas o actividades sospechosas

Puedes usar los registros de auditoría para identificar incumplimientos de políticas o actividad sospechosa:

  • Para identificar un posible elevación de privilegios con Identity and Access Management (IAM) o la evasión de defensa mediante la inhabilitación de Logging, usa los registros de auditoría de la actividad del administrador. Para obtener una consulta de ejemplo que identifique esta situación, consulta Cambios realizados en la configuración de Logging.

  • Para identificar el posible uso inadecuado de las APIs o los datos alojados en servicios como Cloud Storage o BigQuery, usa los registros de auditoría de acceso a los datos. Para obtener una consulta de ejemplo que identifique esta situación, consulta Identifica el uso alto de la API por parte de una principal.

  • Para identificar con qué frecuencia se accede a los datos y qué usuarios lo hacen, consulta todos los registros de auditoría. Para obtener una consulta de ejemplo que identifique esta situación, consulta Determina las acciones más comunes realizadas en el último mes.

Antes de comenzar

  • Asegúrate de tener un proyecto, una carpeta o una organización de Google Cloud que genere registros de auditoría.

  • Asegúrate de tener acceso a una vista en el bucket de registros al que se enrutan los registros de auditoría. El bucket de registros debe actualizarse para usar Estadísticas de observabilidad. Para obtener información sobre cómo crear un bucket de registros que se actualice para usar Estadísticas de observabilidad, consulta Configura buckets de registros.

  • Para obtener los permisos que necesitas para crear receptores y ver registros, pídele a tu administrador que te otorgue los siguientes roles de IAM:

    Para obtener más información sobre cómo otorgar roles, consulta Administra el acceso a proyectos, carpetas y organizaciones.

    También puedes obtener los permisos necesarios a través de roles personalizados o cualquier otro rol predefinido.

    Según los registros de auditoría que quieras ver, es posible que necesites roles o permisos independientes. Para obtener información sobre cómo configurar los roles de IAM, consulta la documentación de Control de acceso con IAM de Logging.

  • Para usar las consultas de este documento en la página Estadísticas de observabilidad, haz lo siguiente:

    1. En la Google Cloud consola, ve a la página Estadísticas de observabilidad:

      Ir a Estadísticas de observabilidad

      Si usas la barra de búsqueda para encontrar esta página, selecciona el resultado cuyo subtítulo es Logging.

    2. Identifica el nombre de la tabla de la vista de registro que quieres consultar.

      Para identificar este nombre, ve a la lista Vistas de registro, ubica la vista de registro y, luego, selecciona Consulta. El panel Consulta se propaga con una consulta predeterminada, que incluye el nombre de la tabla de la vista de registro que se consulta. El nombre de la tabla tiene el formato project_ID.region.bucket_ID.view_ID.

      Para obtener más información sobre cómo acceder a la consulta predeterminada, consulta Consulta una vista de registro.

    3. Reemplaza VIEW por el nombre de la tabla de la vista de registro que deseas consultar y, luego, copia la consulta.

    4. Pega la consulta en el panel Consulta y, luego, haz clic en Ejecutar consulta.

Consultas de muestra

En esta sección, se proporcionan consultas en SQL de ejemplo para consultar registros de auditoría.

Cambios realizados en la configuración de Logging

Para identificar cuándo se inhabilitan los registros de auditoría o cuándo se realizan cambios en la configuración predeterminada de Logging, consulta los registros de auditoría de la actividad del 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
  `VIEW`
WHERE
  proto_payload.audit_log.service_name = "logging.googleapis.com"
  AND log_id = "cloudaudit.googleapis.com/activity"

Determinar las acciones más comunes realizadas en el último mes

Para identificar qué acciones se realizan con más frecuencia en los últimos 30 días, consulta todos los registros de auditoría:

SELECT
  proto_payload.audit_log.method_name,
  proto_payload.audit_log.service_name,
  resource.type,
  COUNT(*) AS counter
FROM
  `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

La consulta anterior busca todos los registros de auditoría de los últimos 30 días y devuelve las 100 acciones más realizadas con información sobre el method_name, service_name, el tipo de recurso y un contador de las acciones realizadas.

Detecta los roles otorgados en una cuenta de servicio

Para identificar la identidad temporal como cuenta de servicio o los roles otorgados en las cuentas de servicio, consulta los registros de auditoría de la actividad del 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
  `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

La consulta anterior busca registros de auditoría que capturen los roles que se otorgan a una principal en una cuenta de servicio. El rol de creador de tokens de cuenta de servicio permite que la principal use la identidad de la cuenta de servicio. La consulta también especifica un período de los últimos siete días y excluye a los beneficiarios aprobados (%@example.com).

Identifica el uso elevado de la API por parte de un principal

Para identificar un uso de la API inusualmente alto por parte de un principal, consulta todos los registros de auditoría:

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 `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 la principal especificada, principal_email, la consulta calcula la cantidad promedio de llamadas a la API por día y la desviación estándar de esas llamadas a la API. Cuando la cantidad promedio de llamadas a la API es mayor que el promedio móvil más tres veces la desviación estándar, la consulta muestra la siguiente información:

  • Un contador de las acciones realizadas.
  • Es el promedio calculado de acciones realizadas por día.
  • Las acciones específicas que se realizaron.

¿Qué sigue?