Neste artigo
- 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
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!
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!