DB監査(Audit Schema Object Access Event)

トレース機能を利用して、Select文の監査をします。

 

Audit Schema Object Access Eventをトレースし、ファイル出力された内容をOSのイベントログへ定期的に出力します。

 

msdb内にSPを2つ作成します。

SP_USRAuditTrace : Audit Schema Object Access EventをトレースするSP

SP_USRAuditTraceCheck : トレースしたファイルをチェックして、イベントログに出力するSP

 

SQLジョブを2つ作成します。

監査 : トレースを開始、チェックするSQLジョブ

監査ログ削除 : 出力された古いトレースファイルを削除するためのSQLジョブ

 

1.トレースログを出力するためのフォルダを準備します。

G:\work\tracelog

 

2.SP_USRAuditTraceを作成します。

sp_trace_setfilterにより監査に必要な条件を調整してください。

※トレース時のサーバ負荷、出力されるトレースファイルの量には注意してください。

use msdb
go

CREATE PROCEDURE [dbo].[SP_USRAuditTrace]
AS

DECLARE
  @ErrorMessage   varchar(2000)
 ,@ErrorSeverity  tinyint
 ,@ErrorState     tinyint

BEGIN TRY

	--トレースファイル名
	declare @basefile varchar(50)
	set @basefile='USRAuditTrace'

	--既に起動済みの監査用トレースがあるか確認
	declare @tid int 
	declare @tfnmae varchar(1000)

	SELECT distinct @tid=traceid, @tfnmae=convert(varchar(1000),value)   FROM :: fn_trace_getinfo(default)
	where convert(varchar(1000),value) like '%' + @basefile + '%'

	--ファイル名に追加する日付を作成
	declare @d1 char(8)
	select @d1 = convert (char(8), GETDATE(), 112)

	declare @stopfg int 
	SET @stopfg = 0

	--トレース中
	IF @tid is not null 
	BEGIN
		--本日のトレース
		IF @tfnmae like '%'+@basefile+@d1+'%'
		BEGIN
			--終了
			RETURN
		END 
		ELSE
		--違う場合は新たにトレースを始める
		BEGIN
			--前回のトレースを止める用
			SET @stopfg = 1
		END
	END

	-- Create a Queue
	declare @rc int
	declare @TraceID int
	declare @maxfilesize bigint

	--サイズ MB
	set @maxfilesize = 10000

	declare @fName sysname
	select @fName = 'G:\work\tracelog\' + @basefile + @d1

	--100ファイルでローテーション
	exec @rc = sp_trace_create @TraceID output, 2, @fName, @maxfilesize, NULL, 100

	if (@rc != 0) goto error

	declare @on bit
	set @on = 1

	--114:Audit Schema Object Access Event
	exec sp_trace_setevent @TraceID, 114, 1, @on
	exec sp_trace_setevent @TraceID, 114, 9, @on
	exec sp_trace_setevent @TraceID, 114, 2, @on
	exec sp_trace_setevent @TraceID, 114, 3, @on
	exec sp_trace_setevent @TraceID, 114, 4, @on
	exec sp_trace_setevent @TraceID, 114, 5, @on
	exec sp_trace_setevent @TraceID, 114, 6, @on
	exec sp_trace_setevent @TraceID, 114, 7, @on
	exec sp_trace_setevent @TraceID, 114, 8, @on
	exec sp_trace_setevent @TraceID, 114, 10, @on
	exec sp_trace_setevent @TraceID, 114, 11, @on
	exec sp_trace_setevent @TraceID, 114, 12, @on
	exec sp_trace_setevent @TraceID, 114, 14, @on
	exec sp_trace_setevent @TraceID, 114, 19, @on
	exec sp_trace_setevent @TraceID, 114, 21, @on
	exec sp_trace_setevent @TraceID, 114, 23, @on
	exec sp_trace_setevent @TraceID, 114, 26, @on
	exec sp_trace_setevent @TraceID, 114, 28, @on
	exec sp_trace_setevent @TraceID, 114, 29, @on
	exec sp_trace_setevent @TraceID, 114, 34, @on
	exec sp_trace_setevent @TraceID, 114, 35, @on
	exec sp_trace_setevent @TraceID, 114, 37, @on
	exec sp_trace_setevent @TraceID, 114, 40, @on
	exec sp_trace_setevent @TraceID, 114, 41, @on
	exec sp_trace_setevent @TraceID, 114, 44, @on
	exec sp_trace_setevent @TraceID, 114, 49, @on
	exec sp_trace_setevent @TraceID, 114, 50, @on
	exec sp_trace_setevent @TraceID, 114, 51, @on
	exec sp_trace_setevent @TraceID, 114, 59, @on
	exec sp_trace_setevent @TraceID, 114, 60, @on
	exec sp_trace_setevent @TraceID, 114, 64, @on
	 
	-- Set the Filters
	declare @intfilter int
	declare @bigintfilter bigint

	--DatabaseID(3) が ユーザデータベース(5) 以上(4)をトレース対象に
	set @intfilter = 5
	exec sp_trace_setfilter @TraceID, 3, 0, 4, @intfilter

