January 27, 2004 at 12:51 am
This might sound daft, but I'll try to explain what I want.
I need to built in some auditing inside my application. It doesn't have to be too clever, so all I propose to do is have each SP insert a record into a table showing the date / time executed, by whom and the command executed.
Thus: if I executed a stored procedure such as:
up_insert_record 'value1', value2, 'value3'
The table would end up containing
27/1/2004 07:44, GCottle, "up_insert_record 'value1', value2, 'value3'"
Date / time and username are easy. I know I can rebuild the command manually inside the stored procedure, but is it possible to get at this value using some function or other before I do start all the type to rebuild each command. It would save quite a bit of time, as all I need then to implement the auditing is to cut and paste just one line into each stored procedure - I have about a hundred to change
As an alternative, is it possible to get at a parameter collection and the values of each parameter, so I can build the command string that way instead? Thus I would end up with:
27/1/2004 07:44, GCottle, "up_insert_record @param1 = 'value1', @param2 = value2, @param3 = 'value3'"
Any ideas?
Many thanks
January 27, 2004 at 1:08 am
Why not just use Profiler..?
No need then to mess with each object.
Thinking about it, I'm not so sure that auditing by implementing the audit in each object to be audited is such a great idea.
Just think of in how many places that auditcode will be duplicated.
And if you want to change something about the audit..? Massive work, I believe.
=;o)
/Kenneth
January 27, 2004 at 1:20 am
Hi,
Thanks for the quick response. Unfortunately, profiler is not an option. Firstly, as a developer, I do not have access to it in production (Getting it in development is difficult enough!). Secondly, it means that I have to have it running continuously. I need to have a permanent audit trail of any data changes which are made.
I agree that having to change each SP to put the auditing is not nice, but if I can do it as a something quick and easy in each as a cut and past operation, then that will ease the pain.
Thanks anyway
January 27, 2004 at 1:36 am
dbcc inputbuffer(@@spid)
January 27, 2004 at 2:00 am
Brilliant!. That does it. I can use that to store the relevant information
Thanks!
January 27, 2004 at 2:42 am
Just note that this will only cover changes made through the actual proc that has this code in it.
If it's possible to change data in any other way, and you need to audit that possibility too,
then a trigger solution may be preferrable instead.
Most audits are more interested in keeping track of changes (ie the outcome) and not particularly the statements sent
(since exec myProc will not necessarily guarantee that anything has changed at all - it may have errored out)
=;o)
/Kenneth
January 27, 2004 at 3:26 am
Already got round that one. I have the application locked down fairly hard, such that no-one can make any changes except through the use of SPs and the SPs will only run if the application-name is set correctlty in the connection string. OK someone determined will get in, but it will slow them down enough for these purposes. - The user base is not that big.
Graham
January 27, 2004 at 6:52 am
I was more thinking along the lines of imports of data from external sources that may also change the data in some way.
If that demand pops up, you'll have to pipe it through the procs in order for it to be audited, and that may not be the most efficient way of importing...
If that should ever be of any concern...
=;o)
/Kenneth
January 27, 2004 at 7:44 am
That's also true and much of the application runs in batch. However, the source data has its data changes logged (SAP R/3) and the folder to which the data is downloaded is well protected. So I won't be bothering to do that one, except for a few places which will be really critical.
January 28, 2004 at 4:24 am
dbcc inputbuffer will only log the command used to execute the first SP if there's a chain of SPs utilised.
I'm with Kenneth. Use Profiler. Not that you'd have it running interactively. Schedule a scripted/customised profiler SP that runs either continously or once a day for 24 hours.
Cheers,
- Mark
January 28, 2004 at 6:11 am
..or use triggers..
If the requirement is to do 100% audit - no exceptions - by far the easiest and most reliable way to do this is by triggers.
Profiler is very nice, but what about the audit if it hiccups?
In the end, I think that the audit requirements will decide which way to go..
=;o)
/Kenneth
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply