보안 통계용 SQL 쿼리

이 문서에서는 로그 애널리틱스 페이지에서 BigQuery 표준 SQL 쿼리를 사용하여 Cloud Logging 감사 로그를 분석하는 방법을 설명합니다. SQL 쿼리를 사용하면 Google Cloud 리소스의 관리 활동 및 액세스에 대한 정보를 제공하는 감사 로그를 집계하고 분석할 수 있습니다.

감사 로그 정보

Google Cloud 서비스에서 작성할 수 있는 감사 로그에는 다음과 같은 4가지 유형이 있습니다.

  • 관리자 활동 감사 로그: 관리자 활동 감사 로그에는 API 호출 또는 리소스의 구성이나 메타데이터를 수정하는 기타 작업이 기록됩니다. 이러한 로그는 항상 작성됩니다. 사용자가 이를 구성, 제외, 중지할 수 없습니다.

  • 데이터 액세스 감사 로그: 데이터 액세스 감사 로그에는 리소스의 구성 또는 메타데이터를 읽는 API 호출뿐만 아니라 사용자가 제공한 리소스 데이터를 생성, 수정 또는 읽는 사용자 주도 API 호출도 기록됩니다. 데이터 액세스는 빈번한 API 작업이므로 이러한 로그는 기본적으로 중지됩니다(BigQuery 제외).

  • 시스템 이벤트 감사 로그: 시스템 이벤트 감사 로그는 리소스 구성을 수정하는 Google Cloud 작업의 로그 항목을 포함합니다. 이러한 로그는 Google 시스템에서 생성되며 사용자 작업에 의해 생성되지 않습니다. 시스템 이벤트 감사 로그는 사용자가 구성, 제외, 중지할 수 없습니다.

  • 정책 거부 감사 로그: 정책 거부 감사 로그는 보안 정책 위반으로 인해 Google Cloud 서비스가 사용자 또는 서비스 계정에 대해 액세스를 거부할 때 기록됩니다. 이러한 로그는 사용 중지할 수 없지만 제외 필터를 사용하여 이러한 로그가 Logging에 저장되지 않도록 할 수 있습니다.

감사 로그에 대한 자세한 내용은 감사 로그 개요를 참고하세요. 감사 로그와 통합된 서비스 목록은 감사 로그가 있는 Google Cloud 서비스를 참조하세요.

감사 로그를 사용하여 정책 위반 또는 의심스러운 활동 식별

감사 로그를 사용하여 정책 위반 또는 의심스러운 활동을 식별할 수 있습니다.

  • Identity and Access Management(IAM)를 사용하여 잠재적인 권한 에스컬레이션을 확인하거나 Logging을 중지하여 방어 회피를 확인하려면 관리자 활동 감사 로그를 사용합니다. 이 시나리오를 식별하는 샘플 쿼리는 Logging 설정 변경사항을 참고하세요.

  • API의 잠재적 오용이나 Cloud Storage 또는 BigQuery와 같은 서비스에서 호스팅되는 데이터를 식별하려면 데이터 액세스 감사 로그를 사용합니다. 이 시나리오를 식별하는 샘플 쿼리는 주 구성원의 높은 API 사용량 식별을 참고하세요.

  • 데이터 액세스 빈도와 데이터에 액세스한 사용자를 확인하려면 모든 감사 로그를 쿼리합니다. 이 시나리오를 식별하는 샘플 쿼리는 지난달에 가장 많이 수행된 작업 확인을 참조하세요.

시작하기 전에

  • 감사 로그를 생성하는 Google Cloud 프로젝트, 폴더 또는 조직이 있는지 확인합니다.

  • 감사 로그가 라우팅되는 로그 버킷의 뷰에 대한 액세스 권한이 있어야 합니다. 로그 애널리틱스를 사용하도록 로그 버킷을 업그레이드해야 합니다. 로그 애널리틱스를 사용하도록 업그레이드된 로그 버킷을 만드는 방법에 대한 자세한 내용은 로그 버킷 구성을 참조하세요.

  • 싱크를 만들고 로그를 보는 데 필요한 권한을 얻으려면 관리자에게 다음 IAM 역할을 부여해 달라고 요청하세요.

    역할 부여에 대한 자세한 내용은 프로젝트, 폴더, 조직에 대한 액세스 관리를 참조하세요.

    커스텀 역할이나 다른 사전 정의된 역할을 통해 필요한 권한을 얻을 수도 있습니다.

    보려는 감사 로그에 따라 별도의 역할이나 권한이 필요할 수 있습니다. IAM 역할 설정에 관한 자세한 내용은 Logging IAM으로 액세스 제어 문서를 참고하세요.

  • 로그 애널리틱스 페이지에서 이 문서의 쿼리를 사용하려면 다음을 수행합니다.

    1. Google Cloud 콘솔에서 로그 분석 페이지로 이동합니다.

      로그 애널리틱스로 이동

      검색창을 사용하여 이 페이지를 찾은 경우 부제목이 Logging인 결과를 선택합니다.

    2. 쿼리할 로그 뷰의 테이블 이름을 식별합니다.

      이 이름을 확인하려면 로그 뷰 목록으로 이동하고 로그 뷰를 찾은 후 쿼리를 선택합니다. 쿼리 창에는 쿼리되는 로그 뷰의 테이블 이름이 포함된 기본 쿼리가 채워집니다. 테이블 이름은 project_ID.region.bucket_ID.view_ID 형식입니다.

      기본 쿼리에 액세스하는 방법에 대한 자세한 내용은 로그 뷰 쿼리를 참조하세요.

    3. TABLE_NAME_OF_LOG_VIEW를 쿼리하려는 로그 뷰의 테이블 이름으로 바꾼 다음 쿼리를 복사합니다.

    4. 쿼리 창에 쿼리를 붙여넣은 다음 쿼리 실행을 클릭합니다.

샘플 쿼리

이 섹션에서는 감사 로그 쿼리를 위한 샘플 SQL 쿼리를 제공합니다.

Logging 설정 변경사항

감사 로그가 중지되는 경우 또는 기본 Logging 설정이 변경되는 경우를 식별하려면 관리자 활동 감사 로그를 쿼리합니다.

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"

지난달에 가장 많이 수행된 작업 확인

지난 30일 동안 가장 많이 수행된 작업을 식별하려면 모든 감사 로그를 쿼리합니다.

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

위 쿼리는 지난 30일 동안의 모든 감사 로그를 검색하고 method_name, service_name 리소스 유형에 대한 정보와 수행된 작업 카운터와 함께 가장 많이 수행된 작업 100개를 반환합니다.

서비스 계정에 부여된 역할 감지

서비스 계정 가장 또는 서비스 계정에 부여된 역할을 식별하려면 관리자 활동 감사 로그를 쿼리합니다.

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

위 쿼리는 서비스 계정의 주 구성원에게 부여되는 역할을 캡처하는 감사 로그를 검색합니다. 서비스 계정 토큰 생성자 역할이 있으면 주 구성원이 서비스 계정을 가장할 수 있습니다. 또한 이 쿼리는 시간 범위를 지난 7일로 지정하고 승인된 피부여자(%@example.com)를 제외합니다.

주 구성원의 높은 API 사용량 식별

주 구성원의 API 사용량이 비정상적으로 높은지 확인하려면 모든 감사 로그를 쿼리합니다.

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

지정된 주 구성원 principal_email에 대해 쿼리가 일일 평균 API 호출 수와 이러한 API 호출의 표준 편차를 계산합니다. 평균 API 호출 수가 실행 평균과 표준 편차의 3배를 합친 값보다 크면 쿼리에서 다음 정보를 표시합니다.

  • 수행된 작업의 카운터
  • 수행된 일일 평균 작업 계산 값
  • 실행된 구체적인 작업

다음 단계