Skip to main content

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





Comments

Post a Comment

Popular posts from this blog

Compiled Query- Improve the performance of Linq to Entity Query

Most of the small or medium IT firms are using the Entity framework for the Data Access layer (DAL). If we write a complex linq to Entity queries performance will always be an issue. But with the Compiled Query Performance can be improved. This below definitions are from MSDN and more details can be found on the MSDN Link that is at the end of this post           When you have an application that executes structurally similar queries many times in the Entity Framework, you can frequently increase performance by compiling the query one time and executing it several times with different parameters. For example, an application might have to retrieve all the QuoteRevision for a particular quotelineStatus, the quotelinestatus is specified at runtime. LINQ to Entities supports using compiled queries for this purpose.               The  compiled query class provides compilation and caching of queries for reuse . Co...
1 comment
Read more

Sql Server Internals - Named Pipes

Named pipes enables clients applications to request a reliable and two way connection between the client application and Sql Server, across a network. A named pipe has a name which follows the UNC naming convention : \\Server\pipe\name The default names pipe for SQL server is :                 \\Server\pipe\sql\query A named instance would have a named pipe of                \\server\pipe\MSSQL$instancename\sql\query Early versions of SQL Server only provided Named Pipes. Named Pipes also allows for impersonation of a clients credentials. The feature is used by SQL server when using Linked Servers. The clients logon credentials are passed across to the Linked Server for authentication. Named Pipes can operate over TCP/IP, NETBEUI or IPX as it indirectly relies on the Common Internet Files Sytems
Post a Comment
Read more

CV Preparation

An old saying, - First Impression is the best impression. When we are applying for the jobs, chances of getting interview depend mainly on CV . Each location has its own CV format. While applying for an UK based job you need to sent a standard UK  format CV (optional cases some organisations do not accepts CV ).  1 Header with Name, phone number and email address (avoid address) 2 Career Conspectus – Describe about your experience, what technology you familiar with, what domain you got experience etc 3. Technical Skills – Mention all the technical skills on this section (In good format, if necessary use tables) 4. Certifications - Mention about the Certifications passed. 5. Employment Chronicle- Mention about the professional experience (Most recent first).Include Project name/Client name  , a brief summary  of project, your responsibility (Means role in the project), and the environment(Which  technologies used) which the projects works 6....
2 comments
Read more