<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQLServerCentral.com
»
Editorials
»
Auditing
20 posts, Page 1 of 2
1
2
»»
Auditing
Rate Topic
Display Mode
Topic Options
Author
Message
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Thursday, July 29, 2010 8:36 PM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406,
Visits: 13,722
Comments posted to this topic are about the item
Auditing
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #961091
Richard Gardner-291039
Richard Gardner-291039
Posted Friday, July 30, 2010 3:04 AM
SSC Veteran
Group: General Forum Members
Last Login: Thursday, March 21, 2013 9:06 AM
Points: 238,
Visits: 409
Our auditing tends to be in two spheres -
1) Where there is interaction with the customer, so sales orders and product specifications are audited.
2) The finance system, we have very fine grained history of all our transactions.
This just takes the form of history tables plus user names & dates.
Of course it is external interactions which dictate what goes on internally, so I guess that is what is being audited, if something goes wrong internally (we make the wrong product or buy the wrong supplies) then we can find out what the catalyst was for that. I guess there's no point auditing internal procedures which are carried out as a result of making an invalid transaction with the outside world.
In terms of SQL, then, nothing, it's all application based, but then that's why we only allow indirect access to the DB. I could imagine in a larger site with lots of people with direct DB access you'd have to go to a whole different level, but I don't see a justification for it here.
Post #961195
mcerkez88
mcerkez88
Posted Friday, July 30, 2010 3:46 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, December 19, 2012 12:46 AM
Points: 6,
Visits: 49
In our system we audit everything by storing a new state of table row into audit log as xml record along with the info who and when made the change. this way by using views on audit log table we can write query and reconstruct each row to any desired point in time if it is necessary to do so.
At application level we log parameters for every query on database to monitor who and when acceded some data.
Post #961209
bwillsie-842793
bwillsie-842793
Posted Friday, July 30, 2010 6:18 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, April 12, 2013 7:43 AM
Points: 107,
Visits: 287
Unfortunately we don't audit as much as I'ld like due to how auditing is implemented in our ERP system.
We audit a few basic fields on certain transactions, but don't audit BOM changes or most other master record changes.
Consequently we rather have to guess who might have made the change based on the date and username of whoever touched the record last.
Does SQL Server have an integrated audit function built in? Something that would let you audit changes to particular fields in tables and grab date, time, userid, and before/after field content?
If not, would it be worth while to anyone other than me to have one?
Post #961279
Jack Corbett
Jack Corbett
Posted Friday, July 30, 2010 6:33 AM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 12:22 PM
Points: 10,571,
Visits: 11,871
The whole topic of auditing can be very complex and honestly, I don't understand or know all the options I even have available to me. Fortunately, I am not currently in an industry that is highly regulated, so I don't have a ton of auditing requirements. In my opinion, this is one of the areas where PASS should be providing best practices and recommendations.
Jack Corbett
Applications Developer
Don't let the good be the enemy of the best. --
Paul Fleming
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #961288
-=JLK=-
-=JLK=-
Posted Friday, July 30, 2010 6:45 AM
Old Hand
Group: General Forum Members
Last Login: Tuesday, January 22, 2013 6:39 AM
Points: 367,
Visits: 297
We audit select "tables". I have a generic trigger I apply to all tables of interest which simply makes a copy of an existing row before it is changed/deleted and places it in an "audit" copy of the same table. All audit tables have triggers which prevent deletions execpt by a specific user account which was created for that purpose only.
There is nothing particularly fancy about this method, it is mostly brute force but it works and with some creative SQL we can reconstruct most activity on our sensitive data as well as recovering accidentally changed data. Once in place it takes care of itself (I don't have to be concerned with the application side of the house) but I do have to be mindfull of table design changes since they need to be applied to the audit copy of the table also.
I would be interested in seeing how mcerkez88 implemented/maintains their xml audit logs.
James.
Post #961305
-=JLK=-
-=JLK=-
Posted Friday, July 30, 2010 6:49 AM
Old Hand
Group: General Forum Members
Last Login: Tuesday, January 22, 2013 6:39 AM
Points: 367,
Visits: 297
mcerkez88 (7/30/2010)
In our system we audit everything by storing a new state of table row into audit log as xml record along with the info who and when made the change. this way by using views on audit log table we can write query and reconstruct each row to any desired point in time if it is necessary to do so.
At application level we log parameters for every query on database to monitor who and when acceded some data.
Would you be willing to post an example of how you implmented your auditing? Curious how you insure all changes are written to the audit log in an appropriate format. Are you just capturing the changes or the entire record that is being changed? If done via trigger or stored procedure I'd appreciate seeing a copy. I understand if you consider it a trade secret or sensitive and don't want to publish it publicly.
Thanks,
James.
Post #961310
Katherine Fraser
Katherine Fraser
Posted Friday, July 30, 2010 7:43 AM
Grasshopper
Group: General Forum Members
Last Login: Friday, May 10, 2013 3:22 PM
Points: 10,
Visits: 144
I work at a company whose clients include pharmacies so several of our applications store PHI (protected health information) and must be "treated with special care" according to HIPAA.
I am currently working on an application to audit any PHI access. That is, any time a stored procedure returns PHI to the application, I'll have to enter a log record showing what data was seen, by whom and when.
I'll be using Service Broker to log the accesses, sending encrypted messages, and storing it in a database encrypted with TDE. I'm not sure what the volume will be yet but it seems like a good idea to send the SSB messages as binary to reduce the size.
------------------------------------------------------
Katherine
Post #961374
Nadrek
Nadrek
Posted Friday, July 30, 2010 7:57 AM
Say Hey Kid
Group: General Forum Members
Last Login: Monday, May 13, 2013 9:25 AM
Points: 670,
Visits: 2,026
Katherine Fraser (7/30/2010)
I work at a company whose clients include pharmacies so several of our applications store PHI (protected health information) and must be "treated with special care" according to HIPAA.
I am currently working on an application to audit any PHI access. That is, any time a stored procedure returns PHI to the application, I'll have to enter a log record showing what data was seen, by whom and when.
I'll be using Service Broker to log the accesses, sending encrypted messages, and storing it in a database encrypted with TDE. I'm not sure what the volume will be yet but it seems like a good idea to send the SSB messages as binary to reduce the size.
------------------------------------------------------
Katherine
How is that going to work when a DBA or developer or process is doing bulk historical reporting, or investigating/troubleshooting to find patterns (selecting millions of rows to let a human spot patterns)?
Post #961388
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Friday, July 30, 2010 8:30 AM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406,
Visits: 13,722
bwillsie-842793 (7/30/2010)
Does SQL Server have an integrated audit function built in? Something that would let you audit changes to particular fields in tables and grab date, time, userid, and before/after field content?
If not, would it be worth while to anyone other than me to have one?
SQL Server 2008 has a built in Auditing feature that is very nice.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #961424
« Prev Topic
|
Next Topic »
20 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