--	exec sp_trace_setfilter @TraceID, 35, 0, 1, N'distribution'

--	IntelliSenseは対象外に
	exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense'

--	ドメインユーザを監査対象にする場合
--	exec sp_trace_setfilter @TraceID, 7, 0, 6, N'ドメイン名'

--	7:NTDomainName 6:NTUserNameがNULLではない
	exec sp_trace_setfilter @TraceID, 7, 0, 1, NULL
	exec sp_trace_setfilter @TraceID, 6, 0, 1, NULL

--	オブジェクトの所有者がdboのもの
	exec sp_trace_setfilter @TraceID, 37, 0, 6, N'dbo'

--	クライアントの SQL Server ログイン名と 等しくない
--	exec sp_trace_setfilter @TraceID, 11, 0, 1, N'SQLServerの実行ユーザ'

--	セッションを開始したユーザーのログイン名と 等しくない
--	exec sp_trace_setfilter @TraceID, 64, 0, 1, N'SQLServerの実行ユーザ'
	exec sp_trace_setfilter @TraceID, 64, 0, 1, NULL

	-- Set the trace status to start
	exec sp_trace_setstatus @TraceID, 1

	--停止フラグが立っている時は、前回のトレースを削除する
	IF @stopfg = 1 
	BEGIN
		-- トレース一時停止
		EXEC sp_trace_setstatus @tid, 0

		-- トレース削除
		EXEC sp_trace_setstatus @tid, 2
	END

	select TraceID=@TraceID
	goto finish

	error: 
	SELECT @ErrorMessage = 'ErrorCode=' + @rc 

	RAISERROR ( @ErrorMessage , 16, 1)
	select ErrorCode=@rc

	finish: 

END TRY

BEGIN CATCH
	SET @ErrorMessage  = ERROR_MESSAGE()
	SET @ErrorSeverity = ERROR_SEVERITY()
	SET @ErrorState    = ERROR_STATE()
	RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)

END CATCH

go

 

3.SP_USRAuditTraceCheckを作成します。

use msdb
go

CREATE PROCEDURE [dbo].[SP_USRAuditTraceCheck]
AS

DECLARE
  @ErrorMessage   varchar(2000)
 ,@ErrorSeverity  tinyint
 ,@ErrorState     tinyint

BEGIN TRY

	--トレースファイル名
	DECLARE @sttime varchar(100)
	DECLARE @edtime varchar(100)
	DECLARE @interval int
	DECLARE @MSG varchar(4000)

	DECLARE @HostName varchar(100)
	DECLARE @LoginName varchar(100)
	DECLARE @ServerName varchar(100)
	DECLARE @DatabaseName varchar(100)
	DECLARE @ObjectName varchar(100)
	DECLARE @ACnt varchar(10)
	DECLARE @SqlCMD1 varchar(3000)
	DECLARE @SqlCMD2 varchar(3000)

	declare @basefile varchar(50)
	set @basefile='USRAuditTrace'

	declare @fName sysname
	select @fName = 'G:\work\tracelog\' + @basefile


	declare @tid int 
	declare @tfnmae varchar(1000)

	SELECT distinct @tid=traceid, @tfnmae=convert(varchar(1000),value) FROM ::fn_trace_getinfo(default)
	where convert(varchar(1000),value) like '%' + @fName + '%'

	SET @MSG =''
	--トレースしていない場合
	IF @tid is null 
	BEGIN
		SET @MSG = 'DB監査メッセージ:監査が行われていません。'
		RAISERROR ('%s', 0, 1 ,@MSG ) WITH LOG
		--終了
		RETURN
	END

	--チェック間隔
	set @interval = -5

	--調査対象 (分)
	set @edtime = convert(varchar(16),GETDATE(),120)
	set @sttime = convert(varchar(16),DATEADD(minute,@interval,@edtime),120)

	--ファイル名に追加する日付を作成
	declare @d1 char(8)
	declare @d2 char(8)
	SET @d1 = convert(char(8) ,convert (date,@edtime), 112)
	SET @d2 = convert(char(8) ,convert (date,@sttime), 112)

	--日付がまたがっているか
	set @SqlCMD1 =''
	IF @d1 <> @d2
	BEGIN
		--前日分
		SET @SqlCMD1=           'select '
		SET @SqlCMD1=@SqlCMD1 + '  ISNULL(HostName,'''') AS ''アクセス元'' '
		SET @SqlCMD1=@SqlCMD1 + ', ISNULL(LoginName,'''') AS ''アクセスユーザー'' '
		SET @SqlCMD1=@SqlCMD1 + ', ISNULL(ServerName,'''') AS ''アクセス先'' '
		SET @SqlCMD1=@SqlCMD1 + ', ISNULL(DatabaseName,'''') AS ''アクセス先DB'' '
		SET @SqlCMD1=@SqlCMD1 + ', ISNULL(ObjectName,'''') AS ''オブジェクト'' '
		SET @SqlCMD1=@SqlCMD1 + ', convert(varchar(10),COUNT(*)) AS ''回数'' '
		SET @SqlCMD1=@SqlCMD1 + ' from ::fn_trace_gettable('''+@fName   +       @d2       +'.trc'',default) '
		SET @SqlCMD1=@SqlCMD1 + 'Where EventClass = 114 '
		SET @SqlCMD1=@SqlCMD1 + 'and convert(datetime,StartTime) >= ''' + @sttime + ''' '
		SET @SqlCMD1=@SqlCMD1 + 'and  convert(datetime,StartTime) < ''' + @edtime + ''' '
		SET @SqlCMD1=@SqlCMD1 + 'group by HostName,LoginName,ServerName,DatabaseName,ObjectName '
		SET @SqlCMD1=@SqlCMD1 + ' UNION ALL '
	END

	--本日分
	SET @SqlCMD2=           'select '
	SET @SqlCMD2=@SqlCMD2 + '  ISNULL(HostName,'''') AS ''アクセス元'' '
	SET @SqlCMD2=@SqlCMD2 + ', ISNULL(LoginName,'''') AS ''アクセスユーザー'' '
	SET @SqlCMD2=@SqlCMD2 + ', ISNULL(ServerName,'''') AS ''アクセス先'' '
	SET @SqlCMD2=@SqlCMD2 + ', ISNULL(DatabaseName,'''') AS ''アクセス先DB'' '
	SET @SqlCMD2=@SqlCMD2 + ', ISNULL(ObjectName,'''') AS ''オブジェクト'' '
	SET @SqlCMD2=@SqlCMD2 + ', convert(varchar(10),COUNT(*)) AS ''回数'' '
	SET @SqlCMD2=@SqlCMD2 + ' from ::fn_trace_gettable('''+@fName   +       @d1       +'.trc'',default) '
	SET @SqlCMD2=@SqlCMD2 + 'Where EventClass = 114 '
	SET @SqlCMD2=@SqlCMD2 + 'and convert(datetime,StartTime) >= ''' + @sttime + ''' '
	SET @SqlCMD2=@SqlCMD2 + 'and  convert(datetime,StartTime) < ''' + @edtime + ''' '
	SET @SqlCMD2=@SqlCMD2 + 'group by HostName,LoginName,ServerName,DatabaseName,ObjectName '

	exec('DECLARE curtables CURSOR FAST_FORWARD FOR '+
	@SqlCMD1 + @SqlCMD2 +
	' order by ISNULL(HostName,''''),ISNULL(LoginName,''''),ISNULL(ServerName,''''),ISNULL(DatabaseName,''''),ISNULL(ObjectName,'''') ')

	OPEN curtables

	-- Loop through all the curtables in the database
	FETCH NEXT
	FROM curtables
	INTO @HostName, @LoginName, @ServerName, @DatabaseName, @ObjectName, @ACnt

	IF @@FETCH_STATUS = 0
	BEGIN
		SET @MSG = 'DB監査メッセージ:' + @sttime + '~' + @edtime  + NCHAR(13) + NCHAR(10) + 'アクセス元,アクセスユーザー,アクセス先,アクセス先DB,オブジェクト,回数' + NCHAR(13) + NCHAR(10)
	END


	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @MSG = @MSG + @HostName + ',' + @LoginName + ',' + @ServerName + ',' + @DatabaseName + ',' + @ObjectName + ',' + @ACnt + NCHAR(13) + NCHAR(10)

		FETCH NEXT
		FROM curtables
		INTO @HostName, @LoginName, @ServerName, @DatabaseName, @ObjectName, @ACnt

	END

	-- Close and deallocate the cursor
	CLOSE curtables
	DEALLOCATE curtables

	IF @MSG <> ''
	BEGIN
		RAISERROR ('%s', 0, 1 ,@MSG ) WITH LOG
	END

