您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2008 審計 SELECT 語句：T-SQL 建立伺服器審計與資料庫審計規格

編輯：關於SqlServer

SQL Server 2008 將審計提升為第一級伺服器物件，支援以 T-SQL 建立 伺服器審計、資料庫審計規格，針對 SELECT 等 DML 作業進行細粒度審計，結果寫入檔案系統，無需 Profiler 或第三方工具。

建立伺服器審計物件 (儲存目標)

USE master;
CREATE SERVER AUDIT SelectAudit
TO FILE (FILEPATH = 'C:\Audits\SelectAudit\',
         MAXSIZE = 1 GB,
         MAX_ROLLOVER_FILES = 10,
         RESERVE_DISK_SPACE = ON)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
-- QUEUE_DELAY 1000ms 非同步寫入，效能影響極小
-- ON_FAILURE = CONTINUE 避免審計失敗導致資料庫掛起

建立資料庫審計規格 (審計什麼、誰)

USE AdventureWorks;
CREATE DATABASE AUDIT SPECIFICATION SelectSpec
FOR SERVER AUDIT SelectAudit
ADD (SELECT ON SCHEMA::HumanResources BY [public])  -- 審計 public 角色對 HR 結構描述的 SELECT
WITH (STATE = ON);  -- 立即啟用

啟用審計物件

ALTER SERVER AUDIT SelectAudit WITH (STATE = ON);
-- 檢視狀態
SELECT name, is_state_enabled FROM sys.server_file_audits;
SELECT name, is_state_enabled FROM sys.database_audit_specifications;

查看審計記錄

SELECT * FROM sys.fn_get_audit_file('C:\Audits\SelectAudit\*', DEFAULT, DEFAULT);
-- 重要欄位：event_time, action_id, succeeded, server_principal_name, database_name, object_name, statement

進階：審計多個資料表/結構描述

CREATE DATABASE AUDIT SPECIFICATION MultiTableSelectSpec
FOR SERVER AUDIT SelectAudit
ADD (SELECT ON OBJECT::Sales.SalesOrderHeader BY [public]),
ADD (SELECT ON OBJECT::Sales.SalesOrderDetail BY [public]),
ADD (SELECT ON SCHEMA::Production BY [db_datareader])
WITH (STATE = ON);

伺服器級審計規格 (登入/登出、權限變更等)

CREATE SERVER AUDIT SPECIFICATION LoginAuditSpec
FOR SERVER AUDIT SelectAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP)
WITH (STATE = ON);