none
SQL Server 2008 & Auditoria vs. Tentativas maus sucedidas RRS feed

  • Pergunta

  • Olá Senhores,

    Para atender requisitos de auditoria estamos implementando a auditoria do SQL Server para registrar tudo que esta sendo alterado no SQL Server ( novos logins, privelegios, alteração de permissoes e outros) e estamos criando relatórios no Report Server para que a gerencia e o pessoal da auditoria possa visualizar. Poderem precisamos chegar no nivel de não apenas mostrar o que foi alterado mas também tentativas mal sucedidas de auteração. Como por exemplo o relatório deve mostrar se o usuário X que não tem permissão de dar permissões a outros usuários tentou dar privilegios para alguém. A auditoria do SQL Server é capaz de chegar nesse nisso? Como posso habilitar isso?

    Desde já agradeço a ajuda de vocês,

    Justo Daniel

    MCP-MCDTS-MCTS SQL Server 2008

    quinta-feira, 28 de março de 2013 21:15

Respostas

  • Olá,

    Primeiramente você pode restringir muitos acessos através de logins, usuários, server roles, database roles e se for SQL Server 2012 poderá usar Custom Roles, com isso elimina-se a necessidade de auditar muitos itens e de gastar "energia" do SQL Server em coisas que podem ser prevenidas por meios melhores. Acredito que sim, você consegue pegar essas informações com Server Audit Especification, deve haver algum grupo que fique visualizando se as roles foram mudadas, quem fez login, logout entre outros. Mas nunca implementei isso em produção.

    Se a resposta foi util, classifique-a


    Att,
    Marcos Freccia [MTA|MCTS|MCITP|MCT SQL Server 2008]
    Blog|Twitter
    Assine também os feeds clicando aqui

    segunda-feira, 1 de abril de 2013 23:57
  • Justo,

    Os links você tem que tentar dentro do Books On-Line!

    Em relação a tentantivas mal sucedidas, se for no processo de login você poderá utilizar Trigger de Logon, veja o exemplo abaixo:

    USE DBSUPSQL;
    GO
     
    ----====================================================================
    --- ESCOPO DE CRIACAO DE TABELA , PK , INDICE ( NONCLUSTERED) 
    ----====================================================================
    
    If Object_Id ('Auditoria_Logon') Is Not Null
     Drop Table Auditoria_Logon ;
    Go
    
    
    Create Table dbo.Auditoria_Logon
    
     (  id_Auditoria_Logon  Int Identity (1,1),
        [Login]        Varchar(100)           , 
        Data_Login     SmallDatetime          ,
        Usuario        Varchar(100)           , 
        Aplicacao      Varchar(100)           , 
        [Host]         Varchar(100)           , 
        [DataBase]     Varchar(100)           , 
        Evento         Varchar(Max))
    
    
    Go 
    Alter Table Auditoria_Logon Add Constraint Pk_Auditoria_Logon  Primary Key (id_Auditoria_Logon)
    Go
    
    Create NonClustered Index ID_01 On Auditoria_Logon ([Login], Data_Login ) With FillFactor = 80 
    Go 
    
    ----====================================================================
    --- TRIGGER DE LOGON 
    ----====================================================================
    
    If Object_Id ('trg_Auditoria_LOgin') Is Not Null
     Drop  trg_Auditoria_LOgin ;
    Go
    
    CREATE TRIGGER trg_Auditoria_LOgin
              ON ALL SERVER FOR LOGON -- With Execute as 'sa'
              AS
    BEGIN
             
    
    Insert Into Auditoria_Logon ( Login,Data_Login,Usuario,Aplicacao,[Host],[DataBase],Evento ) 
    
      Select Login        = ORIGINAL_LOGIN()  , 
             Data_Login   = Getdate()         , 
             Usuario      = USER_NAME()       , 
             Aplicacao    = APP_NAME()        , 
             Host         = HOST_NAME()       , 
             [DataBase]   = DB_NAME()         ,
    
             Evento       = Convert(Varchar(max),EVENTDATA())
    
    ----========================================================================
    --- ESSE FILTRO DEPENDE DA NOSSA NECESSIDADE LOGAR UMA VEZ AO DIA OU LOGAR 
    --- TODA VEZ QUE ABRIR UMA SESSAO COM SQL SERVER ?
    ----=======================================================================
    
    
     Where Not Exists  ( Select * 
                            From Auditoria_Logon aa
                           Where Usuario = USER_NAME()
                             And Convert(Varchar(10), Data_Login ,113) = Convert(Varchar(10), Getdate()  ,113))
            
       END;

    Agora em relação aos processos de manipulação sem autorização, será que alguma Facets, Condicitions e Policies não podem ajudar?

    Talvez seja o caso de criar uma Trigger de DML e de acordo com algum tipo de código de erro do SQL Server registrar esse evento em uma table.

    Veja se este exemplo ajuda em algo:

    Create Table T1 
    (Codigo Int)
    
    Create Table T2
    (Codigo Int)
    
    DENY SELECT ON OBJECT::dbo.T2 TO Pedro;
    
    Execute As User = 'Pedro'
    
    BEGIN TRY
         Select * from T2
    END TRY
    BEGIN CATCH
        If (ERROR_NUMBER() = 229 )
         Print 'Sem permissão'  
    END CATCH;
    GO

    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]


    quinta-feira, 4 de abril de 2013 17:04
    Moderador

