01 October, 2008 / TargetProcess Development Tricks: Setting the custom context to MS SQL Connection

1 comments

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).

1 Comments:

At December 21, 2008 12:50 AM, Blogger Steve Bering said...

You are setting the security context for the connection, but doesn't this connection get shared between multiple user sessions in a web scenario? Don't you have to set this context on each insert/update/delete query?

 

Post a Comment

Links to this post:

Create a Link

<< Home

 

We are developing TargetProcess agile project management software and blogging about our progress.

Subscribe to the RSS feed
Stay tuned by having the latest updates via RSS
Follow TargetProcess on Twitter
Get in touch with our team

Try TargetProcess
TargetProcess quick tour