- 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,
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 How to create an Audit trigger to log object manipulation in 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 |
And that's it, folks!
Obrigado pela visita e até o próximo post.
Portuguese 
English