- 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,
Tudo bem com vocês ?
Depois de MUITO tempo sem postar nenhum artigo técnico, estou começando a ter um tempo novamente para trazer mais scripts e artigos legais que eu gostaria de compartilhar com vocês.
Neste post, vou demonstrar como criar uma trigger para auditar eventos de concessão e remoção de permissões (GRANT e REVOKE) em objetos, databases, alterações de usuários em roles (database roles e server roles), além de permissões diretas em databases. Essa necessidade surgiu através de uma demanda que chegou pra mim, pois alguns usuários estavam perdendo acesso em uma determinada instância e não tinha nenhum controle para identificar quem estava removendo e quem concedeu esses acessos.
Script de criação da tabela de histórico de permissões
Com o script abaixo, você poderá criar a tabela que vai armazenar todo o histórico de permissões concedidas e removidas na instância.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
USE [Auditoria] GO IF (OBJECT_ID('dbo.Trace_Alteracao_Privilegios') IS NULL) BEGIN CREATE TABLE dbo.Trace_Alteracao_Privilegios ( Id_Evento BIGINT IDENTITY(1, 1) PRIMARY KEY, Ds_Evento VARCHAR(255), Dt_Evento DATETIME, Ds_Database VARCHAR(255), Ds_Schema VARCHAR(255), Ds_Objeto VARCHAR(255), Ds_Tipo_Objeto VARCHAR(255), Ds_Usuario VARCHAR(255), Ds_Comando VARCHAR(MAX), Evento XML ); GRANT INSERT ON dbo.Trace_Alteracao_Privilegios TO [public]; END |
Script de criação da trigger
Com o script abaixo, você irá criar a trigger que faz a coleta a auditoria das permissões.
|
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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
USE [master] GO IF ((SELECT COUNT(*) FROM sys.server_triggers WHERE name = 'trgAudit_Privileges') > 0) DROP TRIGGER [trgAudit_Privileges] ON ALL SERVER GO CREATE TRIGGER trgAudit_Privileges ON ALL SERVER FOR DDL_SERVER_SECURITY_EVENTS, DDL_DATABASE_SECURITY_EVENTS AS BEGIN DECLARE @Ds_Evento NVARCHAR(255), @Ds_Schema NVARCHAR(255), @Ds_Database VARCHAR(255), @Ds_Objeto VARCHAR(255), @Ds_TipoObjeto VARCHAR(255), @Evento XML, @Ds_Comando VARCHAR(MAX); SELECT @Ds_Evento = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(max)'), @Ds_Schema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(max)'), @Ds_Objeto = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)'), @Ds_TipoObjeto = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(max)'), @Ds_Database = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(max)'), @Ds_Comando = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'), @Evento = EVENTDATA() INSERT INTO Auditoria.dbo.Trace_Alteracao_Privilegios SELECT @Ds_Evento, GETDATE() AS Dt_Evento, @Ds_Database, @Ds_Schema, @Ds_Objeto, @Ds_TipoObjeto, SUSER_SNAME(), @Ds_Comando, @Evento END |
Resultados
Agora vou demonstrar alguns exemplos do que essa trigger pode auditar.
É isso aí, pessoal!
Um abraço pra vocês e até o próximo post.
Portuguese 
English
Dirceu, boa tarde. Estou utilizando seus scripts em minha base de testes e estou tendo problemas parecidos com o do Laércio. Eu até consigo logar no ambiente, mas não consigo criar login mais. Sou bloqueada pela trigger. Não entendi como isso ocorre, uma vez que a trigger só faz select e insert. Tem ideia do por que isso estar ocorrendo? Obrigada.
Dirceu,
Consegui resolver! Não fui o primeiro a fazer essa besteira e nesse link tem a solução. Deu certo!
http://www.connectsql.com/2012/09/sql-server-disable-logon-trigger-using.html
Desculpa o incômodo e obrigado pela atenção mais uma vez!
Grande abraço!
Desculpe, Durceu. Comentei na postagem errada!
Estou falando daquela sua trigger de Auditoria de Logins. Nela que fiz a caca.
E não consigo logar nem com “sa” e nem com qualquer usuário do domínio. Só com um único usuário público que deixei de fora dela. Ainda bem, senão o sistema teria parado!
Vou ficar te devendo muito se me ajudar a sair dessa sem ter que reinstalar o SQL no servidor.
Olá, Dirceu!
Estava testando essa trigger e achei um problema com uma situação aqui no meu ambiente, de login com Crystal Reports. Na tentativa de ajustar isso setei os parâmetros CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS e ANSI_PADDING para OFF e recriei a trigger. Resultado: não consigo mais logar no servidor! Só consigo com um usuário público que deixei de fora do controle, mas com isso não consigo mais alterar nem excluir a trigger.
Tem alguma sugestão para me ajudar a resolver essa patetada que fiz ?
Laércio,
Bom dia.
Você tentou logar com o usuário “sa” ou algum usuário que seja sysadmin ? Não entendi porque essa trigger causaria esse efeito, porque ela só audita eventos de segurança e não de login.