The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Design of Audit logging using SQL Server 2000

Hey Guys,

Just signed up and it looks like this is a recurring question altogether.

I am currently supporting a legacy ASP.NET system that uses a mix of many strategies for accessing and changing data. On some pages they use SqlDatasource right out of the page itself. Other times it uses SqlCommands on the backend (.aspx.vb files).

Now, they want me to implement some basic audit logging, initially on just a couple of tables. But that will grow as we move on.

Because of the mixed use of strategies, I cannot identify a common place to write the audit code other than on the database itself.

I am familiar with triggers and stored procedures, however I found a caveat. One of the fields that I need to store on the audit table is the user who performed the change. Problem is the user id is only used until the business layer. The database connections are made using sa user.

My dilemma now is how can I make the user id available to the database layer within the trigger context.

Any help would be really appreciated, but remember I am using SQL Server 2000.

Thank you all in advance.
Felipe Coury Send private email
Thursday, January 31, 2008
 
 
Hi,

It looks like you are going to have to write your logs from the applications and not the database.

It would probably be more work to get individual Sql accounts for all of your users anyhow. I've found unless this is put in place early doing it after can be a real headache?

So when a 'loggable event' happens simply write a log from the BLL passing the user from there.

Guess you should be very carefully documenting what needs to be logged, and ensuring that no applications are 'getting through the back door' and making changes without writing an appropriate log.

Not ideal, but at least you will have some form of logging.

My views anyway...
No Name Send private email
Thursday, January 31, 2008
 
 
"The database connections are made using sa user."

Run away ;-)
Odysseus Send private email
Thursday, January 31, 2008
 
 
Odysseus,

It's not a perfect world, I'd say. Unfortunately, we don't have the need nor the budget to make it more correct.

Writing the log from the BLL would be fantastic if there was one. As I said, the SQL commands are run from lots of different interfaces.

One idea I saw somewhere else, was to create a temporary table before insertion/deletion/updating and then, inside the trigger, do a lookup on that table. Do you think that would work?

Thanks!
Felipe Coury Send private email
Thursday, January 31, 2008
 
 
A lookup on what? I assumed that there was no user data held in the database.

I realise there is not a nice set of BLL classes for you to modify, you would just have to be very careful that you log all you need to, from the various different locations. If this is not possible (i.e you do not control the apps / can't make changes) then yes, this is not an option.
No Name Send private email
Thursday, January 31, 2008
 
 
Felipe, I think you may have taken my flippant comment too seriously.

There may not be a complete solution to this, depending on the precise setup you have inherited.

What did you mean with the temporary table idea? I don't see what you are getting at.
Odysseus Send private email
Thursday, January 31, 2008
 
 
Odissey,

Not at all, I forgot to add a smiley face after my comment. Sorry if I gave that impression. Your feedback is really appreciated!

The temporary table idea is something this: before each insert, update or delete, I could make a call from my application to a stored procedure that would be something like:

exec sp_AssignUserId 15

and the stored procedure would do something like:

CREATE TABLE #tempUserId (
  userId int
)

INSERT INTO #tempUserId VALUES (@userId)

then, on the trigger we would do something like

DECLARE @userId int
SELECT @userId = userId FROM #tempUserId
DELETE #tempUserId

INSERT INTO insert_audit_table_here (userId, otherFields)
SELECT @userId, otherFields FROM Inserted

How does that sound?

Thanks again for all the feedback.

Felipe, I think you may have taken my flippant comment too seriously.

There may not be a complete solution to this, depending on the precise setup you have inherited.

What did you mean with the temporary table idea? I don't see what you are getting at.
Felipe Coury Send private email
Thursday, January 31, 2008
 
 
"before each insert, update or delete, I could make a call from my application to a stored procedure that would be something like:"

INSERT INTO Log (user, logInfo) (@User, @logInfo)

??
No Name Send private email
Thursday, January 31, 2008
 
 
I guess that would work, but it doesn't seem very robust. What about concurrency issues?

For example, you can't create the temporary table more than once, so you'd only be able to have one user at a time.

Better to mangle the SPID with the table name, or maybe just have an AuditUsers table, with SPID, UserID columns. Then you can do an insert/update with the stored procedure call.

Using SPIDs will get around the concurrency problems (I think), as each one can only do one thing at a time. You'll have to check that and test it thoroughly.

You'll also need to check that the trigger executes in the same context as the main thread, or none of this will work anyway.
Odysseus Send private email
Thursday, January 31, 2008
 
 
One user at a time? Seems slightly flawed to me?

As for concurrency wouldn't this solve it:

Try
 DoSomething()
 LogSomething()
Catch Exception
 RollItAllBack()