END TRY

BEGIN CATCH
	SET @ErrorMessage  = ERROR_MESSAGE()
	SET @ErrorSeverity = ERROR_SEVERITY()
	SET @ErrorState    = ERROR_STATE()
	RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)

END CATCH
go

 

4.「監査」SQLジョブ作成します。

※このジョブは5分おきに起動されます。

USE [msdb]
GO

/****** Object:  Job [監査]    Script Date: 2015/05/08 17:56:22 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2015/05/08 17:56:22 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'監査', 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'使用できる説明はありません。', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [監査トレースの実施確認]    Script Date: 2015/05/08 17:56:23 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'監査トレースの実施確認', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=3, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC [dbo].[SP_USRAuditTrace]
', 
		@database_name=N'msdb', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [アクセスをチェック]    Script Date: 2015/05/08 17:56:23 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'アクセスをチェック', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'exec [SP_USRAuditTraceCheck]', 
		@database_name=N'msdb', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'スケジュール', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=5, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20150128, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

 

5.「監査ログ削除」SQLジョブ作成します。

※毎日0:01に起動します。

G:\work\tracelog配下の90日以上経過した、*.trcファイルを削除します。

forfiles /s /d -90 /m *.trc /p G:\work\tracelog  /c “cmd /c del @path”

USE [msdb]
GO

/****** Object:  Job [監査ログ削除]    Script Date: 2015/05/08 17:56:55 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2015/05/08 17:56:55 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'監査ログ削除', 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'使用できる説明はありません。', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [削除]    Script Date: 2015/05/08 17:56:55 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'削除', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'CmdExec', 
		@command=N'forfiles /s /d -90 /m *.trc /p G:\work\tracelog  /c "cmd /c del @path"
', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'スケジュール', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20150129, 
		@active_end_date=99991231, 
		@active_start_time=100, 
		@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

 

イベントログにアクセスした内容が記録されました。
auditlog


その他
トレース中の情報を確認

SELECT * FROM :: fn_trace_getinfo(default)

trace_1

 


 

削除する場合

SPとジョブを削除します。

USE msdb
GO
DROP PROCEDURE [dbo].[SP_USRAuditTrace]
GO
DROP PROCEDURE [dbo].[SP_USRAuditTraceCheck]
go

sp_delete_job @job_name = '監査'
go
sp_delete_job @job_name = '監査ログ削除'
go

 


管理人 has written 30 articles