- Auditoria no SQL Server (Server Audit)
- Como criar uma auditoria para monitorar a criação, modificação e exclusão de Jobs no SQL Server
- Como criar uma trigger de Auditoria para logar a manipulação de objetos no SQL Server
- SQL Server – Como implementar auditoria e controle de logins (Trigger de Logon)
- Monitorando operações de DDL e DCL utilizando a fn_trace_gettable do SQL Server
- Utilizando o trace padrão do SQL Server para auditar eventos (fn_trace_gettable)
- SQL Server – Trigger de auditoria de permissões e privilégios a nível de database e instância (GRANT e REVOKE)
- SQL Server – Como monitorar e auditar alterações de dados em tabelas utilizando Change Data Capture (CDC)
- SQL Server 2016 – Como “viajar no tempo” utilizando o recurso Temporal Tables
- SQL Server – Como utilizar auditoria para mapear permissões necessárias reais em um usuário
- SQL Server – Trigger para prevenir e impedir alterações em tabelas
- SQL Server – Como criar um histórico de alterações de dados para suas tabelas (logs para auditoria)
- SQL Server – Como evitar ataques de força bruta no seu banco de dados
- SQL Server – Checklist de Segurança – Uma SP com mais de 70 itens de segurança para validar seu banco de dados
- SQL Server – Como saber a data do último login de um usuário
- SQL Server – Como evitar e se proteger de ataques de Ransomware, como WannaCry, no seu servidor de banco de dados
- SQL Server – Cuidado com a server role securityadmin! Utilizando elevação de privilégios para virar sysadmin
- SQL Server – Como evitar SQL Injection? Pare de utilizar Query Dinâmica como EXEC(@Query). Agora.
- SQL Server – Entendendo os riscos da propriedade TRUSTWORTHY habilitada em um database
- SQL Server – Políticas de Senhas, Expiração de Senha, Troca de Senha Obrigatória e Bloqueio de Login após N tentativas
- SQL Server – Como criar uma auditoria de logins utilizando os logs da instância
Olá pessoal!
Uma necessidade corriqueira de DBA’s é evitar acessos e operações indevidas ou equivocadas, que até mesmo o próprio DBA pode acabar tendo um descuido e executar uma operação errada. A minha ideia nesse post, é demonstrar algumas soluções utilizando triggers.
Criação da tabela de testes
|
1 2 3 4 5 6 7 8 9 10 |
IF (OBJECT_ID('dbo.Teste_Trigger') IS NOT NULL) DROP TABLE dbo.Teste_Trigger CREATE TABLE dbo.Teste_Trigger ( Id INT IDENTITY(1, 1) NOT NULL, Nome VARCHAR(100) NOT NULL ) INSERT INTO dbo.Teste_Trigger VALUES ( 'Dirceu Resende' ), ( 'Teste' ), ( 'Teste 2') SELECT * FROM dbo.Teste_Trigger |
Evitar comandos DML na tabela (Tabela somente leitura)
Caso você não queira permitir operações de DML (INSERT, UPDATE e DELETE) na sua tabela, transformando-a assim, em uma tabela somente leitura (read-only table), basta criar a trigger abaixo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
IF (OBJECT_ID('[dbo].[trgBloqueia_Dml_Teste]') IS NOT NULL) DROP TRIGGER [dbo].[trgBloqueia_Dml_Teste] GO CREATE TRIGGER [dbo].[trgBloqueia_Dml_Teste] ON [dbo].[Teste_Trigger] FOR INSERT, UPDATE, DELETE AS BEGIN IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) BEGIN ROLLBACK TRANSACTION; RAISERROR ('Operações de INSERT não são permitidas na tabela "Teste_Trigger"', 15, 1); RETURN; END IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted) BEGIN ROLLBACK TRANSACTION; RAISERROR ('Operações de DELETE não são permitidas na tabela "Teste_Trigger"', 15, 1); RETURN; END IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) BEGIN ROLLBACK TRANSACTION; RAISERROR ('Operações de UPDATE não são permitidas na tabela "Teste_Trigger"', 15, 1); RETURN; END END; GO |
Resultado:
Evitar DELETE ou UPDATE sem where
Um erro muito comum no dia a dia do DBA, é quando algum analista ou desenvolvedor envia um script de UPDATE ou DELETE para o DBA executar e eles esquecem de colocar a cláusula WHERE. Imaginem o estrago que isso pode gerar no banco de dados.. Uma forma de evitar isso, é utilizando plugins e add-ons, como Redgate SQL Prompt e ApexSQL Complete, que alertam o DBA quando isso vai ocorrer.
Uma outra forma (e mais confiável), é criar uma trigger para isso:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
IF (OBJECT_ID('[dbo].[trgEvita_Acidentes]') IS NOT NULL) DROP TRIGGER [dbo].[trgEvita_Acidentes] GO CREATE TRIGGER [dbo].[trgEvita_Acidentes] ON [dbo].[Teste_Trigger] FOR UPDATE, DELETE AS BEGIN DECLARE @Linhas_Alteradas INT = @@ROWCOUNT, @Linhas_Tabela INT = (SELECT SUM(row_count) FROM sys.dm_db_partition_stats WHERE [object_id] = OBJECT_ID('Teste_Trigger') AND (index_id <= 1)) IF (@Linhas_Alteradas >= @Linhas_Tabela) BEGIN ROLLBACK TRANSACTION; RAISERROR ('Operações de DELETE e/ou UPDATE sem cláusula WHERE não são permitidas na tabela "Teste_Trigger"', 15, 1); RETURN; END END; GO |
Resultado:
Permitir apenas 1 registro alterado por vez na tabela
Uma outra necessidade que pode ocorrer em algumas tabelas, é exigir que apenas 1 registro da tabela seja alterado por vez através de uma operação de DELETE/UPDATE:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
IF (OBJECT_ID('[dbo].[trgEvita_Dml_Muitos_Registros]') IS NOT NULL) DROP TRIGGER [dbo].[trgEvita_Dml_Muitos_Registros] GO CREATE TRIGGER [dbo].[trgEvita_Dml_Muitos_Registros] ON [dbo].[Teste_Trigger] FOR UPDATE, DELETE AS BEGIN DECLARE @Linhas_Alteradas INT = @@ROWCOUNT, @MsgErro VARCHAR(MAX) IF (@Linhas_Alteradas > 1) BEGIN ROLLBACK TRANSACTION; SET @MsgErro = 'Operações de DELETE e/ou UPDATE só podem atualizar 1 registro por vez na tabela "Teste_Trigger", e você tentou atualizar ' + CAST(@Linhas_Alteradas AS VARCHAR(50)) RAISERROR (@MsgErro, 15, 1); RETURN; END END; GO |
Ao tentar atualizar mais de um registro, a trigger irá barrar a operação:
Apresentadas as soluções acima, espero ter tirado suas dúvidas sobre restrição de DML no SQL Server utilizando Triggers. Esse é um recurso bem antigo do SGBD, odiado por uns, amado por outros e que tem sim, sua utilidade quando bem aplicado e seu uso é justificado.
Espero que tenham gostado desse post e caso tenham dúvidas, deixem aqui nos comentários.
Um abraço e até o próximo post!
Portuguese 
English
Muito bom….
Excelente artigo, me ajudou muito!
Não só pelo ensinamento do conteúdo, mas pelo ensinamento da lógica que pode ser aplicada em incalculáveis de cenários diferentes.
Olá Dirceu:
Eu acho que o seu artigo é muito bom, desculpe meu português, sou do Chile. Como você pode evitar a modificação de um ou mais registros em uma tabela, mas com a condição de que o campo “userid” seja igual a XXX. Você poderia me escrever para [email protected]? Muito obrigado
Muito bom o Artigo.
Parabens!