Auditing web user operations 2

[adam0101]

My manager wants us to log any operation on any table in our database. The users have access to the database via a web application. The web application logs into the database with a hard coded username rather than the username of the user using the web application. What are some options for logging CRUD operations and tracing them back to a specific user?

Adam

http://adameslinger.blogspot.com/

 

[ESquared]

This sounds like a web development design issue to me.

You could always write triggers for every data table, but the trick is, how does the database know the user? If the web application doesn't know the user, then the database won't, either.

Try turning on integrated authentication on your database. Or requiring username and password. Some how, some way, the web site has to know who the user is. If it doesn't, nothing can ever be done in the database.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ptheriault]

If all the traffic is coming through your web app then there is nothing you can do with changing your web app.
The Web App would have to capture the login and pass that to the database where you could store the user name in a log table. You would also then have to write a bunch of triggers that would log all your data changes. It wouldn't be a small task.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[adam0101]

The user will be authenticated by the web app, so I'll have their username there. I have to continue to use just the one database username though to connect to the database. I guess I was hoping there was a way to pass in supplemental information to the connection object on the client that I could grab from within a trigger in the DB.

So if I have the username in the web app, is there a generic way to pass that information to a trigger without modifying a stored procedure (because stored procedures are not always used for data access)?

Adam

http://adameslinger.blogspot.com/

 

[ESquared]

If your web app does connection pooling (which I should hope it does) then no matter what you do it's going to be a problem to determine from the databse the user who initiated a query.

You could use application role security; it won't do the trick.

You could use the context info that gets stored in the sysprocesses table; I think that's a bad idea.

Rewrite your web app to handle queries in a generic way that can handle the infrastructure of passing through and recording the user.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[adam0101]

OK, that's what I figured. We use the Enterprise Library, so I'll probably modify our Data Access Application Block provider to log what we need. Thanks.

Adam

http://adameslinger.blogspot.com/