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

  • 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


    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.

    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
  • 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


    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)

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

    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:28
    Moderador
  • 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)
    END

    segunda-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:46
    Moderador
  • 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