Audit trail: Web application

Question

I am working on an audit trail project where we have been told to following.

Track all the tables (200+) in our db with shadow tables just as Hibernate Envers does. It follows that we have create snapshot for a each transactions involving CUD.

In the past, I implemented audit solutions for finite sets of important data for each of my clients. For the current work, my questions are:

1. Does it make sense to audit every single table in the database?
2. How much of value would it be track the data like Envers does? Any application would want to have deltas for specific data points. Querying huge sets of data to figure out deltas seem to be unrealistic.
3. An Envers like solution requires tying up CUD actions with a transaction effectively ruling out triggers. This is because triggers run in their own transactions and hence the data in shadow tables can get out of sync in case of transaction rollbacks from the application. Anything I am missing here?
4. Does anybody suggest using NoSQL DBs for audit trail?

Answer 1

Fully implemented and can be improved more. I hope this may help someone :

public partial  class Entity:DbContext
    {

      public enum AuditActions {I,U,D}

      public override int SaveChanges( )
      {
          ChangeTracker.DetectChanges(); 
          ObjectContext ctx = ((IObjectContextAdapter)this).ObjectContext;
          // string UserName = WindowsIdentity.GetCurrent().Name;
          IPrincipal principal = Thread.CurrentPrincipal;
          IIdentity identity = principal == null ? null : principal.Identity;
          string name = identity == null ? "" : identity.Name;

          //Thread.CurrentPrincipal = new GenericPrincipal(new GenericIdentity((userName), roles);
          List<ObjectStateEntry> objectStateEntryList =
              ctx.ObjectStateManager.GetObjectStateEntries(EntityState.Added
                                                         | EntityState.Modified
                                                         | EntityState.Deleted)
              .ToList();

          List<DBAudit> AuditList = new List<DBAudit>();

          string Audittable = string.Empty; 
          foreach (ObjectStateEntry entry in objectStateEntryList)
          {
               Audittable = entry.EntitySet.ToString();

               if (!entry.IsRelationship && Audittable!="Audit table name")
              {

                  //sIsAuditTble =entry.EntitySet="DBAudit"? true:false;
                  switch (entry.State)
                  {
                      case EntityState.Added:
                        AuditList=  LogDetails(entry, name, AuditActions.I);
                          break;
                      case EntityState.Deleted:
                        AuditList=  LogDetails(entry, name, AuditActions.D);
                          break;
                      case EntityState.Modified:
                        AuditList=  LogDetails(entry, name, AuditActions.U);
                           break;

                  }
              }
          }



              using (var context = new ProjectTrackerEntities())
              {
                  for (int i = 0; i < AuditList.Count; i++)
                  {
                      context.DBAudits.Add(AuditList[i]);
                      context.SaveChanges();
                  }
              }

          return base.SaveChanges();
      }

      public List<DBAudit> LogDetails(ObjectStateEntry entry, string UserName, AuditActions action)
      {
          List<DBAudit> dbAuditList = new List<DBAudit>();

        if (action == AuditActions.I)
          {

              var keyValues = new Dictionary<string, object>();
              var currentValues = entry.CurrentValues;

             // entry.Entity key = new EntityKey();

                  DBAudit audit = new DBAudit();
                  audit.AuditId = Guid.NewGuid().ToString();
                  audit.RevisionStamp = DateTime.Now;
                  audit.TableName = entry.EntitySet.Name;
                  audit.UserName = UserName;
                  audit.OldData = "";
                  audit.Actions = action.ToString();
                  for (int i = 0; i < currentValues.FieldCount; i++)
                  {
                  audit.ChangedColumns = audit.ChangedColumns + currentValues.GetName(i);
                  audit.NewData = audit.NewData + currentValues.GetValue(i);
                  audit.ChangedColumns = audit.ChangedColumns + ", ";
                  audit.NewData = audit.NewData + ", ";
                  }
                  dbAuditList.Add(audit);
                  //LogSave(audit);




          }
          else if (action == AuditActions.D)
          {
              var keyValues = new Dictionary<string, object>();
              var DeletedValues = entry.OriginalValues;

              // entry.Entity key = new EntityKey();


              DBAudit audit = new DBAudit();
              audit.AuditId = Guid.NewGuid().ToString();
              audit.RevisionStamp = DateTime.Now;
              audit.TableName = entry.EntitySet.Name;
              audit.UserName = UserName;
              audit.NewData = "";

              audit.Actions = action.ToString();
              for (int i = 0; i < DeletedValues.FieldCount; i++)
              {
                  audit.ChangedColumns = audit.ChangedColumns + DeletedValues.GetName(i);
                  audit.OldData = audit.OldData + DeletedValues.GetValue(i);
                  audit.ChangedColumns = audit.ChangedColumns + ", ";
                  audit.OldData = audit.OldData + ", ";
              }
              dbAuditList.Add(audit);
          }
          else 
          {

                  foreach (string propertyName in entry.GetModifiedProperties())
                  {
                      DBAudit audit = new DBAudit();
                      DbDataRecord original = entry.OriginalValues;
                      string oldValue = original.GetValue(original.GetOrdinal(propertyName)).ToString();

                      CurrentValueRecord current = entry.CurrentValues;
                      string newValue = current.GetValue(current.GetOrdinal(propertyName)).ToString();

                      audit.AuditId = Guid.NewGuid().ToString();
                      audit.RevisionStamp = DateTime.Now;
                      audit.TableName = entry.EntitySet.Name;
                      audit.UserName = UserName;
                      audit.ChangedColumns = propertyName;
                      audit.OldData = oldValue;
                      audit.NewData = newValue;
                      audit.Actions = action.ToString();
                      dbAuditList.Add(audit);
                      //LogSave(audit);


                    }

          }

        return dbAuditList;


      }



    }