使用temporal表和session_context（SQL Server内存变量）在表上实现审计。 [英] Implementing audit on table using temporal table and session_context(SQL Server memory variables).

问题描述

使用temporal表和session_context（SQL Server内存变量）在表上实现审计。

我正在尝试构建，使用temporal和session_context审计表（SQL Server内存变量）。

请参阅下面的主表和时态表的配置，我的应用程序只会传递列[name]，并会为AppLogin设置SQL Session内存可用（用户用于登录）使用会员上下文为AUDIT_USER [  CONSTRAINT [DF_AUDIT_USER]


   DEFAULT（isnull（CONVERT（[nvarchar]，session_context（N'AppLogin'）），''）））
   FOR [AUDIT_USER]]

当应用程序将记录插入员工时，Audit_user会自动从默认值[session_context（N'AppLogin'）]填充。

问题在于，我希望从会话上下文（SQL内存变量）填充列audit_user进行更新，我已在employee上创建触发器以进行更新并使用会话更新主表context [session_context（N'AppLogin'）]但它也不起作用。

对此提出任何建议。

- 1）主表创建和配置。

DROP TABLE IF EXISTS [dbo]。[EMPLOYEE]

GO

CREATE TABLE [dbo]。[员工]（NAME VARCHAR（50），[AUDIT_USER] NVARCHAR（100），

  CONSTRAINT [PK_EMPLOYEE] PRIMARY KEY CLUSTERED

 （

  [NAME] ASC

 ）

）

GO

ALTER TABLE [dbo]。[EMPLOYEE]  

   ADD  

      AUDIT_DT datetime2（0）生成总是开始隐藏约束DF_ClientBeacon_SysStartTime DEFAULT SYSUTCDATETIME（），

      AUDIT_DT1 datetime2（0）生成总是结束隐藏约束DF_ClientBeacon_SysEndTime DEFAULT CONVERT（datetime2（0），'9999-12-31 23:59:59'），     


      SYSTEM_TIME的周期（AUDIT_DT，AUDIT_DT1），

   CONSTRAINT [DF_AUDIT_USER]

   DEFAULT（isnull（CONVERT（[nvarchar]，session_context（N） 'AppLogin'）），''）））
   FOR [AUDIT_USER]

   

GO  

ALTER TABLE [dbo]。[EMPLOYEE] SET（SYSTEM_VERSIONING = ON（HISTORY_TABLE = dbo.EMPLOYEE_HIST））;

GO

- 1）将记录插入主表

EXEC sys.sp_set_session_context

  @key = N'AppLogin'，

  @value = N'SIVA'，

  @read_only = 0;

GO

选择session_context（N'AppLogin'）

GO

SELECT * FROM [dbo]。[EMPLOYEE]

GO

INSERT [dbo]。[EMPLOYEE]（NAME）VALUES（'siva'）

SELECT * FROM [dbo ]。[员工]

GO

EXEC sys.sp_set_session_context

  @key = N'AppLogin'，

  @value = N'Kumar'，

  @read_only = 0;

GO

选择session_context（ N'AppLogin'）

GO

INSERT [dbo]。[EMPLOYEE]（NAME）VALUES（'kumar'）

SELECT * FROM [dbo]。[EMPLOYEE]

GO - 一切顺利

- 3） 更新员工表以更改名称

EXEC sys.sp_set_session_context

  @key = N'AppLogin'，

  @value = N'SIVAu'，

  @read_only = 0;

GO

选择session_context（ N'AppLogin'）

GO

更新[dbo]。[EMPLOYEE]

Set name ='Sivau'
其中name ='Siva'

GO

INSERT [dbo]。[EMPLOYEE]（NAME）VALUES（'siva' ）

SELECT * FROM [dbo]。[EMPLOYEE]

GO

EXEC sys.sp_set_session_context

  @key = N'AppLogin'，

  @value = N'Kumaru'，

  @read_only = 0;

GO

选择session_context（N'AppLogin'）

GO

更新[dbo ]。[员工]

设置名称='Kumaru'

其中name ='Kumar'

从[dbo]中选择*。[EMPLOYEE_HIST]

解决方案

Hi
Sivakumar.In，

 

感谢您的发帖。

 

