none
Auditoria em ambiente de Produção RRS feed

  • Pergunta

  • Boa noite, a todos

    Estou trabalhando em processo de isolamento de ambientes de SQL Server, e enquanto não consigo tirar o acesso de todos os desenvolvedores deste ambiente, eu gostaria de pelo menos montar uns alertas via email, como se fosse uma auditoria, que quando um especifico usuário ou maquina faça uma conexão no SQL.

    Conto com a ajuda de todos.

    abraços
    Alberto Olavo
    terça-feira, 17 de março de 2009 00:15

Respostas

  • Bom dia Alberto,

    Semana passa precisei fazer uma trigger p/ auditar logins feitos pelo Management Studio, acho que isto pode te ajudar.
    -- Tabela de auditoria
    CREATE TABLE dbo.TB_AutitLogin (
    idPK int not null identity,
    Data datetime null,
    ProcID int null,
    NomeHost varchar(128) null,
    LoginName varchar(128) null,
    App varchar(128) null,
    SchemaAutenticacao varchar(128) null,
    Protocolo varchar(128) null,
    IPcliente varchar(30) null,
    IPservidor varchar(30) null,
    xmlConectInfo xml)
    GO
    CREATE NONCLUSTERED INDEX ix_TB_AutitLogin_01 ON TB_AutitLogin (idPK)
    CREATE CLUSTERED INDEX ix_TB_AutitLogin_02 ON TB_AutitLogin (Data)
    -- Step 2: Cria Trigger de Login
    -- DROP TRIGGER TR_AuditLogin ON ALL SERVER
    CREATE TRIGGER TR_AuditLogin ON ALL SERVER
    WITH EXECUTE AS SELF
    FOR LOGON
    AS
    --gravar login somente de Management Studio
    IF app_name() like 'Microsoft SQL Server%' --AND ORIGINAL_LOGIN()<>'usuario_teste' 
    BEGIN 
    -- Login sucesso 
    --insert @TB_AutitLogin
    INSERT dbo.TB_AutitLogin
    SELECT s.login_time,@@spid,s.[host_name],s.original_login_name, --ORIGINAL_LOGIN(),
    s.program_name,c.auth_scheme,c.net_transport,
    c.client_net_address,c.local_net_address,eventdata()
    FROM sys.dm_exec_sessions s INNER JOIN sys.dm_exec_connections c
    ON s.session_id = c.session_id
    WHERE s.session_id = @@spid 
    AND (c.net_transport <> 'Shared memory' AND c.net_transport <> 'Session')
    END
    GO

     

     

    Att.
    Marcelo Fernandes
    ________________________________________
    Se este post lhe foi útil, por favor classifique-o.

    segunda-feira, 30 de março de 2009 11:14
    Moderador

Todas as Respostas

  • Alberto,

    Qual é a versão do SQL Server que você esta utilizando?

    Montar alertas via e-mail de acordo com o usuário que esta acessando o SQL Server, mas qual é a limitação que você esta tendo para não conseguir fazer este isolamento?
    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    terça-feira, 17 de março de 2009 10:54
    Moderador
  • Boa noite, Junior

    Minha versão é SQL 2005.

    Na verdade não existe uma limitação, mas no meu panorama estou tendo que administrar 
    um ambiente que é dividido com os fornecedores de nosso software, no caso, do USA.

    Então eu gostaria de receber alertas, notificando o acesso deles ao meu servidor.

    Espero ter esclarecido minha necessidade.

    abraços

    Alberto Olavo
    terça-feira, 17 de março de 2009 23:08
  • Boa noite, a todos

    Alguém conseguiria me ajudar com este assunto ?

    abraços
    Alberto Olavo
    segunda-feira, 30 de março de 2009 01:47
  • Bom dia Alberto,

    Semana passa precisei fazer uma trigger p/ auditar logins feitos pelo Management Studio, acho que isto pode te ajudar.
    -- Tabela de auditoria
    CREATE TABLE dbo.TB_AutitLogin (
    idPK int not null identity,
    Data datetime null,
    ProcID int null,
    NomeHost varchar(128) null,
    LoginName varchar(128) null,
    App varchar(128) null,
    SchemaAutenticacao varchar(128) null,
    Protocolo varchar(128) null,
    IPcliente varchar(30) null,
    IPservidor varchar(30) null,
    xmlConectInfo xml)
    GO
    CREATE NONCLUSTERED INDEX ix_TB_AutitLogin_01 ON TB_AutitLogin (idPK)
    CREATE CLUSTERED INDEX ix_TB_AutitLogin_02 ON TB_AutitLogin (Data)
    -- Step 2: Cria Trigger de Login
    -- DROP TRIGGER TR_AuditLogin ON ALL SERVER
    CREATE TRIGGER TR_AuditLogin ON ALL SERVER
    WITH EXECUTE AS SELF
    FOR LOGON
    AS
    --gravar login somente de Management Studio
    IF app_name() like 'Microsoft SQL Server%' --AND ORIGINAL_LOGIN()<>'usuario_teste' 
    BEGIN 
    -- Login sucesso 
    --insert @TB_AutitLogin
    INSERT dbo.TB_AutitLogin
    SELECT s.login_time,@@spid,s.[host_name],s.original_login_name, --ORIGINAL_LOGIN(),
    s.program_name,c.auth_scheme,c.net_transport,
    c.client_net_address,c.local_net_address,eventdata()
    FROM sys.dm_exec_sessions s INNER JOIN sys.dm_exec_connections c
    ON s.session_id = c.session_id
    WHERE s.session_id = @@spid 
    AND (c.net_transport <> 'Shared memory' AND c.net_transport <> 'Session')
    END
    GO

     

     

    Att.
    Marcelo Fernandes
    ________________________________________
    Se este post lhe foi útil, por favor classifique-o.

    segunda-feira, 30 de março de 2009 11:14
    Moderador
  • Alberto,

    Acredito que o exemplo do Marcelo poderá te ajudar!!!!
    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    segunda-feira, 30 de março de 2009 12:04
    Moderador
  • Bom dia, Marcelo

    Muito obrigado pela sua ajuda, estarei aplicando este script em um dos meus servidores de teste para poder validar as funcionalidades, mas tenho uma grande resalva com a utilização de Triggers, basicamente com relação a possível perda de performance do servidor. Então, com sua experiência no assunto, o que você me diz ? existe mesmo perda de performance ou é mito !!!

    abraços e obrigado
    Alberto Olavo
    segunda-feira, 30 de março de 2009 12:06
  • Bom dia Olavo,

    O evento que dispara a trigger é o LOGON, logo se vc possui muito logon no seu server isto vai te consumir um pouco mais de recurso, em emu ambiente tenho em media  1100 logons por dia, e não tenho baixa performance por isto...

    Em relacao ao uso da trigger causar baixa performance, não é mito não.... dependendo do seu script e ambiente a trigger pode lhe causar baixa performance (mais para triggers disparadas por INSERT, DELETE e UPDATE).

    Os amigos MVPS podem lhe dar uma visão melhor... mais esta trigger de logon em específico acredito nao lhe causar baixa performance por ser disparada somente no LOGON.

    Att.
    Marcelo
    Att. Marcelo Fernandes ------------------------------------------------ MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    segunda-feira, 30 de março de 2009 13:14
    Moderador
  • Bom Dia,

    Acho que a explicação do Marcelo já está completa. A trigger representa uma ação adicional ao evento que a chamou e se esse evento ocorre poucas vezes, o overhead tende a ser mínimo. Normalmente se fala muito dos problemas de desempenho e eles realmente existem. Tudo depende também do que está dentro da trigger (não creio que o código seja tão penoso a ponto de você notar queda sensível no desempenho). Faça um teste e avalie a situação antes e depois da trigger.

    Para melhorar um pouco, eu utilizaria o índice clustered sobre o campo Identity, pois, se houver muitos logons no mesmo momento a largura do índice irá aumentar (índices clustered podem ter registros duplicados, mas haverá um sufixo de 4bytes adicional). Claro que esse índice é perfeito para pesquisas do tipo RANGE (bem típicas de auditoria), mas não creio que você vá consultar essa tabela com uma freqüência maior que a de logon.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    OUTER JOIN com mais de duas tabelas ? Será que está mesmo “certo” ?
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!514.entry
    Classifique as respostas. O seu feedback é imprescindível
    segunda-feira, 30 de março de 2009 13:37