Auditing Changes under MVC & Entity Framework (using sprocs)

Question

I have the challenge of needing to audit data changes made by users of an MVC application.

Auditing creation and deletion of records is easy.

Updates is proving to be the problem.

I'm looking for a way to automate this, but the problem I have is that the application is using stored procedures to bring back EF "complex types".

These are then used to build a view model, and after postback, the controller receives a new view model built from the form values passed back from the view. Therefore the original values are no longer available.

Does anyone have any suggestions for a secure way to keep the original values so they can be compared with the updated values, so that changes can be stored?

(I appreciate I could go back to the database for these, but is not efficient, and I would have to retain all the parameters to remake the same call, and find a way to automate that part of the process).

score 0 · Answer 1 · edited May 23 '17 at 10:27

If using SQL Server I recommend that you look into Change Data Capture (CDC).

It's an out of the box solution for auditing changes to the underlying tables of your application and it's relatively straightforward to set up, so there is no need for a custom solution that you then have to maintain.

If you have any supporting applications for your site, they'll also be covered and it also has the benefit of auditing any changes made directly against the database, such as from a DBA running a script.

Since your asp.net application may be running under one particular account, you'll probably need to add additional tracking information to capture the user who made the change. Fortunately this is also relatively straightforward. The following Stack Overflow question covers an approach to this using the ObjectStateManager

score 0 · Answer 2 · answered Nov 22 '15 at 06:43

0

I was lookging for this myself, found this, check out Tracker for EF

answered Nov 22 '15 at 06:43

aggie

798
2
8
23

Louis Ricci · Answer 3 · 2011-12-01T21:00:26.513

Have you tried an Audit Trigger using the INSERTED and DELETED tables. http://weblogs.asp.net/jgalloway/archive/2008/01/27/adding-simple-trigger-based-auditing-to-your-sql-server-database.aspx

OR

In your stored procedures for insert,delete,update you can make use FOR XML AUTO. To get the XML for the record and add it to an audit table. http://www.a2zdotnet.com/View.aspx?Id=71

UPDATE A T-SQL example

BEGIN
-- these tables would be in your database
DECLARE @table TABLE(ID INT IDENTITY(1,1) PRIMARY KEY, STR VARCHAR(10), DT DATETIME)
DECLARE @audit_table TABLE(AuditXML XML, Type VARCHAR(10), Time DATETIME)
-- this is defined at the top of your stored procedure
DECLARE @temp_table TABLE(PK INT)
-- your stored procedure will add an OUTPUT to the temp table
INSERT INTO @table 
    OUTPUT inserted.ID INTO @temp_table
    VALUES  ('test1', GetDate()),
            ('test2', GetDate() + 2)
-- at the end of your stored procedure update your audit table
INSERT INTO @audit_table
    VALUES(
        (
            SELECT * 
                FROM @table 
                WHERE ID IN (SELECT PK FROM @temp_table) 
                FOR XML AUTO
        ), 
        'INSERTION', 
        GETDATE()
    )
-- your audit table will have the record data
SELECT * FROM @audit_table
END

In the example above you could make temp_table a clone of table (have all of the columns from table) and in your OUTPUT clause use INSERTED.* INTO @temp_table, this would avoid have to reselect the records before getting the FOR XML AUTO. Another note, for stored procedures that do DELETE you would use DELETED.* instead of INSERTED.* in your OUTPUT.

In our environment, triggers aren't allowed so the For XML sounds like an option, unfortunately the article doesn't elaborate on how to use it for auditing — CD Smith, Dec 01 '11 at 15:39

Auditing Changes under MVC & Entity Framework (using sprocs)

3 Answers3