Visualizações: 5.189 views
- 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,
Bom dia.
Hoje venho trazer pra vocês, a solução de algumas perguntas que eu me fazia nos ambientes em que trabalho, mas que eu não tinha como responder:
– Quem criou/alterou/excluiu um determinado job?
– Apaguei sem querer um Job.. Como faço pra recriá-lo rapidamente, sem precisar de restaurar backup ?
– Alguém desabilitou um determinado Job.. Quem foi ?
– Alguém alterou a descrição do Job.. Preciso identificar quem foi..
Para responder essas questões, iremos criar uma forma de monitorar a criação, modificação e exclusão de Jobs no SQL Server, através de uma trigger na tabela msdb.dbo.sysjobs.
Criando a tabela de histórico
Antes de criar a trigger que irá recuperar as informações, precisaremos criar a tabela que irá guardar as informações e criar o histórico.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE [dbo].[Job_Audit] ( [Id_Auditoria] [INT] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL, [Dt_Evento] [DATETIME] NULL DEFAULT (GETDATE()), [Ds_Usuario] [VARCHAR](50) NULL, [Ds_Job] [sysname] NULL, [Ds_Hostname] [VARCHAR](50) NULL, [Ds_Query] [VARCHAR](MAX) NULL, [Fl_Situacao] [TINYINT] NULL ) WITH (DATA_COMPRESSION=PAGE) |
Criação da trigger
Agora que criamos a tabela que terá as nossas informações, vamos criar a trigger que irá populá-la.
|
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 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 |
USE [msdb] GO /*************************************************************************************************** -- Trigger para os Jobs ***************************************************************************************************/ IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgJobs_Status') > 0) DROP TRIGGER dbo.trgJobs_Status GO CREATE TRIGGER trgJobs_Status ON sysjobs AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON DECLARE @UserName VARCHAR(50) = SYSTEM_USER, @HostName VARCHAR(50) = HOST_NAME(), @JobName sysname, @New_Enabled INT, @Old_Enabled INT, @ExecStr VARCHAR(100), @Qry VARCHAR(MAX) SELECT @New_Enabled = [enabled] FROM Inserted SELECT @Old_Enabled = [enabled] FROM Deleted SELECT @JobName = [name] FROM Deleted IF (@JobName IS NULL) SELECT @JobName = [name] FROM Deleted -- Identificando a query executada CREATE TABLE #inputbuffer ( [EventType] NVARCHAR(60), [Parameters] INT, [EventInfo] VARCHAR(MAX) ) SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')' INSERT INTO #inputbuffer EXEC (@ExecStr) SET @Qry = (SELECT EventInfo FROM #inputbuffer) -- Verifica se houve alteração de status IF (@New_Enabled != @Old_Enabled) BEGIN IF (@New_Enabled = 1) BEGIN INSERT INTO dbo.Job_Audit ( Ds_Usuario, Ds_Job, Ds_Hostname, Ds_Query, Fl_Situacao ) SELECT @UserName, @JobName, @HostName, @Qry, 1 END IF (@New_Enabled = 0) BEGIN INSERT INTO dbo.Job_Audit ( Ds_Usuario, Ds_Job, Ds_Hostname, Ds_Query, Fl_Situacao ) SELECT @UserName, @JobName, @HostName, @Qry, 0 END END ELSE BEGIN INSERT INTO dbo.Job_Audit ( Ds_Usuario, Ds_Job, Ds_Hostname, Ds_Query ) SELECT @UserName, @JobName, @HostName, @Qry END END GO /*************************************************************************************************** -- Trigger para os Schedules dos Jobs ***************************************************************************************************/ IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgAudit_Schedules') > 0) DROP TRIGGER dbo.trgAudit_Schedules GO CREATE TRIGGER [dbo].[trgAudit_Schedules] ON [dbo].[sysschedules] AFTER UPDATE, DELETE AS BEGIN SET NOCOUNT ON DECLARE @UserName VARCHAR(50) = SYSTEM_USER, @HostName VARCHAR(50) = HOST_NAME(), @JobName VARCHAR(MAX) = '', @ExecStr VARCHAR(100), @Qry VARCHAR(MAX) IF ((SELECT COUNT(*) FROM Inserted) > 0) BEGIN SELECT @JobName += (CASE WHEN @JobName != '' THEN ' | ' ELSE '' END) + A.[name] FROM msdb.dbo.sysjobs A JOIN msdb.dbo.sysjobschedules B ON A.job_id = B.job_id JOIN Inserted C ON B.schedule_id = C.schedule_id END ELSE BEGIN SELECT @JobName += (CASE WHEN @JobName != '' THEN ' | ' ELSE '' END) + A.[name] FROM msdb.dbo.sysjobs A JOIN msdb.dbo.sysjobschedules B ON A.job_id = B.job_id JOIN Deleted C ON B.schedule_id = C.schedule_id END -- Identificando a query executada CREATE TABLE #inputbuffer ( [EventType] NVARCHAR(60), [Parameters] INT, [EventInfo] VARCHAR(MAX) ) SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')' INSERT INTO #inputbuffer EXEC (@ExecStr) SET @Qry = (SELECT EventInfo FROM #inputbuffer) IF (@JobName != '') BEGIN INSERT INTO dbo.Job_Audit ( Ds_Usuario, Ds_Job, Ds_Hostname, Ds_Query ) SELECT @UserName, @JobName, @HostName, @Qry END END GO ALTER TABLE [dbo].[sysschedules] ENABLE TRIGGER [trgAudit_Schedules] GO /*************************************************************************************************** -- Trigger para os Schedules ***************************************************************************************************/ IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgAudit_Jobs_Schedules') > 0) DROP TRIGGER dbo.trgAudit_Jobs_Schedules GO CREATE TRIGGER [dbo].[trgAudit_Jobs_Schedules] ON [dbo].[sysjobschedules] AFTER INSERT AS BEGIN SET NOCOUNT ON DECLARE @UserName VARCHAR(50) = SYSTEM_USER, @HostName VARCHAR(50) = HOST_NAME(), @JobName sysname, @ExecStr VARCHAR(100), @Qry VARCHAR(MAX) IF ((SELECT COUNT(*) FROM Inserted) > 0) BEGIN SELECT @JobName = A.[name] FROM msdb.dbo.sysjobs A JOIN Inserted B ON A.job_id = B.job_id END ELSE BEGIN SELECT @JobName = A.[name] FROM msdb.dbo.sysjobs A JOIN Deleted B ON A.job_id = B.job_id END -- Identificando a query executada CREATE TABLE #inputbuffer ( [EventType] NVARCHAR(60), [Parameters] INT, [EventInfo] VARCHAR(MAX) ) SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')' INSERT INTO #inputbuffer EXEC (@ExecStr) SET @Qry = (SELECT EventInfo FROM #inputbuffer) INSERT INTO dbo.Job_Audit ( Ds_Usuario, Ds_Job, Ds_Hostname, Ds_Query ) SELECT @UserName, @JobName, @HostName, @Qry END GO ALTER TABLE [dbo].[sysjobschedules] ENABLE TRIGGER [trgAudit_Jobs_Schedules] GO |
Consultando as informações
Agora que nossa trigger está ativada, faça algumas alterações em seus Jobs e veja como as informações são gravadas. É possível identificar a data/hora que o evento foi acionado, o usuário que realizou o evento e mesmo a query que foi executada no banco, seja para criação, exclusão ou alteração do Job.
Até mais!
Portuguese 
English
Excelente me ajudou muito . Obrigado
Aqui não está gravando nada, devo alterar algo?
Perfeito. Ótimo Post. Vem muitas informacoes precisas.