[鐵人賽][Denali 新特性探險30]SQL Server Audit Enhancements
概述
稽核功能在Denali這版中有不少改善，好比錯誤操作選項:如果稽核本身錯誤無法寫入紀錄，
你可以選擇:1.繼續 2.關閉SERVER 3.失敗作業(預設選項=繼續)。
過濾條件:你可以針對某個登入或使用者….等執行稽核作業。
還有動作類型多了使用者定義稽核、新增TSQL-stack資訊..等，
這些改善使得稽核功能變得更有彈性及可用性，下面我將簡單實際操作使用者自訂稽核過程。
建立稽核(Using SSMS)
過濾條件(稽核SA)
select * from sys.server_principals
確認 sa 的principal_id。
輸入 server_principal_id=1。
建立伺服器稽核規格(Using SSMS)
稽核動作類型選擇 USER_DEFINED_AUDIT_GROUP。
建立稽核(Using TSQL)
CREATE SERVER AUDIT [Audit-test1]
TO FILE
( FILEPATH = N'e:\db1_audit'
,MAXSIZE = 0 MB
,MAX_FILES = 5
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
WHERE (server_principal_id=1)
GO
alter server audit [Audit-test1] with(state = on )
建立伺服器稽核規格(Using TSQL)
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-test]
FOR SERVER AUDIT [Audit-test1]
ADD (USER_DEFINED_AUDIT_GROUP)
with(state=on)
GO
啟用稽核
建立兩個store procedure內含執行sp_audit_write
create proc usp_getA
as
select * from dbo.A;
declare @myinfo nvarchar(1000);
set @myinfo=N'總共筆數:' +cast(@@rowcount as varchar(10));
exec sp_audit_write @user_defined_event_id =8888 ,
@succeeded = 1 , @user_defined_information = @myinfo;
create proc usp_getAA
as
exec dbo.usp_getA
declare @myinfo nvarchar(1000);
set @myinfo=N'SP 中執行另一個SP';
exec sp_audit_write @user_defined_event_id =8888 ,
@succeeded = 1 , @user_defined_information = @myinfo;
測試使用者自訂稽核
查看稽核紀錄
這裡沒有任何紀錄是正常的，因為我的稽核篩選條件是針對 SA，所以下面我將使用SA登入後再來測試。
使用SA登入
變更連接。
輸入密碼。
執行SP
查看稽核紀錄(這裡我分段擷取)
動作識別碼為:User Defined Audit。
確認工作階段為 sa。
可以看到執行的陳述式。
Denali 稽核會擷取TSQL_stack資訊。
這裡就可以看到我們自行定義的事件識別碼和資訊。
接下來執行另一個SP，就可以明顯感受到其他資訊欄位的作用。
Level=2 ,object_name=usp_getAA，Level=3,object_name=usp_getA。
分別擷取到各自SP的使用者定義資訊。
參考 CREATE SERVER AUDIT (Transact-SQL)
鐵人賽第四屆心得:
這是我個人第三次參加IT邦幫忙所舉辦的鐵人賽(第二、三、四屆)，
每一屆都是自我學習成長的一種挑戰，挑戰連續30天在工作下班和休閒假日之餘分享學習心得筆記。
而這屆我分享的題目是: MS SQL Server新特性探險:探險代碼 Denali，
這30天中我總共分享了 Denali 15個開發面新特性、2個管理面新特性、1個效能面新特性
、1個安全面特性改善和 SSIS 新特性，雖然無法全部並仔細介紹 Denali 所有相關新特性，
但這30天讓自己真的相當充實，也終於可以不用繼續站 00:00~02:00 "棟樑"哨，
今天也替自己連三屆鐵人賽畫下完美句點。