Block tsql updates with missing WHERE clause

views:

answers:

Block tsql updates with missing WHERE clause

In the context of MS SQL Server 2005

Simply put is there a way to stop delete, and update sql statements being executed against the database that don't have a WHERE clause?

Ideally it would be nice to restrict this 'blocking' to a set of users/roles.

+2 A:

Simply answered: no.

It is your responsibility to make sure you don't send those kind of queries. SQL Server cannot "know" or "find out" when a command is truly missing a WHERE clause, or when it really should be executed without any WHERE.

marc_s 2010-10-21 15:03:13

I knew the answer but thought I'd ask the question anyway. Its not me but our silly support team; I was hoping for a novel way to solve this problem.

mouters 2010-10-21 15:09:16

@mouters: one way might be a SET ROWCOUNT (x) limitation - but that opens up a plethora of other problems (paging etc.)....

marc_s 2010-10-21 15:10:01

@mouters: might be nice to be able to define a "return max (n) rows" for each user / role in SQL Server - but AFAIK, right now, there's nothing like that available ....

marc_s 2010-10-21 15:16:35

To explore this further the users are using sql managament studio; any options here or perhaps a custom plugin or macro (to validate sql for a where clause before executing)?

mouters 2010-10-21 15:22:42

@mouters: no, unfortunately, not to my knowledge....

marc_s 2010-10-21 16:11:36

+2 A:

you could create a trigger that does a ROLLBACK of the update if the row count is the same as the count of rows in the table. if you want that trigger code, leave a comment.

KM 2010-10-21 15:34:02

Thanks for your thoughts on this - would it be a generic trigger applied to all tables, also how would this effect performance?

mouters 2010-10-21 15:51:52

triggers are per table, so you'd need one on every table. It certainly wouldn't be the best thing for performance. If your trigger did `IF user_name() IN ('bad1','bad2',..) BEGIN ... END` it would only really impact the bad users. You might not want to hard code the bad users in every trigger, so you could do `IF EXISTS (SELECT 1 FROM YourBadUserTable WHERE BadUserName = user_name() BEGIN ... END`

KM 2010-10-21 16:05:51

You could also communicate with the trigger using CONTEXT_INFO, where valid application UPDATE/DELETEs would set its value and the trigger would ignore any checking, but if someone outside the application tries to alter the database, the trigger could do the extra checking, see: http://stackoverflow.com/questions/2834509/is-it-possible-to-definitively-identify-whether-a-dml-command-was-issued-from-a-s/2834593#2834593

KM 2010-10-21 16:13:54

ansaurus

tags:

views:

answers:

Block tsql updates with missing WHERE clause

related questions