Técnicas de auditoría de bases de datos SQL Server

La auditoría de bases de datos SQL Server no es usada solamente para cumplir con requerimientos de conformidad. Se ha vuelto necesaria para el análisis de acciones de bases de datos, soluciones de problemas y la investigación de actividades sospechosas y maliciosas. La auditoría también puede ayudar a evitar acciones inapropiadas de parte de los usuarios – como si tuviera un sistema CCTV en sus bases de datos.

Hay muchas técnicas de auditoría de SQL Server:

• Auditoría manual – puede ser creada para cumplir con sus requerimientos específicos, pero consume tiempo y es proclive a errores
• Utilizar SQL Server Extended Events – fácil de configurar, un amplio rango de acciones pueden ser auditadas, pero no ofrece información acerca de qué se eliminó/insertó ni tampoco valores antiguos y nuevos para actualizaciones; una auditoría detallada puede causar problemas de desempeño
• Usar desencadenadores de SQL Server – fácil de configurar, pero puede causar problemas de desempeño en bases de datos de altas transacciones
• Leer los registros de transacciones – no hay captura de datos adicional, ya que SQL Server ya sigue estos cambios. Más espacio de almacenamiento es necesario, algunas de las acciones (como EXECUTEs) no son auditadas
• Usar SQL Server Profiler y traces de SQL Server – flexible y complejo. Es difícil leer y filtrar los registros

Cuál es el correcto para usted depende de su ambiente, lo que necesita auditar, dónde quiere almacenar las acciones capturadas y la forma que quiere que tengan los reportes.

Auditoría manual

Las bases de datos SQL Server pueden ser auditadas usando procedimientos almacenados especialmente desarrollados y funciones para rastrear cambios en los datos y los objetos. Provee una solución flexible que requiere una gran cantidad de código y desarrollo, lo cual incrementa el costo y el tiempo de implementación.

Usar SQL Server Extended Events

SQL Server Audit es una característica de SQL Server, introducida primero en la versión 2008 que usa SQL Server Extended Events para auditar acciones de SQL Server. Permite auditar diferentes acciones, proveyendo mucha granularidad en el proceso de configuración y cubriendo un amplio rango de la actividad de SQL Server.

Para crear un nuevo objeto de SQL Server Audit:

1. En SQL Server Management Studio, expanda Security y haga clic derecho en Audits

2. Seleccione New Audit

   

3. Especifique un nombre para la auditoría, establezca si desea almacenar los datos de auditoría en un registro de eventos de la aplicación, un registro de eventos de seguridad o un archivo, y una localización para el archivo de auditoría

   

4. Haga clic en OK y su auditoría aparecerá en el nodo Audits en el Object Explorer de SQL Server Management Studio

5. Por defecto, está deshabilitada y por tanto mostrada con una flecha roja. Para habilitarla, haga clic derecho y selecciones Enable Audit

   

6. Elija si necesita Server Audit Specification o Database Audit Specification, dependiendo si usted quiere auditar la actividad al nivel de una instancia SQL Server o una base de datos

7. Para crear una Database Audit Specification, expanda el nodo de la base de datos que desea auditar, vaya a Security, haga clic derecho en Database Audit Specifications y seleccione New Database Audit

   

8. En el diálogo Create Database Audit Specification, especifique un nombre de especificación, asocie la especificación con el objeto de auditoría creado en el paso #1, especifique la actividad que será auditada en Audit Action Type. Para la auditoría de bases de datos, especifique una base de datos, objeto, o esquema como Object Class, el nombre del objeto auditado, y el login auditado

   

Usted puede ver todas las acciones que pueden ser auditadas usando SQL Server Auditing en la lista desplegable para Audit Action Type

Los Principales que usted debe seleccionar en este diálogo son realmente cuentas de usuario que serán monitoreadas.

Al igual que las auditorías, las especificaciones de auditoría de bases de datos están deshabilitadas por defecto. Para habilitarlas, seleccione esta opción en su menú contextual

Ahora, todas las sentencias DELETE ejecutadas contra Person.BusinessEntityAddress serán auditadas e insertadas en archivos con nombres que empiezan con Audit-, como Audit-AW2012Test_9D93CA4A-8B90-40B8-8B0B-FCBDA77B431D_0_130161593310500000.sqlaudit, y almacenadas en E:\

Dado que puede haber muchas acciones capturadas en una base de datos activa, es recomendado grabar la información de auditoría en un archivo. El archivo sqlaudit no puede ser abierto en un editor de texto o hex. Use Reporting Services o la función T-SQL fn_get_audit_file para analizar los datos.

Por ejemplo:

SELECT event_time,action_id,statement,database_name,server_principal_name
  FROM fn_get_audit_file( 'E:\Test\Audit-*.sqlaudit' , DEFAULT , DEFAULT);
      

lo anterior muestra los siguientes resultados:

Incluso cuando usted selecciona todas las columnas, usted no verá qué fue eliminado realmente, sólo cuándo y quién realizó la eliminación, lo cual es una de las desventajas de este método.

Otros inconvenientes son:

• Como SQL Server Audit usa recursos de SQL Server para una auditoría detallada, esto puede afectar el rendimiento general de SQL Server
• Administrar SQL Server Audit en instancias SQL Server múltiples no puede ser centralizado por defecto
• Analizar y archivar los datos de auditoría (en un archivo, o en registros) involucra importación, reportes y archivado manuales
• Está disponible sólo en versiones de SQL Server 2008 y posteriores
• La auditoría a nivel de base de datos está disponible sólo en las ediciones Enterprise, Developer y Evaluation

Usar desencadenadores de SQL Server

Los desencadenadores de SQL Server son automáticamente activados cuando un cierto evento sucede. Los desencadenadores son realmente procedimientos almacenados ejecutados automáticamente cuando una condición se cumple. Los desencadenadores de Data Manipulation Language (DML) por tanto pueden ser usados para rastrear las sentencias INSERT, UPDATE y DELETE. Usted puede crear estos desencadenadores uno por uno para cada tabla y cada sentencia que desea auditar. Usted también necesita crear un espacio de almacenamiento para la información auditada – por ejemplo, una tabla SQL donde serán insertados el tiempo de la transacción, el nombre de usuario, el tipo de transacción, etc. Para rastrear UPDATEs, es muy útil almacenar los valores antiguos y nuevos.

Por ejemplo, un desencadenador que es activado después de que un registro fuera insertado en la tabla Person.Person inserta un nombre de tabla, un tiempo y fecha cuando el registro fue insertado y el nombre de usuario usado para insertar el registro a la tabla dbo.Repository. Debería verse así:

CREATE TRIGGER PersonPerson_I
ON Person.Person
AFTER INSERT 
AS
   INSERT INTO dbo.repository (
TABLE_NAME,
		TABLE_SCHEMA,
		AUDIT_ACTION_ID,
		MODIFIED_BY,
		MODIFIED_DATE,
		[DATABASE]
	)
	values(
		'Person',
		'Person',
		'Insert',			
		SUSER_SNAME(),
		GETDATE(),
		'AdventureWorks2012'
	) GO

Antes de que tales desencadenadores sean creados, usted debería diseñar y crear la(s) tabla(s) donde el DML será almacenado.

Este método es proclive a errores, ya que hay mucho trabajo manual involucrado.

ApexSQL Trigger es una herramienta de auditoría de bases de dato que captura cambios en los datos que han ocurrido en una base de datos, incluyendo la información acerca de quién hizo el cambio, qué objetos fueron afectado por él, cuándo fue hecho así como la información del login de SQL, la aplicación y el anfitrión usados para hacer el cambio. Almacena toda la información capturada en el repositorio central y la exporta en formatos amigables para imprimir. Para crear desencadenadores, simplemente seleccione las tablas y los tipos de operación que desea auditar.

1. Inicie ApexSQL Trigger

2. Conéctese a la base de datos que desea auditar

   

3. En la cuadrícula principal, seleccione la tabla que desea auditar

   

4. En el panel Fields, seleccione las columnas a auditar

   

5. Seleccione las transacciones a auditar – INSERT, DELETE, UPDATE
6. Repita los pasos del 3 al 5 para todas las tablas que desea auditar
7. En el menú, haga clic en Create triggers

8. El script que genera los desencadenadores especificados es mostrado en el diálogo Script. Verifíquelo y presione F5 para ejecutarlo

   

Una vez que los desencadenadores son creados, serán ejecutados por cada INSERT, DELETE y UPDATE ejecutados contra la tabla y los detalles de la operación son almacenados en las tablas AUDIT_LOG_DATA y AUDIT_LOG_TRANSACTIONS.

Usted puede verlos fácilmente usando los reportes incorporados de ApexSQL Trigger, o creando consultas SQL propias.

Mientras que los desencadenadores proporcionan una auditoría granular y un almacenamiento fácil de acceder, su principal desventaja es que los que son activados en una base de datos atareada pueden causar gastos extras.

Leer los registros de transacciones

Como cada cambio de esquema y datos en una base de datos SQL Server es añadido a un registro de transacciones en línea como una entrada de registro, leer estas entradas puede ser usado como una técnica de auditoría. Abrir un archivo de registro de transacciones en línea de bases de datos, un registro de transacciones suelto o copias de seguridad de registros de transacciones y leerlos no tan fácil. Una de las opciones es usar funciones no documentadas como fn_dblog, fn_dump_dblog, y DBCC PAGE.

Aparte de la complejidad y los problemas con la reconstrucción de UPDATE/BLOB, su mayor desventaja es que muestran valores hex que usted tiene que descifrar.

Para leer registros de transacciones, use un lector de registros de SQL Server como ApexSQL Log. Esta herramienta audita, revierte o rehace cambios de datos y objetos que han afectado una base de datos, incluyendo aquellos que han ocurrido antes de la instalación de ApexSQL Log. También captura información acerca del usuario, la aplicación y el anfitrión usados para hacer cada cambio.

1. Inicie ApexSQL Log

2. Conéctese a la base de datos que desea auditar

   

3. En el paso Select SQL logs to analyze, añada las copias de seguridad de registros de transacciones y los registros de transacciones sueltos que quiere leer. Note que ellos deben formar una cadena completa para proveer una auditoría exitosa

   

4. Use las opciones de Filter setup para reducir el conjunto de resultados usando el tiempo el tipo de operación, el nombre de la tabla, el usuario y otras opciones de filtrado

   

5. Haga clic en Open

6. Los resultados son mostrados en la cuadrícula principal y usted puede crear fácilmente scripts para deshacer y rehacer, o exportarlos a archivos CSV, HTML, XML o archivos SQL y grabarlos en el disco duro

   

Las ventajas de este método son que no hay desencadenadores y tampoco procesos adicionales para capturar la información de auditoría que puede afectar el desempeño de SQL Server. El historial de transacciones puede ser obtenido para el periodo antes de que la herramienta fuera instalada, a diferencia de los desencadenadores y Extended Events.

Las desventajas son:

• Más espacio es requerido para suficientes fuentes de datos, ya que una base de datos tiene que estar en un modelo de recuperación completo, y una cadena completa de registros de transacciones debe existir
• No todas las acciones que un usuario quiera auditar son almacenadas en un registro de transacciones. Por ejemplo, las sentencias SELECT ejecutadas y las consultas no son almacenadas ahí

Usar SQL Server Profiler y traces de SQL Server

Usar SQl Server Profiler y traces de SQl Server para auditar es una solución muy compleja con mucho trabajo manual, y por lo tanto proclive a errores.

ApexSQL Audit es una herramienta de auditoría construida sobre traces de SQL Server, lo que provee información de “quién vio qué”, una auditoría con tolerancia a fallos, reporte centralizado, una GUI amigable para el usuario para configurar la auditoría en más de 230 operaciones, y un repositorio centralizado para almacenar registros de auditoría y configuraciones. La herramienta configura los traces de acuerdo a la configuración que un usuario ha especificado, o usa su configuración por defecto que cubre las solicitudes más comunes de auditoría.

1. Inicie ApexSQL Audit

2. Haga clic en el botón ‘Add server’ para seleccionar un servidor para la auditoría

   

3. Haga clic en el botón ‘Add database’ para seleccionar una base de datos para la auditoría, y seleccione el servidor o base de datos que desea auditar

   

Otra opción es usar el tipo de filtro Avanzado:

Ahora, cuando sea que cualquier operación que usted seleccionó es realizada en el SQL Server auditado, un registro será guardado en la base de datos del repositorio central – ApexSQLCrd.

Para ver los registros de auditoría, usted puede un reporte local integrado, o usar la funcionalidad de reportes web.

Las ventajas de ApexSQL Audit son la fácil configuración de auditoría, un rango amplio de tipos de operaciones auditadas, el repositorio central, los reportes web, de tal modo que incluso usuarios remotos puedan acceder fácilmente a todos los registros de auditoría, y la administración fácil y centralizada.

Como se presentó anteriormente, hay muchas técnicas de auditoría SQL Server que utilizan diferentes características de SQL Server. Desde código manualmente escrito y por tanto proclive a errores, hasta un GUI amigable para el usuario. Algunas de ellas pueden causar un costo extra y problemas de Desempeño en SQL Server, mientras que otras no tiene casi ningún efecto. Determine qué tipo de operaciones necesita auditar y selecciones una herramienta de auditoría ApexSQL para configurar la auditoría y crear reportes en sólo algunos clics del ratón.

Traductor: Daniel Calbimonte