none
Amarmazenar histórico de INSERT/UPDATE e DELETE para identificar alterações indevidas RRS feed

  • Pergunta

  • Olá, estou com uma questão de provar quem fez o que, explicando:

    Um dos sistemas administrativos utiliza/possui um banco de dados próprio (Controle trivial Clientes, Estoque, Financeiro, compras, vendas, etc).., e até então nunca tivemos problemas com esse banco, acontece que dois novos softwares foram contratados na empresa um CRM e outro ecommerce, ambos com integração ao banco de dados já existente. Estamos cientes que a melhor solução seria criar uma camada de API para receber essas solicitações e controlar as requisições. Porém devido ao formato que o cliente negociou com esses fornecedores, foram fornecidos usuários do banco específicos para esses sistemas, porém em várias tabelas ambos tem direito de escrita, pois necessitam atualizar alguns valores. Então agora começa o problema, são vários casos em várias tabelas, mas vamos isolar apenas um:

    Um Cliente possui um campo de "Status" que determina se ele está ativo, inadimplente, bloqueado, limite excedido entre outros. Acontece que alguns registros de clientes foram bloqueados indevidamente provavelmente por alguma falha de um desses novos softwares, e o pior que esse "erro" é percebido semanas ou até mesmo meses depois devido a frequência de compras do cliente, e adivinhem ninguém assume que pode ter um BUG. Na tabela de clientes existe um campo que "loga" quem foi o último usuário do banco que alterou o registro, mas não adianta pois pode não ter sido realmente quem alterou o status nesse exemplo.

    Nossa ideia inicial é criar uma tabela que armazene o histórico de comandos, que contenha O Comando SQL em si, Usuário que executou, data/hora, nome da tabela, e um campo com o ID (PK do registro na origem). Essa tabela deverá guardar TODOS os comandos INSERT, UPDATE e DELETE do banco, similar ao que podemos obter quando utilizamos o profile, ela será armazenada em um banco separado por questão de política de Backup. Com esse conteúdo histórico podemos filtrar a tabela e o ID do registro a ser investigado e finalmente identificar quem executou o comando que modificou o conteúdo.

    Ainda acrescento:

    1) Possuímos backups diários dos últimos 90 dias, mas mesmo com isso, e após muito trabalho só conseguimos identificar o dia que o conteúdo mudou e o último usuário,

    2) Não podemos fornecer o banco aos softwares para que eles investiguem pois existem outras informações que são restritas. Porém preciso fornecer esse "histórico" de comandos de acordo com as tabelas que eles possuem acesso.

    3) Os comandos de SELECT não são necessários

    No caso de nossa ideia, como posso implementar uma forma automática de que TODOS os comandos em TODAS as tabelas sejam capturados e inseridos na tabela de histórico?

    Como você resolveria esse caso?



    • Editado MatheusFernando sexta-feira, 6 de setembro de 2019 11:54 Remover caracteres indesejados
    sexta-feira, 6 de setembro de 2019 11:45

