关于MSSQL audit记录1

{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,
'触发审核的日期和时间' ,
        sequence_number AS '单个审核记录中的记录顺序' ,
        action_id AS '操作的 ID' ,
        succeeded AS '触发事件的操作是否成功' ,
        permission_bitmask AS '权限掩码' ,
        is_column_permission AS '是否为列级别权限' ,
        a.session_id AS '发生该事件的会话的 ID' ,
        server_principal_id AS '执行操作的登录上下文 ID' ,
        database_principal_id AS '执行操作的数据库用户上下文 ID' ,
        target_server_principal_id AS '执行 GRANT/DENY/REVOKE 操作的服务器主体' ,
        target_database_principal_id AS '执行 GRANT/DENY/REVOKE 操作的数据库主体' ,
        object_id AS '发生审核的实体的 ID(服务器对象，DB,数据库对象，架构对象)' ,
        class_type AS '可审核实体的类型' ,
        session_server_principal_name AS '会话的服务器主体' ,
        server_principal_name AS '当前登录名' ,
        server_principal_sid AS '当前登录名 SID' ,
        database_principal_name AS '当前用户' ,
        target_server_principal_name AS '操作的目标登录名' ,
        target_server_principal_sid AS '目标登录名的 SID' ,
        target_database_principal_name AS '操作的目标用户' ,
        server_instance_name AS '审核的服务器实例的名称' ,
        database_name AS '发生此操作的数据库上下文' ,
        schema_name AS '此操作的架构上下文' ,
        object_name AS '审核的实体的名称' ,
        statement AS 'TSQL 语句（如果存在）' ,
        additional_information AS '单个事件的唯一信息，以 XML 的形式返回' ,
        file_name AS '记录来源的审核日志文件的路径和名称' ,
        audit_file_offset AS '包含审核记录的文件中的缓冲区偏移量' ,
        user_defined_event_id AS '作为 sp_audit_write 参数传递的用户定义事件 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 '触发审核的日期和时间' ,
--b.connect_time,
        sequence_number AS '单个审核记录中的记录顺序' ,
        action_id AS '操作的 ID' ,
        succeeded AS '触发事件的操作是否成功' ,
        permission_bitmask AS '权限掩码' ,
        is_column_permission AS '是否为列级别权限' ,
        a.session_id AS '发生该事件的会话的 ID' ,
        server_principal_id AS '执行操作的登录上下文 ID' ,
        database_principal_id AS '执行操作的数据库用户上下文 ID' ,
        target_server_principal_id AS '执行 GRANT/DENY/REVOKE 操作的服务器主体' ,
        target_database_principal_id AS '执行 GRANT/DENY/REVOKE 操作的数据库主体' ,
        object_id AS '发生审核的实体的 ID(服务器对象，DB,数据库对象，架构对象)' ,
        class_type AS '可审核实体的类型' ,
        session_server_principal_name AS '会话的服务器主体' ,
        server_principal_name AS '当前登录名' ,
        server_principal_sid AS '当前登录名 SID' ,
        database_principal_name AS '当前用户' ,
        target_server_principal_name AS '操作的目标登录名' ,
        target_server_principal_sid AS '目标登录名的 SID' ,
        target_database_principal_name AS '操作的目标用户' ,
        server_instance_name AS '审核的服务器实例的名称' ,
        database_name AS '发生此操作的数据库上下文' ,
        schema_name AS '此操作的架构上下文' ,
        object_name AS '审核的实体的名称' ,
        statement AS 'TSQL 语句（如果存在）' ,
        additional_information AS '单个事件的唯一信息，以 XML 的形式返回' ,
        file_name AS '记录来源的审核日志文件的路径和名称' ,
        audit_file_offset AS '包含审核记录的文件中的缓冲区偏移量' ,
        user_defined_event_id AS '作为 sp_audit_write 参数传递的用户定义事件 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