SqlServer 審核（指令碼樣本），sqlserver樣本

SqlServer 審核（指令碼樣本），sqlserver樣本

此文章主要是指令碼樣本，更多說明看官方文檔：審核（資料庫引擎）

--必須在 master 資料庫中建立審核USE master;GO--建立伺服器審核對象--https://msdn.microsoft.com/zh-cn/library/cc280448(v=sql.100).aspxCREATE SERVER AUDIT [Audit_ToFile]TO FILE (--目標類型：FILE(檔案)/APPLICATION_LOG(應用程式記錄檔)/SECURITY(安全日誌)  FILEPATH = N'E:\'--稽核線索的路徑, MAXSIZE = 100MB--審核檔案最大大小(MB、GB、TB 或 UNLIMITED), MAX_ROLLOVER_FILES = 5--最大檔案數(或者UNLIMITED), RESERVE_DISK_SPACE = ON--預先分配MAXSIZE大小(MAXSIZE<>UNLIMITED 時適用。預設：OFF))WITH (  QUEUE_DELAY = 1000--強制審核前時間，預設1000(毫秒)，值 0 指示同步傳遞, ON_FAILURE = CONTINUE--無法寫入目標檔案時：CONTINUE(預設) | SHUTDOWN(需要許可權)--, AUDIT_GUID = <GUID>--資料庫鏡像使用)GO--更改伺服器審核對象（與建立格式一樣）--https://msdn.microsoft.com/zh-cn/library/cc280563%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396ALTER SERVER AUDIT [Audit_ToFile]MODIFY NAME = [Audit_ToFile]--更改審核名稱GOALTER SERVER AUDIT [Audit_ToFile]WITH (STATE = ON )--啟用審核收集記錄GO--刪除審核(必須禁用審核收集記錄)--https://msdn.microsoft.com/zh-cn/library/cc280899(v=sql.100).aspxALTER SERVER AUDIT [Audit_ToFile]WITH (STATE = OFF )GOIF  EXISTS (SELECT * FROM sys.server_audits WHERE name = N'Audit_ToFile')DROP SERVER AUDIT [Audit_ToFile]GO

--建立伺服器審核規範對象--https://msdn.microsoft.com/zh-cn/library/cc280767%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396CREATE SERVER AUDIT SPECIFICATION [Audit_Specification_Server]FOR SERVER AUDIT [Audit_ToFile]--應用此規範的審核名稱ADD ( FAILED_LOGIN_GROUP )--伺服器層級可審核操作組的名稱 (如 登入失敗審核)WITH ( STATE = ON )--允許或禁止審核收集此審核規範的記錄GO--SQL Server 審核操作組和操作--https://msdn.microsoft.com/zh-cn/library/cc280663%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396--更改伺服器審核規範對象(必須將審核規範的狀態設定為 OFF 選項)--https://msdn.microsoft.com/zh-cn/library/cc280682(v=sql.100).aspxALTER SERVER AUDIT SPECIFICATION [Audit_Specification_Server]WITH ( STATE = OFF )GOALTER SERVER AUDIT SPECIFICATION [Audit_Specification_Server]FOR SERVER AUDIT [Audit_ToFile]ADD ( SUCCESSFUL_LOGIN_GROUP ) ,ADD ( LOGOUT_GROUP ),DROP ( LOGIN_CHANGE_PASSWORD_GROUP )WITH ( STATE = ON )GO/* 必須將審核規範的狀態設定為 OFF 選項，以便更改審核規範,否則出現錯誤:訊息 33229，層級 16，狀態 1，第 1 行禁用審核規範時，對審核規範的更改必須已完成。*/--刪除伺服器審核規範對象(必須將審核規範的狀態設定為 OFF 選項)--https://msdn.microsoft.com/zh-cn/library/cc280603%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396ALTER SERVER AUDIT SPECIFICATION [Audit_Specification_Server]WITH ( STATE = OFF )GOIF  EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'Audit_Specification_Server')DROP SERVER AUDIT SPECIFICATION [Audit_Specification_Server]GO

--建立資料庫審核規範對象 (只對當前資料庫建立審核)--https://msdn.microsoft.com/zh-cn/library/cc280404%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396USE AdventureWorks2008R2;GOCREATE DATABASE AUDIT SPECIFICATION [Audit_Specification_Database]FOR SERVER AUDIT [Audit_ToFile] ADD (SCHEMA_OBJECT_CHANGE_GROUP)WITH ( STATE = ON )GOCREATE DATABASE AUDIT SPECIFICATION [Audit_Specification_Database]FOR SERVER AUDIT [Audit_ToFile] ADD ( SELECT ON dbo.ErrorLog BY [dbo])WITH ( STATE = ON )GO--更改資料庫審核規範對象 (必須禁止審核收集此審核規範的記錄)--https://msdn.microsoft.com/zh-cn/library/cc280645%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396ALTER DATABASE AUDIT SPECIFICATION [Audit_Specification_Database]WITH ( STATE = OFF )GOALTER DATABASE AUDIT SPECIFICATION [Audit_Specification_Database]FOR SERVER AUDIT [Audit_ToFile]ADD ( UPDATE,INSERT,DELETE ON dbo.ErrorLog BY [dbo]),DROP ( SELECT ON dbo.ErrorLog BY [dbo])WITH ( STATE = ON )GO--刪除資料庫審核規範對象 (必須禁止審核收集此審核規範的記錄)--https://msdn.microsoft.com/zh-cn/library/cc280479%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396ALTER DATABASE AUDIT SPECIFICATION [Audit_Specification_Database]WITH ( STATE = OFF )GOIF  EXISTS (SELECT * FROM sys.database_audit_specifications WHERE name = N'Audit_Specification_Database')DROP DATABASE AUDIT SPECIFICATION [Audit_Specification_Database]GO

--當刪除資料庫AdventureWorks2008R2的表ErrorLog時delete from  dbo.ErrorLog--稽核線索記錄如下

--相關視圖查詢select * from sys.server_audits--審核對象select * from sys.dm_server_audit_status--審核的目前狀態select * from sys.server_audit_specifications--伺服器審核規範對象select * from sys.server_audit_specification_details--伺服器審核規範對象的審核操作select * from sys.database_audit_specifications--資料庫審核規範對象select * from sys.database_audit_specification_details--資料庫審核規範對象的審核操作select * from sys.dm_audit_actions--所有可用的操作組select * from sys.dm_audit_class_type_map--稽核線索中的欄位對應select * from sys.server_file_audits--審核檔案資訊select * from sys.fn_get_audit_file('E:\Audit_ToFile_*.sqlaudit',default,default);