Kueri SQL untuk insight keamanan

Dokumen ini menjelaskan cara menganalisis log audit Cloud Logging menggunakan kueri SQL standar BigQuery di halaman Log Analytics. Kueri SQL memungkinkan Anda menggabungkan dan menganalisis log audit, yang memberikan informasi tentang aktivitas dan akses administratif di resource Google Cloud Anda.

Tentang log audit

Ada empat jenis log audit yang dapat ditulis oleh layanan Google Cloud:

  • Log audit Aktivitas Admin: Log audit Aktivitas Admin mencatat panggilan API atau tindakan lainnya yang mengubah konfigurasi atau metadata resource. Log ini selalu ditulis; Anda tidak dapat mengonfigurasi, mengecualikan, atau menonaktifkannya.

  • Log audit Akses Data: Log audit Akses Data merekam panggilan API yang membaca konfigurasi atau metadata resource, serta panggilan API yang dilakukan pengguna untuk membuat, mengubah, atau membaca data resource yang diberikan pengguna. Karena mengakses data adalah operasi API yang sering dilakukan, log ini dinonaktifkan secara default (kecuali untuk BigQuery).

  • Log audit Peristiwa Sistem: Log audit Peristiwa Sistem berisi entri log untuk tindakan Google Cloud yang mengubah konfigurasi resource. Log ini dihasilkan oleh sistem Google; log ini tidak dihasilkan oleh tindakan pengguna. Anda tidak dapat mengonfigurasi, mengecualikan, atau menonaktifkan log audit Peristiwa Sistem.

  • Log audit Kebijakan Ditolak: Log audit Kebijakan Ditolak dicatat saat layanan Google Cloud menolak akses ke pengguna atau akun layanan karena pelanggaran kebijakan keamanan. Log ini tidak dapat dinonaktifkan, tetapi Anda dapat menggunakan filter pengecualian untuk mencegah log ini disimpan di Logging.

Untuk informasi selengkapnya tentang log audit, lihat Ringkasan log audit. Untuk mengetahui daftar layanan yang terintegrasi dengan log audit, lihat Layanan Google Cloud dengan log audit.

Menggunakan log audit untuk mengidentifikasi pelanggaran kebijakan atau aktivitas yang mencurigakan

Anda dapat menggunakan log audit untuk mengidentifikasi pelanggaran kebijakan atau aktivitas yang mencurigakan:

Sebelum memulai

  • Pastikan Anda memiliki project, folder, atau organisasi Google Cloud yang menghasilkan log audit.

  • Pastikan Anda memiliki akses ke tampilan di bucket log tempat log audit diarahkan. Bucket log harus diupgrade untuk menggunakan Log Analytics. Untuk mengetahui informasi tentang cara membuat bucket log yang diupgrade untuk menggunakan Log Analytics, lihat Mengonfigurasi bucket log.

  • Untuk mendapatkan izin yang diperlukan guna membuat sink dan melihat log, minta administrator untuk memberi Anda peran IAM berikut:

    Untuk mengetahui informasi selengkapnya tentang cara memberikan peran, lihat Mengelola akses ke project, folder, dan organisasi.

    Anda mungkin juga bisa mendapatkan izin yang diperlukan melalui peran khusus atau peran bawaan lainnya.

    Bergantung pada log audit yang ingin Anda lihat, Anda mungkin memerlukan peran atau izin terpisah. Untuk mengetahui informasi tentang cara menetapkan peran IAM, lihat dokumentasi Kontrol akses dengan IAM Logging.

  • Untuk menggunakan kueri dalam dokumen ini di halaman Log Analytics, lakukan hal berikut:

    1. Di konsol Google Cloud, buka halaman Log Analytics:

      Buka Log Analytics

      Jika Anda menggunakan kotak penelusuran untuk menemukan halaman ini, pilih hasil yang subjudulnya adalah Logging.

    2. Identifikasi nama tabel untuk tampilan log yang ingin Anda buat kueri.

      Untuk mengidentifikasi nama ini, buka daftar Tampilan log, temukan tampilan log, lalu pilih Kueri. Panel Kueri diisi dengan kueri default, yang mencakup nama tabel tampilan log yang dikueri. Nama tabel memiliki format project_ID.region.bucket_ID.view_ID.

      Untuk mengetahui informasi selengkapnya tentang cara mengakses kueri default, lihat Mengkueri tampilan log.

    3. Ganti TABLE_NAME_OF_LOG_VIEW dengan nama tabel untuk tampilan log yang ingin Anda buat kueri, lalu salin kueri.

    4. Tempelkan kueri di panel Query, lalu klik Run query.

Sampel kueri

Bagian ini memberikan contoh kueri SQL untuk membuat kueri log audit.

Perubahan pada setelan Logging

Untuk mengidentifikasi kapan log audit dinonaktifkan atau kapan perubahan dilakukan pada setelan logging default, buat kueri log audit Aktivitas Admin:

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"

Menentukan tindakan paling umum yang dilakukan dalam sebulan terakhir

Untuk mengidentifikasi tindakan yang paling sering dilakukan dalam 30 hari terakhir, buat kueri semua log audit:

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

Kueri sebelumnya menelusuri semua log audit dalam 30 hari terakhir, dan menampilkan 100 tindakan yang paling sering dilakukan dengan informasi tentang method_name, service_name, jenis resource, dan penghitung tindakan yang dilakukan.

Mendeteksi peran yang diberikan pada akun layanan

Untuk mengidentifikasi peniruan identitas akun layanan, atau peran yang diberikan di akun layanan, buat kueri log audit Aktivitas Admin:

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

Kueri sebelumnya menelusuri log audit yang merekam peran yang diberikan kepada akun utama di akun layanan. Peran pembuat Token Akun Layanan memungkinkan akun utama meniru identitas akun layanan. Kueri ini juga menentukan rentang waktu tujuh hari terakhir dan mengecualikan penerima hibah yang disetujui (%@example.com).

Mengidentifikasi penggunaan API yang tinggi oleh akun utama

Untuk mengidentifikasi penggunaan API yang sangat tinggi oleh akun utama, buat kueri semua log audit:

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

Untuk akun utama yang ditentukan, principal_email, kueri menghitung jumlah rata-rata panggilan API per hari, dan deviasi standar panggilan API tersebut. Jika jumlah rata-rata panggilan API lebih besar dari rata-rata yang berjalan ditambah tiga kali deviasi standar, kueri akan menampilkan informasi berikut:

  • Penghitung tindakan yang dilakukan.
  • Tindakan rata-rata yang dihitung yang dilakukan per hari.
  • Tindakan spesifik yang dilakukan.

Langkah selanjutnya