AUDIT trong SQL Server 2012

Ngoài những tính năng về Audit đã có trong các phiên bản trước, SQL Server 2012 cung cấp thêm rất nhiều tính năng mới, trong đó bao gồm một số cải tiến mạnh mẽ cho khả năng giám sát (audit) SQL Server.

Phần đầu của bài viết này xin khái quát lại tất cả những tính năng về audit có trong những phiên bản SQL Server trước đây, bao gồm các tính năng:

• Trigger
• SQL Server Audit
• Change Data Capture (CDC)
• Change Tracking (CT)
• Khả năng tạo User-defined Audit
• Khả năng lọc Audit (Audit Filtering)
• Hỗ trợ tự động khôi phục log (Audit resilience)

1. Trigger

Trigger là 1 đối tượng (object) trong database cho phép chạy thủ tục lưu trữ (stored procedure) nào đó mỗi khi có 1 thay đổi xảy ra. Có 2 loại Trigger:

• DDL Trigger: áp dụng cho những thay đổi liên quan đến cấu trúc của database, như: ALTER DATABASE, CREATE TABLE, …
• DML Trigger: áp dụng cho những thay đổi liên quan trực tiếp đến dữ liệu, như: UPDATE, INSERT, DELETE.

Trigger có thể được sử dụng với nhiều mục đích khác nhau, ví dụ như:

• Để ngăn không cho thay đổi cấu trúc của 1 Table nào đó (DDL Trigger cho ALTER TABLE)
• Để định dạng lại giá trị trước khi chèn vào 1 Table nào đó (DML Trigger cho Insert với chế độ Instead hoặc After)
• Để log lại những thay đổi về mặc cấu trúc hay dữ liệu của 1 Table (DDL Trigger hoặc DML Trigger)

Các Trigger có thể được cài đặt để log lại những thay đổi đó ở nhiều nơi:

• Lưu lại những thay đổi trong 1 hoặc nhiều table, thuộc 1 hoặc nhiều database khác nhau, của 1 hoặc nhiều server khác nhau (sử dụng Linked Server).
• Sử dụng Service Broker gửi message đến nhiều nơi khác nhau, như: file trong server, event trong Event Viewer, hay email đến người quản trị.

Ví dụ sau đây sẽ tạo 1 Trigger bắt tất cả các sự kiện liên quan đến các câu lệnh DDL ở trong database Test và lưu vào trong table DDL_Log.

USE Test
GO

-- tạo bảng DDL_Log
CREATE TABLE DDL_Log
(
PostTime datetime,
DB_User nvarchar (100),
Event nvarchar (100),
TSQL nvarchar (2000)
)
GO

-- tạo Trigger
CREATE TRIGGER myDDLTrigger
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS

DECLARE @data XML
SET @data = EVENTDATA()
INSERT DDL_Log (PostTime, DB_User, Event, TSQL)
VALUES
(
GETDATE(),
CONVERT(nvarchar(100), CURRENT_USER),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
) ;

GO

2. SQL Server Audit

SQL Server Audit có từ phiên bản SQL Server 2008 Enterprise. Tính năng này đơn giản hoá khả năng giám sát tự động (automatic audit) và có thể thay thế cho việc cài đặt các Trigger. SQL Server Audit có thể cấu hình giám sát ở 2 mức (level): instance và database.

Các thành phần của SQL Server Audit:

• Audit Object: Trong mỗi Instance chúng ta có thể tạo ra nhiều audit khác nhau. Ứng với mỗi Audit sẽ phải chỉ ra 1 audit destination để ghi lại những thông tin được giám sát.
• Server Audit Specification: quy định cho 1 Audit cụ thể nào đó 1 tập các hành động của Instance cần giám sát, ví dụ: CREATE LOGIN, ALTER DATABASE, … . Chúng ta có thể tạo 1 server audit specification cho mỗi SQL Server Audit.
• Database Audit Specification: quy định cho 1 Audit cụ thể nào đó 1 tập các hành động của database object cần giám sát, ví dụ: CREATE TABLE, ALTER VIEW, … . Chúng ta có thể tạo 1 database audit specification cho mỗi SQL Server Audit.
• Target: chính là Audit destination được chỉ ra trong mỗi Audit. Target có thể là 1 file, 1 Windows Security event log, hay 1 Windows Application event log.

Ví dụ sau đây sẽ tạo 1 Audit để log lại thông tin mỗi khi có 1 connection nào đó login bị fail (do nhiều nguyên nhân khác nhau: không đúng username, sai password, …) và lưu những thông tin đó trong Windows Application log.

USE master ;
GO

--Tạo Audit
CREATE SERVER AUDIT mySQLServerAudit TO APPLICATION_LOG WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
GO

--Tạo Server Audit Specification và gán cho Audit
CREATE SERVER AUDIT SPECIFICATION FailedLoginServerAuditSpecification FOR SERVER AUDIT mySQLServerAudit ADD (FAILED_LOGIN_GROUP);

--Enable audit
ALTER SERVER AUDIT mySQLServerAudit WITH (STATE = ON);
GO

3. Change Data Capture (CDC)

Microsoft bắt đầu cung cấp tính năng CDC từ phiên bản SQL Server 2008 để đơn giản hoá và có thể thay thế cho việc cài đặt các DML Trigger trong việc lưu lại dấu vết của những dữ liệu đã bị thay đổi.

Sau khi bật và cấu hình tính năng, CDC sẽ ghi lại tất cả các thay đổi (bao gồm Insert, Update, Delete) về dữ liệu trên 1 table và lưu trữ những thay đổi đó trong các System table.

CDC có thể lưu lại 1 số thông tin như sau:

• Update mask: chỉ ra lệnh DML tác động lên dữ liệu, trong đó: 1 = Delete, 2 = Insert, 3 = Before Update, 4 = After Update
• Các dữ liệu bị thay đổi
• Thời điểm dữ liệu bị thay đổi
• …

Dựa vào những thông tin trên, người quản trị có thể dễ dàng kiểm tra, theo dõi những thay đổi này thông qua các Stored Procedure hoặc xem trực tiếp các system table do CDC tạo ra.

Ví dụ sau đây sẽ bật tính năng CDC cho table Test và xem những thay đổi đó thông qua việc truy vấn stored procedure hay system table.

--bật tính năng CDC cho database
EXEC sys.sp_cdc_enable_db
GO

--bật tính năng CDC cho table Test
EXEC sys.sp_cdc_enable_table N'dbo', N'Test',DEFAULT,DEFAULT, 1
GO

/* thực hiện 1 số lệnh Insert, Update, Delete */

-- xem thông tin bằng cách truy vấn stored procedure
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_TestTable
(sys.fn_cdc_get_min_lsn('dbo_Test'), sys.fn_cdc_get_max_lsn(),N'all')

--xem thông tin trực tiếp từ system table
SELECT * FROM cdc.dbo_Test_CT

4. Change Tracking (CT)

Change Tracking cho phép các ứng dụng theo dõi được những thay đổi trong các table của ứng dụng đó. Điểm khác biệt của CT so với CDC là CT không lưu lại các dữ liệu hiện hành mỗi khi có thay đổi mà chỉ lưu lại 1 số thông tin cơ bản như Primary key của dòng dữ liệu có thay đổi, số lần thay đổi của dòng dữ liệu đó, …

Phải enable tính năng Change Tracking cho từng table mà bạn muốn theo dõi

-- bật tính năng Change Tracking cho database
ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

--bật tính năng Change Tracking cho table
ALTER TABLE Test
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

Sau khi bật Change Tracking, chúng ta có thể sử dụng 1 số view hoặc function để xem thông tin:

--liệt kê các database trong instance có bật tính năng Change Tracking
SELECT * FROM sys.change_tracking_databases

--liệt kê các table trong database hiện tại có bật tính năng Change Tracking
SELECT * FROM sys.change_tracking_tables

/*liệt kê tất cả các dòng dữ liệu trong table Test (đã bật  tính năng Change Tracking) cùng với các thông tin về version tuơng ứng */
SELECT t.*, ct.*
FROM Test t CROSS APPLY
CHANGETABLE ( VERSION Test, (id), t.id ) AS ct;