我尝试了你的脚本，我认为它可以很好地工作。你能否分享一下你当前的结果和预期的结果？这样我就能找到问题。

< p style ="margin：0in; FONT-FAMILY：宋体; font-size：11.0pt"> 

最好的问候，

Rachel

Implementing audit on table using temporal table and session_context(SQL Server memory variables).

I am trying to build, audit for a table using temporal and session_context(SQL Server memory variables).

See below my main table and the configuration for temporal table, my applications only will pass column [name] and will set SQL Session memory vriable for AppLogin(User used to login to the application).

I have created default constraint using session context for column AUDIT_USER [ CONSTRAINT [DF_AUDIT_USER]
  DEFAULT (isnull(CONVERT([nvarchar],session_context(N'AppLogin')),''))
  FOR [AUDIT_USER]]

While applications inserts records into employee then Audit_user automatically populated from the default value[session_context(N'AppLogin')].

The issue is that, I want populate column audit_user from session context(SQL memory variable) for update, I have created trigger on employee for update and updated main table using session context [session_context(N'AppLogin')] but it did not work either.

Any advise on this.

--1) Main table creation and configuration.

DROP TABLE IF EXISTS [dbo].[EMPLOYEE]
GO
CREATE TABLE [dbo].[EMPLOYEE] (NAME VARCHAR(50), [AUDIT_USER] NVARCHAR(100),
 CONSTRAINT [PK_EMPLOYEE] PRIMARY KEY CLUSTERED
 (
 [NAME] ASC
 )
)
GO

ALTER TABLE [dbo].[EMPLOYEE]  
   ADD  
      AUDIT_DT datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_ClientBeacon_SysStartTime DEFAULT SYSUTCDATETIME(),
      AUDIT_DT1 datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_ClientBeacon_SysEndTime DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),     
      PERIOD FOR SYSTEM_TIME (AUDIT_DT, AUDIT_DT1),
  CONSTRAINT [DF_AUDIT_USER]
  DEFAULT (isnull(CONVERT([nvarchar],session_context(N'AppLogin')),''))
  FOR [AUDIT_USER]
   
GO  

ALTER TABLE [dbo].[EMPLOYEE] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EMPLOYEE_HIST));
GO

--1) Inserting records into Main table

EXEC sys.sp_set_session_context
 @key = N'AppLogin' ,
 @value = N'SIVA' ,
 @read_only = 0;
GO
Select session_context(N'AppLogin')
GO

SELECT * FROM [dbo].[EMPLOYEE]
GO

INSERT [dbo].[EMPLOYEE] (NAME) VALUES ('siva')
SELECT * FROM [dbo].[EMPLOYEE]
GO

EXEC sys.sp_set_session_context
 @key = N'AppLogin' ,
 @value = N'Kumar' ,
 @read_only = 0;
GO
Select session_context(N'AppLogin')
GO

INSERT [dbo].[EMPLOYEE] (NAME) VALUES ('kumar')
SELECT * FROM [dbo].[EMPLOYEE]
GO -- All well as expected

--3)  Update Employee tables to change name

EXEC sys.sp_set_session_context
 @key = N'AppLogin' ,
 @value = N'SIVAu' ,
 @read_only = 0;
GO
Select session_context(N'AppLogin')
GO

Update [dbo].[EMPLOYEE]
Set name = 'Sivau'
Where name = 'Siva'

GO

INSERT [dbo].[EMPLOYEE] (NAME) VALUES ('siva')
SELECT * FROM [dbo].[EMPLOYEE]
GO

EXEC sys.sp_set_session_context
 @key = N'AppLogin' ,
 @value = N'Kumaru' ,
 @read_only = 0;
GO
Select session_context(N'AppLogin')
GO

Update [dbo].[EMPLOYEE]
Set name = 'Kumaru'
Where name = 'Kumar'

Select * from [dbo].[EMPLOYEE_HIST]

解决方案

Hi Sivakumar.In,

 

Thank you for your posting .

 

I tried your script and I think it can work well. Could you please share us your current result and your expected result ? So that I could find the issue .

 

Best Regards,

Rachel

这篇关于使用temporal表和session_context（SQL Server内存变量）在表上实现审计。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！