Auditing changes made to table design (syscolumns table)
From: Sean Aitken (seandotaitken@tekelecdotcom)
Date: 09/10/02
- Next message: bouscal: "Access denied on xp_sendmail"
- Previous message: Thomas: "Analysis Services Security"
- Next in thread: Andrew J. Kelly: "Re: Auditing changes made to table design (syscolumns table)"
- Reply: Andrew J. Kelly: "Re: Auditing changes made to table design (syscolumns table)"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Sean Aitken" <seandotaitken@tekelecdotcom> Date: Tue, 10 Sep 2002 14:09:38 -0400
Hello all,
We recently had a problem arise where a user managed to change the
security settings for a particular ClearQuest table which managed to cause
problems for other certain users. I researched various transaction log
analysis tools, which proved to be a fruitless effort as the only thing that
seemed capable of reasonably searching and filtering the data would cost us
a few hundred dollars. I continued to dig and found the 'undocumented'
"DBCC LOG (...)" command. This seemed promising, except there were 2
variations on the parameters, and using SQL 7 seems to supply me with the
LESS flexible ( dbName, ReportingLevel ) parameters. This was a decent
start I thought. Keep in mind I know the database and the tables I would
like to see transactions against, so already I could eliminate 95% of the
returned data. After running the command with a detail level 2, I was able
to see some slightly useful information, among the 420,000 returned rows. I
ran again with level 3, and it never finished. Managed to build a 1.5 Gig
file in my temp directory (after setting output of Query analyzer to 'file')
The first few rows returned showed dates from 11/01. This seemed futile.
So, since the action log couldn't be recovered without unnecessary
expendutures, I began to implement a trigger-based audit solution. First
step, I attempted to write a trigger to log when a change was made to the
syscolumns table in that database. This seemed pretty cool, cuz I could
then log the workstation and the user that performed the change. So my
bubble was busted after reading that you can't create triggers against
system tables. Back to the drawing board. I then considered the profiler,
which was not really an option since it has to intercept and look for
certain criteria against every transaction. (this would include extended
stored proc to do the same)
So, as I sit scratching my brain, can anyone help me find an easy way to
capture who makes changes to table structures, or any other system table for
that matter.
Any help is GREATLY appreciated!
Sean Aitken
Tekelec
Morrisville, NC
(Please reply to the news group, as I have been unable to find postings with
a viable solution thats geared toward my specific problem, and I'm sure
others could benefit)
- Next message: bouscal: "Access denied on xp_sendmail"
- Previous message: Thomas: "Analysis Services Security"
- Next in thread: Andrew J. Kelly: "Re: Auditing changes made to table design (syscolumns table)"
- Reply: Andrew J. Kelly: "Re: Auditing changes made to table design (syscolumns table)"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
