none
DDL Trriger (Auditoria) RRS feed

  • Pergunta

  • Salve galera !!!

    Encontrei um exemplo a respeito de DDL Trigger no link http://www.50minutos.com.br/?p=81 e estou com algumas dúvidas:

    1) o que realmente posso conseguir com a DDL Trigger (alteração estrutura de banco de dados somente?  e alteração de dados?
    2) a criação da tabela LOG tem a coluna MAQUINA definida como SYSNAME DEFAULT HOST_NAME(). Porque?
    3) Quando a trigger é disparada no insert da tabela log é incluido somente o evento que ocorreu?

    Enfim, preciso aprender mais sobre DDL Trigger, pois estou tendo alterações, tanto de estutura como de dados, que preciso pegar quem está fazendo isso.

    Alguém pode me ajudar?

    Abraço

    Pablicio




    CREATE DATABASE EXEMPLO_TRIGGER
    GO
    –-
    USE EXEMPLO_TRIGGER
    –-
    CREATE TABLE LOG
    (
       MAQUINA SYSNAME DEFAULT HOST_NAME(),
       DADOS XML
    )
    –-
    CREATE TRIGGER DDL_VIGIA_TABLE
    ON DATABASE
    FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
    AS
    INSERT INTO LOG(DADOS) VALUES (EVENTDATA())
    GO
    –-
    CREATE TABLE PESSOA
    (
       COD INT IDENTITY, NOME VARCHAR(50)
    )
    –-
    SELECT * FROM LOG
    –-
    ALTER TABLE PESSOA ADD PRIMARY KEY ( COD )
    –-
    SELECT * FROM LOG
    –-
    DROP TABLE PESSOA
    –-
    SELECT * FROM LOG
    quinta-feira, 6 de março de 2008 13:29

Respostas

  • Bom Dia Pablício,

     

    Trigger (independente do tipo) é uma ação que é disparada de forma reativa a um evento (você nunca poderá chamar uma trigger diretamente). As triggers DML (Data Manipulation Language) reagem a eventos de alteração de dados (INSERT, UPDATE e DELETE) enquanto as triggers DDL (Data Definition Language) reagem a eventos de alteração de estrutura (basicamente CREATE, ALTER e DROP)

     

    Se a idéia é monitorar quem está alterando dados, você terá que contar com as triggers DML. Se a idéia é monitorar quem está alterando estrutura, a idéia é contar com as triggers DDL. Uma não poderá monitorar eventos da outra já que são disparadas por eventos diferentes.

     

    Respondendo às suas perguntas

     

    1) o que realmente posso conseguir com a DDL Trigger (alteração estrutura de banco de dados somente?  e alteração de dados?

    R: Somente alteração de estruturas, as de dados necessitam de triggers DML e não DDL


    2) a criação da tabela LOG tem a coluna MAQUINA definida como SYSNAME DEFAULT HOST_NAME(). Porque?

    Caso o nome da máquina não seja informado, ele vai pegar a variável HOST_NAME que captura o nome da máquina remota que disparou o comando.

     
    3) Quando a trigger é disparada no insert da tabela log é incluido somente o evento que ocorreu?

    Sim. Se a trigger foi de INSERT, apenas os eventos relacionados a INSERT são capturados. Você pode criar uma única trigger para capturar todos os eventos ou criar uma trigger para cada evento (mais recomendado).

     

    As triggers podem ser utilizadas para capturar informações referente ao evento ou ainda cancelar o evento que a chamou. Com as triggers DML você pode capturar informações sobre o INSERT, UPDATE e o DELETE e até eventualmente cancelar esses eventos (impedindo que esses comandos sejam disparados). O mesmo é válido para triggers DDL no caso dos eventos de CREATE, ALTER e DROP.

     

    Opcionalmente, você pode utilizar o Profiler para verificar quem está disparando comandos diretamente ao servidor.

     

    [ ]s,

     

    Gustavo

    quinta-feira, 6 de março de 2008 14:14

Todas as Respostas

  • Bom Dia Pablício,

     

    Trigger (independente do tipo) é uma ação que é disparada de forma reativa a um evento (você nunca poderá chamar uma trigger diretamente). As triggers DML (Data Manipulation Language) reagem a eventos de alteração de dados (INSERT, UPDATE e DELETE) enquanto as triggers DDL (Data Definition Language) reagem a eventos de alteração de estrutura (basicamente CREATE, ALTER e DROP)

     

    Se a idéia é monitorar quem está alterando dados, você terá que contar com as triggers DML. Se a idéia é monitorar quem está alterando estrutura, a idéia é contar com as triggers DDL. Uma não poderá monitorar eventos da outra já que são disparadas por eventos diferentes.

     

    Respondendo às suas perguntas

     

    1) o que realmente posso conseguir com a DDL Trigger (alteração estrutura de banco de dados somente?  e alteração de dados?

    R: Somente alteração de estruturas, as de dados necessitam de triggers DML e não DDL


    2) a criação da tabela LOG tem a coluna MAQUINA definida como SYSNAME DEFAULT HOST_NAME(). Porque?

    Caso o nome da máquina não seja informado, ele vai pegar a variável HOST_NAME que captura o nome da máquina remota que disparou o comando.

     
    3) Quando a trigger é disparada no insert da tabela log é incluido somente o evento que ocorreu?

    Sim. Se a trigger foi de INSERT, apenas os eventos relacionados a INSERT são capturados. Você pode criar uma única trigger para capturar todos os eventos ou criar uma trigger para cada evento (mais recomendado).

     

    As triggers podem ser utilizadas para capturar informações referente ao evento ou ainda cancelar o evento que a chamou. Com as triggers DML você pode capturar informações sobre o INSERT, UPDATE e o DELETE e até eventualmente cancelar esses eventos (impedindo que esses comandos sejam disparados). O mesmo é válido para triggers DDL no caso dos eventos de CREATE, ALTER e DROP.

     

    Opcionalmente, você pode utilizar o Profiler para verificar quem está disparando comandos diretamente ao servidor.

     

    [ ]s,

     

    Gustavo

    quinta-feira, 6 de março de 2008 14:14
  • Gustavo, mais uma vez não sei como agradecer. Muito obrigado !!!

    Aproveitando mais um pouco da sua boa vontade, gostaria de uma idéia.

    Como falei anteriormente, estamos tendo problema tanto com dados como com estrutura.

    O que você acha ou julga importante guardar de um log? (nome usuário, nome máquina, comando executado, e etc..)

    Outra coisa, eu consigo pegar alterações em procedure (create proc, alter proc, view e etc)?

    Valeu !!!
    quinta-feira, 6 de março de 2008 15:49
  • Olá Pablício,

     

    Se você implementar e funcionar... Já me sentirei agradecido. É sempre um prazer ajudar.

     

    Se os problemas são com dados e estrutura, você precisará de mecanismos diferentes para controlá-los se for usar triggers. Você pode utilizar o Profiler se desejar logar essas atividades, mas aí o volume pode ser muito grande. O ideal mesmo é fazer uma boa gerência de permissões (não dê mais permissões do que o necessário) e tente centralizar o acesso à dados via SP (assim fica mais fácil controlar).

     

    "O valor de uma informação deve ser maior do que o custo para se obtê-la e mantê-la."

     

    Você pode guardar todas esses dados no log. Certamente servirão de muita utilidade em algum momento. Enquanto você os armazena, espaço é consumido e mais recursos são utilizados para obter esses dados. Se esse custo valer a pena armezene todos eles. Se não valer a pena, coloque apenas aqueles julgados imprescindíveis. Isso não serei eu quem vai dizer.

     

    Não há como guardar o comando executado se você estiver utilizando triggers, mas é possível guardar as demais informações.

     

    Com as triggers DDL você pode capturar informações sobre instruções de CREATE PROC, ALTER PROC, etc. Afinal são eventos disparados por instruções DDL.

     

    [ ]s,

     

    Gustavo

    quinta-feira, 6 de março de 2008 18:35
  • Gustavo,

    Estou começando um trabalho de rever as permissões, pois todos utilizam SA ou outro usário que está como SysAdmin. Enfim, tá uma loucura.

    Criei o código abaixo que vai ajudar no que refere a estrutura do banco (já está rodando no banco - não sei se é suficiente, mas estou pegando muita coisa que falavam que não faziam).

    Porém preciso criar um código de trigger para colocar em algumas tabelas (principais) que identificassem principalmente qual ID está sendo alterado e de prefência guardar qual(is) valores anteriores estão sendo executado.
    Você tem algum exemplo que possa me mandar? Preciso saber insert, update e delete.

    Gostei demais da frase que colocou na post anterior: "O valor de uma informação deve ser maior do que o custo para se obtê-la e mantê-la." Essa já está no meu msn.

    Valeu !!!


    USE [master]
    GO
    CREATE DATABASE Auditoria
    GO
    USE [Auditoria]
    GO
    CREATE TABLE dbo.auditoriaestrutura
                              (datalog      DATETIME,
                               tipoevento   VARCHAR(8000),
                               nomeservidor VARCHAR(8000),
                               nomeusuario  VARCHAR(8000),
                               nomemaquina  VARCHAR(8000),    
                               nomebanco    VARCHAR(8000),
                               nomeschema   VARCHAR(8000),
                               nomeobjeto   VARCHAR(8000),
                               tipoobjeto   VARCHAR(8000),
                               comandosql   VARCHAR(8000),
                               evento       XML)
                 
    GO
    USE [master]
    GO
    CREATE DATABASE EXEMPLO_TRIGGER
    GO
    USE [EXEMPLO_TRIGGER]
    go
    IF  EXISTS (SELECT * FROM sys.triggers WHERE name = N'auditoriaestrutura' AND parent_class=0)
        DROP TRIGGER auditoriaestrutura ON DATABASE   
    GO
    CREATE TRIGGER auditorialog
    ON DATABASE
    FOR DDL_DATABASE_LEVEL_EVENTS
    AS
    DECLARE @data XML
    SET @data = EVENTDATA()
    INSERT INTO auditoria.dbo.auditoriaestrutura
       (datalog
        ,tipoevento
        ,nomeservidor
        ,nomeusuario
        ,nomemaquina
        ,nomebanco
        ,nomeschema
        ,nomeobjeto
        ,tipoobjeto
        ,comandosql
        ,evento)
    VALUES
       (GETDATE()
        ,@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(8000)')
        ,@data.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(8000)')
        ,@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(8000)') 
        ,HOST_NAME() 
        ,@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(8000)')
        ,@data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(8000)')
        ,@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(8000)')
        ,@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(8000)')
        ,@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(8000)')
        ,EVENTDATA()
        )
    GO   
    quinta-feira, 6 de março de 2008 18:50
  • Olá Pablício,

     

    É sempre assim, os supostos culpados são sempre inocentes até que se prove o contrário. Controlar alterações de dados somente via trigger DML (ou profiler). As triggers DDL são ineficazes para tal. O problema de controlar as alterações é que quando uma chave primária é alterada, é complicado mapear registros já que se a PK identificava o registro como único, você perde a referência (se você trocar o seu CPF para um novo, como saberei um dia o seu CPF antigo se o seu cadastro foi atualizado ?)

     

    Para atualizações que não envolvam a PK, segue uma sugestão.

     

    Code Snippet

    CREATE TABLE tblTeste (CODIGO INT, NOME VARCHAR(20))

    GO

     

    CREATE TABLE tblAudit (

    Data SMALLDATETIME DEFAULT GETDATE(),

    CODIGOANT INT,

    NOMEANT VARCHAR(20),

    CODIGOPOS INT,

    NOMEPOS VARCHAR(20))

    GO

     

    CREATE TRIGGER trgTeste ON tblTeste

    FOR INSERT, UPDATE, DELETE

    AS

    BEGIN

    INSERT INTO tblAudit (CODIGOANT, NOMEANT, CODIGOPOS, NOMEPOS)

    SELECT Ant.Codigo, Ant.Nome, Pos.Codigo, Pos.Nome

    FROM

    DELETED As Ant FULL OUTER JOIN INSERTED As Pos

    ON Ant.Codigo = Pos.Codigo

    END

     

    INSERT INTO tblTeste (CODIGO, NOME) VALUES (1, 'Nome 1')

    SELECT * FROM tblTeste

    SELECT * FROM tblAudit

     

    UPDATE tblTeste SET Nome = 'Cliente de código 1'

    WHERE Codigo = 1

    SELECT * FROM tblTeste

    SELECT * FROM tblAudit

     

    DELETE FROM tblTeste WHERE Codigo = 1

    SELECT * FROM tblTeste

    SELECT * FROM tblAudit

     

    DROP TABLE tblTeste

    DROP TABLE tblAudit

     

     

    Esse foi um esboço inicial, veja que quando você insere ele guarda os valores posteriores, quando você atualize ele guarda os anteriores e quando você exclui ele grava os anteriores. Se você alterar um ID, será realizada uma exclusão e uma inserção de acordo com o log.

     

    [ ]s,

     

    Gustavo

    quinta-feira, 6 de março de 2008 19:26
  • Gustavo,

    Estou tentando fazer com que o código abaixo funcione. Mas está dando erro. quando altero o valor da coluna da tabela que estou utilizando.

    O erro é: the row values updated or deleted either do not make the row unique or they alter multiple rows (3 rows)

    Outra coisa, tem como fazer uma trigger que pegue a chave primária para ser o idanterior e posterior e eu consiga identificar qual o nome da tabela que está sendo executada a trigger? Daí crio uma função que percorre as colunas do registro e gravo todos como text.

    tem como me ajudar?

    Valeu !!!



    USE [AUDITORIA]
    CREATE TABLE auditoriaregistro(datalog DATETIME
                                   ,idanterior int
                                   ,registroanterior TEXT
                                   ,idnovo INT
                                   ,registronovo TEXT
                                   ,nomeservidor VARCHAR(8000)
                                   ,nomeusuario VARCHAR(8000)
                                   ,nomemaquina VARCHAR(8000)
                                   ,nomebanco VARCHAR(8000)
                                   ,nomeobjeto VARCHAR(8000)
                                   )            
                                           
    GO           
    USE [EXEMPLO_TRIGGER]
    GO
    IF OBJECT_ID ('auditoria_tabela','TR') IS NOT NULL
        DROP TRIGGER auditoria_tabela
    GO
    CREATE TRIGGER auditoria_tabela
    ON dbo.tabela
    AFTER INSERT, UPDATE, DELETE
    AS 
    BEGIN

        INSERT INTO auditoria.dbo.[auditoriaregistro] (
                    [datalog]
                    ,[idanterior]
                    --,[registroanterior]
                    ,[idnovo]
                    --,[registronovo]
                    ,[nomeservidor]
                    ,[nomeusuario]
                    ,[nomemaquina]
                    ,[nomebanco]
                    --,[nomeobjeto]
                )
        SELECT
                GETDATE()
                ,Ant.id
                --Ant.registroanterior,
                ,Pos.id
                --Pos.id
                ,@@SERVERNAME
                ,SUSER_NAME()
                ,HOST_NAME()
                ,DB_NAME()
        FROM
        DELETED As Ant FULL OUTER JOIN INSERTED As Pos
        ON Ant.id = Pos.id

    END

    GO
    quinta-feira, 6 de março de 2008 19:54
  • Olá Pablício,

     

    Esse é o problema de se permitir alterações em chaves primárias, você teoricamente "perde o rastro". O exemplo que postei funciona para alterações em qualquer coluna mesmo a coluna que você está relacionando as tabelas (no caso a coluna ID). Recomendaria então criar uma coluna Identity na tabela e fazer o FULL OUTER JOIN por essa coluna Identity. Dessa forma, a coluna Identity nunca seria trocada e a trigger iria funcionar.

     

    Você pode capturar o nome da tabela em que a trigger disparou. Veja o código abaixo:

     

    Code Snippet

    CREATE TABLE tblTeste (CODIGO INT, NOME VARCHAR(20))

    GO

    CREATE TRIGGER trgTeste ON tblTeste

    FOR INSERT, UPDATE, DELETE

    AS

    BEGIN

    SELECT OBJECT_NAME(Parent_Obj) FROM SYSOBJECTS WHERE ID = @@PROCID

    END

    INSERT INTO tblTeste VALUES (1, 'gasads')

     

     

    [ ]s,

     

    Gustavo

     

    quinta-feira, 6 de março de 2008 21:00
  • Gustavo, na realidade o erro não acontece quando faço instruções sql (update, delete, insert) se eu fizer o open na tabela e tentar trocar a mão, o erro acontece.

    Valeu !!!
    quinta-feira, 6 de março de 2008 21:06
  • Gustavo,

    Tire uma dúvida, por favor. Estou utilizando o código abaixo e a trigger não dispara quando renomeio uma tabela. Vi no help que a DDL_DATABASE_LEVEL_EVENTS pega somente os eventos em verde. Sabe me dizer como pego o rename da tabela?

    Valeu !!!

    Children: DDL_TRIGGER_EVENTS, DDL_FUNCTION_EVENTS, DDL_SYNONYM_EVENTS, DDL_SSB_EVENTS, DDL_DATABASE_SECURITY_EVENTS, DDL_EVENT_NOTIFICATION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS, DDL_TYPE_EVENTS, DDL_XML_SCHEMA_COLLECTION_EVENTS, DDL_PARTITION_EVENTS, DDL_ASSEMBLY_EVENTS

    ----- Trigger -----

    IF  EXISTS (SELECT * FROM sys.triggers WHERE name = N'tgr_auditorialog' AND parent_class=0)
        DROP TRIGGER tgr_auditorialog ON DATABASE   
    GO
    CREATE TRIGGER tgr_auditorialog
    ON DATABASE
    FOR DDL_DATABASE_LEVEL_EVENTS
    AS
    DECLARE @data XML
    SET @data = EVENTDATA()
    INSERT INTO ng_auditoria.dbo.adt_estrutura
       (datalog
        ,tipoevento
        ,nomeservidor
        ,nomeusuario
        ,nomemaquina
        ,nomebanco   
        ,nomeschema
        ,nomeobjeto
        ,tipoobjeto
        ,comandosql
        ,evento)
    VALUES
       (GETDATE()
        ,@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(8000)')
        ,@data.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(8000)')
        ,@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(8000)') 
        ,HOST_NAME() 
        ,@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(8000)')
        ,@data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(8000)')
        ,@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(8000)')
        ,@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(8000)')
        ,@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(8000)')
        ,EVENTDATA()
        )
    GO             

    segunda-feira, 10 de março de 2008 18:53
  • Bom Dia Pablício,

     

    A DDL Trigger é capaz de capturar eventos DDL, mas isso não significa que qualquer evento DDL possa dispará-la. Infelizmente renomear uma tabela parece ser um desses eventos.

     

    Essa limitação pode ser superada com o uso de Event Notification já que essas além de capturar todos os eventos das Triggers DDL são capazes de capturar eventos do Trace. No entanto, para trabalhar com elas você precisará de alguns conhecimentos em Service Broker.

     

    [ ]s,

     

    Gustavo

     

    terça-feira, 11 de março de 2008 12:33