-
-----創(chuàng)建審核對象之前需要切換到master數(shù)據(jù)庫
-
--USE [master]
-
--GO
-
--CREATE SERVER AUDIT MyAudit TO FILE(FILEPATH='D:\SqlAudits') --這里指定文件夾不能指定文件，生成文件都會保存在這個文件夾
-
--GO
-
--實(shí)際上，我們在創(chuàng)建審核對象的同時可以指定審核選項(xiàng)，下面是相關(guān)腳本
-
--把日志放在磁盤的好處是可以使用新增的TVF:sys.[fn_get_audit_file] 來過濾和排序?qū)徍藬?shù)據(jù)，如果把審核數(shù)據(jù)保存在Windows 事件日志里查詢起來非常麻煩
-
USE [master]
-
GO
-
CREATE SERVER AUDIT MyAudit TO FILE(
-
FILEPATH='D:\SqlAudits',
-
MAXSIZE=2GB,
-
MAX_ROLLOVER_FILES=12)
-
WITH (
-
ON_FAILURE=CONTINUE,
-
QUEUE_DELAY=1000);
-
ALTER SERVER AUDIT MyAudit WITH(STATE =ON)
-
--MAXSIZE：指明每個審核日志文件的最大大小是4GB
-
--MAX_ROLLOVER_FILES：指明滾動文件數(shù)目，類似于SQL ERRORLOG，達(dá)到多少個文件之后刪除前面的歷史文件，這里是6個文件
-
--ON_FAILURE：指明當(dāng)審核數(shù)據(jù)發(fā)生錯誤時的操作，這里是繼續(xù)進(jìn)行審核，如果指定shutdown，那么將會shutdown整個實(shí)例
-
--queue_delay：指明審核數(shù)據(jù)寫入的延遲時間，這里是1秒，最小值也是1秒，如果指定0表示是實(shí)時寫入，當(dāng)然性能也有一些影響
-
--STATE：指明啟動審核功能，STATE這個選項(xiàng)不能跟其他選項(xiàng)共用，所以只能單獨(dú)一句
-
--在修改審核選項(xiàng)的時候，需要先禁用審核，再開啟審核
-
--ALTER SERVER AUDIT MyFileAudit WITH(STATE =OFF)
-
--ALTER SERVER AUDIT MyFileAudit WITH(QUEUE_DELAY =1000)
-
--ALTER SERVER AUDIT MyFileAudit WITH(STATE =ON)
-
-
-
USE [T_restore]
-
GO
-
CREATE DATABASE AUDIT SPECIFICATION CaptureDBActionToEventLog
-
FOR SERVER AUDIT MyAudit
-
ADD (database_object_change_group),
-
ADD (INSERT,UPDATE,DELETE ON schema::dbo BY PUBLIC)
-
WITH (STATE =ON)
-
--我們先在D盤創(chuàng)建sqldbaudits文件夾
-
--第一個操作組對數(shù)據(jù)庫中所有對象的DDL語句create，alter，drop等進(jìn)行記錄
-
--第二個語句監(jiān)視由任何public用戶（也就是所有用戶）對dbo架構(gòu)的任何對象所做的DML操作
-
-
the following sql show how to read the bitry audt file
-
SELECT [event_time] AS '觸發(fā)審核的日期和時間' ,
-
sequence_number AS '單個審核記錄中的記錄順序' ,
-
action_id AS '操作的 ID' ,
-
succeeded AS '觸發(fā)事件的操作是否成功' ,
-
permission_bitmask AS '權(quán)限掩碼' ,
-
is_column_permission AS '是否為列級別權(quán)限' ,
-
session_id AS '發(fā)生該事件的會話的 ID' ,
-
server_principal_id AS '執(zhí)行操作的登錄上下文 ID' ,
-
database_principal_id AS '執(zhí)行操作的數(shù)據(jù)庫用戶上下文 ID' ,
-
target_server_principal_id AS '執(zhí)行 GRANT/DENY/REVOKE 操作的
-
target_database_principal_id AS '執(zhí)行 GRANT/DENY/REVOKE 操作的數(shù)據(jù)庫主體' ,
-
object_id AS '發(fā)生審核的實(shí)體的 ID(服務(wù)器對象，DB,數(shù)據(jù)庫對象，架構(gòu)對象)' ,
-
class_type AS '可審核實(shí)體的類型' ,
-
session_server_principal_name AS '會話的服務(wù)器主體' ,
-
server_principal_name AS '當(dāng)前登錄名' ,
-
server_principal_sid AS '當(dāng)前登錄名 SID' ,
-
database_principal_name AS '當(dāng)前用戶' ,
-
target_server_principal_name AS '操作的目標(biāo)登錄名' ,
-
target_server_principal_sid AS '目標(biāo)登錄名的 SID' ,
-
target_database_principal_name AS '操作的目標(biāo)用戶' ,
-
server_instance_name AS '審核的服務(wù)器實(shí)例的名稱' ,
-
database_name AS '發(fā)生此操作的數(shù)據(jù)庫上下文' ,
-
schema_name AS '此操作的架構(gòu)上下文' ,
-
object_name AS '審核的實(shí)體的名稱' ,
-
statement AS 'TSQL 語句（如果存在）' ,
-
additional_information AS '單個事件的唯一信息，以 XML 的形式返回' ,
-
file_name AS '記錄來源的審核日志文件的路徑和名稱' ,
-
audit_file_offset AS '包含審核記錄的文件中的緩沖區(qū)偏移量' ,
-
user_defined_event_id AS '作為 sp_audit_write 參數(shù)傳遞的用戶定義事件 ID' ,
-
user_defined_information AS '于記錄用戶想要通過使用 sp_audit_write 存儲過程記錄在審核日志中的任何附加信息'
-
FROM sys.[fn_get_audit_file]('D:\SqlAudits\MyAudit_1FE965A7-77D0-41A6-9D40-543162C722F2_0_131447450891790000.sqlaudit',
-
DEFAULT, DEFAULT)
{color:red}* how to change the audit actions{color}
--each time diable the audit before you chagne anything
{code:sql}
use datayesdb
go
alter DATABASE AUDIT SPECIFICATION CaptureDBActionToEventLog with (STATE =OFF)
use master
ALTER SERVER AUDIT MyAudit WITH(STATE =OFF)
{code}
--switch to user db and change your audit actions
{code:Sql}
use datayesdb
go
alter DATABASE AUDIT SPECIFICATION CaptureDBActionToEventLog
FOR SERVER AUDIT MyAudit
Drop (INSERT,UPDATE,DELETE ON schema::dbo BY PUBLIC),
Add (UPDATE,DELETE ON schema::dbo BY PUBLIC)
alter DATABASE AUDIT SPECIFICATION CaptureDBActionToEventLog WITH (STATE =ON)
{code}
--select the detail into from database_audit_specification_details for verification
{code:sql}select * from sys.database_audit_specification_details
{code}}
database_specification_id audit_action_id audit_action_name class class_desc major_id minor_id audited_principal_id audited_result is_group
------------------------- --------------- ------------------------------------------------------------ ----- ------------------------------------------------------------ ----------- ----------- -------------------- ------------------------------------------------------------ --------
65536 MNDO DATABASE_OBJECT_CHANGE_GROUP 0 DATABASE 0 0 0 SUCCESS AND FAILURE 1
65536 DL DELETE 3 SCHEMA 1 0 0 SUCCESS AND FAILURE 0
65536 UP UPDATE 3 SCHEMA 1 0 0 SUCCESS AND FAILURE 0
(3 行受影響)
--the following script will help you get the audit records
{code:sql}
use datayesdb
gp
SELECT CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset, event_time),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS event_time,
'觸發(fā)審核的日期和時間' ,
sequence_number AS '單個審核記錄中的記錄順序' ,
action_id AS '操作的 ID' ,
succeeded AS '觸發(fā)事件的操作是否成功' ,
permission_bitmask AS '權(quán)限掩碼' ,
is_column_permission AS '是否為列級別權(quán)限' ,
a.session_id AS '發(fā)生該事件的會話的 ID' ,
server_principal_id AS '執(zhí)行操作的登錄上下文 ID' ,
database_principal_id AS '執(zhí)行操作的數(shù)據(jù)庫用戶上下文 ID' ,
target_server_principal_id AS '執(zhí)行 GRANT/DENY/REVOKE 操作的服務(wù)器主體' ,
target_database_principal_id AS '執(zhí)行 GRANT/DENY/REVOKE 操作的數(shù)據(jù)庫主體' ,
object_id AS '發(fā)生審核的實(shí)體的 ID(服務(wù)器對象，DB,數(shù)據(jù)庫對象，架構(gòu)對象)' ,
class_type AS '可審核實(shí)體的類型' ,
session_server_principal_name AS '會話的服務(wù)器主體' ,
server_principal_name AS '當(dāng)前登錄名' ,
server_principal_sid AS '當(dāng)前登錄名 SID' ,
database_principal_name AS '當(dāng)前用戶' ,
target_server_principal_name AS '操作的目標(biāo)登錄名' ,
target_server_principal_sid AS '目標(biāo)登錄名的 SID' ,
target_database_principal_name AS '操作的目標(biāo)用戶' ,
server_instance_name AS '審核的服務(wù)器實(shí)例的名稱' ,
database_name AS '發(fā)生此操作的數(shù)據(jù)庫上下文' ,
schema_name AS '此操作的架構(gòu)上下文' ,
object_name AS '審核的實(shí)體的名稱' ,
statement AS 'TSQL 語句（如果存在）' ,
additional_information AS '單個事件的唯一信息，以 XML 的形式返回' ,
file_name AS '記錄來源的審核日志文件的路徑和名稱' ,
audit_file_offset AS '包含審核記錄的文件中的緩沖區(qū)偏移量' ,
user_defined_event_id AS '作為 sp_audit_write 參數(shù)傳遞的用戶定義事件 ID' ,
user_defined_information AS '于記錄用戶想要通過使用 sp_audit_write 存儲過程記錄在審核日志中的任何附加信息',
b.CLIENT_NET_ADDRESS AS 'ClientIPAddress'
FROM sys.[fn_get_audit_file]('D:\SqlAudits\*.sqlaudit',
DEFAULT, DEFAULT) a left join SYS.DM_EXEC_CONNECTIONS b
on a.session_id=b.session_id
where event_time between
dateadd(mi, -10,event_time) and event_time
{code}
--you can get the audit file path and detail info with the following scipt
select * from sys.server_file_audits
--create procedure
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE audit_record
@i int = 10
AS
BEGIN
/****** Script for SelectTopNRows command from SSMS ******/
insert into [MyAudit].[dbo].[Audit_DYDB_UPDL]
( [event_time]
,[sequence_number]
,[action_id]
,[succeeded]
,[permission_bitmask]
,[is_column_permission]
,[session_id]
,[server_principal_id]
,[database_principal_id]
,[target_server_principal_id]
,[target_database_principal_id]
,[object_id]
,[class_type]
,[session_server_principal_name]
,[server_principal_name]
,[server_principal_sid]
,[database_principal_name]
,[target_server_principal_name]
,[target_server_principal_sid]
,[target_database_principal_name]
,[server_instance_name]
,[database_name]
,[schema_name]
,[object_name]
,[statement]
,[additional_information]
,[file_name]
,[audit_file_offset]
,[user_defined_event_id]
,[user_defined_information]
,[CLIENT_NET_ADDRESS])
SELECT CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset, event_time),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS '觸發(fā)審核的日期和時間' ,
--b.connect_time,
sequence_number AS '單個審核記錄中的記錄順序' ,
action_id AS '操作的 ID' ,
succeeded AS '觸發(fā)事件的操作是否成功' ,
permission_bitmask AS '權(quán)限掩碼' ,
is_column_permission AS '是否為列級別權(quán)限' ,
a.session_id AS '發(fā)生該事件的會話的 ID' ,
server_principal_id AS '執(zhí)行操作的登錄上下文 ID' ,
database_principal_id AS '執(zhí)行操作的數(shù)據(jù)庫用戶上下文 ID' ,
target_server_principal_id AS '執(zhí)行 GRANT/DENY/REVOKE 操作的服務(wù)器主體' ,
target_database_principal_id AS '執(zhí)行 GRANT/DENY/REVOKE 操作的數(shù)據(jù)庫主體' ,
object_id AS '發(fā)生審核的實(shí)體的 ID(服務(wù)器對象，DB,數(shù)據(jù)庫對象，架構(gòu)對象)' ,
class_type AS '可審核實(shí)體的類型' ,
session_server_principal_name AS '會話的服務(wù)器主體' ,
server_principal_name AS '當(dāng)前登錄名' ,
server_principal_sid AS '當(dāng)前登錄名 SID' ,
database_principal_name AS '當(dāng)前用戶' ,
target_server_principal_name AS '操作的目標(biāo)登錄名' ,
target_server_principal_sid AS '目標(biāo)登錄名的 SID' ,
target_database_principal_name AS '操作的目標(biāo)用戶' ,
server_instance_name AS '審核的服務(wù)器實(shí)例的名稱' ,
database_name AS '發(fā)生此操作的數(shù)據(jù)庫上下文' ,
schema_name AS '此操作的架構(gòu)上下文' ,
object_name AS '審核的實(shí)體的名稱' ,
statement AS 'TSQL 語句（如果存在）' ,
additional_information AS '單個事件的唯一信息，以 XML 的形式返回' ,
file_name AS '記錄來源的審核日志文件的路徑和名稱' ,
audit_file_offset AS '包含審核記錄的文件中的緩沖區(qū)偏移量' ,
user_defined_event_id AS '作為 sp_audit_write 參數(shù)傳遞的用戶定義事件 ID' ,
user_defined_information AS '于記錄用戶想要通過使用 sp_audit_write 存儲過程記錄在審核日志中的任何附加信息',
b.CLIENT_NET_ADDRESS AS 'ClientIPAddress' --into MyAudit..Audit_DYDB_UPDL
FROM sys.[fn_get_audit_file]('D:\SqlAudits\*.sqlaudit',
DEFAULT, DEFAULT) a left join SYS.DM_EXEC_CONNECTIONS b
on a.session_id=b.session_id
where
CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset, event_time),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) between dateadd(mi, -@i,getdate()) and getdate()
END
GO
------------------------------------------
--create job
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'DBA_Audit_10min',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'record the audit each 10 min',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'dba_monitor', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'DBA_Audit_10min', @server_name = N'SH-DM-DB04'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'DBA_Audit_10min', @step_name=N'DBA_audit_record_10',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'MyAudit..audit_record 10',
@database_name=N'datayesdb',
@flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'DBA_Audit_10min',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'record the audit each 10 min',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'dba_monitor',
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DBA_Audit_10min', @name=N'DBA_audit_10',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=10,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20170719,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
-
USE [master]
-
-
GO
-
-
CREATE SERVER AUDIT SPECIFICATION [MyAudit_login]
-
FOR SERVER AUDIT [MyAudit]
-
ADD (SUCCESSFUL_LOGIN_GROUP)
-
-
GO
-
-
ALTER SERVER AUDIT SPECIFICATION [MyAudit_login] WITH(STATE =ON)
當(dāng)前題目：關(guān)于MSSQLaudit記錄1
當(dāng)前路徑：
http://weahome.cn/article/jdgghj.html
重慶分公司
重慶分公司
