トレース機能を利用して、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ジョブ
１．トレースログを出力するためのフォルダを準備します。
G:\work\tracelog
２．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
３．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
４．「監査」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
５．「監査ログ削除」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
イベントログにアクセスした内容が記録されました。
その他
トレース中の情報を確認
SELECT * FROM :: fn_trace_getinfo(default)
削除する場合
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
