January 6, 2016 at 9:04 am
Hi,
i would like to audit DML operations on a table (deletes mostly) but the problem is, that it needs to answer a specific condition.
i need to catch a specific condition of columns (for example, i have a bit column "to_delete" and if its value is 0 i want to catch only those delete statements that deleted records where "to_delete = 0".
i am familiar with Database Audit Specifications but it will audit all deletes, and the problem is that in my specific table i have millions of deletes per day so it will be impossible to track the transaction i need.
Any Suggestions?
January 6, 2016 at 9:08 am
Can you post the DDL for the table?
😎
January 6, 2016 at 9:19 am
i cant give you the whole DDL of the table (i cant really understand why you need it), but i can add the specific condition of the delete i want to catch:
WHERE ExpDate = '9999-12-31 00:00:00.000'
as in "delete on a row that ExpDate was set to 9999-12-31 00:00:00.000"
ExpDate is datetime type.
let me know if you need more info.
January 6, 2016 at 9:30 am
Just makes answering easier, otherwise one has to make it up.
😎
There are few patterns for doing this, what information do you need to capture in the audit?
January 6, 2016 at 9:42 am
i understand.
lets say this is my table, so you can use that as a reference:
TABLE [test1]
ID [int] NOT NULL,
ExpDate [datetime] NOT NULL
i want to log all the deletes from test1 WHERE ExpDate = '9999-12-31 00:00:00.000'
the problem that i'm having is sporadic, unknown deletes from the table of values that should not be deleted at all (checked against the above statement), so the information i require is everything that will help me identify what end point issued the delete, so i guess that would be, not limited to:
SPID, Login, HostName, DBName, command/specific sql query of the delete, sql_text, session_id, program name and etc.
hope that is enough information.
January 6, 2016 at 10:06 am
My understanding is that you want to trace what application, login, session, statement, etc. is issueing specific types of DELETE operations against a table. You can do this using an Extended Events trace.
If this has more to do with retaining a permanent audit of delete operations matching a specific criteria, then you can do this with a DELETE TRIGGER on the table, applying a filter to the 'deleted' virtual table and inserting the result into an audit table.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 6, 2016 at 2:22 pm
No, i dont want this as a permanent audit solution. i just want to catch the process that is issues those deletes.
the delete trigger crossed my mind, but because im having millions of deletes a day on the table im afraid of the overhead the trigger will create.
if there will be no other options then yes, i guess i will have to resort to delete triggers but first i want to explore (if there are any) any sql server engine options that will leave less footprint on the DB.
I should explore extended events options more, as long as its not creating a heavy overhead on the DB and can filter based on specific conditions like i'm having.
January 6, 2016 at 3:12 pm
If all you want to do is trace delete operations on the table that match specific criteria, that is resolving the root cause of some issue caused by a user or application, then use an extended event trace. Also a trace that captures to a file or event log will be far less resource intensive than a trigger that inserts into an audit table.
One interesting thing that a delete trigger can do for you, as a counter measure going forward, is it can be used to enforce a constraint whereby a transaction including a delete operation meeting certain disallowed criteria will be rolled back. But I tend to avoid using triggers because it results in implicit transactions and cursor based operations that can cause performance issues and blocking. For what it's worth, you can read more here about the behaviour of a ROLLBACK TRAN statement within a trigger.
https://msdn.microsoft.com/en-us/library/ms181299(v=sql.110).aspx
To prevent applications from deleting rows in an inappropriate way, the best solution may be to implement a stored procedure to perform the delete operation, grant application exec permission on this stored procedure, while denying the application direct delete access on the table. It sounds like you essentially need to allow the application delete functionality while preventing certain types of logically disallowed delete operations.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 6, 2016 at 3:19 pm
we dont issue direct deletes, that's the thing. we have proper cleaners (SPs) based on criteria, the problem is that somehow a rogue process somehow manages to delete rows anyways but i think the extended events will do the trick.
thanks for the help!
January 6, 2016 at 3:23 pm
neomerdien (1/6/2016)
we dont issue direct deletes, that's the thing. we have proper cleaners (SPs) based on criteria, the problem is that somehow a rogue process somehow manages to delete rows anyways but i think the extended events will do the trick.thanks for the help!
It sounds like you need to disallow INSERT, UPDATE, DELETE permission to accounts that don't need it, and also insure that application accounts are not in the SYSADMIN or DBO role.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 6, 2016 at 10:20 pm
neomerdien (1/6/2016)
i understand.lets say this is my table, so you can use that as a reference:
TABLE [test1]
ID [int] NOT NULL,
ExpDate [datetime] NOT NULL
i want to log all the deletes from test1 WHERE ExpDate = '9999-12-31 00:00:00.000'
the problem that i'm having is sporadic, unknown deletes from the table of values that should not be deleted at all (checked against the above statement), so the information i require is everything that will help me identify what end point issued the delete, so i guess that would be, not limited to:
SPID, Login, HostName, DBName, command/specific sql query of the delete, sql_text, session_id, program name and etc.
hope that is enough information.
Here is a modification of a generic audit example, modified to catch only deletes on the maxdate conditions.
😎
USE tempdb;
GO
SET NOCOUNT ON;
/*
Test environment for auditing schema objects
1. Audited table dbo.TBL_TEST_AUDIT
2. Audit table dbo.TBL_GENERIC_AUDIT
3. Modification Blocking trigger on dbo.TBL_GENERIC_AUDIT
4. Audit trigger on dbo.TBL_TEST_AUDIT
5. Session information function dbo.XUDF_GET_SESSION_CONNECTION_INFO
*/
IF OBJECT_ID('dbo.TBL_TEST_AUDIT') IS NOT NULL DROP TABLE dbo.TBL_TEST_AUDIT;
IF OBJECT_ID('dbo.TBL_GENERIC_AUDIT') IS NOT NULL DROP TABLE dbo.TBL_GENERIC_AUDIT;
IF OBJECT_ID('dbo.XUDF_GET_SESSION_CONNECTION_INFO') IS NOT NULL DROP FUNCTION dbo.XUDF_GET_SESSION_CONNECTION_INFO;
DECLARE @SQL_STR_001 NVARCHAR(MAX) = N'
CREATE FUNCTION dbo.XUDF_GET_SESSION_CONNECTION_INFO()
/* Returns user, session, execution and relevant system information for auditing and diagnostics/delbuging
*/
RETURNS XML
AS
BEGIN
RETURN
(
SELECT * FROM
(
SELECT
SCON.session_id
,SCON.most_recent_session_id
,SCON.connect_time
,SCON.net_transport
,SCON.protocol_type
,SCON.protocol_version
,SCON.endpoint_id
,SCON.encrypt_option
,SCON.auth_scheme
,SCON.node_affinity
,SCON.num_reads
,SCON.num_writes
,SCON.last_read
,SCON.last_write
,SCON.net_packet_size
,SCON.client_net_address
,SCON.client_tcp_port
,SCON.local_net_address
,SCON.local_tcp_port
,SCON.connection_id
,SCON.parent_connection_id
,SES.login_time
,SES.host_name
,SES.program_name
,SES.host_process_id
,SES.client_version
,SES.client_interface_name
,CONVERT(VARCHAR(100),SES.security_id,1) AS security_id
,SES.login_name
,SES.nt_domain
,SES.nt_user_name
,SES.status
,CONVERT(VARCHAR(100),SES.context_info,1) AS context_info
,SES.cpu_time
,SES.memory_usage
,SES.total_scheduled_time
,SES.total_elapsed_time
,SES.last_request_start_time
,SES.last_request_end_time
,SES.reads
,SES.writes
,SES.logical_reads
,SES.is_user_process
,SES.text_size
,SES.language
,SES.date_format
,SES.date_first
,SES.quoted_identifier
,SES.arithabort
,SES.ansi_null_dflt_on
,SES.ansi_defaults
,SES.ansi_warnings
,SES.ansi_padding
,SES.ansi_nulls
,SES.concat_null_yields_null
,SES.transaction_isolation_level
,SES.lock_timeout
,SES.deadlock_priority
,SES.row_count
,SES.prev_error
,CONVERT(VARCHAR(100),SES.original_security_id,1) AS original_security_id
,SES.original_login_name
,SES.last_successful_logon
,SES.last_unsuccessful_logon
,SES.unsuccessful_logons
,SES.group_id
,SES.database_id
,SES.authenticating_database_id
,SES.open_transaction_count
,CONVERT(VARCHAR(100),SCON.most_recent_sql_handle,1) AS most_recent_sql_handle
FROM sys.dm_exec_connections SCON
OUTER APPLY sys.dm_exec_sessions SES
WHERE SCON.session_id = @@SPID
AND SES.session_id = @@SPID
) AS RWLog
FOR XML AUTO
)
END
;
';
EXEC (@SQL_STR_001);
/*
Generic audit table
*/
CREATE TABLE dbo.TBL_GENERIC_AUDIT
(
AUDIT_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_GENERIC_AUDIT_AUDIT_ID PRIMARY KEY CLUSTERED WITH ( FILLFACTOR = 100 /*, DATA_COMPRESSION = PAGE */ )
,AUDIT_TIMESTAMP DATETIME NOT NULL CONSTRAINT DFLT_DBO_TBL_GENERIC_AUDIT_AUDIT_TIMESTAMP DEFAULT ( GETDATE() )
,AUDIT_COL_IDENTITY INT NOT NULL
,AUDIT_ACTION_TYPE TINYINT NOT NULL
,AUDIT_SESSION_XML XML NOT NULL CONSTRAINT DFLT_DBO_TBL_GENERIC_AUDIT_AUDIT_SESSION_XML DEFAULT ( dbo.XUDF_GET_SESSION_CONNECTION_INFO() )
,AUDIT_XML XML NOT NULL
);
GO
CREATE TRIGGER dbo.TRG_AUDIT_TBL_GENERIC_AUDIT_READONLY ON dbo.TBL_GENERIC_AUDIT
INSTEAD OF UPDATE, DELETE
AS
BEGIN
INSERT INTO dbo.TBL_GENERIC_AUDIT (AUDIT_COL_IDENTITY,AUDIT_ACTION_TYPE,AUDIT_XML)
SELECT
D.AUDIT_ID
,ISNULL((SIGN(I.AUDIT_ID) * 127),255)
,( SELECT
CASE
WHEN I.AUDIT_ID IS NOT NULL THEN 3
ELSE 2
END AS '@Type'
,(
SELECT
AUDIT_ID
,AUDIT_TIMESTAMP
,AUDIT_COL_IDENTITY
,AUDIT_ACTION_TYPE
FROM inserted B
WHERE A.AUDIT_ID = B.AUDIT_ID
FOR XML PATH('INSERTED'), TYPE,ELEMENTS XSINIL
)
,(
SELECT
AUDIT_ID
,AUDIT_TIMESTAMP
,AUDIT_COL_IDENTITY
,AUDIT_ACTION_TYPE
FROM deleted C
WHERE A.AUDIT_ID = C.AUDIT_ID
FOR XML PATH('DELETED'), TYPE,ELEMENTS XSINIL
)
FROM deleted A
WHERE D.AUDIT_ID = A.AUDIT_ID
FOR XML PATH('ACTION'),TYPE,ELEMENTS XSINIL
)
FROM deleted D
LEFT OUTER JOIN inserted I
ON D.AUDIT_ID = I.AUDIT_ID;
END
GO
CREATE TABLE dbo.TBL_TEST_AUDIT
(
TEST_AUDIT_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
,ExpDate DATETIME NOT NULL DEFAULT(2958463.0)
,TEST_AUDIT_COL_1 INT NOT NULL
,TEST_AUDIT_COL_2 VARCHAR(50) NOT NULL
,TEST_AUDIT_COL_3 NVARCHAR(500) NULL
);
GO
CREATE TRIGGER dbo.TRG_AUDIT_DBO_TBL_TEST_AUDIT
ON dbo.TBL_TEST_AUDIT
/* TRIGGER TO CATCH DELETES MEETING A SPECIFIC CONDITITON
OF ExpDate = '9999-12-31 00:00:00.000'
*/
FOR DELETE
AS
BEGIN
WITH IDENTITY_VALUES(ID_VAL, ACTION_TYPE) AS
(
SELECT
X.ID_VAL
,SUM(X.ACTION_TYPE) AS ACTION_TYPE
FROM
(
SELECT
D.TEST_AUDIT_ID AS ID_VAL
,CONVERT(TINYINT,2,0) AS ACTION_TYPE
FROM deleted D
WHERE D.ExpDate = 2958463.0
) AS X
GROUP BY X.ID_VAL
)
INSERT INTO dbo.TBL_GENERIC_AUDIT (AUDIT_COL_IDENTITY,AUDIT_ACTION_TYPE,AUDIT_XML)
SELECT
X.ID_VAL
,X.ACTION_TYPE
,(
SELECT
X.ACTION_TYPE AS '@Type'
,(
SELECT
*
FROM deleted C
WHERE A.ID_VAL = C.TEST_AUDIT_ID
FOR XML PATH('DELETED'), TYPE,ELEMENTS XSINIL
)
FROM IDENTITY_VALUES A
WHERE X.ID_VAL = A.ID_VAL
FOR XML PATH('ACTION'),TYPE,ELEMENTS XSINIL
)
FROM IDENTITY_VALUES X
END
GO
/* Testin the objects
*/
INSERT INTO dbo.TBL_TEST_AUDIT (ExpDate, TEST_AUDIT_COL_1,TEST_AUDIT_COL_2,TEST_AUDIT_COL_3)
VALUES ('2014-12-31 00:00:00.000',10,'ABC',N'Despite such apparent rivalries and the huge sums each is investing in R&D, the bigger story is that they are co-operating to deliver 5G.')
,('9999-12-31 00:00:00.000',20,'DEF',N'"That''s until 6G comes along in around 2040," Prof Tafazolli remarks')
,('2015-12-31 00:00:00.000',30,'GHI',NULL)
,('9999-12-31 00:00:00.000',40,'JKL',NULL)
,('2016-12-31 00:00:00.000',50,'MNO',N'In South Korea, which spearheaded work on 4G, Samsung hopes to launch a temporary trial 5G network in time for 2018''s Winter Olympic Games.');
SELECT * FROM dbo.TBL_TEST_AUDIT;
SELECT * FROM dbo.TBL_GENERIC_AUDIT;
-- THIS DELETE IS NOT AUDITED
DELETE FROM dbo.TBL_TEST_AUDIT WHERE TEST_AUDIT_ID = 3;
SELECT * FROM dbo.TBL_TEST_AUDIT;
SELECT * FROM dbo.TBL_GENERIC_AUDIT;
-- THIS DELETE IS AUDITED
DELETE FROM dbo.TBL_TEST_AUDIT WHERE TEST_AUDIT_ID = 4;
SELECT * FROM dbo.TBL_TEST_AUDIT;
SELECT * FROM dbo.TBL_GENERIC_AUDIT;
-- THIS DELETE IS AUDITED
DELETE FROM dbo.TBL_TEST_AUDIT;
SELECT * FROM dbo.TBL_TEST_AUDIT;
SELECT * FROM dbo.TBL_GENERIC_AUDIT;
Edit: Typo
January 7, 2016 at 3:05 am
Hi,
looks really good, thanks!
so just to make sure, i need to replace TBL_TEST_AUDIT with my table and my columns and i cant audit the deletes in question, correct?
also, im guessing this would still fire on all the deletes, but will insert only the audited condition rows. i'm still a bit afraid of the impact it will cause. first i will try the extended events approach and if it will not be enough i will use the audit trigger approach.
Thanks for the help!!
January 7, 2016 at 3:23 am
neomerdien (1/7/2016)
Hi,looks really good, thanks!
so just to make sure, i need to replace TBL_TEST_AUDIT with my table and my columns and i cant audit the deletes in question, correct?
also, im guessing this would still fire on all the deletes, but will insert only the audited condition rows. i'm still a bit afraid of the impact it will cause. first i will try the extended events approach and if it will not be enough i will use the audit trigger approach.
Thanks for the help!!
You are right, replace TBL_TEST_AUDIT with your table and the filter columns and conditions in the delete trigger CTE.
😎
The impact of the trigger firing and not doing anything is minimal, it would be easy to use the example I posted to generate a test harness with few millions of rows in order to quantify that impact. I've used this in high volume / traffic systems without any problems.
If you implement this then you can have a disable trigger script ready, measure the impact and disable the trigger if it gets in the way.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply