Consultas de SQL para obtener información valiosa sobre la seguridad

En este documento se describe cómo analizar los registros de auditoría de Cloud Logging mediante consultas de SQL estándar de BigQuery en la página Analíticas de registros. Las consultas SQL te permiten agregar y analizar tus registros de auditoría, que proporcionan información sobre las actividades administrativas y los accesos a tus recursos de Google Cloud .

Acerca de los registros de auditoría

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

  • Registros de auditoría de la actividad administrativa: registran las llamadas a la API u 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 datos: registran las llamadas a la API que leen la configuración o los metadatos de los recursos, así como las llamadas a la API iniciadas por el usuario que crean, modifican o leen los datos de los recursos proporcionados por el usuario. Como el acceso a los datos es una operación de API frecuente, estos registros están inhabilitados de forma predeterminada (excepto en BigQuery).

  • Registros de auditoría de los eventos del sistema: contienen entradas de registro deGoogle Cloud acciones que modifican la configuración de los recursos. Estos registros los generan los sistemas de Google, 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: se registran cuando un servicio deniega el acceso a un usuario o a una cuenta de servicio debido a una infracción de la política de seguridad. Google Cloud 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 el artículo Descripción general de los registros de auditoría. Para ver una lista de los servicios integrados con registros de auditoría, consulta los Google Cloud servicios con registros de auditoría.

Usar registros de auditoría para identificar infracciones de las políticas o actividades sospechosas

Puede usar los registros de auditoría para identificar infracciones de las políticas o actividades sospechosas:

Antes de empezar

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

  • Asegúrate de que tienes acceso a una vista del contenedor de registros al que se dirigen los registros de auditoría. El contenedor de registros debe actualizarse para usar Analíticas de registros. Para obtener información sobre cómo crear un contenedor de registros que se actualice para usar Analíticas de registros, consulta Configurar contenedores de registros.

  • Para obtener los permisos que necesitas para crear receptores y ver registros, pide a tu administrador que te conceda los siguientes roles de gestión de identidades y accesos:

    Para obtener más información sobre cómo conceder roles, consulta el artículo Gestionar el acceso a proyectos, carpetas y organizaciones.

    También puedes conseguir los permisos necesarios a través de roles personalizados u otros roles predefinidos.

    En función de los registros de auditoría que quieras ver, es posible que necesites roles o permisos independientes. Para obtener información sobre cómo definir roles de gestión de identidades y accesos, consulta la documentación sobre control de acceso con gestión de identidades y accesos de Logging.

  • Para usar las consultas de este documento en la página Analíticas de registros, haz lo siguiente:

    1. En la Google Cloud consola, ve a la página Analíticas de registros:

      Vaya a Analíticas de registros.

      Si usas la barra de búsqueda para encontrar esta página, selecciona el resultado cuya sección sea Registro.

    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, busca la vista de registro y selecciona Consulta. El panel Consulta se rellena con una consulta predeterminada que incluye el nombre de la tabla de la vista de registro consultada. 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 Consultar una vista de registro.

    3. Sustituye TABLE_NAME_OF_LOG_VIEW por el nombre de la tabla de la vista de registro que quieras consultar y, a continuación, copia la consulta.

    4. Pega la consulta en el panel Consulta y, a continuación, haz clic en Ejecutar consulta.

Consultas de ejemplo

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

Cambios realizados en los ajustes de registro

Para identificar cuándo se inhabilitan los registros de auditoría o cuándo se realizan cambios en la configuración de registro predeterminada, consulta los registros de auditoría de actividad de administración:

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 las acciones más habituales que se han realizado en el último mes

Para identificar qué acciones se han realizado 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
  `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

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, el service_name, el tipo de recurso y un contador de las acciones realizadas.

Detectar los roles concedidos en una cuenta de servicio

Para identificar la suplantación de cuentas de servicio o los roles concedidos en cuentas de servicio, consulta los registros de auditoría de actividad de 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

La consulta anterior busca registros de auditoría que capturen los roles que se han concedido a un principal en una cuenta de servicio. El rol Creador de tokens de cuenta de servicio permite al principal suplantar la cuenta de servicio. La consulta también especifica un periodo de los últimos siete días y excluye a los beneficiarios aprobados (%@example.com).

Identificar el uso elevado de la API por parte de una entidad principal

Para identificar un uso inusualmente alto de la API por parte de una entidad, 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 `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 la entidad de seguridad especificada, principal_email, la consulta calcula el número medio de llamadas a la API por día y la desviación estándar de esas llamadas. Si el número medio de llamadas a la API es superior a la media móvil más tres veces la desviación estándar, la consulta mostrará la siguiente información:

  • Contador de las acciones realizadas.
  • Es la media de las acciones realizadas al día.
  • Las acciones específicas que se han realizado.

Siguientes pasos