March 10, 2011 at 10:10 am
Hi!!
Today I had a problem with an important table on my database.
Someone updated all rows in that table, by mistake, because he did not put the where clause.
I'm wondering if exists some way to ensure that everyone use the Where clause on this table, with some kind of trigger. Is that possible?
Thks, excuse my rusty english 😀
March 10, 2011 at 10:18 am
It could be something like this:
CREATE TRIGGER My_Trigger
ON MyTable
FOR DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQLBuffer nvarchar(4000)
DECLARE @buffer TABLE (
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000)
)
INSERT @buffer
EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'
SELECT @SQLBuffer = EventInfo
FROM @buffer
IF @SQLBuffer NOT LIKE '%WHERE%'
BEGIN
RAISERROR('Please provide a WHERE clause!!!!',16,1)
ROLLBACK
END
END
Be aware that it won't prevent people from truncating the table or deleting the wrong data anyway.
Hope this helps
Gianluca
-- Gianluca Sartori
March 10, 2011 at 10:41 am
I like that idea, Gianluca;
the only problem with dbcc inputbuffer is it is limited to 256 characters....I'd think offhand that a delete is going to be inside that limit to find the WHERE clause; it's typically DELETE FROM TABLENAME WHERE ....
but if you had a complex DELETE FROM, the where statement might be outside of that first 256 chars just keep that in mind for those "edge"/rare situations.
Lowell
March 10, 2011 at 10:54 am
Lowell (3/10/2011)
the only problem with dbcc inputbuffer is it is limited to 256 characters....
It was limited to 256 characters in SQL 2000, but from 2005 onwards it returns nvarchar(4000).
The real problem is that this trigger not only won't prevent deleting all rows by accident, but could also prevent deleting a few rows legitimately:
DELETE t
FROM MyTable t
INNER JOIN RowsToDelete r
ON t.id = r.id
See? No WHERE clause, but legitimate statement. It's very hard to decide whether the statement should be blocked based on its syntax.
Another possibile solution could be checking table count in the trigger:
CREATE TRIGGER My_Trigger
ON MyTable
FOR DELETE
AS
BEGIN
SET NOCOUNT ON;
IF (SELECT COUNT(*) FROM MyTable) = 0
BEGIN
RAISERROR('You can''t delete all rows from this table!',16,1)
ROLLBACK
END
END
But, again, how can you say that emptying the table isn't a legitimate operation?
-- Gianluca Sartori
March 10, 2011 at 1:42 pm
Thks about your answers, but I have a question (i'm newbie in this kind of stuff): on my post i'm talking about updates, and not about deletes 😉
Can I do this to update too? It works by the same way?
March 10, 2011 at 1:48 pm
man i learn more hear every day; thanks once again to you Gianluca;
yes, your trigger could look for a WHERE statement...you could also limit the trigger to test for specific users, and also whether a WHERE statement exists...but it really sounds like people have access to adhoc queries when they should not.
Lowell
March 10, 2011 at 2:41 pm
Also, the WHERE-clause-check trigger would need to cover WHERE 1=1, too.
I actually like your last approach, Gianluca. Instead of trying to analyze the code, check for the number of affected rows. Nice! One could query the DELETED and/or INSERTED table and compare the number of affected rows to the total number of rows in the target table after the update.
That still won't prevent from a c.u.r.s.o.r. deleting one row at a time based on a declaration without a WHERE clause...
@rpfelgueiras: you might want to get familiar with the point in time recovery concept SQL Server offers. Since it's almost impossible to prevent against updating or deleting all rows "at once" (including loop approaches) you probably should look into conepts how to recover the data....
March 10, 2011 at 3:57 pm
Here is a similar but slightly different version that has a get-out clause that allows deliberate global updates...
CREATE TRIGGER PreventGlobalUpdateTrigger ON YourTable
AFTER UPDATE,DELETE
AS
DECLARE @rowc BIGINT;
SET @rowc = @@ROWCOUNT;
-- Test for existence of temp table that allows global update
-- if #allowglobalupdate exists in tempdb then you can affect the whole table - just once.
IF OBJECT_ID('tempdb..#allowglobalupdate') IS NOT NULL
BEGIN
-- Remove the temp table so that you can't accidentally run global update twice
DROP TABLE #allowglobalupdate;
-- And then quit the trigger - allowing the update to happen.
RETURN;
END;
-- Choose some percentage that you consider to be too many rows to update
-- Here I have chosen 95% of the current rowcount
IF @rowc>=0.95 * (SELECT COUNT_BIG(*) FROM YourTable)
BEGIN
ROLLBACK TRAN;
RAISERROR(N'
You are not allowed to perform a global update on this table.
Please create a temp table called #allowglobalupdate to enable this facility.',11,1);
END;
Having put this up here, I wouldn't recommend doing it on a busy table - it's overhead you don't need.
Look into decent backup strategies and train your people not to run ad-hoc queries without testing them inside a transaction with rollback.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);March 10, 2011 at 3:59 pm
rpfelgueiras (3/10/2011)
Hi!!Today I had a problem with an important table on my database.
Someone updated all rows in that table, by mistake, because he did not put the where clause.
I'm wondering if exists some way to ensure that everyone use the Where clause on this table, with some kind of trigger. Is that possible?
Thks, excuse my rusty english 😀
The real answer is, of course, only allow access to the table through stored procedures that you control!
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);March 11, 2011 at 1:40 am
rpfelgueiras (3/10/2011)
Thks about your answers, but I have a question (i'm newbie in this kind of stuff): on my post i'm talking about updates, and not about deletes 😉Can I do this to update too? It works by the same way?
Oh, my bad! Sorry, I must have missed it from the start.
However, it works for updates as well. You will have to play with counts from the physical table and the DELETED logical table (it will hold all rows modified).
I suppose that for some reason you can't deny direct access to the table: it happended to me some years ago and I ended up coding a trigger to capture and reject all modifications from SSMS, unless explicitly requested by setting a CONTEXT_INFO:
CREATE TRIGGER MyTrigger
ON MyTable
FOR INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @program_name nvarchar(128)
SELECT @program_name = program_name
FROM sys.dm_exec_sessions
WHERE session_id = @@spid
-- Is it SSMS?
IF @program_name LIKE '%Management%Studio%'
BEGIN
IF CONTEXT_INFO() IS NULL OR CONTEXT_INFO() <> 0xCAFEBABE
BEGIN
-- Accidental update? No, thanks.
RAISERROR('No updates allowed here!!',16,1)
ROLLBACK
RETURN
END
ELSE
BEGIN
-- Explicit update, let through and reset CONTEXT_INFO.
SET CONTEXT_INFO 0x0
END
END
END
GO
--- Script to allow updates from SSMS:
SET CONTEXT_INFO 0xCAFEBABE
UPDATE MyTable
SET SomeField = 1
WHERE Id = 100
It doesn't prevent people from messing with data with a bad query (or intentionally), but it prevents accidental updates. Also, the error message you get when a query is issued without setting CONTEXT_INFO reminds you that this is a VERY important table and you should update it carefully.
-- Gianluca Sartori
September 22, 2012 at 4:23 am
instead stopping scripts without WHERE clause for a particular table (which we do by triggers),
is there a way like parsing all queries running on SSMS which doesnt have a WHERE clause, may be by any add-on parser tool, which does for us.?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply