We are developing the new audit history mechanism. SQL triggers were added. They do the shadow copy of added/deleted/updated data in important tables such as user story, project. The problem is that we need the custom context in these triggers implementation such as logged user and client date. In other words we need to set some custom info into the connection session before any change.
The code below shows how to set and extract context on MS SQL side:
GO CREATE PROCEDURE setTpCnt @userID INT, @clientDate DATETIME AS DECLARE @BinVar varbinary(128) SET @BinVar = CAST(CAST(@userID as nvarchar(20)) + '_' + CONVERT(nvarchar(100), @clientDate, 13) + '_' AS varbinary(128)) SET CONTEXT_INFO @BinVar GO CREATE FUNCTION f_GetLoggedUserID() RETURNS INT AS BEGIN DECLARE @CONTEXT AS NVARCHAR(120) SET @CONTEXT = NULL SELECT @CONTEXT = CAST(CONTEXT_INFO AS NVARCHAR(120)) FROM master.dbo.sysprocesses WHERE spid = @@spid IF (@CONTEXT IS NULL) RETURN NULL RETURN CAST(SUBSTRING(@CONTEXT, 0, CHARINDEX('_', @CONTEXT)) as INT) END GO CREATE FUNCTION f_GetClientTime() RETURNS DATETIME AS BEGIN DECLARE @PAD_INDEX AS INT DECLARE @CONTEXT AS NVARCHAR(120) SET @CONTEXT = NULL SELECT @CONTEXT = CAST(CONTEXT_INFO AS NVARCHAR(120)) FROM master.dbo.sysprocesses WHERE spid = @@spid IF (@CONTEXT IS NULL) RETURN NULL SET @PAD_INDEX = CHARINDEX('_', @CONTEXT) SET @CONTEXT = SUBSTRING(@CONTEXT, @PAD_INDEX + 1, LEN(@CONTEXT) - @PAD_INDEX) SET @CONTEXT = SUBSTRING(@CONTEXT, 0, CHARINDEX('_', @CONTEXT)) RETURN CONVERT(DATETIME, @CONTEXT, 13) END GO
We can do the following things with the procedure and functions above
- We can set the context using stored procedure setTpCnt
- We can get the logged user anywhere using function f_GetLoggedUserID
- We can get the client time using the function f_GetClientTime
Now we need to set the context from our client. Only our client knows the logged user id and the date. We need somehow to the call of stored procedure setTpCnt in our C# client. We are using NHibernate. So we need to figure out how to pass the custom context information into every connection which is created by NHibernate.
Please find the solution below. We created the custom driver for NHibernate to make a call to stored procedure with setting required value:
#region using System; using System.Data; using System.Data.SqlClient; using NHibernate.Driver; using NHibernate.SqlCommand; using NHibernate.SqlTypes; using Tp.BusinessObjects.Components.Authentication; #endregion namespace Tp.BusinessObjects.Data { public class Driver : SqlClientDriver { private bool _isSecurityInjected; public override IDbConnection CreateConnection() { var connection = base.CreateConnection(); _isSecurityInjected = false; return connection; } public override IDbCommand GenerateCommand(CommandType type, SqlString sqlString, SqlType[] parameterTypes) { var command = base.GenerateCommand(type, sqlString, parameterTypes); if (_isSecurityInjected) return command; var commandText = command.CommandText; if (string.IsNullOrEmpty(commandText)) return command; if ((commandText.IndexOf("INSERT ", StringComparison.InvariantCultureIgnoreCase) < 0) && (commandText.IndexOf("UPDATE ", StringComparison.InvariantCultureIgnoreCase) < 0) && (commandText.IndexOf("DELETE ", StringComparison.InvariantCultureIgnoreCase) < 0)) { return command; } var userID = UserAuthentication.UserID; if (userID != null) { _isSecurityInjected = true; var text = @"EXEC setTpCnt @cnt_userID, @cnt_ClientDate" + Environment.NewLine; command.CommandText = text + commandText; command.Parameters.Add(new SqlParameter("@cnt_userID", userID)); command.Parameters.Add(new SqlParameter("@cnt_ClientDate", CurrentDate.Value)); } return command; } } }
Now we need to improve NHibernate configuration to include the driver created above. It should be done in the following way
Will keep you informed about other tricks (if have time for sure).