How do you audit your business objects?

How do you audit your business objects?

Old forum URL: forums.lhotka.net/forums/t/6116.aspx


bpb2221 posted on Friday, January 09, 2009

I am having a tough time trying to figure out how to implement a business object to audit other business objects.  We want to track changes to our business objects.  We have individual audit tables for each business object we want to track.

I have been reading all of the forum posts on auditing and I am very confused.  I know we can't add the calls to the Insert statements for auditing through the data portal in a business base class.  I also know that we need a read only business class for the auditing so that I can read from the DB.   From what I have gathered it would be better if we did not have auditing code in the business object we are auditing.

What I don't know is  what is the best way implement an object that does the audit record inserting.  I have read various methods such as inserting or calling the auditing stored procedure in any INSERT, UPDATE, or DELETE procedures that relate to the business object.  I have also read about using the Observer pattern and creating your own interface, which is confusing. 

How are you implementing an object(s) to track your business object auditing?

SonOfPirate replied on Friday, January 09, 2009

You do have many options and, you are right, it can be confusing which direction to take.

It sounds like you are already on the right track understanding that your auditor should be a separate entity from your other business objects.  The question then becomes how to communicate audit information between your business objects and your auditor.

If you are dealing with a pretty fixed application, you could code calls to the auditor in your business objects just as you would include Trace statements.  This becomes tedious if the project is pretty active and you have many developers working on it - trying to make sure everyone puts the necessary instrumentation in the code is always difficult.  There are ways through base classes and delegation that much of this can be abstracted, however.  In any event, going this route will require you to put calls into your business objects whenever you want an audit record created.

The Observer pattern frees you from having to do anything within your business objects to support auditing.  You would instead have to code your auditor so that it "listens to" or "observes" your business objects and writes audit information as appropriate.  You do this by wiring up event handlers in your auditor that responds to events from your business objects.  So, for instance, if you want to write an audit record when your BO is saved, the auditor should wire-up to the Saved event on the BO.  This works great but can be cumbersome if your object model is dynamic.  You'll have to be very diligent about wiring and unwiring objects every time one is created or removed.

Aspect-Oriented Programming (AOP) is another route.  There are some existing AOP logging-type solutions out there that use an attribute and configuration-based approach to extending your business objects with auditing capabilities.  I'm not a huge fan of AOP in .NET, but that's just my opinion and I've seen plenty of instances where it has been used successfully on projects.

Hope that helps.

 

Fintanv replied on Friday, January 09, 2009

We do auditing at the database table level.  Each table has an equiv. <table>_audit.  We then use triggers to capture the change (insert, update, delete), who made the change, what the original data was, and what it was changed to.  No business objects involved.

bpb2221 replied on Friday, January 09, 2009

Fintanv:
We do auditing at the database table level.  Each table has an equiv. <table>_audit.  We then use triggers to capture the change (insert, update, delete), who made the change, what the original data was, and what it was changed to.  No business objects involved.


How do you determine who made the change from SQL?

sergeyb replied on Friday, January 09, 2009

We just have “last changed by” column in table you need to audit.  As you are setting this column, you can use its value in auditing table.  Alternatively you can pass in user into to each SP.

 

Sergey Barskiy

Principal Consultant

office: 678.405.0687 | mobile: 404.388.1899

cid:_2_0648EA840648E85C001BBCB886257279
Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

 

From: bpb2221 [mailto:cslanet@lhotka.net]
Sent: Friday, January 09, 2009 2:44 PM
To: Sergey Barskiy
Subject: Re: [CSLA .NET] How do you audit your business objects?

 

Fintanv:

We do auditing at the database table level.  Each table has an equiv. <table>_audit.  We then use triggers to capture the change (insert, update, delete), who made the change, what the original data was, and what it was changed to.  No business objects involved.



How do you determine who made the change from SQL?


bpb2221 replied on Friday, January 09, 2009

sergeyb:

We just have “last changed by” column in table you need to audit.  As you are setting this column, you can use its value in auditing table.  Alternatively you can pass in user into to each SP.



I just slapped my forehead.  I have the same setup, but I needed someone to point the way.   Thanks.


@ajj3085

How are you doing your auditing?

ajj3085 replied on Friday, January 09, 2009

