SQL Server 2016 Audit 搭配 Temporal 資料,傳回使用者查詢時所看到的資料

Terry 提了一個有趣的需求,回傳使用者查詢時所看到的資料

USE [master]
GO
CREATE SERVER AUDIT [Audit-20150917-085416]
TO FILE
(    FILEPATH = N’C:\temp’
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
)
GO
alter server audit [Audit-20150917-085416] with(state=on)
go

create database dbTemporal
–非 CDC 從 ldf 透過 log reader 讀交易紀錄放到 system table
alter database dbTemporal set recovery simple
go
use dbTemporal
go

–自行指定記錄時間歷程的對應資料表
create table tbOrg
(
    PK int IDENTITY NOT NULL PRIMARY KEY,
    c1 nvarchar(500),
    StartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, –Hidden 關鍵字會隱藏這個欄位
    DueTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, 
    PERIOD FOR SYSTEM_TIME (StartTime,DueTime) 
)
go

CREATE TABLE tbOrgHistory(
    PK int NOT NULL,
    c1 nvarchar(500) NULL,
    StartTime datetime2(7) NOT NULL,
    DueTime datetime2(7) NOT NULL
)
go

ALTER TABLE tbOrg SET (SYSTEM_VERSIONING = ON
(HISTORY_TABLE =  dbo.tbOrgHistory)
)
go

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20150917-085751]
FOR SERVER AUDIT [Audit-20150917-085416]
ADD(SELECT,INSERT,UPDATE,DELETE,EXECUTE ON database::dbTemporal BY public)
WITH (STATE = ON)
GO

–某個時間點查詢語法看到的資料
create proc spGetSnapshot @sql nvarchar(max),@time datetime2(7)
as
    set @sql += ‘ for system_time as of “‘ + convert(varchar(100),dateadd(hour,-8,@time)) + “" –轉成 UTC 時間
    exec(@sql)
go

———————————————————————————–
–Audit 搭配 Temporal 檢視回傳的資料
insert tbOrg(c1) values(‘a’)
waitfor delay ’00:00:01′
declare @t datetime2(7)=sysdatetime()
select * from tbOrg –模擬使用者查詢
waitfor delay ’00:00:01′
update tbOrg set c1=c1 + ‘1’
insert tbOrg(c1) values(‘d2’)
declare @sql nvarchar(max)

–Audit 也是用 UTC 時間
SELECT top(1) @sql=statement
FROM sys.fn_get_audit_file (‘C:\temp\Audit*.sqlaudit’,default,default)
where event_time=dateadd(hour,-8,@t) and object_name=’tbOrg’;
select @sql
exec spGetSnapshot @sql,@t

ALTER TABLE tbOrg SET (SYSTEM_VERSIONING = OFF)
drop table tbOrg
drop table tbOrgHistory

ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20150917-085751]
WITH (STATE = OFF)
GO
DROP DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20150917-085751]
GO

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: