Audit anyone
-
I'm wondering if some sort of tutorial exists explaining the basics of database auditing.
Our users are complaining data in an application mysteriously vanishes. So I need to audit delete and update statements against
several tables and identify the user doing the nasty. Not sure if writing to a file or using application logs is best to save the results.Thanks for any advice.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
You can start with this...
https://bradmcgehee.com/2010/03/30/an-introduction-to-sql-server-2008-audit/ - fizzleme - Wednesday, January 10, 2018 11:37 AM
Have you ruled out the possibility of application bug? Does the app keep an audit trail? How have you investigated so far?
-
It's very likely it's an application bug. However the app is not audited. Luckily it is running in enterprise edition.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
- fizzleme - Wednesday, January 10, 2018 12:37 PM
I was trying to look up a blog by Gail Shaw on server side tracing for you and then I bumped into this - too good not to share.
-
Anyway, if the issue happens frequently, like once every few minutes, you can consider using SQL Profiler to filter for queries operating on the target table. Doing a server side trace would be the recommended approach. Setting up a trigger may be too heavy handed and not allows on a business system. For this very purpose, I use Extended Events - look under Degree of Parallelism event and you will see what's available to you.
- RandomStream - Wednesday, January 10, 2018 12:53 PMfizzleme - Wednesday, January 10, 2018 12:37 PM
I was trying to look up a blog by Gail Shaw on server side tracing for you and then I bumped into this - too good not to share.
hope that price is in Rupees.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
- RandomStream - Wednesday, January 10, 2018 12:56 PM
The problem is very sporadic. The users have had to re-enter that data several times. It seems they are getting tired of doing that. It could take days or even weeks for the problem to remanifest.
As a beginning, I will only audit deletes. Makes sense since entire rows of data disappear.When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
My bad. I think I did see Amazon Asia on the right hand side.
-
I would create a history table and an update, delete trigger that inserts deleted along with user and datetime of the edit.
- fizzleme - Wednesday, January 10, 2018 12:37 PM
Do the users connect through the application or do they each have dedicated users in the DB?
-
This was removed by the editor as SPAM
-
expletive deleted. They gave me the wrong server name. The SQL Server is standard edition, so Auditing won't work.
I need a way to track when rows are deleted from about 8 tables. The users have an application front end and the product is supported by a vendor so mucking with the code
isn't a great idea. SQL Profiler may work but how can one capture record deletions from specific tables? annoyed....any suggestions are appreciated.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
Add a created-on column to each table. Create history tables with the same structure as the tables you want to audit and add a valid-from datetime2(7), a valid-until datetime2(7) and a modified-by defaulted to suser(). Create an update, delete trigger and insert from deleted into the history table and use the created on for valid-from and sysdatetime for valid-until.
-
If you are running SQL 2014 or above, please try this on your test server. You need to make sure the filename is pointing to a valid folder on your system or it will not create the event session.
Once created, it will be in disabled state. Examine its properties, adjust and add additional target table names, then enable it. It will generate audit trail in the target folder.
CREATE EVENT SESSION [SQLDeleteAudit] ON SERVER
ADD EVENT sqlserver.sp_statement_completed(
ACTION(sqlos.task_time,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.server_principal_sid,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
WHERE (([sqlserver].[equal_i_sql_unicode_string]([object_name],N'table_a') OR [sqlserver].[equal_i_sql_unicode_string]([object_name],N'table_b') OR [sqlserver].[equal_i_sql_unicode_string]([object_name],N'table_c')) AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%delete%')))
ADD TARGET package0.event_file(SET filename=N'\\APSRVR3\Backups\APSRVR1.SQLAudit\APSRVR1.SQLDeleteAudit.xel',max_rollover_files=(25))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply