SoloCodigo

Bases de Datos => MySQL => Mensaje iniciado por: RadicalEd en Jueves 23 de Marzo de 2017, 15:04

Título: Auditoria tablas en MySQL y PostgreSQL
Publicado por: RadicalEd en Jueves 23 de Marzo de 2017, 15:04
Hace rato no posteo nada en mi amado SoloCodigo, les adjunto auditorias para bases de datos PostgreSQL y MySQL, la segunda es una mamera.

Si se puede mejorar, sobre todo la parte del MySQL, seré feliz

PostgreSQL

Tabla
Código: SQL
  1. CREATE TABLE auditoria
  2. (
  3.   fecha_cambio TIMESTAMP WITH TIME zone DEFAULT now(),
  4.   nombre_disparador text,
  5.   tipo_disparador text,
  6.   nivel_disparador text,
  7.   comando text,
  8.   tabla text,
  9.   old_info text,
  10.   new_info text
  11. )
  12. WITH (
  13.   OIDS=FALSE
  14. );
  15. ALTER TABLE auditoria
  16.   OWNER TO postgres;
  17.  

Función (Stored Procedure)
Código: SQL
  1. CREATE OR REPLACE FUNCTION sp_auditoria_log()
  2.   RETURNS TRIGGER AS
  3. $BODY$
  4.   DECLARE
  5.   BEGIN
  6.  
  7.       IF (TG_OP = 'DELETE') THEN
  8.           INSERT INTO auditoria (nombre_disparador,tipo_disparador,nivel_disparador,comando,tabla,old_info,new_info)
  9.           VALUES (TG_NAME,TG_WHEN,TG_LEVEL,TG_OP,TG_TABLE_NAME,OLD,NULL);
  10.           RETURN OLD;
  11.       ELSIF (TG_OP = 'UPDATE') THEN
  12.           INSERT INTO auditoria (nombre_disparador,tipo_disparador,nivel_disparador,comando,tabla,old_info,new_info)
  13.           VALUES (TG_NAME,TG_WHEN,TG_LEVEL,TG_OP,TG_TABLE_NAME,OLD,NEW);
  14.           RETURN NEW;
  15.       ELSIF (TG_OP = 'INSERT') THEN
  16.           INSERT INTO auditoria (nombre_disparador,tipo_disparador,nivel_disparador,comando,tabla,old_info,new_info)
  17.           VALUES (TG_NAME,TG_WHEN,TG_LEVEL,TG_OP,TG_TABLE_NAME,NULL,NEW);
  18.           RETURN NEW;
  19.       END IF;
  20.       RETURN NULL;
  21.  
  22.   END;
  23. $BODY$
  24.   LANGUAGE plpgsql VOLATILE
  25.   COST 100;
  26. ALTER FUNCTION tg_auditoria_log()
  27.   OWNER TO postgres;

Trigger
Código: SQL
  1. CREATE TRIGGER tg_disparador_nombre_tabla
  2.   AFTER INSERT OR UPDATE OR DELETE
  3.   ON nombre_tabla
  4.   FOR EACH ROW
  5.   EXECUTE PROCEDURE sp_auditoria_log();

MySQL

Tabla
Código: SQL
  1. CREATE TABLE auditoria (
  2.   fecha_cambio DATETIME NOT NULL DEFAULT NOW() ,
  3.   nombre_disparador VARCHAR(30) NOT NULL,
  4.   tipo_disparador VARCHAR(15) NOT NULL,
  5.   nivel_disparador VARCHAR(15) NOT NULL,
  6.   comando VARCHAR(45) NOT NULL,
  7.   tabla VARCHAR(45) NOT NULL,
  8.   old_info LONGTEXT NOT NULL,
  9.   new_info LONGTEXT NOT NULL
  10. );

Stored Procedure
Código: SQL
  1. DROP PROCEDURE IF EXISTS sp_auditoria_log;
  2. DELIMITER $$
  3. CREATE PROCEDURE sp_auditoria_log (
  4.                                    IN disparador VARCHAR(30),
  5.                                    IN tipo VARCHAR(15),
  6.                                    IN nivel VARCHAR(15),
  7.                                    IN comando VARCHAR(45),
  8.                                    IN tabla VARCHAR(45),
  9.                                    IN oldInfo LONGTEXT,
  10.                                    IN newInfo LONGTEXT)
  11. BEGIN
  12.       INSERT INTO auditoria (nombre_disparador,tipo_disparador,
  13.                                     nivel_disparador,comando,tabla,old_info,new_info)
  14.       VALUES (disparador, tipo, nivel, comando, tabla, oldInfo, newInfo);
  15. END $$
  16.  
Trigger
A diferencia de postgres, se necesita crear disparador por cada DML, además se debe colocar cada campo, tanto nuevo como viejo, en los 2 últimos campos del SP.

Código: SQL
  1. DROP TRIGGER IF EXISTS tg_disparador_nombre_tabla;
  2. DELIMITER $$
  3. CREATE TRIGGER tg_disparador_nombre_tabla
  4.    AFTER INSERT ON nombre_tabla
  5.    FOR EACH ROW
  6. BEGIN
  7.      CALL sp_auditoria_log('tg_disparador_nombre_tabla',
  8.                            'AFTER', 'ROW', 'INSERT',
  9.                            'nombre_tabla',
  10.                            NULL,
  11.                            CONCAT(NEW.campo1, ',', NEW.campo2, ',', NEW.campo3, ',', NEW.campo4, ',', NEW.campoN));
  12. END
  13. $$
  14. DELIMITER ;
  15.  
  16. DROP TRIGGER IF EXISTS tg_disparador_nombre_tabla;
  17. DELIMITER $$
  18. CREATE TRIGGER tg_disparador_nombre_tabla
  19.    AFTER UPDATE ON nombre_tabla
  20.    FOR EACH ROW
  21. BEGIN
  22.      CALL sp_auditoria_log('tg_disparador_nombre_tabla',
  23.                            'AFTER', 'ROW', 'UPDATE',
  24.                            'nombre_tabla',
  25.                            CONCAT(OLD.campo1, ',', OLD.campo2, ',', OLD.campo3, ',', OLD.campo4, ',', OLD.campoN),
  26.                            CONCAT(NEW.campo1, ',', NEW.campo2, ',', NEW.campo3, ',', NEW.campo4, ',', NEW.campoN));
  27. END;
  28. $$
  29. DELIMITER ;
  30.  
  31. DROP TRIGGER IF EXISTS tg_disparador_nombre_tabla;
  32. DELIMITER $$
  33. CREATE TRIGGER tg_disparador_nombre_tabla
  34.    AFTER DELETE ON nombre_tabla
  35.    FOR EACH ROW
  36. BEGIN
  37.      CALL sp_auditoria_log('tg_disparador_nombre_tabla',
  38.                            'AFTER', 'ROW', 'DELETE',
  39.                            'nombre_tabla',
  40.                            CONCAT(OLD.campo1, ',', OLD.campo2, ',', OLD.campo3, ',', OLD.campo4, ',', OLD.campoN),
  41.                            NULL);
  42. END;
  43. $$
  44. DELIMITER ;

Adjunto SP que genera los triggers de las tablas que se le pasen como parametro, las imprime en un select, si conocen una forma de ejecutarlos directamente, con PREPARE-EXECUTE y no me funcionó.
Código: SQL
  1. DROP PROCEDURE IF EXISTS sp_genera_triggers;
  2. DELIMITER $$
  3. CREATE PROCEDURE sp_genera_triggers (IN schema_auditar VARCHAR(50),
  4.                                      IN tablasNo LONGTEXT CHARSET utf8)
  5. BEGIN
  6.     DECLARE tg_insert, tg_update, tg_delete LONGTEXT;
  7.     DECLARE stmt, tablasTrigger LONGTEXT;
  8.     DECLARE nombreTabla VARCHAR(50);
  9.     DECLARE completo INT DEFAULT FALSE;
  10.  
  11.     -- --------------------------------------------------------
  12.     -- [ Se escogen las tablas que están por fuera de la lista]
  13.     -- --------------------------------------------------------
  14.     DECLARE tablas_lista CURSOR
  15.     FOR SELECT TABLE_NAME
  16.         FROM INFORMATION_SCHEMA.TABLES  
  17.         WHERE TABLE_SCHEMA=schema_auditar AND
  18.               !FIND_IN_SET(TABLE_NAME, tablasNo);
  19.              
  20.     DECLARE CONTINUE HANDLER
  21.         FOR NOT FOUND SET completo = TRUE;
  22.        
  23.     OPEN tablas_lista;
  24.  
  25.     cursor_loop: LOOP
  26.         FETCH tablas_lista INTO nombreTabla;
  27.  
  28.         IF completo THEN
  29.             LEAVE cursor_loop;
  30.         END IF;
  31.  
  32.         -- -------------------------------------------------------------------
  33.         -- [ Crea los triggers INSERT/UPDATE/DELETE de las tablas permitidas ]
  34.         -- -------------------------------------------------------------------
  35.         SET tg_update := CONCAT('DROP TRIGGER IF EXISTS ',
  36.                                  schema_auditar,
  37.                                  REPLACE(nombreTabla, 'tn_', '.tg_upd_'), ';\n',
  38.                                  'DELIMITER $$\n',
  39.                                  'CREATE TRIGGER ',
  40.                                  schema_auditar,
  41.                                  REPLACE(nombreTabla, 'tn_', '.tg_upd_'), '\n',
  42.                                  '   AFTER UPDATE ON ', nombreTabla,'\n',
  43.                                  '   FOR EACH ROW\n',
  44.                                  'BEGIN\n',
  45.                                  '     CALL sp_auditoria_log(\'', nombreTabla, '\',\n',
  46.                                  '                           \'AFTER\', \'ROW\', \'UPDATE\', \n',
  47.                                  '                           \'', nombreTabla, '\', \n');
  48.                                  
  49.         SET tg_delete := REPLACE(tg_update, 'tg_upd_', 'tg_del_');
  50.         SET tg_delete := REPLACE(tg_delete, 'UPDATE', 'DELETE');
  51.         SET tg_insert := REPLACE(tg_update, 'tg_upd_', 'tg_ins_');
  52.         SET tg_insert := REPLACE(tg_insert, 'UPDATE', 'INSERT');
  53.        
  54.         SET stmt := (SELECT CONCAT('                           CONCAT(',
  55.                                    GROUP_CONCAT(CONCAT('OLD.', COLUMN_NAME) SEPARATOR ', \',\', '),
  56.                                    '),\n')
  57.                      FROM information_schema.COLUMNS
  58.                      WHERE TABLE_SCHEMA=BINARY schema_auditar AND
  59.                            TABLE_NAME=BINARY nombreTabla);
  60.         SET tg_update := CONCAT(tg_update, stmt);
  61.         SET tg_delete := CONCAT(tg_delete, stmt,
  62.                                 '                           NULL',
  63.                                 ');\nEND;\n$$\nDELIMITER ;\n\n');
  64.        
  65.         SET stmt := (SELECT CONCAT('                           CONCAT(',
  66.                                    GROUP_CONCAT(CONCAT('NEW.', COLUMN_NAME) SEPARATOR ', \',\', '),
  67.                                    ')')
  68.                      FROM information_schema.COLUMNS
  69.                      WHERE TABLE_SCHEMA=BINARY schema_auditar AND
  70.                            TABLE_NAME=BINARY nombreTabla);
  71.         SET tg_update := CONCAT(tg_update, stmt, ');\nEND;\n$$\nDELIMITER ;\n\n');
  72.         SET tg_insert := CONCAT(tg_insert,
  73.                                 '                           NULL,\n',
  74.                                 stmt, ');\nEND\n$$\nDELIMITER ;\n\n');
  75.  
  76.  
  77.         SET tablasTrigger := CONCAT(tg_insert, tg_update, tg_delete);
  78.        
  79.         SELECT tablasTrigger;
  80.        
  81.     END LOOP;
  82.     CLOSE tablas_lista;
  83.    
  84.     SET script := tablasTrigger;
  85. END $$
  86.  

Código: SQL
  1. SET @tablasNo:='tabla1,tabla2,tabla3,tabla4,tablaN';
  2.  
  3. CALL sp_genera_triggers('nombre_schema', @tablasNo, @scriopt);