| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-11-14 : 08:45:13
|
| Working late last night. It seemed like a good subject line for where I am - if there's a word for a pre-design phase of development, it should be called pondering.Eventually - I'll have to build in an audit trail for course administrators of our web based course. Key items to track would include updates, deletions and insertions. Lesser items would include login/logoff and select.A KEY functionality is reporting clearly and easily which of several course administrators is leaving footprints in the audit trail. I have the AdminID in every SP, and I've considered using different database usernames for each course administrator if it would yeild benefit using SQL internal audit capability.With my feet firmly grounded in my own ignorance, my pondering has led me to believe I should do it myself and not rely on any built-in audit SQL has to offer. (a mistake?)CREATE TABLE MyAudit (MyAuditID INT NOT NULL PRIMARY KEY (1,1) ,AuditID INT NOT NULL , -- The Administrator's IDAuditSP varchar (100) , -- ugh! The SP name that was called?AuditParm varchar (255) , -- The parameters to the SP..AuditDate DATETIME NOT NULL)Every SP would insert an entry in the above table. A column for the command alone might help to filter rows (select,insert,update,login,logout). This solution gives a high-level audit trail targeting application level operations of the course. It ought to lead to some straight-forward reporting.That's where my knowledge of this subject stops. I hope anyone will post comments on other approaches to solve this problem.Sam |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-11-14 : 08:54:26
|
| I'm curious what you mean by "built-in audit" ... as far as I know, SQL Server does not provide any audit on a data level. Any data history capture would require some home-grown solution such as what you suggest.Most people place the DML to capture changes in triggers on base tables, so that you don't have to repeat your audit logic in every proc ....Jay White{0} |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-11-14 : 09:13:03
|
| Well, I thought I read somewhere that SQL had some internal audit log which segregated operations by DB / Username. Maybe not. I sometimes imagine things that seem like a good idea then believe they're real later :-DThe trigger solution may be the way to go. It would / could report results down to the column and row I suppose.Thanks Jay,Sam |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-11-14 : 09:14:58
|
| In a couple of projects where we needed a good audit trail, I used triggers on the relevant tables (for UPDATE and DELETE). These triggers inserted the deleted row into a copy of the table in an audit database, with some extra information about time, operation etc.This works like a charm for us. I have a scheduled job which backs up the database and truncates the tables once a month. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-11-14 : 09:25:32
|
quote:
Well, I thought I read somewhere that SQL had some internal audit log which segregated operations by DB / Username.
To the best of my knowledge the only built in Audit that SQL Server does is with respect to success or failure of logins.Another option is to create trace that captures DML against certain tables. It depends what you are after. The trace will give you the exact statement run against the data, the trigger / audit trail will give you the before and after values of the data.Jay White{0} |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-11-14 : 09:54:35
|
| One slight problem is transactions. If the user attempts a statement that causes a transaction to be rolled back, then no trace in your history table will be left of the attempt. This might be more cleanly implemented in a layer above SQL Server. A general audit information SP could be created to log whatever is passed to it. If you put all of the data access for your ASP or ASP.NET project into a set of data classes, you could move all your access logging code into those classes instead of the server.This could also enable you to add logging of which pages are accessed by a given user.----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-11-14 : 10:00:20
|
| Lavos, there is another way that keeps your data tier where it should be ... use SQL-DMO to log outside the present transaction ... it's a bit more effort to code, but if you want to capture failed changes it is the way to go ...I don't like the idea of auditing outside the data tier because it will be difficult to prevent physical desesign exposure.Jay White{0} |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-11-15 : 13:00:39
|
| But where is the code to access SQL-DMO going to go? If the individuals are accessing the server through normal non-administrator accounts, then the COM related procedures can't be used. A SQL Agent job couldn't be run unless they create a job for each user.It's going to get complicated very quickly. I still think that it depends on what needs to be logged.Right now, reading over the original comment, all access should probably be done using stored procedures which can write the auditing information needed using seperate statements. Triggers won't work due to transactions rolling back the entries made in the audit table. (Actually, there is a way to make it work in triggers, but I think it's a bit dangerous to use.)----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-11-15 : 13:17:09
|
quote:
Right now, reading over the original comment, all access should probably be done using stored procedures which can write the auditing information needed using seperate statements. Triggers won't work due to transactions rolling back the entries made in the audit table.
Wouldn't a error in the proc rollback the audit the same way a trigger would ... The bigger question is do you want to audit changes that didn't happen because of rollback (I would say no).You're right about the admin - COM thing though ...Jay White{0} |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-11-15 : 15:02:00
|
| what about Log Explorer ... the transaction log keeps track of inserts/updates/deletes ect .. |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-11-21 : 20:22:21
|
| Depends on the error and how transactions are being used. Triggers are automatically part of a transaction. If you ever have to work with a linked server that doesn't support DTC inside of a trigger, you are going to learn to hate it.If you can keep your logging statements outside of your normal transactions, then......As for logging failed accesses, I imagine it'd be really great to know that Bob is trying to delete random customers when he doesn't have permission to do so :) (but that falls under detecting the problem and dealing with it instead of waiting for an error to occur and dealing with that.) YMMV.----------------------"O Theos mou! Echo ten labrida en te mou kephale!"Edited by - Lavos on 11/21/2002 23:06:55 |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-11-21 : 20:44:17
|
| If you're willing to spend a little money ($3000) I'd take a look at Entegra from Lumigent (http://www.lumigent.com/AboutLumi/News/2002-11-12.htm). It will generate complete audit logs for insert, update, delete, login/logout, schema changes, security changes, etc. It does this by reading the log file and recording what it finds there. I saw a demo today at the PASS conference and it's a pretty amazing product.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
|
Pondering an audit trail