October 10, 2011 at 9:29 am
Hi all,
Im looking for a way to audit any deletes updates etc on a certain DB.
so far ive played with c2 auditing (seems massive over kill)
and creating a sql server audit and setting it to log updates, deletes on the tables i want. But Id like to know the statement they used to delete, update and i dont seem to have this using sql server audits. (I looked at putting it in file and to the app log)
Is it possible to do using the audit capabilities of 2008 r2 auditing or does it need triggers to be made?
thanks for any advice!
S
October 10, 2011 at 11:15 am
It depends on what you need to audit.
Are you just auditing the commands, or do you need to audit the actual data changes as well?
C2 will audit both, but it is a bit overkill if that's all you need.
A trigger-based audit log (see my articles on the subject) will audit the data changes.
A trace will audit the commands.
Trigger + trace will audit both, without all the C2 overkill, but you have to ties the data changes back to the trace events and that's not always easy.
Using stored procedures for all data manipulation commands will allow you to do both things, but you have to make sure all changes are made via the procs, and that all procs are built to insert records into your audit log.
Plusses and minuses for each option.
You can find my articles on auditing on this site, at:
http://www.sqlservercentral.com/articles/Auditing/63247/
http://www.sqlservercentral.com/articles/Auditing/63248/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 10, 2011 at 12:13 pm
GSquared (10/10/2011)
It depends on what you need to audit.Are you just auditing the commands, or do you need to audit the actual data changes as well?
C2 will audit both, but it is a bit overkill if that's all you need.
GSquared i thought that C2 auditing was just a trace that met specific definitions/requirements, but did not have the actual data changes...so you can capture the text of the command and whodunit, but not the data?
i'm under the impression for data changes, you need Change Data Capture...
Where am i misremembering?
Lowell
October 10, 2011 at 12:25 pm
Lowell (10/10/2011)
GSquared (10/10/2011)
It depends on what you need to audit.Are you just auditing the commands, or do you need to audit the actual data changes as well?
C2 will audit both, but it is a bit overkill if that's all you need.
GSquared i thought that C2 auditing was just a trace that met specific definitions/requirements, but did not have the actual data changes...so you can capture the text of the command and whodunit, but not the data?
i'm under the impression for data changes, you need Change Data Capture...
Where am i misremembering?
If I'm not mistaken, C2 will record the DML command in the audit trail. As you said, "...the text of the command...". That's not precisely "the data change", but if you see "UPDATE dbo.MyTable SET SecurityLevel = 10 WHERE UserID = MyUserID", you can tell what columns are being changed and what values they are being set to. That's the same, effectively, as a change audit, but without the easy rollback and query functionality of a data change log.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 11, 2011 at 1:57 am
Hi guys thanks for the replies!
I need to be able on request list if an update or delete has been found to be incorrect:
Name of person who made change,
Time it happened,
Computer (may be beneficial)
some way to track what record was effected (hence thinking query)
I noticed that c2 doesnt really cover the last rule. When i look at the log i see
DELETE [AdventureWorks].[Sales].[Individual] WHERE [CustomerID]=@1
Which doesnt let me know specificly which ID was deleted.
Is it down to Triggers and making a table to put the data into?
S
October 11, 2011 at 6:09 am
Easiest way to do that is an audit table.
Here are the articles I wrote on the subject:
http://www.sqlservercentral.com/articles/Auditing/63247/
http://www.sqlservercentral.com/articles/Auditing/63248/
Make sure to read through the discussions, as there's a lot of good data in those.
That should give you a good idea of your auditing options and help you pick which is best for you.
Currently, my favorite is a before-values-only XML audit table. Easiest to implement, very easy to refactor to fit any number of tables, or tables with changing data structures.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 14, 2011 at 4:17 am
hey,
thanks for the links, will get my reading on 🙂
S
October 19, 2011 at 10:44 am
It's all built in:-
CREATE DATABASE AUDIT SPECIFICATION PCI_Txn_Database_Spec
FOR SERVER AUDIT PCI_Audit
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SELECT ON dbo.Customer BY dbo),
ADD (INSERT ON dbo.Customer BY dbo),
ADD (UPDATE ON dbo.Customer BY dbo),
ADD (DELETE ON dbo.Customer BY dbo),
ADD (EXECUTE ON dbo.usp_SubmitPO BY public)
Then you just run reports over the audit specification.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply