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

How to write secure mvc application using encrypting URL

There are lots ways available to write the secure Mvc application. In my experience I came across lots of secure application in public facing. I want to tell about some of the mechanism I followed.
1- encrypting the URL Parameters and preserving id's encrypted on the client side
1- I always make sure if I pass Id or any sensitive data into the view always make sure it's encrypted. By doing so make sure if we forced to use HTML.hidden or HTML.hidden for have the encrypted values, in action link if I pass any parameters from the client side (eg:- edit or create or navigating between different actions we can make sure that all the values are encrypted)
During the design of the actions results if it's http get I usually encrypt the sensitive data
[httpget]
Public actionresult display()
{
TestModel testModel=new TestModel();
testModel.id= encrypt(id);
Return View(testModel);
}
[httppost]
Public ActionResult Display(string id)
{
Guid d_id= new Guid(decrypt(id));
// do operati…
Post a Comment
Read more

How to add a stored procedure in Entity Framework

Step1- Open the .edmx file
Step2- Right click in .edmx file and select Update Model from Database


Step3- Select Add tab, select Stored Procedures and select the procedure you want to add and click finish as shown in below figure a

Step4- Build the Entity Project to make sure nothing is broke

Step5- Right click on .edmx file and  select Model Browser



Step6- Model Browser Popup will appear as  below figure. Click on the Strored Procedures in PricingToolModel.Stroe and click the newly added SP (it will appear here)
Step7- The Add Function Import screen will appear

Based on the return we can set up.

If you are returning a multiple column Click on the Get Column Information First, then click the new Complex Type. After that you can see the Return of Collection will select the Complex and a complex type is generated by EF

If its returning scalar you can select which return type like int, string etc...

If its return entitty (means table) please select the correct table from the list..

Click…
Post a Comment
Read more

Bootstrap Server Side Pagination - alternative to data tables

BackgroundMost of the Web developers are familiar with the Datatable.Net for pagination. DataTables can integrate seamlessly with Bootstrap using Bootstrap's table styling options to present a consistent interface with your Bootstrap driven site / app. The main disadvantage with Data table.net is it is applying the pagination at the client side and for the large data set it can take minutes to load the data. Like every other designer I also faced the problem with Data table.net. So what is the best option to replace the client side paging and searching. So I forced to create a custom module to do the pagination and searching. The below sample don’t support the sorting, but got an option to extend the functionality. Please check my Next Post for sorting functionality The technologies used include, MVC 4.0, HTML5, Bootstarp.css, Knockout. js, Ajax, Web Api 2.0, Linq to SQL and C#. Knockout.js Model’s Filter Modelvar filter = function () { var self = this; self.searchText = ko.observable(…
Post a Comment
Read more