You can do this using  System.Data.SqlClient.SqlTransaction() in .Net.

Dunno, seems straight forward to me, but maybe I'm missing something?
No Name Send private email
Thursday, January 31, 2008
 
 
Here's what I'd recommend.

* Create a table which includes the user ID as a column
* Create a record in this table from the business layer (which knows the user ID), before any any operation that CRUDs the tables which are being audited; do this using the "Serializable" transaction level.
* Create database triggers on the tables that you're auditing; these triggers can read the user ID from the table that's touched by the business layer.
* Remove the user ID from the table at the end of the transaction.

The advantages are:
* The current user ID is poked into the database by the business layer (which is necessary because the buusiness layer is the only entity that's aware of the user ID)
* Audit records are written by database triggers, which is the most reliable and low-maintenance way to ensure that the audit-records are written.

The disadvantage is the need to use the "Serializable" transaction level.
Christopher Wells Send private email
Thursday, January 31, 2008
 
 
When bringing an existing app closer to the present, I used the logging block of the enterprise library. In the data layer, the only things logged were exceptions, and user details were not present. In the business layer, I'd include the user details (where they were available) as well as other information in the audits.

While the enterprise library appears to be cumbersome to some folks, it turned out to be reasonable.
Peter Send private email
Thursday, January 31, 2008
 
 
@No Name:
I fail to see how what you mentioned addresses concurrency concerns. Perhaps you would care to expand.

@Christopher Wells:
Do you think that the serializable transaction isolation level would work here? You create the record in the table, then the triggers see the record in the table (hopefully, see notes above), but how do they know which one to act on?

I fail to see how the serializable transaction isolation level helps here.

Actually, I can see a very complicated way of tricking SQL Server into doing this, but I don't think it is a good way of doing it.

I prefer the AuditUsers table/SPID option discussed previously.
Odysseus Send private email
Friday, February 01, 2008
 
 
@Odysseus
Would you explain your solution in more detail? I think that using the SPID wouldn't work because of the OP's saying, "Problem is the user id is only used until the business layer. The database connections are made using sa user."

> You create the record in the table, then the triggers see the record in the table (hopefully, see notes above), but how do they know which one to act on?

Thy know which one because it would be the only record (or the most recent record) in the table.

For example a typical business transaction might look like this:

BEGIN TRANSACTION
SELECT * FROM CurrentUser
INSERT INTO CurrentUser (UserId) VALUES (@myuserid)
... now various other CRUD operations which trigger auditing ...
DELETE FROM CurrentUser
END TRANSACTION

I think that the serializable transaction isolation level should ensure that no more than one such transaction proceeds concurrently (that any subsequent simultaneous transaction would block on CurrentUser table), and therefore the trigger implementations can use code like ..

DECLARE @userId int
SET @userId = (SELECT MAX(CurrentUser.UserId) FROM CurrentUser)

... to select the UserId from the guaranteed-to-be-the-one-and-only record in the CurrentUser table.
Christopher Wells Send private email
Friday, February 01, 2008
 
 
> I think that using the SPID wouldn't work because  ...

I'm also assuming, perhaps wrongly, that this is a 3-tier solution where the database connections for many users are initiated by one application server (so you can't use the app server's process ID to determine which user initiated the connection).
Christopher Wells Send private email
Friday, February 01, 2008
 
 
Why not spend a bit of time refactoring your application into an architecture that isnt so brittle.

A good first step would be to create a simplistic business logic layer and move all SQL calls from the ASP.Net pages (and code behinds) to that.  You can then change the SqlDataSource on the ASP.Net pages to an ObjectDataSource.

Monday, February 04, 2008
 
 
Since you have to change the application everywhere the operation is done anyway, you may as well take this opportunity to at least funnel all those calls to the same SPs (which will require an application level user identifier).  Now that you've started to enfore your interface to the database, you also have the (non-SQl Server) user IDs.  Then you can log from your db-layer SPs.

Obviously users can still go directly to the tables (unless you remove the direct table rights from the shared applciation login - something I would strongly recommend - giving it only rights to your well-defined SP layer which makes up you db interface) in other parts of the application, but you already had that problem anyway, with people forgetting to log before and after their individual calls.

It is also possible to pass additional information in the connection string using the "Application Name" - this is totally user settable - but may affect your connection pooling.  This can actually be parsed out for your logging if you wish - it shows up in sp_who2, for isntance.
Cade Roux Send private email
Friday, February 08, 2008
 
 
In my previous project, we have chosen a simple approach: we just add a call to auditing SP to all insert/update/delete SPs, which need audit, auditing SP will insert logged data to another database, named audit database. Of course, all statements are in transaction.
Tien Do
Thursday, February 14, 2008
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz