A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
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.
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...
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?
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.
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 (
INSERT INTO #tempUserId VALUES (@userId)
then, on the trigger we would do something like
DECLARE @userId int
SELECT @userId = userId FROM #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.
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.
One user at a time? Seems slightly flawed to me?
As for concurrency wouldn't this solve it:
You can do this using System.Data.SqlClient.SqlTransaction() in .Net.
Dunno, seems straight forward to me, but maybe I'm missing something?
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.
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.
I fail to see how what you mentioned addresses concurrency concerns. Perhaps you would care to expand.
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.
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:
SELECT * FROM CurrentUser
INSERT INTO CurrentUser (UserId) VALUES (@myuserid)
... now various other CRUD operations which trigger auditing ...
DELETE FROM CurrentUser
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.
> 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).
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.
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.
Thursday, February 14, 2008
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz