Antes y después de auditar en SQL Server

Simplemente archivar información para auditar una base de datos es una cosa, pero reconstruir un historial de auditoría exitosamente para proveer datos forenses significativos es otra. Es importante poder ver un historial completo de los cambios del usuario, así como poder revertir los cambios que pueden haber sido accidentales o maliciosos.

Idealmente, tal información del valor añadido puede ser obtenida sin requerir una gran cantidad de datos archivados o creando un impacto significativo en el desempeño en los servidores auditados.

En este artículo, vamos a presentar dos diferentes enfoque y soluciones para antes y después de la auditoría.

ApexSQL Audit

ApexSQL Audit es una herramienta de cumplimiento de normas y auditoría para SQL Server que audita todas las operaciones realizadas en la instancia SQL Server auditada, incluyendo cambios DDL y DML, actividades relacionadas a ingresos, usuarios, permisos y seguridad.

Aunque ApexSQL Audit configura y utiliza rastros para auditar actividades SQL Server, para realizar una auditoría de antes y después usa una diferente tecnología: desencadenadores SQL. Estos desencadenadores pueden ser aplicados para capturar cambios específicos hechos sólo en objetos seleccionados o en todos los objetos en una base de datos. La información puede ser capturada en todas las operaciones DML (INSERT, UPDATE y DELETE), e incluye detalles acerca de quién hizo el cambio y cuándo, qué objetos fueron afectados, qué inicio de sesión, aplicación y host fueron usados, así como el valor original (antes).

La auditoría basada en desencadenadores trabaja en una manera simple – cuando el cambio ocurre en la tabla/campo auditado, el desencadenador es gatillado y captura la información acerca de la operación ejecutada. Toda la información capturada luego es almacenada en 2 tablas que son automáticamente creadas en la base de datos auditada. Esto permite un fácil acceso directamente desde la aplicación, o desde SQL Server Management Studio para investigar los resultados o crear reportes de auditoría profundos.

Para configurar la auditoría antes y después en ApexSQL Audit, los siguientes pasos necesitan ser completados:

1. Inicie la Interfaz de Usuario de ApexSQL Audit

2. Haga clic en el botón Before-After en la cinta principal:

   

3. En el diálogo Before and after data auditing haga clic en el botón New para iniciar el asistente:

   

4. Seleccione la instancia SQL Server y la base de datos, provea detalles de autenticación válidos y haga clic en el botón OK:

   

5. Seleccione las tablas y columnas a las que se aplicarán los desencadenadores y especifique las operaciones que activarán los desencadenadores (INSERT, UPDATE y/o DELETE):

   

6. Haga clic en el botón Create en la cinta principal:

   

7. El script SQL Server que ha sido creado aplicará desencadenadores predefinidos para elegir tablas/columnas. Ejecútelo para completar el proceso de configuración haciendo clic en el botón Execute :

   

8. Con esto, la auditoría de antes y después ha sido configurada, y cualquier cambio que ocurra será capturado y almacenado en el repositorio central.

Para inspeccionar antes y después de los cambios, uno simplemente necesita crear el reporte de auditoría. Antes de que lleguemos a esa parte, veamos un típico caso de uso donde ApexSQL Audit es usado para identificar algunos cambios inusuales en una tabla particular.

En caso de notar cambios en la tabla de comisión de ventas donde los valores de la comisión han sido cambiado de una manera no autorizada:

Tenemos una tabla “Sales Transaction” que consiste en 3 columnas: 1. Sales Person ID, 2. Comission Level ID y 3. Comission %

Aquí se ve la tabla en su estado original:

Ahora, digamos que los valores de Comission % fueron todos cambiados a nuevos valores:

4.5 –>> 6

5.5 –>> 7

6.5 –>> 8

Este cambio malicioso puede pasar desapercibido si la auditoría de base de datos no es realizada.

ApexSQL Audit puede ser usado para capturar este cambio, y prevenir el daño.

Dado que el historial de datos es colectado en el repositorio central, uno sólo necesita leer los datos de ahí, creando un reporte de auditoría estándar.

Para hacerlo, los siguientes pasos deben ser ejecutados:

1. En el diálogo Before and after data auditing, conéctese a la base de datos (pasos 1-4 de la guía previa) y haga clic en el botón Standard , desde el grupo Reports:

   

2. Provea información variada de filtro para reducir el reporte y obtener sólo resultados relevantes, y haga clic en el botón Apply para obtener los resultados:

   

3. En el reporte, es fácil ver los cambios exactos que han ocurrido,así como los valores de antes y después. Información adicional como quién hizo los cambios, cuándo y cómo también está disponible:

   

Como una característica extra, es fácil configurar una alerta para ser desencadenada cuando cambios UPDATE hayan sido hechos en algunas tablas. ApexSQL Audit puede ser instruido para mandar un correo electrónico de alerta cuando UPDATEs potencialmente maliciosos han sido realizados en tablas específicas. Aquí hay una guía paso a paso acerca de cómo obtener una alerta desde ApexSQL Audit cuando un cierto registro cambia en la base de datos SQL Server.

Pros

• Parte integral de una herramienta con capacidades completas de auditoría que audita todas las operaciones realizadas en la instancia SQL Server auditada
• Los datos son almacenado en un repositorio central
• Configuración rápida y fácil
• Mecanismos de alerta

Contras

• Impacto pequeño en el desempeño debido a la implementación de desencadenadores directamente en la base de datos auditada
• Capacidades forenses limitadas (no se cuenta con el historial de filas completo)
• No se cuenta con capacidades de restauración

Otra solución – ApexSQL Log

Sólo leyendo el registro de transacciones de SQL Server, la funcionalidad “antes y después” puede ser obtenida sin costo para el desempeño del sistema y sin requerimientos para almacenar/archivar datos adicionales.

ApexSQL Log, un lector de transacciones SQL Server, provee una solución ideal y efectiva a los requerimientos de antes y después típicos ofreciendo ventajas funcionales clave y eliminando las desventajas de almacenaje masivo adicional para la auditoría de antes y después. Implementado junto a ApexSQl Audit, estas herramientas pueden proveer un espectro completo de cobertura de auditoría, pero pueden hacerlo mucho más eficientemente y con economía de recursos.

ApexSQL Log usa una tecnología pasiva que simplemente lee el registro de transacciones versus archivar grandes volúmenes de datos. Así que no hay un requerimiento de almacenamiento o una degradación del desempeño para sistemas auditados. Pero provee vistas de datos poderosas de antes y despu’es, as’i como un historial completo de cambios. Finalmente, ApexSQL Log tiene capacidades extremadamente poderosas para deshacer/rehacer para crear scripts para revertir o reproducir las transacciones seleccionadas.

ApexSQL Log, dado puede ser usado solamente cuando es requerido en servidores afectados, no requiere licencias para cubrir cada servidor. Menos licencias pueden ser compradas, las opciones de licencias flotantes y licencias de sitio también están disponibles para reducir el costo por servidor. O ApexSQL Log puede ser agrupado con ApexSQL DBA a un costo adicional nominal por instancia SQL Server.

Ahora examinemos las capacidades del enfoque de ApexSQL Log, incluyendo a) auditoría antes y después b) historial a nivel de filas c) transacciones UNDO y REDO ya que se relaciona con este particular caso/estudio.

Aquí está lo que necesita ser hecho para obtener los datos de la auditoría de antes y después:

1. Inicie ApexSQL Log y haga clic en el botón New:

   

2. En el asistente de sesión, provea lo detalles de una conexión de base de datos y credenciales válidas, y haga clic en el botón Next:

   

3. En el paso Select SQL logs to analyze del asistente, añada todas las copias de seguridad de registros para asegurar que la cadena completa de copias de seguridad de registros de transacciones esté disponible. Esto es esencial, dado que ApexSQL Log necesita la cadena completa para reconstruir el historial de filas y los detalles antes/después. Haga clic en el botón Next para avanzar:

   

4. En el paso Filter setup del asistente, use varios filtros para reducir el rango de auditoría tanto como sea posible, de modo que transacciones específicas puedan ser localizadas con facilidad. Proveer rangos de fecha/hora personalizados o seleccionar tablas específicas para auditar afecta de gran manera a los resultados, haciéndolos más fáciles de explorar. Después de que todos los filtros han sido añadidos, haga clic en el botón Next para proceder:

   

5. Finalmente, elija la opción Open results in grid para completar la auditoría y mostrar los resultados en una tabla exhaustiva:

   

6. Seleccionar una operación de la tabla mostrará los detalles en el panel Operation details, en la parte inferior de la ventana principal. Aquí, los valores de antes (Old) y después (New) pueden ser vistos para la operación seleccionada:

   

Para ver el historial completo de filas, simplemente seleccione la pestaña Row history en la parte baja del panel. Para nuestro caso, vemos que el usuario Zwerka\FEAR ha hecho 8 cambios en el valor inicial de Comission %:

Otra gran característica de ApexSQL Log es que nos permite simplemente revertir estos cambios. Si decidimos que son no deseados, podemos simplemente revertirlos con sólo unos pocos clics:

1. Seleccione una o más transacciones que necesitan ser revertidas y elíjalas en la tabla:

   

2. En el menú principal, haga clic en el botón Create undo script:

   

3. ApexSQL Log crea un script que revertirá los cambios seleccionado a su estado original. El script creado será mostrado y estará disponible para ediciones adicionales si es necesario:

   

4. El único paso restante es ejecutar el script, lo cual puede ser hecho directamente desde el editor, o el script puede ser grabado y ejecutado desde SQL Server Management Studio después.

Si vemos nuestra tabla de nuevo, veremos que las filas actualizadas maliciosamente están de vuelta en su estado original:

Pros

• El historial de filas completo está disponible
• ApexSQL Log puede auditar operaciones que han ocurrido incluso antes de que la aplicación haya sido instalada en el sistema – si la información es presentada en los archivos de registros de transacciones, ApexSQL Log puede leerla
• No hay impacto en el desempeño – dado que ApexSQL Log sólo lee los archivos de registros de transacciones, no tiene impacto en el servidor SQL Server que está siendo auditado
• Capacidades de recuperación completa – todos los cambios maliciosos o no deseado pueden ser revertidos a su estado original con sólo unos pocos clics

Contras

• Menos capacidades de auditoría que ApexSQL Audit
• No hay mecanismos de alerta
• La base de datos tiene que estar en modo de recuperación completa, y una cadena completa de archivos/copias de seguridad de registros de transacciones debe estar disponible, tomando espacio de disco en la red.

Algunas preguntas frecuentes acerca de ApexSQL Log

P: ¿Cómo almaceno los datos de registros de transacciones lo suficientemente atrás para hacer un antes y después histórico?

R: La gran mayoría de las situaciones de auditoría de tipo forense de antes y después suceden en horas, días o semanas recientes. No meses o años. En la mayoría de los casos, esta información está ya disponible en el registro en línea. En otros, las copias de seguridad de registros de transacciones pueden ser encadenadas para información histórica yendo tan atrás como sea necesario.

P: ¿Hay algún impacto en el desempeño a la auditoría antes y después con ApexSQL Log?

R: No, no hay ningún impacto. ApexSQL Log no tiene cargas adicionales durante la captura de datos de auditoría. La aplicación simplemente lee los registros de transacciones para recolectar datos de auditoría. Esto permite que la auditoría sea realizada durante momentos de baja carga o incluso desconectada y puesta en otro servidor.

P: ¿Cómo puedo configurar mi sistema para una auditoría de antes y después óptima?

R: Para asegurar que el archivo de registro de transacciones incluya todos los datos históricos, es importante tener una base de datos en modo de recuperación completo. Esto asegura que los datos en los registros de transacciones en línea nunca son sobrescritos y estarán disponibles para ApexSQL Log.

P: ¿ApexSQL Log y ApexSQL Audit pueden ser comprado juntos?

R: Sí, vía ApexSQL DBA bundle

ApexSQL DBA permite a los Administradores de Bases de Datos apalancar las características de ApexSQL Audit para una auditoría tradicional sinérgicamente, con las capacidades de ApexSQL Log, para una mejor auditoría de antes y después, análisis de historia y deshacer transacciones/recuperar datos. Usados en conjunto, estas herramientas pueden mejorarse mutuamente para proveer capacidades poderosas de auditoría, incluyendo antes y después, con un costo eficiente, mientras que limita la sobrecarga del sistema y los requerimientos de almacenamiento de datos.

Traductor: Daniel Calbimonte