- Auditing in SQL Server (Server Audit)
- How to Create an Audit to Monitor Job Creation, Modification, and Deletion in SQL Server
- How to create an Audit trigger to log object manipulation in SQL Server
- SQL Server - How to implement login auditing and control (Logon Trigger)
- Monitoring DDL and DCL operations using SQL Server's fn_trace_gettable
- Using the standard SQL Server trace to audit events (fn_trace_gettable)
- SQL Server – Permissions and privileges audit trigger at database and instance level (GRANT and REVOKE)
- SQL Server - How to monitor and audit data changes in tables using Change Data Capture (CDC)
- SQL Server 2016 - How to "time travel" using the Temporal Tables feature
- SQL Server - How to use auditing to map actual required permissions on a user
- SQL Server - Trigger to prevent and prevent changes in tables
- SQL Server - How to Create a Data Change History for Your Tables (Audit Logs)
- SQL Server - How to avoid brute force attacks on your database
- SQL Server – Security Checklist – An SP with over 70 security items to validate your database
- SQL Server - How to know the last login date of a user
- SQL Server - How to avoid and protect yourself from Ransomware attacks like WannaCry on your database server
- SQL Server - Watch out for the securityadmin server role! Using elevation of privileges to become sysadmin
- SQL Server – How to avoid SQL Injection? Stop using Dynamic Query like EXEC(@Query). Now.
- SQL Server - Understanding the risks of the TRUSTWORTHY property enabled on a database
- SQL Server - Password Policies, Password Expiration, Mandatory Password Change and Login Blocking after several Attempts
- SQL Server - How to create a login audit using instance logs
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 [Auditing] 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 Auditing.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.
And that's it, folks!
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.