Inquiridor
Log operações dos usuários

Discussão Geral
-
Bom dia pessoal,
Estou com alguns problemas internos onde frequentemente são excluídas databases que não deveriam. A minha versão é sql server 2008R2 Enterprise. Gostaria de saber como posso recuperar logs das operações de exclusões e se tem como identificar o IP, pois utilizamos um usuário único para cada setor. Se não der para recuperar o IP, pode ser o usuário do banco mesmo. É necessário que as bases estejam em modo cheio? Outra coisa, hoje não fazemos esse procedimento de auditoria, alguma sugestão, software, triggers?
Obrigado
Rodrigo
quinta-feira, 6 de dezembro de 2012 12:30
Todas as Respostas
-
Rodrigo,
Já pensou em utilizar trigger DDL para impedir que os bancos sejam excluídos ou então fazer auditoria? (http://msdn.microsoft.com/pt-br/library/ms186406(v=sql.105).aspx)
Também pode utilizar o recurso Policy-Based Management. (http://msdn.microsoft.com/pt-br/library/bb510667(v=sql.105).aspx)
Abçs
- Editado _Juliano_Alves_ quinta-feira, 6 de dezembro de 2012 12:46
quinta-feira, 6 de dezembro de 2012 12:35 -
Juliano,
Não sou especialista em Banco de dados, não conheço essas duas opções. A trigger dei uma pesquisada, é mais a nível de database porém na minha situação há um giro muito grande de exclusões e restore e seria inviável configurar isso para cada base. Com relação as políticas, você poderia me ajudar como configurar, se há como, para bloquear exclusões de databases que não seja do teu usuário, por exemplo.
Rodrigo
quinta-feira, 6 de dezembro de 2012 13:31 -
Rodrigo, dá uma olhada nesse artigo e veja se consegue se orientar para criar a política.
quinta-feira, 6 de dezembro de 2012 13:51 -
Criar as regras eu entendi, só queria uma listagem do que cada função faz.quinta-feira, 6 de dezembro de 2012 13:54
-
Rodrigo, utilizando trigger DDL você consegue impedir e até auditar, segue exemplo:
ALTER TRIGGER nome_trigger ON ALL SERVER FOR DROP_DATABASE AS IF (SYSTEM_USER <> 'DOMINIO\usuario') BEGIN PRINT 'Operação não autorizada' ROLLBACK TRANSACTION
-- Inclua aqui o insert em sua tabela de auditoria se desejar END
Abçs
- Editado _Juliano_Alves_ quinta-feira, 6 de dezembro de 2012 16:40
quinta-feira, 6 de dezembro de 2012 16:35 -
Juliano,
Dei uma fuçada nessa rotina e acabei fazendo o script abaixo que funcionou 100%. Porém eu queria buscar também o nome da base que for excluída quando satisfazer a primeira ação. Tem como?
Alter
TRIGGER PrevineDropDatabase ON ALL SERVER
FOR
DROP_DATABASE
AS
IF
(SYSTEM_USER <> 'qualid') AND (SYSTEM_USER <> 'desenv') AND(SYSTEM_USER <> 'suporte')
BEGIN
PRINT 'OPERACAO NAO AUTORIZADA'
ROLLBACK TRANSACTION
Insert Into SimulacaoBlog.dbo.LogDatabases (DsAlteracao, NmUsuario, NmUsuario_Sistema, DtAlteracao)
Values ('Tentativa de exclusao', User, System_User, GetDate()) -- Inclua aqui o insert em sua tabela de auditoria se desejar
END
ELSE
BEGIN
PRINT 'OPERACAO AUTORIZADA'
Insert Into SimulacaoBlog.dbo.LogDatabases (DsAlteracao, NmUsuario, NmUsuario_Sistema, DtAlteracao)
sexta-feira, 7 de dezembro de 2012 19:18 -
Rodrigo,
Como você esta utilizando o SQL Server 2008, poderia tentar utilizar o objeto Audit existente a partir desta versão que possibilidade fazer este controle de auditoria no SQL Server em níveis de : Servidor, Banco de Dados, Objetos e Usuários.
Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]
segunda-feira, 10 de dezembro de 2012 16:28Moderador -
Junior,
Obrigado pela dica, porém eu preciso para as duas versões. No 2008 até tentei porém nas actions não tem a opção DELETE, não sei se é somente em uma versão mais avançada. A versão do 2008 é Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) . Para o 2005 vou utilizar as triggers.
Rodrigo
segunda-feira, 10 de dezembro de 2012 17:04 -
Junior,
Aproveitando seu apoio, construí a trigger abaixo (mudei da anterior) e está 100% conforme meu esperado, porém além de gravar em um database eu queria salvar sempre em um arquivo no disco do servidor, pois os demais usuários possuem acesso completo e podem querer excluir. Utilizei um tal exec xp_cmdshell mas travou geral o banco. Podes me ajudar como fazer junto com essa trigger?
ALTER TRIGGER TRIGGERAUDITORIA ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
DECLARE @ResultXML XML
SET @ResultXML = EVENTDATA()
DECLARE @Culpado SYSNAME, @HoradoCrime DATETIME, @ProvadoCrime SYSNAME, @TipodoCrime SYSNAME, @Maquina nvarchar(50)
SET @Culpado = @ResultXML.value('(EVENT_INSTANCE/LoginName)[1]','SYSNAME')
SET @HoradoCrime = @ResultXML.value('(EVENT_INSTANCE/PostTime)[1]','DATETIME')
SET @ProvadoCrime = @ResultXML.value('(EVENT_INSTANCE/TSQLCommand/CommandText)[1]','SYSNAME')
SET @TipodoCrime = @ResultXML.value('(EVENT_INSTANCE/EventType)[1]','SYSNAME')
SET @Maquina = HOST_NAME()
Insert Into AUDITORIA.dbo.LogDatabases (TIPO_ALTERACAO, USUARIO, COMANDO_EXECUTADO, DATA_HORA, MAQUINA)
Values (@TipodoCrime, @Culpado, @ProvadoCrime, @HoradoCrime, @Maquina)
ENDsegunda-feira, 10 de dezembro de 2012 17:29 -
Rodrigo,
Não utilize a xp_cmdshell dentro da trigger, pois como a trigger é um recurso condicional ela vai ficar aberta aguardando a conclusão da xp_cmdshell para se encerrar isso poderá gerar deadlocks.
Minha sugestão, você poderia criar um Job que realiza a leitura dos dados e posteriormente grave no arquivo deseja, até mesmo fazendo uso do comando SQLCMD ou BCP.
Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]
segunda-feira, 10 de dezembro de 2012 17:46Moderador -
Rodrigo,
Veja estes exemplos de trigger:
SELECT [Spid] = session_Id , ecid , [Database] = DB_NAME(sp.dbid) , [User] = nt_username , [Status] = er.status , [Wait] = wait_type , [Individual Query] = SUBSTRING (qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2) ,[Parent Query] = qt.text , Program = program_name , Hostname , nt_domain , start_time FROM sys.dm_exec_requests er INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt WHERE session_Id > 50 -- Ignore system spids. AND session_Id NOT IN (@@SPID) -- Ignore this current statement. ORDER BY 1, 2
Create Table DDL_LogTransacoes (TransID Int Identity(1,1) Primary Key, DataTransacao DateTime Default GetDate(), UsuarioTransacao VarChar(100) Default User_Name(), LoginTransacao Varchar(100) Default Suser_Name(), LoginUsuarioSQLTransacao Varchar(100) Default Original_Login(), TipoEvento NVarchar(200) Null, Objeto NVarchar(200) Null, TipoObjeto NVarchar(200) Null, Comando_TSQL NVarchar(Max) Null) Create Trigger Trigger_DDL_LogTransacoes On Database For DDL_DATABASE_LEVEL_EVENTS As Begin Set NoCount On Declare @DadosXML XML Set @DadosXML=EVENTDATA() Insert Into DDL_LogTransacoes(TipoEvento,Objeto,TipoObjeto,Comando_TSQL) Values(@DadosXML.value('(EVENT_INSTANCE/EventType)[1]','NVarchar(200)') , @DadosXML.value('(EVENT_INSTANCE/ObjectName)[1]','NVarchar(200)') , @DadosXML.value('(EVENT_INSTANCE/ObjectType)[1]','NVarchar(200)') , @DadosXML.value('(EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVarchar(Max)')) End Create Table Teste (Codigo Int, Descricao Varchar(100)) Insert Into Teste Values (1,'Arroz') Insert Into Teste Values (2,'Arroz') Insert Into Teste Values (3,'Arroz') Update Teste Set Descricao='Feijão' Where Codigo=3 Select * from DDL_LogTransacoes Select * from sys.event_notification_event_types Where TYPE_NAME Like '%Events%'
Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]
segunda-feira, 10 de dezembro de 2012 17:50Moderador