답변

감사 메커니즘(로그 또는 변경 이력)은 데이터의 모든 변경 사항을 추적하고 투명성을 보장하기 위해 구현됩니다. 주요 접근 방식은 여러 가지가 있습니다:

감사 트리거 (Audit Triggers): 데이터를 삽입, 업데이트 또는 삭제한 후에(old/new 상태를) 감사 테이블에 저장하는 특별한 AFTER/BEFORE 트리거입니다. 장점 — 응용 프로그램에 대한 투명성; 단점 — DML 작업의 성능 저하 가능성.
별도의 테이블에 이력 격리 (History Table): 변경 시 "이전" 행을 타임스탬프와 함께 이력 테이블에 복사하는 기록 버전 관리를 사용할 수 있습니다. 기본 테이블은 현재 데이터만 포함합니다.
내장 메커니즘 (Change Data Capture, Temporal Tables): 예를 들어 SQL Server에서 SYSTEM_VERSIONED TEMPORAL TABLE을 활성화하면 데이터베이스가 자동으로 변경 이력을 저장합니다.

예시 (MySQL, 감사 트리거):

CREATE TABLE user_audit (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  action VARCHAR(10),
  old_value TEXT,
  new_value TEXT,
  changed_at DATETIME
);

DELIMITER //
CREATE TRIGGER audit_user_update
  AFTER UPDATE ON users
  FOR EACH ROW BEGIN
    INSERT INTO user_audit (user_id, action, old_value, new_value, changed_at)
    VALUES (OLD.id, 'UPDATE', OLD.name, NEW.name, NOW());
END//
DELIMITER ;

함정 질문

질문: 모든 변경 이력의 전체 프로토콜이 필요하다면 업데이트에 대해서만 감사 트리거를 갖는 것으로 충분합니까?

답변: 아닙니다. 전체 이력은 모든 작업에 대한 트리거를 결합해야만 얻을 수 있습니다: INSERT(새로운 행을 기록), UPDATE(변경을 기록) 및 DELETE(삭제된 기록을 기록).

예시:

-- 각 작업에 대해 3개의 개별 트리거를 작성해야 합니다. 그렇지 않으면 이력에 "구멍"이 생깁니다.

주제의 세부 사항을 모르고 인한 실제 오류 예시

이야기 1: 한 회사는 감사 트리거로 업데이트만 사용했으며 1년 동안 삭제된 기록이 기록되지 않는 것을 인지하지 못했습니다. 결국 사건 분석 중 중요한 사용자 계정이 언제 삭제되었는지를 파악할 수 없었습니다.

이야기 2: 프로젝트에서 변경의 이전 값과 새로운 값을 직렬화된 JSON 형태로 하나의 텍스트 필드에 저장하여 분석이 복잡해졌습니다. 복잡한 파서와 보고를 위한 별도의 데이터베이스 복제가 필요했습니다.

이야기 3: 높은 부하의 데이터베이스에서 개발자들이 감사 트리거를 구현했으나 로그 테이블의 크기 증가를 고려하지 않았습니다. 시간이 지나면서 이력 테이블의 크기가 기본 테이블보다 50배 커져서 저장소가 가득 차고 전체 프로젝트에 문제가 발생했습니다.

SQL 테이블에서 감사 또는 변경 추적 메커니즘(변경 이력/로깅)을 어떻게 구현할 수 있나요? 어떤 접근 방식이 있으며, 이점과 단점은 무엇이며, 이러한 기능을 설계할 때 어떤 점을 고려해야 하나요?

답변

예시 (MySQL, 감사 트리거):

함정 질문

예시:

주제의 세부 사항을 모르고 인한 실제 오류 예시