Todas as Respostas

  • Mateus, crie um banco de auditoria e uma tabela com as saídas do select abaixo. Depois crie uma proc que rode a cada minuto ou 5 minutos, enfim, no tempo que você achar necessário, fazendo um INSERT/SELECT.

    Note que a consulta abaixo, vai pegar alterações feitas por logins sysadmin, inclusive o campo de texto que foi feito. Com isso você pode fazer suas alterações conforme sua necessidade.

    OBS: a tabela vai ficando muito grande, então tem que ir criando históricos ou fazendo purge regularmente.

    SELECT  sson.login_name,sson.login_time, db_name(dbid)database_name, conn.session_id,
    sqltxt.text,  conn.last_read, sson.status, sson.host_name
    FROM sys.dm_exec_connections conn
    INNER JOIN sys.dm_exec_sessions sson 
    ON conn.session_id = sson.session_id
    CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS sqltxt
    WHERE sson.login_name IN (SELECT SSPs.name FROM sys.server_principals SSPs LEFT JOIN
       sys.server_role_members SSRM ON SSPs.principal_id  = SSRM.member_principal_id
       LEFT JOIN sys.server_principals SSPs2   ON SSRM.role_principal_id = SSPs2.principal_id 
       WHERE SSPs2.name = 'sysadmin')


    Luiz Fernando Lima - MCTS SQL Server - Microsoft Partner


    sexta-feira, 6 de setembro de 2019 14:01
  • Matheus,

    Além das sugestões do Luiz Fernando, existem alguns recursos adicionados desde a versão 2008 do SQL Server conhecidos como CDC - Change Data Capture e CT - Change Tracking que podem também te ajudar a implementar este mesmo tipo de controle, mas no caso diretamente relacionado ao dado que foi alterado e o que foi alterado.

    Veja abaixo os exemplos:

    -- Habilitando o CDC para o Banco de Dados --
    Use CDCDatabase
    Go
    
    Exec sys.sp_cdc_enable_db
    Go
    
    -- Desabilitando o CDC para o Banco de Dados --
    Use CDCDatabase
    Go
    
    Exec sys.sp_cdc_disable_db
    Go
    
    -- Criando a Tabela de Exemplo --
    Create Table Produtos
     (Codigo Int Identity(1,1),
       Descricao VarChar(20))
    Go
    
    -- Adicionando a Chave Primaria --
    Alter Table Produtos
        Add Constraint [PK_Codigo_Produtos] Primary Key (Codigo)
    Go
    
    -- Inserindo a Massa de Registros para Teste --   
    Declare @ContadorRegistros Int
    Set @ContadorRegistros=1
    
    While @ContadorRegistros <=1000
     Begin
     
      If @ContadorRegistros =1
       Insert Into Produtos Values ('Produto Nº: 1')
      Else
       Insert Into Produtos Values ('Produto Nº: '+Convert(VarChar(4),@@Identity+1))
       
       Set @ContadorRegistros += 1;
     End
     
    -- Visualizando os Dados --   
    Select * from Produtos
          
    -- Habilitando o Change Data Capture para trabalhar sobre a table Produtos --
    EXECUTE sys.sp_cdc_enable_table
        @source_schema = N'dbo',
        @source_name = N'Produtos',
        @role_name = N'cdc_Admin';
    GO
    
    -- Retornando todas as linhas capturadas pelo CDC --
    DECLARE @from_lsn binary(10), 
                      @to_lsn binary(10)
    
    SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Produtos')
    SET @to_lsn   = sys.fn_cdc_get_max_lsn()
    
    SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Produtos
      (@from_lsn, @to_lsn, N'all')
    Go
    
    -- Inserindo novos dados --
    Insert Into Produtos Values ('Produto Nº: '+Convert(VarChar(4),@@Identity+1))
    Go
    
    -- Atualizando dados já existentes --
    Update Produtos
    Set Descricao= Descricao+' - Upd'
    Where Codigo Between 11 And 21
    Go
    
    -- Retornando todas as linhas capturadas pelo CDC com Net Changes--
    DECLARE @from_lsn binary(10), 
                      @to_lsn binary(20)
    
    SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Produtos')
    SET @to_lsn   = sys.fn_cdc_get_max_lsn()
    
    SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Produtos
                                  (@from_lsn, @to_lsn, N'all')
    GO
    
    -- Retornando as colunas utilizadas pelo CDC para Captura --
    Execute sys.sp_cdc_get_captured_columns 
                             @capture_instance = N'dbo_Produtos';
    Go
    
    -- Retornando informações de configuração da captura de dados de alteração de uma tabela específica --
    Execute sys.sp_cdc_help_change_data_capture 
                              @source_schema = N'dbo', 
                              @source_name = N'Produtos';
    Go
    
    -- Retornando informações de configuração da captura de dados de alteração de todas as tabelas --
    EXECUTE sys.sp_cdc_help_change_data_capture;

    --create test DB
    USE master;
    GO
    
    CREATE DATABASE CDCTracking;
    GO
    
    ALTER DATABASE CDCTracking SET
    CHANGE_TRACKING = ON
     (AUTO_CLEANUP = ON,          -- automatic tracking table clean up process
      CHANGE_RETENTION = 1 HOURS  -- specify the time frame for which tracked information will be maintained -- 
    );
    GO
    
    --create test table
    USE CDCTracking;
    GO
    
    CREATE TABLE dbo.tb
    (id int
     CONSTRAINT PK_tb_id PRIMARY KEY,
     col1 int,
     col2 varchar(10),
     col3 nvarchar(max),
     col4 varbinary(max));
    GO
    
    ALTER TABLE dbo.tb
    ENABLE CHANGE_TRACKING
     WITH(TRACK_COLUMNS_UPDATED = ON  -- With this option, you can include columns also whose values were changed
     );
    GO
    
    SELECT CHANGE_TRACKING_CURRENT_VERSION() as Currentversion,
           CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb')) as minvalidversion;
    GO
    
    -- testing
    
    -- a.insert data 
    INSERT dbo.tb(id, col1, col2, col3, col4)
    VALUES(1,1, 'AA', 'AAA', 0x1),
    	  (2,2, 'BB', 'BBB', 0x2),
          (3,3, 'CC', 'CCC', 0x2);
     
    SELECT CHANGE_TRACKING_CURRENT_VERSION() as Currentversion,
           CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb')) as minvalidversion,
           *
    FROM CHANGETABLE(CHANGES dbo.tb, 0) CHG LEFT JOIN dbo.tb DATA
                                             ON DATA.id = CHG.id;
     
    -- b. update data 
    BEGIN TRAN;
    UPDATE dbo.tb SET
    col1 = 11
    WHERE id = 1;
     
    UPDATE dbo.tb SET
    col1 = 111
    WHERE id = 1;
    COMMIT TRAN;
     
    SELECT CHANGE_TRACKING_CURRENT_VERSION() as Currentversion,
           CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb')) as minvalidversion,
           *
    FROM CHANGETABLE(CHANGES dbo.tb, 0) CHG LEFT JOIN dbo.tb DATA
                                             ON DATA.id = CHG.id;


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 6 de setembro de 2019 16:28
  • criar uma tabela que armazene o histórico de comandos, que contenha O Comando SQL em si, Usuário que executou, data/hora, nome da tabela, e um campo com o ID (PK do registro na origem). Essa tabela deverá guardar TODOS os comandos INSERT, UPDATE e DELETE do banco,

    Matheus,

    em outro tópico você cita caso em que está a utilizar SQL Server 2008 R2, edição Express; o caso deste tópico é para o mesmo contexto? Se for 2008 R2 Express, não há como utilizar Auditoria do SQL Server (SERVER AUDIT); assim, você nem precisa testar possíveis sugestões que envolvam esse recurso...

    Sobre "foram fornecidos usuários do banco específicos para esses sistemas, porém em várias tabelas ambos tem direito de escrita, pois necessitam atualizar alguns valores", uma forma de garantir a consistência do banco de dados e que as regras de negócio sejam seguidas é criar procedimentos armazenados a serem utilizados pelos novos softwares, em conjunto com visões que limitem o que os novos softwares podem consultar ou atualizar. De certa forma funciona como a camada adicional que você citou, só que implementada através de objetos do SQL, como funções, visões e procedimentos.

    ---

    Para registrar os comandos executados é necessário criar tabela com colunas específicas para cada informação a registrar. Eis um exemplo:

     

    -- código #1 v2
    -- Autor:  José Diz/BH

    USE BD_auditoria;
    go

    CREATE TABLE dbo.Historico_IUD (
        --Nome_banco sysname not null default db_name(),
        Nome_esquema sysname not null,
        Nome_tabela sysname not null,
        Acao char(1) not null check (Acao in ('I', 'U', 'D')),
        Comando nvarchar(4000) not null,
        Horario datetimeoffset(2) not null default SYSDATETIMEOFFSET(),
        NomeHost sysname not null default HOST_NAME(),
        Servidor sysname not null default @@servername,
        Usuario sysname not null default USER,
        Login sysname not null default SYSTEM_USER,
        Aplicacao sysname not null default PROGRAM_NAME()
    );


    Na tabela acima há várias colunas cujo conteúdo você deve avaliar se necessita ou não. É preciso ficar atento ao espaço ocupado por cada linha, pois cada comando SQL irá acrescentar mais uma linha na tabela de auditoria, cuja tendência é crescer rapidamente. No SQL Server existe a possibilidade de compactar tabelas, mas é um recurso disponível somente nas edições topo de linha, como Enterprise e Datacenter. Era, pois a partir da versão 2016 (nível de atualização SP1) está disponível em todas as edições. Eis um bom motivo para migrar do SQL Server 2008 R2 Express para o SQL Server 2016 Express (desde que os sistemas em uso estejam homologados para o SQL Server 2016).

    Com relação ao que registrar na tabela de histórico, não é possível guardar o ID da tabela pois um comando pode alterar várias linhas de uma vez. Um tipo de auditoria é a que você solicita, em que se registram os comandos que manipularam o banco de dados. Outro tipo de auditoria é aquela que registra o que foi alterado nas tabelas, mas sem registrar o comando SQL. Neste caso é necessário criar uma nova tabela de histórico para cada cada tabela a ser monitorada, por causa das diferenças de estrutura entre cada tabela a ser monitorada. Se serão monitoradas 10 tabelas, então é necessário criar 10 tabelas de histórico.  Há como mesclar os dois tipos de auditoria.

    ---

    Através do uso de procedimento de gatilho (trigger) é possível interceptar os comandos SQL desejados: INSERT, UPDATE ou DELETE. Considerando-se a tabela criada no código #1, podemos ter o seguinte procedimento:

    -- código #2 v2
    -- Autor:  José Diz/BH

    CREATE TRIGGER registraIUD_tabela
      on dbo.tabela
      after INSERT, UPDATE, DELETE
    as
    begin
    set nocount on;
    declare @xI bit, @xD bit, @Acao char(1), @Comando nvarchar(4000);
    set @xI= case when exists (SELECT * from INSERTED) then 1 else 0 end;
    set @xD= case when exists (SELECT * from DELETED) then 1 else 0 end;

    -- define tipo de atualização
    set @Acao = case when (@xI = 1) and (@xD = 0) then 'I'
                     when (@xI = 1) and (@xD = 1) then 'U'
                     when (@xI = 0) and (@xD = 1) then 'D' end;
                     
    -- encerra, se não há nada a processar
    IF @Acao is null
      return;                 
       
    -- obtém o comando SQL que ativou o procedimento de gatilho
    declare @inputbuffer table (EventType nvarchar(30), Parameters smallint, EventInfo nvarchar(4000));
    set @Comando = N'DBCC INPUTBUFFER (' + STR(@@SPID) + N') WITH NO_INFOMSGS;';
    INSERT into @inputbuffer (EventType, Parameters, EventInfo)
      EXECUTE sp_executesql @Comando;

    set @Comando= (SELECT EventInfo from @inputbuffer);
        
    -- registra o comando SQL na tabela de auditoria
    INSERT into BD_auditoria.dbo.Historico_IUD (Nome_esquema, Nome_tabela, Acao, Comando)
      SELECT 'dbo', 'tabela', @Acao, @Comando;

    end;
    go


    Entretanto, é necessário criar o mesmo procedimento para cada uma das tabelas que for necessário monitorar.

    A solução proposta nesta resposta, ainda em elaboração, atende ao que necessita?

    ---

    Se não for possível utilizar a compactação nativa do SQL Server, uma possibilidade é implementar mecanismos que reduzam o espaço ocupado pela tabela de auditoria; por exemplo, registrar o texto de cada comando SQL em uma tabela à parte e na tabela de histórico somente um apontador para o texto da consulta SQL. Isto é possível de se fazer automaticamente, mas esta solução não é eficiente caso o sistema gere consultas SQL ad-hoc em excesso onde cada uma seja diferente da outra, mesmo que por pequenos detalhes.

     

    Lembre-se de marcar esta resposta se ela te ajudou a resolver o problema.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    domingo, 8 de setembro de 2019 01:11
  • Matheus,

    Uma outra possibilidade aplicada a sua necessidade (mas não é a solução, é um complemento) seria adotar algo similar a uma Auditoria In Live, veja abaixo um exemplo que o Dirceu Resende e eu utilizamos em abril deste ano no MVPConf 2019:

    SELECT ser.session_id As 'SessionID',

    ssp.ecid, DB_NAME(ssp.dbid) As 'DatabaseName', ssp.nt_username as 'User', ser.status As 'Status', ser.wait_type As 'Wait', SUBSTRING (sqt.text, ser.statement_start_offset/2, (CASE WHEN ser.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), sqt.text)) * 2 ELSE ser.statement_end_offset END - ser.statement_start_offset)/2) As 'Individual Query', sqt.text As 'Parent Query', ssp.program_name As 'ProgramName', ssp.hostname, ssp.nt_domain As 'NetworkDomain', ser.start_time FROM sys.dm_exec_requests ser INNER JOIN sys.sysprocesses ssp On ser.session_id = ssp.spid CROSS APPLY sys.dm_exec_sql_text(ser.sql_handle)as sqt WHERE ser.session_Id > 50 AND ser.session_Id NOT IN (@@SPID) ORDER BY SessionID, ssp.ecid Go

    Este exemplo vai lhe permitir realizar uma auditoria no que esta sendo acessado de forma externa ao SQL Server, ou seja, através de aplicações que estão fazendo acesso, torna-se possível com este exemplo capturer as querys que estão em processamento.

    Por fim, este outro exemplo que também utilizamos no MVPConf 2019, nos permite identificar as permissões necessários de cada usuário para realizar operações de Insert, Update e Delete:

    Autores: Dirceu Resende e Pedro Galvão Junior
    
    -- Criando a tabela para armazenar o histórico de acessos --
    CREATE TABLE [dbo].[Auditoria_Acesso]
    (
        [Id_Auditoria] [bigint] NOT NULL IDENTITY(1, 1),
        [Dt_Auditoria] [datetime] NOT NULL,
        [Cd_Acao] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
        [Ds_Maquina] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
        [Ds_Usuario] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
        [Ds_Database] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
        [Ds_Schema] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
        [Ds_Objeto] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
        [Ds_Query] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [Fl_Sucesso] [bit] NOT NULL,
        [Ds_IP] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
        [Ds_Programa] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
        [Qt_Duracao] [bigint] NOT NULL,
        [Qt_Linhas_Retornadas] [bigint] NOT NULL,
        [Qt_Linhas_Alteradas] [bigint] NOT NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    WITH
    (
    DATA_COMPRESSION = PAGE
    )
    GO
    
    ALTER TABLE [dbo].[Auditoria_Acesso] ADD CONSTRAINT [PK__Auditori__E9F1DAD4EE3743FE] PRIMARY KEY CLUSTERED ([Id_Auditoria]) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY]
    GO
    
    -- Criando a Server Audit filtrando os usuários --
    USE [master]
    GO
    
    IF ((SELECT COUNT(*) FROM sys.server_audits WHERE [name] = 'Auditoria_Acessos') > 0)
    BEGIN
        ALTER SERVER AUDIT [Auditoria_Acessos] WITH (STATE = OFF);
        DROP SERVER AUDIT [Auditoria_Acessos]
    END
    
    
    CREATE SERVER AUDIT [Auditoria_Acessos]
    TO FILE
    (	
        FILEPATH = N'C:\Audit\',
        MAXSIZE = 10 MB,
        MAX_ROLLOVER_FILES = 16,
        RESERVE_DISK_SPACE = OFF
    )
    WITH
    (	
        QUEUE_DELAY = 1000,
        ON_FAILURE = CONTINUE,
        AUDIT_GUID = '0b5ad307-ee47-43db-a169-9af67cb661f9'
    )
    WHERE (([server_principal_name] LIKE '%User' OR [server_principal_name] LIKE 'LS_%') AND [application_name]<>'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense' AND NOT [application_name] LIKE 'Red Gate Software%' AND NOT [server_principal_name] LIKE 'GRUPODADALTO\%' AND NOT [server_principal_name] LIKE 'WWWUser')
    GO
    
    
    ALTER SERVER AUDIT [Auditoria_Acessos] WITH (STATE = ON)
    GO
    
    
    -- Criando a Database Audit capturando os acessos --
    DECLARE @Query VARCHAR(MAX)
    SET @Query = '
    
    IF (''?'' NOT IN (''master'', ''tempdb'', ''model'', ''msdb''))
    BEGIN
    
        USE [?];
    
        IF ((SELECT COUNT(*) FROM sys.database_audit_specifications WHERE [name] = ''Auditoria_Acessos'') > 0)
        BEGIN
    
            ALTER DATABASE AUDIT SPECIFICATION [Auditoria_Acessos] WITH (STATE = OFF);
            DROP DATABASE AUDIT SPECIFICATION [Auditoria_Acessos];
    
        END
    
        CREATE DATABASE AUDIT SPECIFICATION [Auditoria_Acessos]
        FOR SERVER AUDIT [Auditoria_Acessos]
        ADD (DELETE ON DATABASE::[?] BY [public]),
        ADD (EXECUTE ON DATABASE::[?] BY [public]),
        ADD (INSERT ON DATABASE::[?] BY [public]),
        ADD (SELECT ON DATABASE::[?] BY [public]),
        ADD (UPDATE ON DATABASE::[?] BY [public])
        WITH (STATE = ON);
        
    END'
    Go
    
    EXEC sys.sp_MSforeachdb @Query
    Go
    
    -- Criando a Stored Procedure para armazenar os dados coletados --
    IF (OBJECT_ID('dbo.stpAuditoria_Acessos_Carrega_Dados') IS NULL) EXEC('CREATE PROCEDURE dbo.stpAuditoria_Acessos_Carrega_Dados AS SELECT 1')
    GO
    
    ALTER PROCEDURE dbo.stpAuditoria_Acessos_Carrega_Dados
    AS
    BEGIN
    
        DECLARE @TimeZone INT = DATEDIFF(HOUR, GETUTCDATE(), GETDATE())
        DECLARE @Dt_Max DATETIME = DATEADD(SECOND, 1, ISNULL((SELECT MAX(Dt_Auditoria) FROM .Auditoria_Acesso), '1900-01-01'))
    
        INSERT INTO dbo.Auditoria_Acesso
        (
            Dt_Auditoria,
            Cd_Acao,
            Ds_Maquina,
            Ds_Usuario,
            Ds_Database,
            Ds_Schema,
            Ds_Objeto,
            Ds_Query
            Fl_Sucesso,
            Ds_IP,
            Ds_Programa,
            Qt_Duracao,
            Qt_Linhas_Retornadas,
            Qt_Linhas_Alteradas
        )
        SELECT DISTINCT
            DATEADD(HOUR, @TimeZone, event_time) AS event_time,
            action_id,
            server_instance_name,
            server_principal_name,
            [database_name],
            [schema_name],
            [object_name],
            [statement],
            succeeded,
            client_ip,
            application_name,
            duration_milliseconds,
            response_rows,
            affected_rows
        FROM 
            sys.fn_get_audit_file('C:\Audit\*.sqlaudit', DEFAULT, DEFAULT)
        WHERE 
            DATEADD(HOUR, @TimeZone, event_time) >= @Dt_Max
    
    END
    Go
    
    -- Consultando os dados coletados --
    Select * from Auditoria_Acessos
    Go
    
    -- Mapeando as permissões com base na auditoria --
    SELECT DISTINCT 
        Ds_Usuario,
        Ds_Database, 
        Cd_Acao, 
        Ds_Objeto,
        'USE [' + Ds_Database + ']; GRANT ' + (CASE Cd_Acao
            WHEN 'UP' THEN 'UPDATE'
            WHEN 'IN' THEN 'INSERT'
            WHEN 'DL' THEN 'DELETE'
            WHEN 'SL' THEN 'SELECT'
            WHEN 'EX' THEN 'EXECUTE'
        END) + ' ON [' + Ds_Schema + '].[' + Ds_Objeto + '] TO [' + Ds_Usuario + '];' AS Comando 
    FROM 
        dirceuresende..Auditoria_Acesso 
    WHERE 
        Cd_Acao <> 'UNDO'
    ORDER BY
        Ds_Usuario,
        Ds_Database,
        Ds_Objeto
    Go

    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    segunda-feira, 9 de setembro de 2019 11:17
  • Olá, Sim a versão do SQL Server é a Express porém na versão 17 (14.0.1000), nesse caso o CDC não pode ser aplicado. Fiz o testes com a solução por trigger , veja o resultado da captura:

    (@p27 int, @p0 int, @p1 datetime2(7), @p2 datetime2(7), @p3 datetime2(7), @p4 smallint, @p5 smallint, @p6 nvarchar(4000), @p7 nvarchar(4000), @p8 nvarchar(1000), @p9 int, @p10 smallint, @p11 smallint, @p12 smallint, @p13 datetime2(7), @p14 nvarchar(20), @p15 nvarchar(20), @p16 smallint, @p17 datetime2(7), @p18 int, @p19 int, @p20 int, @p21 int, @p22 int, @p23 int, @p24 int, @p25 int, @p26 int)
    SET
       NOCOUNT 
       ON;
    UPDATE
       [GmComTab] 
    SET
       [ComCod] = @p0, [ComDtaCad] = @p1, [ComDtaFin] = @p2, [ComDtaOpe] = @p3, [ComES] = @p4, [ComFreMod] = @p5, [ComHis] = @p6, [ComInfAdd] = @p7, [ComInfAddCpl] = @p8, [ComMovStkId] = @p9, [ComNfeIdDest] = @p10, [ComOri] = @p11, [ComPadTPag] = @p12, [ComPrz] = @p13, [ComRefExt] = @p14, [ComRefReq] = @p15, [ComSit] = @p16, [ComUsuDta] = @p17, [ComUsuId] = @p18, [CpgId] = @p19, [DestPesId] = @p20, [EmpId] = @p21, [OriCtaStkId] = @p22, [PreId] = @p23, [TipOpeId] = @p24, [TransPesId] = @p25, [VendPesId] = @p26 
    WHERE
       [ComId] = @p27;
    SELECT
       @@ROWCOUNT;

    Porém não consigo detectar os valores, veja o resultado capturado via SQl Profiler:

    exec sp_executesql N'SET NOCOUNT ON;
     
    UPDATE
       [GmComTab] 
    SET
       [ComCod] = @p0, [ComDtaCad] = @p1, [ComDtaFin] = @p2, [ComDtaOpe] = @p3, [ComES] = @p4, [ComFreMod] = @p5, [ComHis] = @p6, [ComInfAdd] = @p7, [ComInfAddCpl] = @p8, [ComMovStkId] = @p9, [ComNfeIdDest] = @p10, [ComOri] = @p11, [ComPadTPag] = @p12, [ComPrz] = @p13, [ComRefExt] = @p14, [ComRefReq] = @p15, [ComSit] = @p16, [ComUsuDta] = @p17, [ComUsuId] = @p18, [CpgId] = @p19, [DestPesId] = @p20, [EmpId] = @p21, [OriCtaStkId] = @p22, [PreId] = @p23, [TipOpeId] = @p24, [TransPesId] = @p25, [VendPesId] = @p26 
    WHERE
       [ComId] = @p27;
    SELECT
       @@ROWCOUNT;
    ',N'@p27 int,
    @p0 int,
    @p1 datetime2(7),
    @p2 datetime2(7),
    @p3 datetime2(7),
    @p4 smallint,
    @p5 smallint,
    @p6 nvarchar(4000),
    @p7 nvarchar(4000),
    @p8 nvarchar(1000),
    @p9 int,
    @p10 smallint,
    @p11 smallint,
    @p12 smallint,
    @p13 datetime2(7),
    @p14 nvarchar(20),
    @p15 nvarchar(20),
    @p16 smallint,
    @p17 datetime2(7),
    @p18 int,
    @p19 int,
    @p20 int,
    @p21 int,
    @p22 int,
    @p23 int,
    @p24 int,
    @p25 int,
    @p26 int',@p27=23,@p0=7,@p1='2019 - 09 - 11 15:26:38.0870000',@p2=NULL,@p3='2019 - 09 - 11 00:00:00',@p4=-1,@p5=0,@p6=N'VENDA 7 - ANA MARIA DA SILVA',@p7=NULL,@p8=NULL,@p9=NULL,@p10=0,@p11=0,@p12=3,@p13='2019 - 09 - 11 00:00:00',@p14=NULL,@p15=NULL,@p16=1,@p17='2019 - 09 - 11 15:26:38.1330000',
    @p18 = 1,
    @p19 = 1,
    @p20 = 94814,
    @p21 = 1,
    @p22 = 7,
    @p23 = 1,
    @p24 = 2,
    @p25 = NULL,
    @p26 = 107767

    Nesse caso ainda preciso evoluir a forma de captura.

    Obrigado pela atenção,


    • Editado MatheusFernando terça-feira, 17 de setembro de 2019 10:39 Não havia removido o nome real do conteúdo
    terça-feira, 17 de setembro de 2019 10:37
  • Olá, Sim a versão do SQL Server é a Express porém na versão 17 (14.0.1000)

    Ok, compreendido.

    A solução proposta para a auditoria deve funcionar tanto na versão 2008 R2 quanto na versão 2017 do SQL Server ou pode ser compatível somente com SQL Server 2016 e em diante?

    As sugestões a seguir dependem desta sua resposta.


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    quarta-feira, 25 de setembro de 2019 12:57