I have an _Audit table, which matches the original table + a new key column.

calls to apInsertInvoice will automatically call apInsertInvoiceAudit... so basically I'm doing it at the stored procedure level, but I thought you couldn't go that route so I didn't mention it.  Smile [:)]

simon_may replied on Saturday, January 10, 2009

I am interested to understand why it is not advisable to have the business object responxible for its own audit records. Perhaps, it they can spare the time,  someone might summarise for me. Thanks

tmg4340 replied on Saturday, January 10, 2009

The short answer is that auditing is not usually part of the use case that created the need for the business object.  Auditing is usually a system-level concern.  Thus, you could argue that adding the auditing functionality to the BO breaks the Single Responsibility Principle.

Stepping away from OO theory, separating the auditing function from your BO's makes the BO's easier to maintain.  It also allows the auditing functionality to be centralized, making it easier to manage as well.  Lastly, it allows the auditing system to more easily change over time.  It's not uncommon for systems that use BO's to audit to later switch to a database-based system, or vice-versa.

HTH

- Scott

SonOfPirate replied on Saturday, January 10, 2009

Keep in mind though, that not all databases or data access technologies support the same features.  Triggers are a good example.  If you know for a certainty that you will only ever be developing for a single database, then absolutely make the most of what that platform offers.  For some of us, we have to support multiple platforms (not to mention the never ending argument about how much logic should be in sprocs) so we have to design for data-agnosticity and have to develop to the lowest common feature set.  This is where AOP and other approaches have given us great solutions with complete flexibility at the back-end.

simon_may replied on Sunday, January 11, 2009

Scott, SOP.

Thanks for that, particularly as you answered on a Saturday.

I haven't myself implemented auditing todate. But it seems to me that it is the required functionality of the auditing that will determine how it will be implemented in a CSLA environment.

As I getnerate my BOs from an Applicaion definition I will gernerate support for the BOs to be audited, Then providing an Audit service that can be configured on a per implementatation basis to suit the customers' specific audit requirements should provide the most flexible and portable approach.

You guys have been most helpful, thanks again.

Regards

Simon

ajj3085 replied on Monday, January 12, 2009

Well, I did it in the data layer because there's no reason (for me) to keep that extra state around and this is mostly a data operation... so let the database do what it does best.. insert rows.  This keeps my business code simpler, and the audit happens at the same time my insert or update happens.  The only audit related BOs I have are read only, so users can view the audit history.

bpb2221 replied on Monday, January 12, 2009

@ajj3085:
If you are auditing in your data layer, could you please take a look at this post:
http://forums.lhotka.net/forums/thread/29703.aspx

You may be able to help.  Thanks

ajj3085 replied on Monday, January 12, 2009

I looked at your post.  There's no code in my UI or business layer, because my stored procedure calls another stored procedure to create the audit record.   So I don't have any code C# or VB code that does auditing.  For example, apProductInsert (the top level record for my Product BO) calls apProductAuditInsert.  The BO and even Linq are oblivious to this happening though.

If you want to have a separate BO for auditing, you'd build it just like you would any other Csla BO I would think.  It would likely be a child of the main editable object and it would be saved when the main BO is saved.  You shouldn't be using anything from you DAL in  your UI code, and that rule applies to everything, not just a discussion on auditing.

Others here would be better suited if you want to have a BO handle your auditing.

ajj3085 replied on Friday, January 09, 2009

I do something similar, but without triggers.  I have an employee table, which contains the domain id (domain\username).  I use suser_username() in sql server to look up the employee record by domain id, and get the EmployeeId number.  Of course, that's all predicated on teh fact that you're using Integrated Authentication.

I have done non-integrated authentication, and it worked similar but every stored procedure call required the id of the user making the change.

Fintanv replied on Monday, January 12, 2009

We are using Oracle with the Oracle provided ODP.net.  This way we can have a single login/password for the app, but pass the user name in as part of the client id property supported by ODP (I think this is a 10g and higher functionality).

In the trigger the following will get the username for entry into the audit table:
nvl(upper(SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')), upper(SYS_CONTEXT('USERENV','OS_USER')))

In the past I have done something similar with SQL Server (not using integrated security) and the connection string property Workstation ID.

Copyright (c) Marimer LLC