Với những tính năng trên, người quản trị có đủ khả năng để giám sát 1 cách chi tiết những thay đổi về cấu trúc cũng như về dữ liệu trong các database của mình .

Thêm vào đó, thay vì chỉ được cung cấp trong bản Enterprise như ở phiên bản SQL Server 2008 thì nay SQL Server Audit được tích hợp trong tất cả các bản edition của SQL Server 2012 với tốc độ được cải thiện 1 cách đáng kể cùng nhiều tính năng hơn.

5. User-Defined Audit

User-Defined Audit cho phép các ứng dụng tự tuỳ biến, tạo ra các sự kiện của riêng mình và ghi thông tin audit log 1 cách linh hoạt hơn, ví dụ như: ghi vào audit log tên của các user đăng nhập vào application thay cho tên login chung được khai báo trong connection string để ứng dụng kết nối đến database.

Chúng ta có thể add thêm các sự kiện (audit event) như vậy bằng việc sử dụng thủ tục (stored procedure) sp_audit_write. Khi đó tất cả các sự kiện sẽ được lưu trong group USER_DEFINED_AUDIT_GROUP. Lưu ý rằng để sử dụng được, trước đó USER_DEFINED_AUDIT_GROUP phải được enable.

Ví dụ sau sẽ ghi vào audit log 1 sự kiện với id = 141 cùng 1 số thông tin tuỳ thích.

EXEC sp_audit_write
  @user_defined_event_id =  141,
  @succeeded = 0,
  @user_defined_information = N'My information' ;

Một số cột mới (column) được thêm vào sys.server_audits, sys.server_file_audits, và sys.fn_get_audit_file để theo dõi các user-defined audit event..

6. Audit Filtering

Trước đây, thật không dễ dàng để chỉ  lọc (theo 1 điều kiện cụ thể nào đó) những sự kiện mà người quản trị đang quan tâm. Điều này đã được khắc phục trong phiên bản SQL Server 2012 với tính năng Audit Filtering.

Giờ đây, SQL Server Audit hỗ trợ khả năng lọc những sự kiện cần audit trước khi chúng được ghi vào audit log thông qua mệnh đề WHERE  trong câu lệnh CREATE SERVER AUDIT và ALTER SERVER AUDIT.

Ví dụ sau sẽ tạo 1 Server Audit chỉ lưu vào audit log những sự kiện có id = 141

CREATE SERVER AUDIT [MyFilteredAuditEvent]
WHERE user_defined_event_id = 141;
GO

7. Audit Resilience

Audit Resilience cung cấp khả năng tuỳ biến sự phản hồi của SQL Server cũng như giảm thiểu mất dữ liệu audit khi việc ghi audit log bị lỗi do nhiều nguyên nhân khác nhau như lỗi ghi dữ liệu, lỗi mạng, …

Có 2 option mới để hỗ trợ tính năng này và sẽ được cấu hình mỗi khi tạo 1 Audit:

• On Audit Log Failure: sẽ có 3 lựa chọn khác nhau quy định phản ứng của SQL Server khi không thể ghi Audit log: Continue, Shutdown Server, hoặc Fail operation. Tính năng mới hỗ trợ này rất quan trọng vì ở phiên bản trước chỉ có 1 lựa chọn duy nhất là shutdown server hay không mà thôi.
• Maximum Rollover Files: Trước đây chỉ có 2 lựa chọn để quy định số file log được dùng để lưu audit log: không giới hạn số lượng log file hoặc giới hạn số luợng log file. Trong trường hợp có giới hạn, khi số lượng log file chạm ngưỡng tối đa, file log cũ nhất sẽ tự động xoá đi để có thể ghi thêm file log mới hơn (gọi là roll-over). SQL Server 2012 cung cấp thêm 1 lựa chọn nữa cho phép giữ lại 1 số lượng file log cố định mà không bị mất thông tin audit vì hiện tượng roll-over.

Trên đây là 1 số cải tiến trong SQL Server 2012 nhằm cung cấp khả năng Audit dữ liệu 1 cách linh hoạt và hữu ích cho những người quản trị.

Nguồn: q u a n g n h a t 1 4 0 1 . w o r d p r e s s . c o m