February 20, 2007 at 3:26 am
Hi,
This is my first post on this forum, so please bear with me.
I have a web service that handles all of the connections to a certain database, and now I need to implement auditing on the database. The web service only uses stored procedures to manipulate the data, so I can't do it at application/web service level, and the database doesn't know which user is accessing the data, since the web service handles all of the connections.
My question is weather someone knows of a way to somehow pass a 'hidden' variable with every call to a stored procedure so that a trigger can know which user is executing the command.
Any other suggestion of how I might be able to implement this will be greatly appreciated.
February 20, 2007 at 11:22 am
can you modify the code and the stored procedures to pass/use a new parameter? If you can't modify the web service, you'll really be hampered in auditing.
I'm assuming the web service requires a user to login someplace, and that information is in the session, or otherwise avaliable when the procedures are called...
so if you modify the procedure to take a new, optional parameter like this:
ALTER PROCEDURE PR_DoSomething(Param1 int, UserName varchar(30) = 'unknown') as .....
you could then enhance the proc to do some sort of auditing with the new parameter.
that way you'd have the web's login username, instead of the database username the applicaiton is probably using. you could certainly add multiple parameters in order to enhance the audit trail.
there's other ways, like using CONTEXT_INFO, but if you are going to use that and change the stored proc, you might as well add a parameter. fiddleing with context_info means you'd need to edit the web service code as well....
here's CONTEXT INFO example for fun:
DECLARE @var VARBINARY(128)
SET @var = CAST(N'Hello World' AS VARBINARY(128))
SET CONTEXT_INFO @var
GO
-- Select the context information
DECLARE @sess VARBINARY(128), @var NVARCHAR(64)
SET @sess = (SELECT context_info FROM master.dbo.sysprocesses
WHERE spid = @@spid)
SET @var = CAST(@sess AS NVARCHAR(64))
print @var
SELECT CAST(context_info AS NVARCHAR(64)) AS RESULTS FROM master.dbo.sysprocesses
WHERE spid = @@spid
Lowell
February 20, 2007 at 10:46 pm
Thanx for the reply.
I have thought about rewriting the stored procs, but the only problem is that I have something like 130 Stored Procs on the server, with about 80 of them manipulating the data. I know there must be an easier way than to rewrite all of them...
February 21, 2007 at 4:12 am
you could also enhance the web application by adding another call to an audit stored proc...so what i'm saying is for every call in the web app that is doing an update/insert/delete by calling one of the 80 stored procedures, immediately after those lines of code, you could add amother proc call to the db that could track that web user called procedure x (optionaly with these parameters)
since this is a web app, I can't think of an easier way to audit who is changing data. Regardless, it sounds like a bigger job than you are expecting.
the idea above is really doing nothing more than the same thing as my first suggestion, except at the applicaiton level instead of inside the stored procedures. It really depends on how easy it is to edit/update the applicaiton layer and the stored procs
Lowell
February 21, 2007 at 6:30 am
Thanx for the reply.
I solved my problem by using almost the approach that you mentioned. I created a wrapper StoredProc that basically takes a username, stored proc name and that sp's parameters as parameters. This is all done at the web service's data access level, so all I'm doing now is routing all of the stored procs through the wrapper proc. The wrapper stored proc executes the other procs, and those procs fire the various triggers. The triggers update the audit table, and sets a unique ID, based on the datetime and the data that was changed, in another table. This ID is then used by the wrapper sp to match the username to the audit data in the audit table.
This is quite a messy way of implementing this, but at this stage it was the only way of getting the job done. The CLR functionality of SQL Server 2005 made things a lot easier than it would have been.
Anyway, Thanks for your help.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply