Tags

, , , , , , , , ,

AUDIT TRONG SQL SERVER 2012 (Phần 1)

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)

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 .

Vậy trong SQL Server 2012 sẽ có thêm những cải tiến gì?

Đón đọc phần 2…
… còn tiếp … 

Enjoy!!!
Nhat Phan