Bloquear update sem where

SQL Server

14/12/2016

Opa pessoal, como posso impedir que o comando update sem a cláusula where possa ser executado no sql server?
Calebe Menezes

Calebe Menezes

Curtidas 0

Melhor post

Aline Bianchini

Aline Bianchini

14/12/2016

Olá Calebe, tudo bem?

Não é a forma mais bonita de ser resolver eu acho, mas o que você pode fazer é criar um Trigger.

Você pode:

1. Procurar na clausula a palavra 'WHERE'. Seria algo tipo:

CREATE TRIGGER My_Trigger
   ON  MyTable
   FOR UPDATE
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('Comando sem WHERE',16,1)
		ROLLBACK
	END
	
END


2.Comparar se o número de linhas afetadas é igual ao número de linhas da tabela que você está tentando fazer o update, se for, bloquear o comando. Seria algo tipo:

CREATE TRIGGER My_trigger
ON MyTable 
FOR UPDATE AS 
BEGIN
     DECLARE @Count int
     SET @Count = @@ROWCOUNT;
         
     IF @Count >= (SELECT SUM(row_count)
         FROM sys.dm_db_partition_stats 
         WHERE OBJECT_ID = OBJECT_ID('MyTable' ) 
         AND index_id = 1)
     BEGIN
         RAISERROR('Cannot update all rows',16,1) 
         ROLLBACK TRANSACTION
         RETURN;
     END
END
GO
GOSTEI 4

Mais Respostas

Mariana Carvalho

Mariana Carvalho

14/12/2016

Bom dia Calebe, acho que o impedimento tecnicamente seria a criação de um usuário com permissão limitada, mas não lembro especificamente se pode ser feito apenas com clausula.

A outra maneira, é mais conhecida, conscientização, ter uma politica de backup.
GOSTEI 2
Mariana Carvalho

Mariana Carvalho

14/12/2016

Eu achei complicado, dificil mesmo! rsrsrs.
Aline, você que fez essa trigger? fora a questão que escreveu, existe algum problema em usa-la?
GOSTEI 0
David Styveen

David Styveen

14/12/2016

Bom dia, Calebe.

Deixar a consulta implícita de transação não resolveria? Dessa forma, todo comando executado deve confirmar a transação.
GOSTEI 1
Mariana Carvalho

Mariana Carvalho

14/12/2016

Feito no banco esse procedimento David?
GOSTEI 0
Fabio Parreira

Fabio Parreira

14/12/2016

Cara, uso este código no meu.
Funciona muito bem, uso ele na produção.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[NOMEDATRIGGER_COMANDO_INVALIDO]
ON  [dbo].[SUATABELA]
FOR UPDATE,DELETE AS 
BEGIN
     DECLARE @Count int
     SET @Count = @@ROWCOUNT;
         
     IF @Count >= (SELECT sum(rowcnt)
         FROM sysindexes 
         WHERE ID = OBJECT_ID('dbo.SUATABELA' ))
     BEGIN
         RAISERROR('Comando inválido',16,1) 
         ROLLBACK TRANSACTION
         RETURN;
     END
  END
GOSTEI 3
Calebe Menezes

Calebe Menezes

14/12/2016

Opa gente, valeu pela ajuda!

Realmente minha intenção é deixar alguma coisa "programática" já fazendo o impedimento da execução desse tipo de query.

É claro que além disso o pessoal é alertado para tomar cuidado quando executar suas queries rs.

Acho que a solução do trigger seria a melhor para esse meu caso.

Obrigado a todos pelas dicas!
GOSTEI 1
David Styveen

David Styveen

14/12/2016

Pessoal, desculpe a demora.

Fim de ano corrido.

Em Ferramentas > Opções > Execução de Consulta > SQL Server > ANSI > marcar a opção SET IMPLICIT_TRANSACTIONS

Essa opção irá abrir uma transação para qualquer comando DML, sendo assim necessário sua confirmação após executar o comando.
Essa opção ajuda a minimizar as ocorrências de comandos sem where, porém não os impede.
O problema dessa solução, é que deverá ser configurada em cada instalação do SQL Server Management Studio. Portanto, ela não impede, por exemplo, que um comando enviado por um aplicação faça o update ou delete sem where.

Fica a dica para conhecimento.

Abraços.
GOSTEI 3
Calebe Menezes

Calebe Menezes

14/12/2016

Opa David Styveen,

Show de bola mano! Grande colaboração!
GOSTEI 0
POSTAR