SQL Server - 使用者稽核 - 威力加強版

最近遇到一個情況，需要紀錄使用者查詢了那些資料表或下了那些語法，基本上在SQL Server 2008可以透過稽核的功能來完成，但我覺得有點陽春，無法得到我想要的資訊，而且也進行後續的統計，所以老樣子還是透過SQL Profiler來進行錄制，但是由於這是一個開放式的架構，所以還是有可能有其他的使用者進行資料庫的存取，雖然在SQL Profiler可以透過HostName取得電腦名稱，但是有些名稱還是會無法解析。

         首先為了得到使用者的IP，所以我透過了Logon Trigger來實作，但是只能捉到使用者有登入，但是後續的狀態還是一樣無法得知，而這些資訊其實可以透過SQL Profiler，所以我下一步希望可以將登入的使用者IP與SQL Profiler進行結合，這樣我就可以得到完整的資訊，但是要怎麼進行結合問題就來了，後來我透過了session_id與process_id兩個欄位的值，終於解了這個難題，也將原本的稽核功能更推進了一步。

方法與原始碼如下，再請參考。

1、建立儲存稽核資訊的資料表


CREATE TABLE [dbo].[cary_audit_logs](
[sn] [int] IDENTITY(1,1) NOT NULL,
[account] [varchar](100) NULL,
[ip_addr] [varchar](20) NULL,
[conn_time] [datetime] NULL,
[session_id] [int] NULL,
[process_id] [int] NULL,
CONSTRAINT [PK_cary_audit_logs] PRIMARY KEY CLUSTERED
(
[sn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


2、建立Logon Trigger

CREATE TRIGGER cary_audit_user_logs
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @ed XML
SET @ed = EVENTDATA()

declare @ip_addr varchar(20);
declare @conn_time datetime;
declare @session_id int;
declare @process_id int;

SELECT
@ip_addr = client_net_address,
@conn_time = connect_time
FROM sys.dm_exec_connections;

SELECT
@session_id = session_id,
@process_id = host_process_id
FROM sys.dm_exec_sessions
WHERE is_user_process = 1


INSERT INTO [cary_db].[dbo].[cary_audit_logs]
([account]
,[ip_addr]
,[conn_time]
,[session_id]
,[process_id]
)
VALUES
(  
@ed.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)'),
@ip_addr,
@conn_time,
@session_id,
@process_id
)
END


關鍵字：SQL Server、Audit、Logon Triggers、Sql Server Audit