Post reply

Enforce Where clause on every update statement

  • March 10, 2011 at 10:10 am

    #147595

    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

    #1297222

    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

    #1297238

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 10, 2011 at 10:54 am

    #1297241

    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

    #1297357

    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

    #1297362

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 10, 2011 at 2:41 pm

    #1297380

    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....


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • March 10, 2011 at 3:57 pm

    #1297393

    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);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • March 10, 2011 at 3:59 pm

    #1297396

    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);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • March 11, 2011 at 1:40 am

    #1297474

    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

    #1541059

    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 11 (of 11 total)

    You must be logged in to reply to this topic. Login to reply

    Cookies on SQLServerCentral

    This website stores cookies on your computer.

    These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

    To find out more about the cookies we use, see our Privacy Policy