Início / dba / Perguntas / 90283
Accepted
John Little
Asked: 2015-01-28 03:23:40 +0800 CST

MySQL Script para automatizar a criação de tabelas e gatilhos de auditoria (histórico)?

  • 772

Para requisitos regulatórios e de fraude, precisamos registrar todas as alterações na maioria das tabelas no banco de dados. A maneira como fizemos isso com muito sucesso em um projeto anterior é ter uma cópia de cada tabela, idêntica, exceto que:

  1. 4 colunas adicionais: DateChanged, UserId, Action (Create, Update, delete) e IP
  2. O Id pk não é mais único.
  3. todas as outras restrições (FK, índices exclusivos, etc.) foram removidas.
  4. as tabelas de auditoria estão em um esquema de auditoria separado

Em um job anterior, os DBAs Oracle escreveram um script para gerar tudo isso automaticamente, fizeram o seguinte:

  1. Criou um novo esquema chamado audit se não existir
  2. Itere sobre cada tabela t no esquema normal:
    1. criou uma nova tabela no esquema de auditoria com o mesmo nome de tabela, exceto prefixado com a_, por exemplo, a_t
    2. Adicionadas todas as mesmas colunas da tabela original, mais as 4 colunas adicionais (DateChanged, UserId, IP Address e Action)
    3. gatilhos gerados e adicionados à tabela original (se ainda não estiverem lá) para:
    4. Se estiver atualizando, escreva uma nova linha na tabela a_ correspondente, com ação de "Atualizar" contendo os valores pré-atualizados (antigos) em todas as colunas (a tabela principal terá os novos valores)
    5. Se estiver excluindo, adicione uma linha que seja uma cópia da linha da tabela principal, mas ação = "Excluir".
    6. Se inserir, adicione a linha ao esquema de auditoria

NOTA:

  1. Todas as tabelas possuem um ID PK.
  2. Algumas tabelas precisam ser excluídas para desempenho ou porque não são necessárias (por exemplo, a tabela de saldo que é atualizada por um gatilho)

A beleza desse sistema é que você pode consultar o que mudou e quando, ou quem fez alterações no quê, e ver o registro antes e depois da alteração. as alterações estão no nível da linha do banco de dados, não no nível da coluna individual.

Alguém já se deparou com algo assim para MYSQL 5.6? Não temos um DBA em nossa equipe que poderia escrever algo assim do zero, mas sabemos o suficiente para modificar algo semelhante.

mysql audit

2 respostas

  1. Best Answer

    Acabei de escrever isso hoje cedo. É uma instrução select funcionando no banco de dados information_schema, que produz o esquema para as tabelas de auditoria e os gatilhos.

    SET GLOBAL group_concat_max_len = 1000;

SET @dbName = "[[[your_db_name_here]]]";

SELECT concat("DROP TABLE IF EXISTS `", @dbName, "`.`", table_data.audit_table, "`;\r",
          "CREATE TABLE `", @dbName, "`.`", table_data.audit_table, "`\r",
          "(\r",
          "  `auditAction` ENUM ('INSERT', 'UPDATE', 'DELETE'),\r",
          "  `auditTimestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,\r",
          "  `auditId` INT(14) AUTO_INCREMENT,",
          column_defs, ",\r"
          "  PRIMARY KEY (`auditId`),\r",
          "  INDEX (`auditTimestamp`)\r",
          ")\r",
          "  ENGINE = InnoDB;\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.insert_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.insert_trigger, "`\r",
          "  AFTER INSERT ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('INSERT',", table_data.NEWcolumn_names, ");\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.update_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.update_trigger, "`\r",
          "  AFTER UPDATE ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('UPDATE',", table_data.NEWcolumn_names, ");\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.delete_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.delete_trigger, "`\r",
          "  AFTER DELETE ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('DELETE',", table_data.OLDcolumn_names, ");\r\r"
)
FROM (
   # This select builds a derived table of table names with ordered and grouped column information in different
   # formats as needed for audit table definitions and trigger definitions.
   SELECT
     table_order_key,
     table_name                                                                      AS db_table,
     concat("audit_", table_name)                                                    AS audit_table,
     concat(table_name, "_inserts")                                                  AS insert_trigger,
     concat(table_name, "_updates")                                                  AS update_trigger,
     concat(table_name, "_deletes")                                                  AS delete_trigger,
     group_concat("\r  `", column_name, "` ", column_type ORDER BY column_order_key) AS column_defs,
     group_concat("`", column_name, "`" ORDER BY column_order_key)                   AS column_names,
     group_concat("`NEW.", column_name, "`" ORDER BY column_order_key)               AS NEWcolumn_names,
     group_concat("`OLD.", column_name, "`" ORDER BY column_order_key)               AS OLDcolumn_names
   FROM
     (
       # This select builds a derived table of table names, column names and column types for
       # non-audit tables of the specified db, along with ordering keys for later order by.
       # The ordering must be done outside this select, as tables (including derived tables)
       # are by definition unordered.
       # We're only ordering so that the generated audit schema maintains a resemblance to the
       # main schema.
       SELECT
         information_schema.tables.table_name        AS table_name,
         information_schema.columns.column_name      AS column_name,
         information_schema.columns.column_type      AS column_type,
         information_schema.tables.create_time       AS table_order_key,
         information_schema.columns.ordinal_position AS column_order_key
       FROM information_schema.tables
         JOIN information_schema.columns
           ON information_schema.tables.table_name = information_schema.columns.table_name
       WHERE information_schema.tables.table_schema = @dbName
             AND information_schema.columns.table_schema = @dbName
             AND information_schema.tables.table_name NOT LIKE "audit\_%"
     ) table_column_ordering_info
   GROUP BY table_name
 ) table_data
ORDER BY table_order_key
INTO OUTFILE "[[[your_output_file]]]"
    • 6

  2. A resposta de Vanyo quase funcionou para mim. Foi bom quando movi o backtick nas referências NEWe OLD(então, em vez de `NEW.column1`eu recebo NEW.`column1`):

    group_concat("NEW.`", column_name, "`" ORDER BY column_order_key)               AS NEWcolumn_names,
group_concat("OLD.`", column_name, "`" ORDER BY column_order_key)               AS OLDcolumn_names
    • 1

relate perguntas