Computer >> Hướng Dẫn Máy Tính >  >> Lập Trình >> Cơ Sở Dữ Liệu

Cách Kiểm Toán Hoạt Động Truncate và Delete Trong MSSQL Server

Giới thiệu

Bài viết này thảo luận về các bước để xác định người dùng chịu trách nhiệm cắt bỏ (truncate) và xóa dữ liệu từ các bảng trên MSSQL Server, cũng như xác định thực thể thực hiện hành động.

Ví dụ: Bảng nào bị truncate khi nào, dữ liệu bị xóa khỏi bảng nào khi nào? Ai đã truncate bảng và xóa dữ liệu?

Lý do thu thập thông tin này

Mục đích là để xem ai đó đã xóa dữ liệu cố ý hay vô tình, từ đó truy tìm cá nhân và thực hiện biện pháp phòng ngừa. Chúng tôi nhận được một số yêu cầu từ khách hàng tìm kiếm thông tin này. Khi biết chính xác thời gian hoạt động xóa dữ liệu (purge), chúng ta có thể dễ dàng khôi phục dữ liệu với mệnh đề STOP AT trong quá trình sao lưu log.

Tóm tắt vấn đề

Giữa 17h và 19h ngày 5 tháng 1 năm 2020, bảng "dump_truncate" bị truncate và dữ liệu từ bảng "dump_delete" bị xóa khỏi cơ sở dữ liệu "truncate_test". Các câu hỏi cần giải quyết:

  • Xác định vấn đề là gì.
  • Ai đã xóa dữ liệu từ bảng "dump_delete"?
  • Số hàng nào đã bị xóa từ bảng "dump_delete"?
  • Ai đã truncate bảng "dump_truncate"?
  • Khi nào các bảng này bị truncate và xóa?
  • Lịch trình sao lưu hiện tại trên máy chủ:
    • Sao lưu đầy đủ (Full backup) hàng tuần vào Chủ nhật.
    • Sao lưu khác biệt (Diff backup) hàng ngày lúc 13h.
    • Sao lưu log (Log backup) tạo ra mỗi 15 phút.

Điều kiện tiên quyết

  • Cơ sở dữ liệu phải ở chế độ khôi phục đầy đủ (Full recovery mode).
  • Sao lưu đầy đủ, khác biệt và log phải có sẵn.

Phương pháp tiếp cận ở mức cao

  1. Xác định sao lưu log nào chứa các hoạt động delete và truncate.
  2. Xác định chi tiết các hoạt động truncate và delete bằng cách sử dụng sao lưu log.

Xác định sao lưu log chứa hoạt động delete và truncate

LƯU Ý: Các bước này được thực hiện bằng cách tạo bản sao mới của cơ sở dữ liệu.

  1. Khôi phục sao lưu đầy đủ ngày 2 tháng 1 (Chủ nhật) với chế độ standby.
  2. Khôi phục sao lưu khác biệt ngày 5 tháng 1 với chế độ standby.
  3. Khôi phục sao lưu log với chế độ standby. Kiểm tra số lượng hàng của bảng sau mỗi lần khôi phục log để xem sao lưu log nào chứa log của hoạt động truncate và delete.

Khi khôi phục sao lưu log từ 18h, bảng "dump_truncate" trống và các mục từ bảng "dump_delete" bị thiếu. Điều này cho thấy:

Giữa 17h45 và 18h, bảng "dump_truncate" đã bị truncate và dữ liệu từ bảng "dump_delete" đã bị xóa cùng lúc.

Xác định chi tiết hoạt động truncate và delete bằng sao lưu log

Bước 1: Thu thập ID giao dịch (Transaction ID) cho tất cả hoạt động truncate và delete xảy ra giữa 17h45 và 18h

Truy vấn:

-- Truy vấn fn_dblog để tìm transaction ID

Chúng tôi phát hiện hai hoạt động, delete và truncate, được thực hiện lúc 17h50, và các hoạt động này được thực hiện bởi tài khoản đăng nhập RP Dev.

Bước 2: Tìm tên bảng liên quan đến transaction ID

Để lấy thông tin hoạt động delete:

I) Xác định Object ID và Partition ID của hoạt động delete.

Từ kết quả, ta suy ra:

  • Description và Transaction Name: Hoạt động delete được thực hiện.
  • Begin Time: Hoạt động delete bắt đầu lúc 2022/01/05 17:50:22:493
  • Login_Name: RP_DEV đã chạy hoạt động delete.
  • Lock Information: Mỗi hàng bắt đầu bằng tiền tố "HoBt" đại diện cho một lần xóa hàng, tổng cộng 7 hàng.
  • Object ID: ID đối tượng liên quan đến bảng bị xóa dữ liệu.
  • Partition ID: Partition ID của đối tượng nơi dữ liệu bị xóa.

II) Tìm bảng chứa Object ID và Partition ID.

Truy vấn:

SELECT OBJECT_NAME(object_id) AS TableName FROM sys.partitions WHERE partition_id = [Partition_ID]

Kết luận: Dữ liệu từ "dump_delete" đã bị xóa bởi người dùng RP_DEV lúc 17h50 dưới transaction ID '0000:00016a96' và tổng cộng 7 hàng đã bị xóa.

Để lấy thông tin hoạt động truncate:

I) Xác định Object ID và Partition ID của hoạt động truncate.

Truy vấn:

-- Truy vấn fn_dblog cho hoạt động truncate

Kết quả đầu ra của hoạt động truncate khác với delete:

  • Partition ID column: Không hiển thị đúng Partition ID. Thông tin này nằm trong cột Description. Partition ID được highlight: 72057594043564032 và 72057594043629568
  • Lock Description: Luôn có dòng SCH_M_OBJECT row trong Lock Description hiển thị đúng Object ID. Object ID: 885578193

II) Tìm bảng chứa Object ID và Partition ID.

Truy vấn:

SELECT OBJECT_NAME(object_id) AS TableName FROM sys.partitions WHERE partition_id IN (72057594043564032, 72057594043629568)

Kết luận: Dữ liệu từ "dump_truncate" đã bị truncate lúc 17h50 bởi người dùng RP_DEV dưới transaction ID '0000:00016a95'.

Kết luận

Việc biết ai thực hiện hoạt động truncate và delete trên dữ liệu rất hữu ích để ngăn chặn việc này xảy ra lại khi kiểm toán chưa được bật. Trong trường hợp delete, doanh nghiệp sẽ biết số hàng bị xóa. Việc có thời gian khôi phục chính xác sẽ rất hữu ích để khôi phục dữ liệu. Ngoài ra, các công cụ thứ ba như ApexSQL Log và ApexSQL Recover cũng có thể được sử dụng để khôi phục dữ liệu.

Hãy để các chuyên gia của chúng tôi hướng dẫn bạn trong hành trình nền tảng dữ liệu thế hệ mới.

Sử dụng tab Feedback để đưa ra nhận xét hoặc đặt câu hỏi. Bạn cũng có thể bắt đầu cuộc trò chuyện với chúng tôi.