How to do Auditing in web application with Context Info

Background 

Last couple of days I had issues while trying to implement the audit in one of the asp.net application.  Especially if tables don’t have the structure to accommodate who modified or when modified fields. So what will do? Is it practical to add all this fields in tables and implement auditing? Yes if you have 5 or 10 tables. What happens if there are 100’s of tables?

 I came across this issue. Fortunately some one told about the ContextInfo. Before starting a database connection set the context info with the current user details. And when the operation finishes call a trigger and update the auditing table with contextInfo.

What time context info update?

For all db operation a connection need to open. So conextinfo can be set after the open connection.

 create an OpenConnection() method in the base class which all Execute…() methods call, instead of calling cmd.Connection.Open() directly. Then call SetConext method there.

Implementation

1. OpenConnection

internal override void OpenConnection(IDbConnection con)

        {

            con.Open();

            //For Auditing purpose

            SetContext(con as SqlConnection);

        }

2. SetContext

protected virtual void SetContext(IDbConnection conn)

        {

            string currentUserName = GetCurrentUserName();

            string spName = "sp_set_context";

            if (conn != null)

            {

                if (conn.State != ConnectionState.Open)

                    conn.Open();

              

                IDbCommand cmd = conn.CreateCommand();

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.CommandText = spName;

                IDbDataParameter param = cmd.CreateParameter();

                param.ParameterName = "@username";

                param.DbType = DbType.String;

                param.Size = 255;

                param.Value = currentUserName;

                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();

            }

        }

3. sp_set_current_context

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_set_context]

      @username nvarchar(256)

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

    declare @Ctx varbinary(128)

     

      select @Ctx = convert(varbinary(128), @username)

      set context_info @Ctx

END

GO

4.Function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[f_get_current_user]()
RETURNS uniqueIdentifier
AS
BEGIN

DECLARE @Username nvarchar(256)
DECLARE @UserId uniqueIdentifier
DECLARE @Ctx varbinary(128)
SELECT @Ctx = CONTEXT_INFO()
SELECT @Username = CAST( @Ctx AS nvarchar(256)) 
IF (@Username is null or @Username = '') select @Username = SYSTEM_USER

SELECT @UserId=UserId from Users where UserName=@Username;

RETURN @UserId
END

5.Trigger

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[TR_audit_log] ON [dbo].[TableName] FOR INSERT, DELETE, UPDATE

AS

BEGIN

      SET NOCOUNT ON;

      IF TRIGGER_NESTLEVEL(OBJECT_ID('TR_audit_log')) > 1 RETURN

      DECLARE @user_key uniqueidentifier, @tp INT = 0

      IF EXISTS(SELECT 1 FROM deleted) SET @tp = @tp + 1

      IF EXISTS(SELECT 1 FROM inserted) SET @tp = @tp + 2

     

      DECLARE @i TABLE (Id int, Name nvarchar(100), audit_data VARCHAR(MAX),

                  PRIMARY KEY (Id))

      DECLARE @d TABLE (Id int, Name nvarchar(100), audit_data VARCHAR(MAX),

                  PRIMARY KEY (Id))

      INSERT INTO @i SELECT Id, Name,

            (SELECT Id, Name

                        FOR XML RAW('audit'))

      FROM inserted

      INSERT INTO @d SELECT Id, Name,

            (SELECT Id, Name

                        FOR XML RAW('audit'))

      FROM deleted

     

      IF @tp = 2

      BEGIN

            INSERT INTO audit_log ()

      END ELSE IF @tp = 1

      BEGIN

            INSERT INTO audit_log ()

      END ELSE

      BEGIN

            INSERT INTO audit_log (

)

                  END

      SET NOCOUNT OFF;

END

GO