0Pricing
SQL Academy · Pelajaran

Mengaudit Akses

Lacak siapa yang dapat melihat apa.

Mengaudit Akses adalah pelajaran SQL Academy gratis di CoddyKit. Ini adalah pelajaran 4 dari 4. Kamu bisa membaca pelajaran lengkapnya di bawah secara gratis — lalu praktikkan langsung di browser dengan editor kode bawaan dan tutor AI 24/7. Ini adalah bagian dari jalur belajar SQL Academy, dan progresmu tersinkronisasi di web dan aplikasi CoddyKit. Kursus SQL Academy mencakup 4 pelajaran total.

Mengapa Mengaudit Akses

Mengetahui siapa yang mengakses data apa dan kapan merupakan landasan keamanan basis data. Pengauditan menciptakan jejak peristiwa yang andal sehingga Anda dapat mendeteksi access yang tidak sah, menyelidiki insiden, dan memenuhi persyaratan kepatuhan seperti GDPR, HIPAA, atau SOC 2.

Dalam pelajaran ini, Anda akan mempelajari cara merancang tabel audit, merekam peristiwa access secara otomatis dengan pemicu, menggunakan fitur pencatatan bawaan PostgreSQL, dan memeriksa jejak audit untuk menjawab pertanyaan: siapa yang dapat melihat apa?

Merancang Tabel Catatan Audit

Langkah pertama adalah tabel khusus yang mencatat setiap peristiwa penting. Catatan audit yang baik menyimpan nama tabel, jenis operasi, nilai lama dan baru, pengguna yang melakukan tindakan, serta stempel waktu yang tepat.

Contoh di bawah membuat tabel audit_log untuk keperluan umum menggunakan kolom JSONB guna menyimpan rekaman keadaan baris—cukup fleksibel untuk menangani tabel apa pun tanpa perubahan skema.

CREATE TABLE audit_log (
  id          BIGSERIAL PRIMARY KEY,
  event_time  TIMESTAMPTZ NOT NULL DEFAULT now(),
  db_user     TEXT NOT NULL DEFAULT current_user,
  app_user    TEXT,
  table_name  TEXT NOT NULL,
  operation   TEXT NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE','SELECT')),
  row_id      BIGINT,
  old_data    JSONB,
  new_data    JSONB
);

Mencatat Pengguna Saat Ini

PostgreSQL menyediakan beberapa fungsi bawaan untuk mengidentifikasi siapa yang menjalankan kueri. current_user mengembalikan nama peran yang berlaku setelah SET ROLE. session_user selalu mengembalikan peran login asli, terlepas dari penggantian peran.

Untuk aplikasi yang menggunakan satu peran basis data bersama, tetapi meneruskan pengguna tingkat aplikasi melalui SET LOCAL app.current_user, Anda dapat membaca pengaturan tersebut dengan current_setting().

-- Who is the database user right now?
SELECT current_user,
       session_user;

-- Read an application-level user injected by the app layer
SELECT current_setting('app.current_user', true) AS app_user;

Menulis Fungsi Pemicu Audit

Fungsi pemicu merupakan cara paling andal untuk merekam peristiwa perubahan data karena fungsi ini aktif secara otomatis—tidak ada kode aplikasi yang dapat melewatinya. Fungsi di bawah mencatat setiap INSERT, UPDATE, dan DELETE pada tabel apa pun yang dipasangi fungsi tersebut, serta menyimpan nilai baris lama dan baru sebagai JSONB.

Perhatikan penggunaan TG_TABLE_NAME (tabel yang mengaktifkan pemicu) dan row_to_json() untuk mengubah nilai baris menjadi format yang dapat disimpan.

CREATE OR REPLACE FUNCTION fn_audit_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  INSERT INTO audit_log (
    db_user,
    app_user,
    table_name,
    operation,
    row_id,
    old_data,
    new_data
  ) VALUES (
    current_user,
    current_setting('app.current_user', true),
    TG_TABLE_NAME,
    TG_OP,
    COALESCE(NEW.id, OLD.id),
    CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE row_to_json(OLD)::JSONB END,
    CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE row_to_json(NEW)::JSONB END
  );
  RETURN NULL;
END;
$$;

Memasang Pemicu ke Tabel

Setelah fungsi pemicu tersedia, pasangkan fungsi tersebut ke setiap tabel yang ingin Anda audit dengan pernyataan CREATE TRIGGER. Penggunaan AFTER memastikan data benar-benar telah ditulis sebelum entri log dibuat. Klausa FOR EACH ROW mengaktifkan pemicu satu kali untuk setiap baris yang diubah.

Di sini, pemicu diterapkan pada tabel hipotetis patients, sehingga setiap INSERT, UPDATE, dan DELETE dicatat secara otomatis.

