locked
Log operações dos usuários RRS feed

 > 

  • Discussão Geral

  • Discussion
    Entrar para Votar
    0
    Entrar para Votar

     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

  • Discussion
    Entrar para Votar
    0
    Entrar para Votar

    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


    quinta-feira, 6 de dezembro de 2012 12:35
  • Discussion
    Entrar para Votar
    0
    Entrar para Votar

    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
  • Discussion
    Entrar para Votar
    0
    Entrar para Votar

    Rodrigo, dá uma olhada nesse artigo e veja se consegue se orientar para criar a política.

    http://imasters.com.br/artigo/20457/sql-server/gerenciamento-de-objetos-baseado-em-policies-no-sql-server-2008

    Abçs

    quinta-feira, 6 de dezembro de 2012 13:51
  • Discussion
    Entrar para Votar
    0
    Entrar para Votar
    Criar as regras eu entendi, só queria uma listagem do que cada função faz.
    quinta-feira, 6 de dezembro de 2012 13:54
  • Discussion
    Entrar para Votar
    0
    Entrar para Votar

    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


    quinta-feira, 6 de dezembro de 2012 16:35
  • Discussion
    Entrar para Votar
    0
    Entrar para Votar

    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)

    Values ('Base excluída', User, System_User, GetDate()) END

    sexta-feira, 7 de dezembro de 2012 19:18
  • Discussion
    Entrar para Votar
    0
    Entrar para Votar

    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:28
    Moderador
  • Discussion
    Entrar para Votar
    0
    Entrar para Votar

    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
  • Discussion
    Entrar para Votar
    0
    Entrar para Votar

    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)
    END

    segunda-feira, 10 de dezembro de 2012 17:29
  • Discussion
    Entrar para Votar
    0
    Entrar para Votar

    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:46
    Moderador
  • Discussion
    Entrar para Votar
    0
    Entrar para Votar

    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:50
    Moderador