0Pricing
SQL Academy · บทเรียน

การตรวจสอบการเข้าถึง

ติดตามว่าใครดูข้อมูลใดได้บ้าง

การตรวจสอบการเข้าถึง เป็นบทเรียน SQL Academy ฟรีบน CoddyKit นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน SQL Academy และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส SQL Academy มีบทเรียนทั้งหมด 4 บทเรียน

เหตุใดจึงต้องตรวจสอบ access

การทราบว่า ผู้ใดเข้าถึงข้อมูลใดและเมื่อใดถือเป็นรากฐานสำคัญของการรักษาความปลอดภัยฐานข้อมูล การตรวจสอบจะสร้างร่องรอยเหตุการณ์ที่น่าเชื่อถือ เพื่อให้คุณตรวจพบการเข้าถึงโดยไม่ได้รับอนุญาต สืบสวนเหตุการณ์ และปฏิบัติตามข้อกำหนดด้านการปฏิบัติตามมาตรฐาน เช่น GDPR, HIPAA หรือ SOC 2

ในบทเรียนนี้ คุณจะได้เรียนรู้วิธีออกแบบตารางตรวจสอบ บันทึกเหตุการณ์ access โดยอัตโนมัติด้วยทริกเกอร์ ใช้ความสามารถด้านการบันทึกข้อมูลในตัวของ PostgreSQL และสอบถามประวัติการตรวจสอบเพื่อตอบคำถามว่า ผู้ใดมองเห็นข้อมูลใดได้บ้าง

การออกแบบตารางบันทึกการตรวจสอบ

ขั้นตอนแรกคือการสร้างตารางเฉพาะสำหรับบันทึกเหตุการณ์สำคัญทุกเหตุการณ์ บันทึกการตรวจสอบที่ดีควรเก็บชื่อตาราง ประเภทการดำเนินการ ค่าเดิมและค่าใหม่ ผู้ใช้ที่ดำเนินการ และการประทับเวลาที่แน่นอน

ตัวอย่างด้านล่างสร้างตาราง audit_log สำหรับใช้งานทั่วไป โดยใช้คอลัมน์ JSONB เพื่อเก็บภาพบันทึกของแถว ซึ่งยืดหยุ่นเพียงพอสำหรับทุกตารางโดยไม่ต้องเปลี่ยนแปลงโครงสร้าง

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
);

การบันทึกผู้ใช้ปัจจุบัน

PostgreSQL มีฟังก์ชันในตัวหลายรายการสำหรับระบุว่าใครกำลังเรียกใช้คำสั่งสอบถาม current_user ส่งคืนชื่อบทบาทที่มีผลหลังจากใช้ SET ROLE ส่วน session_user จะส่งคืนบทบาทที่ใช้เข้าสู่ระบบครั้งแรกเสมอ โดยไม่ขึ้นกับการสลับบทบาท

สำหรับแอปพลิเคชันที่ใช้บทบาทฐานข้อมูลร่วมเพียงบทบาทเดียว แต่ส่งผู้ใช้ระดับแอปพลิเคชันผ่าน SET LOCAL app.current_user คุณสามารถอ่านค่าดังกล่าวด้วย 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;

การเขียนฟังก์ชันทริกเกอร์สำหรับตรวจสอบ

ฟังก์ชันทริกเกอร์เป็นวิธีที่น่าเชื่อถือที่สุดในการบันทึกเหตุการณ์การเปลี่ยนแปลงข้อมูล เนื่องจากฟังก์ชันจะทำงานโดยอัตโนมัติ ทำให้โค้ดแอปพลิเคชันไม่สามารถหลีกเลี่ยงได้ ฟังก์ชันด้านล่างจะบันทึก INSERT, UPDATE และ DELETE ทุกครั้งในตารางใดก็ตามที่ผูกฟังก์ชันไว้ พร้อมเก็บค่าแถวเดิมและแถวใหม่เป็น JSONB

โปรดสังเกตการใช้ TG_TABLE_NAME (ตารางที่ทำให้ทริกเกอร์ทำงาน) และ row_to_json() เพื่อแปลงค่าแถวให้อยู่ในรูปแบบที่จัดเก็บได้

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;
$$;

การผูกทริกเกอร์กับตาราง

เมื่อมีฟังก์ชันทริกเกอร์แล้ว คุณสามารถผูกฟังก์ชันนั้นกับแต่ละตารางที่ต้องการตรวจสอบได้ด้วยคำสั่ง CREATE TRIGGER การใช้ AFTER ช่วยให้มั่นใจว่าข้อมูลถูกเขียนเรียบร้อยแล้วก่อนสร้างรายการบันทึก ส่วนอนุประโยค FOR EACH ROW จะทำให้ทริกเกอร์ทำงานหนึ่งครั้งต่อแถวที่ถูกแก้ไข

ในที่นี้ ทริกเกอร์ถูกนำไปใช้กับตารางสมมติ patients เพื่อบันทึก INSERT, UPDATE และ DELETE ทุกครั้งโดยอัตโนมัติ

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

การตรวจสอบคำสั่ง SELECT

ทริกเกอร์สำหรับการเปลี่ยนแปลงข้อมูลจะบันทึกเฉพาะการเขียนข้อมูลเท่านั้น หากต้องการตรวจสอบการเข้าถึงข้อมูลเพื่ออ่าน คุณต้องใช้แนวทางอื่น ทางเลือกหนึ่งคือทริกเกอร์ระดับคำสั่ง AFTER SELECT (รองรับใน PostgreSQL 14+ สำหรับบางบริบท) รูปแบบที่ใช้กันทั่วไปกว่าคือบันทึกการอ่านอย่างชัดเจนภายในฟังก์ชันหรือมุมมองที่ครอบตารางอ่อนไหวไว้

ตัวอย่างด้านล่างครอบตารางอ่อนไหวไว้ในฟังก์ชัน ซึ่งจะบันทึกการอ่านทุกครั้งก่อนส่งคืนผลลัพธ์

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;
$$;

การบันทึกข้อมูลในตัวของ PostgreSQL

postgresql.conf ของ PostgreSQL มีความสามารถด้านการบันทึกข้อมูลฝั่งเซิร์ฟเวอร์ที่ทรงพลังและไม่ต้องใช้โค้ดแอปพลิเคชัน การตั้งค่า log_min_duration_statement จะบันทึกคำสั่งสอบถามที่ใช้เวลานานเกินขีดจำกัด การตั้งค่า log_connections และ log_disconnections จะบันทึกว่าใครเข้าสู่ระบบและออกจากระบบ

คำสั่งสอบถามด้านล่างใช้มุมมองระบบ pg_stat_activity เพื่อดูเซสชันที่กำลังทำงานอยู่ ซึ่งเป็นรูปแบบหนึ่งของการตรวจสอบ access แบบเรียลไทม์ที่ใช้ทรัพยากรน้อย

-- 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;

การสืบค้นบันทึกการตรวจสอบ

บันทึกการตรวจสอบจะมีคุณค่าก็ต่อเมื่อคุณสามารถสืบค้นได้อย่างมีประสิทธิภาพ คำถามที่พบบ่อยได้แก่ ผู้ใช้ใดเข้าถึงระเบียนล่าสุด แถวหนึ่งมีการเปลี่ยนแปลงอย่างไรเมื่อเวลาผ่านไป และในช่วง 24 ชั่วโมงที่ผ่านมา มีการอ่านข้อมูลอ่อนไหวกี่ครั้ง

คำสั่งสืบค้นด้านล่างค้นหาผู้ใช้ทั้งหมดที่เข้าถึงระเบียนผู้ป่วยที่ระบุ โดยเรียงจากรายการล่าสุดก่อน

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;

การตรวจจับรูปแบบการเข้าถึงที่น่าสงสัย

เมื่อรวบรวมข้อมูลการตรวจสอบแล้ว คุณสามารถเขียนคำสั่งสืบค้นเพื่อแจ้งความผิดปกติได้ เช่น ผู้ใช้ที่จู่ ๆ อ่านแถวจำนวนมากกว่าปกติ หรือระเบียนข้อมูลอ่อนไหวเดียวกันถูกเข้าถึงหลายครั้งในช่วงเวลาสั้น ๆ อาจบ่งชี้ถึงความพยายามลักลอบนำข้อมูลออก

คำสั่งสืบค้นด้านล่างนับเหตุการณ์ SELECT ของผู้ใช้แอปพลิเคชันแต่ละรายในช่วงหนึ่งชั่วโมงที่ผ่านมา และเน้นผู้ที่อ่านมากกว่า 100 แถว

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;

การปกป้องบันทึกการตรวจสอบ

บันทึกการตรวจสอบที่สามารถแก้ไขได้ย่อมไม่น่าเชื่อถือ คุณควรจำกัดสิทธิ์อย่างเข้มงวดเพื่อไม่ให้ผู้ใช้ทั่วไปและบทบาทของแอปพลิเคชันสามารถ DELETE หรือ UPDATE แถวได้ แนวทางที่ปลอดภัยที่สุดคือมอบสิทธิ์เฉพาะ INSERT ให้บทบาทของแอปพลิเคชัน และสงวน SELECT ไว้สำหรับบทบาทผู้ตรวจสอบโดยเฉพาะ

คุณยังสามารถบังคับให้บันทึกไม่เปลี่ยนแปลงได้ด้วยทริกเกอร์ที่ทำให้เกิดข้อยกเว้น หากมีใครพยายาม UPDATE หรือ DELETE แถวการตรวจสอบ

-- 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();

การตรวจสอบการตัดสินใจของนโยบาย RLS

เมื่อเปิดใช้งานการรักษาความปลอดภัยระดับแถว (RLS) PostgreSQL จะซ่อนแถวโดยไม่แสดงข้อผิดพลาด แทนที่จะทำให้เกิดข้อผิดพลาด วิธีนี้ทำให้ทราบได้ยากว่าผู้ใช้พยายามอ่านแถวที่ตนไม่มีสิทธิ์ดูหรือไม่ เทคนิคหนึ่งคือเพิ่มนโยบายแบบผ่อนปรนที่แทรกระเบียนการตรวจสอบเสมอ ก่อนที่นโยบายแบบจำกัดจะกรองแถวออก

คำสั่งสืบค้นด้านล่างแสดงวิธีตรวจสอบว่ามีนโยบาย RLS ใดอยู่ในตาราง และนโยบายเหล่านั้นใช้กับบทบาทใด

-- 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;

ตรวจสอบความเข้าใจ

ทดสอบความเข้าใจเกี่ยวกับการตรวจสอบการเข้าถึงในภาษาเอสคิวแอล

สรุปบทเรียน

ในบทเรียนนี้ คุณได้สำรวจวิธีสร้างระบบตรวจสอบการเข้าถึงที่สมบูรณ์ใน PostgreSQL:

  • ตารางบันทึกการตรวจสอบ — ตารางที่ใช้ JSONB ซึ่งบันทึกว่าใครทำอะไรและเมื่อใด
  • ฟังก์ชันทริกเกอร์ — บันทึกเหตุการณ์ INSERT, UPDATE และ DELETE โดยอัตโนมัติสำหรับทุกตารางที่เชื่อมต่ออยู่ โดยใช้ TG_TABLE_NAME, row_to_json() และ current_user
  • การตรวจสอบการอ่านข้อมูล — ห่อหุ้มตารางที่มีข้อมูลอ่อนไหวไว้ในฟังก์ชันที่บันทึกเหตุการณ์ SELECT ก่อนส่งคืนข้อมูล
  • การเฝ้าติดตามที่มีมาให้ — pg_stat_activity แสดงเซสชันที่กำลังทำงานอยู่ ส่วนการบันทึกฝั่งเซิร์ฟเวอร์จะเก็บคำสั่งสืบค้นโดยไม่ต้องเปลี่ยนแปลงโค้ด
  • การตรวจจับความผิดปกติ — คำสั่งสืบค้นแบบรวมในบันทึกการตรวจสอบสามารถแจ้งเตือนเมื่อปริมาณการเข้าถึงผิดปกติ
  • การไม่เปลี่ยนแปลง — เพิกถอน UPDATE/DELETE จากตารางบันทึกการตรวจสอบ และเพิ่มทริกเกอร์ที่บล็อกการแก้ไขโดยมิชอบ

เส้นทางการตรวจสอบที่ออกแบบมาอย่างดีเป็นเครื่องมือที่น่าเชื่อถือที่สุดสำหรับตอบคำถามว่า ใครเข้าถึงอะไร และเป็นรากฐานของการตรวจสอบด้านการปฏิบัติตามข้อกำหนดหรือความปลอดภัยทุกประเภท

คำถามที่พบบ่อย

บทเรียน “การตรวจสอบการเข้าถึง” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “การตรวจสอบการเข้าถึง” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส SQL Academy ให้อัปเกรดเป็น CoddyKit PRO คอร์ส SQL Academy มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “การตรวจสอบการเข้าถึง”

ติดตามว่าใครดูข้อมูลใดได้บ้าง คุณปฏิบัติ SQL Academy ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน SQL Academy หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน SQL Academy บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน

บทเรียน “การตรวจสอบการเข้าถึง” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน SQL Academy นี้ได้ไหม

ได้ บทเรียน SQL Academy ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

  1. บทบาทและสิทธิ์
  2. นโยบายความปลอดภัยระดับแถว
  3. สิทธิ์ระดับคอลัมน์
  4. การตรวจสอบการเข้าถึง
← กลับไปที่ SQL Academy