Read-Only Audit Trail possible???

[Beeps]

Hi all,

I was wondering if I could challenge anybody that could offer advice and/or perspective on how one would create an audit trail in VB.Net (or visual basic) that would be read-only??

It seems like a pretty easy task to write a sub, macro, procedure etc. that could capture user modificiations and activities, but I've been looking around and have yet to find a plausable way to have this audit trail read only (even to admin level privledges). There are some great example posted around this site of how to do this like
Michael Red's 181-291.

This is specifically regarding FDA regulation 21 CFR part 11 and the integrity of electronic records for Food and Drug industry. I've worked with HMI packages and automation software that does meet the stringent requirements but they are very expensive and really not that great.

Any ideas or suggestions/info for a place to start would be great!!

Thanks in advance.

Brian

 

[Craig0201]

Brian,

The way I would do this is to use a DB table and allow users read-only access to the table and use stored procedures to insert rows. Give the users exec permissions on the SP and you have it. But at some level, something must be able to write to it (and DBAs could give themselves permissions, etc.....). No system is foolproof but this is as close as you get.....

Craig

 

[chiph]

It's easy to use a trigger to cause audit information to be written to another table for INSERT and UPDATE operations. SQL Server doesn't have a trigger for SELECT operations, in case you need to audit who viewed certain information.

In that case, a set of stored procedures would be the way to go. You'd have one to return you a set of records (and write who asked for them), and another for data changes (to write who changed a value).

Chip H.

 

[Beeps]

Chip and Craig

Thanks for your replies, I appriciate your input. Yeah that's the way I figure it too. I was thinking that an internal record system for an application somehow?

 

[Craig0201]

Beeps,

That is a trigger as Chip suggested. I add a row, the trigger fires to add the audit record.

Craig