Todas as Respostas

  • Olá,

    Primeiramente você pode restringir muitos acessos através de logins, usuários, server roles, database roles e se for SQL Server 2012 poderá usar Custom Roles, com isso elimina-se a necessidade de auditar muitos itens e de gastar "energia" do SQL Server em coisas que podem ser prevenidas por meios melhores. Acredito que sim, você consegue pegar essas informações com Server Audit Especification, deve haver algum grupo que fique visualizando se as roles foram mudadas, quem fez login, logout entre outros. Mas nunca implementei isso em produção.

    Se a resposta foi util, classifique-a


    Att,
    Marcos Freccia [MTA|MCTS|MCITP|MCT SQL Server 2008]
    Blog|Twitter
    Assine também os feeds clicando aqui

    segunda-feira, 1 de abril de 2013 23:57
  • Justo,

    Sinceramente todo processo de auditoria é algo que tem que ser muito bem pensado e analisado, pois o custo de processamento que é gerado em alguns cenários pode impactar em muito no uso do servidor.

    Desde o SQL Server 2008 a Microsoft introduziu o objeto Audit que tem justamente esta finalidade que você necessita, e fui muito melhorado no R2 e agora no 2012.

    Além de toda parte de Segurança que o próprio SQL Server oferece, muitas configurações também pode ser feitas no próprio Windows e monitoradas e catalogadas pelo Event Viewer do Windows o poderá ser útil.

    Em relação as tentativas de manipulação de dados, o Transact Log é o responsável em armazenar tudo isso, mas a leitura e acesso ao mesmo não é realizado de forma nativa, o que requer o uso de ferramentas próprias.

    Em alguns caso um Trigger do tipo DML é algo que poderá ajudar, mas com muito cuidado, para não sobrecarregar a parte transacional do SQL Server.

    Veja se este link do Books On-Line poderá ajudar: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.pt/s10de_4deptrbl/html/0c1fca2e-f22b-4fe8-806f-c87806664f00.htm


    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]

    terça-feira, 2 de abril de 2013 17:22
    Moderador
  • Marcos & Junior,

             Primeiramente. Obrigado pelas respostas. Acompano bastante os foruns e vejo sempre a proatividades de vocês em ajudar.

             A ideia de minha gerencia é realmente ter um relatorio contendo também as tentativas mal sucedidas como somos uma empresa de seguimento bancário esse tipo de informação é importante para nós. Entendo e concordo que é uma processo que tenha que ser analisado e bem pensado. Estamos nesse nível de maturidade porém a gerencia deseja que sejam registrado essas tentativas mas  sucedidas. Vou testar melhor cada uma das audit specifications para ver qual dela poderá me atender e post os resultados. Obrigado.

    Junior,

         Desculpa mais eu tentei acessar esse link porém não obtive sucesso. Como faço?

    

    Justo Daniel

    MCP-MCDTS-MCTS SQL Server 2008

           

    terça-feira, 2 de abril de 2013 20:00
  • Justo,

    Os links você tem que tentar dentro do Books On-Line!

    Em relação a tentantivas mal sucedidas, se for no processo de login você poderá utilizar Trigger de Logon, veja o exemplo abaixo:

    USE DBSUPSQL;
    GO
     
    ----====================================================================
    --- ESCOPO DE CRIACAO DE TABELA , PK , INDICE ( NONCLUSTERED) 
    ----====================================================================
    
    If Object_Id ('Auditoria_Logon') Is Not Null
     Drop Table Auditoria_Logon ;
    Go
    
    
    Create Table dbo.Auditoria_Logon
    
     (  id_Auditoria_Logon  Int Identity (1,1),
        [Login]        Varchar(100)           , 
        Data_Login     SmallDatetime          ,
        Usuario        Varchar(100)           , 
        Aplicacao      Varchar(100)           , 
        [Host]         Varchar(100)           , 
        [DataBase]     Varchar(100)           , 
        Evento         Varchar(Max))
    
    
    Go 
    Alter Table Auditoria_Logon Add Constraint Pk_Auditoria_Logon  Primary Key (id_Auditoria_Logon)
    Go
    
    Create NonClustered Index ID_01 On Auditoria_Logon ([Login], Data_Login ) With FillFactor = 80 
    Go 
    
    ----====================================================================
    --- TRIGGER DE LOGON 
    ----====================================================================
    
    If Object_Id ('trg_Auditoria_LOgin') Is Not Null
     Drop  trg_Auditoria_LOgin ;
    Go
    
    CREATE TRIGGER trg_Auditoria_LOgin
              ON ALL SERVER FOR LOGON -- With Execute as 'sa'
              AS
    BEGIN
             
    
    Insert Into Auditoria_Logon ( Login,Data_Login,Usuario,Aplicacao,[Host],[DataBase],Evento ) 
    
      Select Login        = ORIGINAL_LOGIN()  , 
             Data_Login   = Getdate()         , 
             Usuario      = USER_NAME()       , 
             Aplicacao    = APP_NAME()        , 
             Host         = HOST_NAME()       , 
             [DataBase]   = DB_NAME()         ,
    
             Evento       = Convert(Varchar(max),EVENTDATA())
    
    ----========================================================================
    --- ESSE FILTRO DEPENDE DA NOSSA NECESSIDADE LOGAR UMA VEZ AO DIA OU LOGAR 
    --- TODA VEZ QUE ABRIR UMA SESSAO COM SQL SERVER ?
    ----=======================================================================
    
    
     Where Not Exists  ( Select * 
                            From Auditoria_Logon aa
                           Where Usuario = USER_NAME()
                             And Convert(Varchar(10), Data_Login ,113) = Convert(Varchar(10), Getdate()  ,113))
            
       END;

    Agora em relação aos processos de manipulação sem autorização, será que alguma Facets, Condicitions e Policies não podem ajudar?

    Talvez seja o caso de criar uma Trigger de DML e de acordo com algum tipo de código de erro do SQL Server registrar esse evento em uma table.

    Veja se este exemplo ajuda em algo:

    Create Table T1 
    (Codigo Int)
    
    Create Table T2
    (Codigo Int)
    
    DENY SELECT ON OBJECT::dbo.T2 TO Pedro;
    
    Execute As User = 'Pedro'
    
    BEGIN TRY
         Select * from T2
    END TRY
    BEGIN CATCH
        If (ERROR_NUMBER() = 229 )
         Print 'Sem permissão'  
    END CATCH;
    GO

    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]


    quinta-feira, 4 de abril de 2013 17:04
    Moderador