August 21, 2008 at 5:18 am
Hi All,
Please give some information about how to audit all DML Operations performed
on the Database.
All Inputs are welcome !!
Thanks,
Amit Khanna
August 21, 2008 at 7:33 am
Amit Khanna (8/21/2008)
Hi All,Please give some information about how to audit all DML Operations performed
on the Database.
All Inputs are welcome !!
Thanks,
Amit Khanna
You have several choices, depending on what you want to achieve/the resources you can use. A very basic solution is to use DML triggers, but this is easy to get around. If you want something that is more difficult to get around, look at third party tools that read the transaction log (Red Gate, APEX, Lumigent, etc). You may also be able to achieve quite a lot by using traces (but this will not give you back how the data has changed). If you can start using SQL Server 2008, then you may want to look at "Change Data Capture", a new feature that may be able to help.
Regards,
Andras
August 21, 2008 at 8:00 am
Be careful what you ask for here. Auditing all DML can be a tremendous amount of data. Be prepared to allocate lots of disk space to this process.
Dr. Andras has given some good suggestions above. If you need more thoughts, you might want to give more details about what your needs are.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 22, 2008 at 12:05 am
Hi Dr. Andras,
Is there any freeware tool that read the transaction log out of those that you are mentioned ?
Thanks,
Amit Khanna
August 22, 2008 at 1:18 am
Amit Khanna (8/22/2008)
Hi Dr. Andras,Is there any freeware tool that read the transaction log out of those that you are mentioned ?
Thanks,
Amit Khanna
I'm not aware of one for SQL Server 2005. For 2000 there is. I know nothing about you requirements, but auditing SQL Server, as Steve mentioned, is likely to be rather resource intensive, and if you need it for, let's say, to satisfy legal requirements, I'd go for a product that is well supported, and has a very well documented impact on SQL Server.
Regards,
Andras
August 22, 2008 at 3:41 pm
Recording ALL DML operations would be like drinking from a fire hydrant. Throttle it down to only what you really need to monitor and then spend the money and get a good application to analyze them if it is going to be an ongoing operation. I'm afraid I'm not familiar with Red Gate, Apex, etc. to give you a recommendation.
Running a trace is probably the way to go but that isn't perfect since many organizations give the DBAs admin rights to the server itself. So they can not only start/alter/stop the trace, they also have access to the resulting trace files.
David
August 22, 2008 at 3:58 pm
Yamachari (8/22/2008)
Recording ALL DML operations would be like drinking from a fire hydrant. Throttle it down to only what you really need to monitor and then spend the money and get a good application to analyze them if it is going to be an ongoing operation. I'm afraid I'm not familiar with Red Gate, Apex, etc. to give you a recommendation.Running a trace is probably the way to go but that isn't perfect since many organizations give the DBAs admin rights to the server itself. So they can not only start/alter/stop the trace, they also have access to the resulting trace files.
David
There will always be someone somewhere with access to the data that could tamper with it to hide inappropriate/illegal activity. Remember, a locked door only keeps out the law-abiding citizen.
Individuals in positions like a DBA should be a highly trusted individual, and this person should have high ethical/moral standards.
😎
August 22, 2008 at 9:36 pm
There will always be someone somewhere with access to the data that could tamper with it to hide inappropriate/illegal activity. Remember, a locked door only keeps out the law-abiding citizen.
Individuals in positions like a DBA should be a highly trusted individual, and this person should have high ethical/moral standards.
😎
Very true. You can only mitigate the risks, never eliminate them.
The most locked down servers I've seen are for my current employer's retail stores. They have entire disk encryption, credit card columns are encrypted yet again, DBAs only have access to a couple of folders and the Wintel people don't have access to SQL tools.
Each has several traces that can be run at any time but only one is run 24/7. It monitors activity on tables with credit card data in them for PCI compliance.
One of the DBAs is working with a team installing SAP. They turned the profiler on like the original person is thinking about (I forgot the exact settings but it was quite a few) and in one hour they had 50GB of trace files.
David
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply