- 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,
Boa tarde!
Neste post vou demonstrar como identificar os eventos de DDL e DCL que ocorrem na instância SQL Server. Isso nos permite identificar qual o usuário que criou/alterou/apagou um objeto, ou quem adicionou/removeu um usuário em uma role de sistema ou role de database e várias outras coisas sem a necessidade de criar nenhum controle ou monitoramento interno, utilizando apenas o trace padrão do SQL Server, onde iremos consultar seus dados através da função fn_trace_gettable.
Há um tempo atrás eu publiquei aqui o post Como criar uma trigger de Auditoria para logar a manipulação de objetos no SQL Server que permitia criar uma server trigger que realiza essa auditoria. Apesar da função fn_trace_gettable retornar boa parte das informações disponíveis nesse controle interno, essa trigger permite fazer o versionamento dos objetos alterados, como procedures, functions, tabelas, etc. Ou seja, caso você queira automatizar o versionamento de código-fonte de objetos no banco de dados, o post mais indicado é o citado logo acima.
Relembrando os conceitos de DDL e DCL
Acredito que esses conceitos já são bem conhecidos pela maioria de vocês, mas vou reforçar o que é cada coisa para não restarem dúvidas:
Operações DDL (Data Definition Language)
Sâo utilizados para definir ou alterar estruturas ou schemas de bancos de dados
CREATE – Utilizado para criar objetos no banco de dados
ALTER – Altera a estrutura de um objeto
DROP – Remove um objeto do banco de dados
TRUNCATE – Apapga todos os registros de uma tabela, incluindo o espaço alocado.
COMMENT – Adiciona comentários em objetos
RENAME – Renomeia um objeto
Operações DCL (Data Control Language)
Sâo utilizados para gerenciamento de permissões e privilégios de usuários em determinados objetos da instância SQL Server
GRANT – Concede acesso para um usuário em um determinado objeto
REVOKE – Remove o acesso de um usuário em um determinado objeto
Identificando os tipos de eventos do trace
Utilizando a query abaixo, podemos identificar rapidamente todos os EventClass que podemos utilizar na função ::fn_trace_gettable:
|
1 2 3 4 5 6 7 8 |
DECLARE @id INT = ( SELECT id FROM sys.traces WHERE is_default = 1 ) SELECT DISTINCT eventid, name FROM fn_trace_geteventinfo(@id) A JOIN sys.trace_events B ON A.eventid = B.trace_event_id |
Identificando os eventos de DDL e DCL na instância SQL Server
Com a query abaixo, é possível identificar quem foi a última pessoa a alterar um determinado objeto na instância (Ex: Stored Procedure), quem adicionou um determinado usuário em uma role, quem apagou uma tabela, quem executou um comando de TRUNCATE, etc, e tudo isso sem precisar criar nenhum controle além dos já disponíveis e ativados por padrão no SQL Server.
|
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 |
DECLARE @Ds_Arquivo_Trace VARCHAR(255) = (SELECT SUBSTRING([path], 0, LEN([path])-CHARINDEX('\', REVERSE([path]))+1) + '\Log.trc' FROM sys.traces WHERE is_default = 1) SELECT A.HostName, A.ApplicationName, A.NTUserName, A.NTDomainName, A.LoginName, A.SPID, A.EventClass, B.name, A.EventSubClass, A.TextData, A.StartTime, A.ObjectName, A.DatabaseName, A.TargetLoginName, A.TargetUserName FROM [fn_trace_gettable](@Ds_Arquivo_Trace, DEFAULT) A JOIN master.sys.trace_events B ON A.EventClass = B.trace_event_id WHERE A.EventClass IN ( 164, 46, 47, 108, 110, 152 ) AND A.StartTime >= GETDATE()-7 AND A.LoginName NOT IN ( 'NT AUTHORITY\NETWORK SERVICE' ) AND A.LoginName NOT LIKE '%SQLTELEMETRY$%' AND A.DatabaseName != 'tempdb' AND NOT (B.name LIKE 'Object:%' AND A.ObjectName IS NULL ) AND NOT (A.ApplicationName LIKE 'Red Gate%' OR A.ApplicationName LIKE '%Intellisense%' OR A.ApplicationName = 'DacFx Deploy') ORDER BY StartTime DESC |
É isso aí, pessoal!
Obrigado pela visita e até o próximo post.
Portuguese 
English