CREATE TRIGGER trg_audit_patients
AFTER INSERT OR UPDATE OR DELETE
ON patients
FOR EACH ROW
EXECUTE FUNCTION fn_audit_changes();

Mengaudit Kueri SELECT

Pemicu perubahan data hanya menangkap operasi penulisan. Untuk mengaudit access baca, Anda memerlukan pendekatan yang berbeda. Salah satu opsi adalah pemicu tingkat pernyataan AFTER SELECT (didukung dalam PostgreSQL 14+ untuk konteks tertentu). Pola yang lebih umum adalah mencatat pembacaan secara eksplisit di dalam fungsi atau tampilan yang membungkus tabel sensitif.

Contoh di bawah membungkus tabel sensitif dalam suatu fungsi yang mencatat setiap pembacaan sebelum mengembalikan hasil.

CREATE OR REPLACE FUNCTION get_patient_record(p_id INT)
RETURNS SETOF patients
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  -- Log the read access
  INSERT INTO audit_log (db_user, app_user, table_name, operation, row_id)
  VALUES (
    current_user,
    current_setting('app.current_user', true),
    'patients',
    'SELECT',
    p_id
  );

  RETURN QUERY
  SELECT * FROM patients WHERE id = p_id;
END;
$$;

Pencatatan Bawaan PostgreSQL

postgresql.conf milik PostgreSQL menyediakan pencatatan sisi server yang kuat dan tidak memerlukan kode aplikasi. Pengaturan log_min_duration_statement mencatat kueri apa pun yang melampaui ambang batas. Pengaturan log_connections dan log_disconnections mencatat siapa yang masuk dan keluar.

Kueri di bawah menggunakan tampilan sistem pg_stat_activity untuk melihat sesi yang sedang aktif—suatu bentuk pemantauan access secara langsung yang ringan.

-- See who is currently connected and what they are running
SELECT pid,
       usename        AS db_user,
       application_name,
       client_addr,
       state,
       query_start,
       LEFT(query, 80) AS current_query
FROM   pg_stat_activity
WHERE  datname = current_database()
ORDER  BY query_start DESC;

Menjalankan Kueri pada Log Audit

Log audit hanya bernilai jika Anda dapat menjalankan kueri terhadapnya secara efektif. Pertanyaan umum meliputi: pengguna mana yang terakhir mengakses suatu rekaman, apa yang berubah pada suatu baris dari waktu ke waktu, dan berapa banyak pembacaan data sensitif yang terjadi dalam 24 jam terakhir.

Kueri di bawah ini menemukan semua pengguna yang mengakses rekaman pasien tertentu, diurutkan mulai dari yang paling baru.

SELECT event_time,
       db_user,
       app_user,
       operation,
       old_data,
       new_data
FROM   audit_log
WHERE  table_name = 'patients'
  AND  row_id = 42
ORDER  BY event_time DESC
LIMIT  20;

Mendeteksi Pola Akses Mencurigakan

Setelah data audit dikumpulkan, Anda dapat menulis kueri yang menandai anomali. Misalnya, pengguna yang tiba-tiba membaca jauh lebih banyak baris daripada biasanya, atau rekaman sensitif yang sama diakses beberapa kali dalam jangka waktu singkat, dapat mengindikasikan upaya pengambilan data secara tidak sah.

Kueri di bawah ini menghitung peristiwa SELECT per pengguna aplikasi dalam satu jam terakhir dan menyoroti siapa pun yang telah membaca lebih dari 100 baris.

SELECT app_user,
       COUNT(*) AS records_accessed
FROM   audit_log
WHERE  operation = 'SELECT'
  AND  event_time >= now() - INTERVAL '1 hour'
GROUP  BY app_user
HAVING COUNT(*) > 100
ORDER  BY records_accessed DESC;

Melindungi Log Audit Itu Sendiri

Log audit yang dapat diubah tidak dapat dipercaya. Anda harus menguncinya agar pengguna biasa dan peran aplikasi tidak dapat menghapus atau memperbarui baris. Pendekatan paling aman adalah memberikan hanya INSERT kepada peran aplikasi dan membatasi SELECT untuk peran auditor khusus.

Anda juga dapat menegakkan sifat tidak dapat diubah dengan pemicu yang memunculkan pengecualian jika ada yang mencoba memperbarui atau menghapus baris audit.

-- Only the app role may insert; nobody may update or delete
REVOKE ALL     ON audit_log FROM PUBLIC;
GRANT  INSERT  ON audit_log TO app_role;
GRANT  SELECT  ON audit_log TO auditor_role;

