database - tablas - ¿El mejor diseño para una tabla de base de datos de registro de cambios/auditoría?

ver historial de consultas en sql server (9)

De acuerdo con el principio de separación: -

1) Las tablas de datos de auditoría deben estar separadas de la base de datos principal. Porque la base de datos de auditoría puede tener muchos datos históricos.

entonces tiene sentido desde la utilización de la memoria también para mantenerlo separado.

2) No use disparadores para auditar la base de datos de agujeros. Porque terminará con problemas de soporte de bases de datos diferentes. También debe escribir uno para DB2, SQLServer y Mysql, etc.

Necesito crear una tabla de base de datos para almacenar diferentes registros de cambios / auditoría (cuando algo fue agregado, eliminado, modificado, etc.). No necesito almacenar información particularmente detallada, así que estaba pensando algo así como:

id (para evento)
usuario que lo activó
nombre del evento
descripción del evento
fecha y hora del evento

¿Me estoy perdiendo de algo? Obviamente puedo seguir mejorando el diseño, aunque no planeo complicarlo (la creación de otras tablas para tipos de eventos o cosas así está fuera de cuestión ya que es una complicación para mi necesidad).

En el proyecto en el que estoy trabajando, el registro de auditoría también comenzó desde el diseño minimalista, como el que describió:

event ID event date/time event type user ID description

La idea era la misma: mantener las cosas simples.

Sin embargo, rápidamente se hizo obvio que este diseño minimalista no era suficiente. La típica auditoría se estaba reduciendo a preguntas como esta:

Who the heck created/updated/deleted a record with ID=X in the table Foo and when?

Entonces, para poder responder esas preguntas rápidamente (usando SQL), terminamos teniendo dos columnas adicionales en la tabla de auditoría

object type (or table name) object ID

Fue entonces cuando el diseño de nuestro registro de auditoría realmente se estabilizó (hace algunos años).

Por supuesto, la última "mejora" funcionaría solo para las tablas que tenían claves sustitutas. ¿Pero adivina que? ¡Todas nuestras tablas que valen la pena tener una clave así!

En general, la auditoría personalizada (crear varias tablas) es una mala opción. Los desencadenadores de base de datos / tabla se pueden deshabilitar para omitir algunas actividades de registro. Las tablas de auditoría personalizadas pueden ser manipuladas. Pueden ocurrir excepciones que disminuyan la aplicación. No mencionar las dificultades para diseñar una solución robusta. Hasta ahora, veo casos muy simples en esta discusión. Necesita una separación completa de la base de datos actual y de los usuarios privilegiados (DBA, Desarrolladores). Todos los RDBMS convencionales proporcionan instalaciones de auditoría que incluso el DBA no puede desactivar, alterar el secreto. Por lo tanto, la capacidad de auditoría proporcionada por el proveedor de RDBMS debe ser la primera opción. Otra opción sería un lector de registro de transacciones de terceros o un lector de registro personalizado que inserte información descompuesta en el sistema de mensajería que termina en algunas formas de Audit Data Warehouse o controlador de eventos en tiempo real. En resumen: Solution Architect / "Hands on Data Architect" necesita implicar el destino de un sistema basado en los requisitos. Por lo general, es algo muy serio solo para entregarlo a los desarrolladores por la solución.

Hay muchas maneras de hacer esto. Mi forma favorita es:

0 - Agregue un campo mod_user a su tabla fuente (la que desea registrar)

1 - Cree una tabla de registro que contenga los campos que desea registrar, más un campo log_datetime y seq_num. seq_num es la clave principal.

2 - Construya un desencadenador en la tabla de origen que compruebe si hay un cambio en cualquier campo supervisado, e inserta el registro actual en la tabla de registro en cualquier cambio

Ahora tienes un registro de cada cambio y quién lo hizo.

Hay muchas respuestas interesantes aquí y en preguntas similares. Lo único que puedo agregar de la experiencia personal es ...

Coloque su tabla de auditoría en otra base de datos. Lo ideal es que desee la separación de los datos originales. Si necesita restaurar su base de datos, realmente no desea restaurar la pista de auditoría.
Desnormalizar tanto como sea razonablemente posible. Desea que la tabla tenga las menores dependencias posibles de los datos originales. La tabla de auditoría debe ser simple y rápida para recuperar datos. No hay combinaciones o búsquedas extravagantes en otras tablas para llegar a los datos.

Hay varias cosas más que podría desear auditar, como nombres de tabla / columna, computadora / aplicación desde la cual se realizó la actualización y más.

Ahora, esto depende de la auditoría detallada que realmente necesita y en qué nivel.

Comenzamos a construir nuestra propia solución de auditoría basada en disparadores y queríamos auditar todo y también tener una opción de recuperación a mano. Esto resultó ser demasiado complejo, por lo que terminamos con la herramienta de terceros basada en disparadores de ingeniería inversa ApexSQL Audit para crear nuestra propia solución personalizada.

Consejos:

-Incluir valores antes / después

-Incluye 3,4 columnas para almacenar la clave principal (en caso de que sea una clave compuesta)

-Guardar datos fuera de la base de datos principal como ya sugirió Robert

- Dedique una cantidad decente de tiempo en la preparación de informes, especialmente aquellos que podría necesitar para la recuperación

-Plan para almacenar el nombre del host / aplicación: esto podría ser muy útil para rastrear actividades sospechosas

Lo que tenemos en nuestra mesa:

Primary Key Event type (e.g. "UPDATED", "APPROVED") Description ("Frisbar was added to blong") User Id User Id of second authoriser Amount Date/time Generic Id Table Name

Los puntos genéricos de identificación en una fila en la tabla que se actualizó y el nombre de la tabla es el nombre de esa tabla como una cadena. No es un buen diseño de base de datos, pero es muy útil. Todas nuestras tablas tienen una sola columna de clave sustituta, así que esto funciona bien.

También registramos valores antiguos y nuevos y la columna de la que provienen, así como la clave principal de la tabla que se está auditando en una tabla de detalles de auditoría. ¿Piensa para qué necesita la tabla de auditoría? No solo quiere saber quién hizo un cambio y cuándo, sino que cuando ocurre un cambio malo, quiere una manera rápida de recuperar los datos.

Mientras diseña, debe escribir el código para recuperar datos. Cuando necesite recuperarse, generalmente tiene prisa, lo mejor es estar preparado.

Tarde en la fiesta, pero recomiendo encarecidamente el proyecto AutoAudit .
Es 100% gratuito y de código abierto. Está escrito por los MVP de SQL, Paul Nielsen y John Sigouin. Es muy estable y actualmente se encuentra en la versión 3.30.

Simple de instalar. Simplemente ejecute el SP que proporcionan. Creará un Esquema de Auditoría, algunos SP de mantenimiento y los disparadores necesarios para realizar la auditoría. A partir de ahí, simplemente elija qué tablas le gustaría auditar y con qué detalle.