-- Trigger to block any tampering
CREATE OR REPLACE FUNCTION fn_protect_audit()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'audit_log rows are immutable';
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_protect_audit
BEFORE UPDATE OR DELETE ON audit_log
FOR EACH ROW EXECUTE FUNCTION fn_protect_audit();

Mengaudit Keputusan Kebijakan RLS

Saat Keamanan Tingkat Baris aktif, PostgreSQL menyembunyikan baris secara diam-diam alih-alih menimbulkan kesalahan. Hal ini menyulitkan untuk mengetahui apakah pengguna mencoba membaca baris yang tidak diizinkan untuk dilihatnya. Salah satu tekniknya adalah menambahkan kebijakan permisif yang selalu menyisipkan rekaman audit sebelum kebijakan restriktif menyaring baris.

Kueri di bawah ini menunjukkan cara memeriksa kebijakan RLS mana yang ada pada suatu tabel dan peran mana yang diterapkannya.

-- View all RLS policies on the patients table
SELECT polname       AS policy_name,
       polcmd        AS command,
       polroles::TEXT AS applies_to,
       polqual::TEXT  AS using_expression,
       polwithcheck::TEXT AS with_check_expression
FROM   pg_policy
WHERE  polrelid = 'patients'::REGCLASS
ORDER  BY polname;

Uji Pemahaman

Uji pemahaman Anda tentang pengauditan akses dalam SQL.

Ringkasan Pelajaran

Dalam pelajaran ini Anda mempelajari cara membangun sistem pengauditan akses yang lengkap di PostgreSQL:

  • Tabel log audit — tabel berbasis JSONB yang mencatat siapa melakukan apa dan kapan.
  • Fungsi pemicu — secara otomatis mencatat peristiwa INSERT, UPDATE, dan DELETE untuk setiap tabel yang terhubung menggunakan TG_TABLE_NAME, row_to_json(), dan current_user.
  • Pengauditan pembacaan — membungkus tabel sensitif dalam fungsi yang mencatat peristiwa SELECT sebelum mengembalikan data.
  • Pemantauan bawaan — pg_stat_activity menampilkan sesi aktif; pencatatan sisi server menangkap kueri tanpa perubahan kode.
  • Deteksi anomali — kueri agregat pada log audit dapat menandai volume akses yang tidak biasa.
  • Ketidakberubahan — mencabut UPDATE/DELETE dari tabel audit dan menambahkan pemicu pemblokir untuk mencegah pengubahan tanpa izin.

Jejak audit yang dirancang dengan baik adalah alat Anda yang paling andal untuk menjawab siapa mengakses apa dan menjadi tulang punggung setiap penyelidikan kepatuhan atau keamanan.

Pertanyaan yang Sering Diajukan

Apakah pelajaran “Mengaudit Akses” gratis?

Ya — teks lengkap “Mengaudit Akses” gratis dibaca di sini di web. Untuk praktiknya secara interaktif (editor kode bawaan dan tutor AI 24/7) dan buka sisa kursus SQL Academy, upgrade ke CoddyKit PRO. Kursus SQL Academy mencakup 4 pelajaran total.

Apa yang akan aku pelajari di “Mengaudit Akses”?

Lacak siapa yang dapat melihat apa. Kamu berlatih SQL Academy dengan kode praktik yang langsung kamu jalankan di browser, dan tutor AI 24/7 menjawab pertanyaanmu saat kamu mengerjakan pelajaran ini.

Apakah aku perlu pengalaman untuk memulai SQL Academy?

Tidak diperlukan pengalaman sebelumnya. SQL Academy di CoddyKit dirancang untuk pemula hingga pelajar tingkat lanjut, jadi kamu bisa memulai di sini atau dari awal dan belajar sesuai kecepatan kamu sendiri. Ini adalah pelajaran 4 dari 4.

Berapa lama pelajaran “Mengaudit Akses” memakan waktu?

Sebagian besar pelajaran CoddyKit memakan waktu sekitar 5–10 menit. Setiap pelajaran ringkas dan interaktif, jadi kamu membuat kemajuan stabil dan melanjutkan dari tempat kamu tinggalkan di web dan aplikasi.

Bisakah aku menulis dan menjalankan kode dalam pelajaran SQL Academy ini?

Ya. Setiap pelajaran SQL Academy menyertakan editor kode bawaan, jadi kamu menulis dan menjalankan kode nyata langsung di browser dan mendapatkan umpan balik AI instan — tidak diperlukan penyiapan lokal.

Semua pelajaran dalam kursus ini

  1. Peran dan Hak Istimewa
  2. Kebijakan Keamanan Tingkat Baris
  3. Izin Tingkat Kolom
  4. Mengaudit Akses
← Kembali ke SQL Academy