Uno de los tópicos de seguridad esenciales de SQL Server es encontrar quién hizo algo, qué y cuándo. La capacidad de proveer una historia de datos para varios propósitos de auditoría, algunos de los cuales son reforzados por leyes de Estados Unidos a través de regulaciones de cumplimiento, es una tarea seria para cualquier Administrador de Base de Datos. Dado que las políticas de negocios o las regulaciones de cumplimiento requieren la auditoría de cambios en los datos dentro de una base de datos, varias técnicas de adaptación a los requerimientos son usadas para realizar autorías a la base de datos. La común es usar desencadenadores de auditoría en bases de datos SQL Server.
Un desencadenador es un tipo especial de objeto de base de datos que es automáticamente ejecutado bajo ciertas condiciones – por ejemplo, acciones realizadas por el usuario. Actualmente, cuatro tipos de desencadenadores en SQL Server, y los primeros dos son los más comúnmente usados:
- Desencadenadores DDL (haciendo auditoría a CREATE, ALTER, DROP y muchos procedimientos almacenados definidos por el sistema que realizan operaciones DDL) son frecuentemente usados para tareas administrativas como regular y hacer auditoría a operaciones de base de datos
- Desencadenadores DML – nuestro foco estará en este tipo de desencadenadores, dado que proveen auditoría a cambios en datos. Este tipo de desencadenadores de auditoría actúan en sentencias DML (como INSERT, UPDATE y DELETE) al igual que en procedimientos almacenados que ejecutan operaciones similares a las DML
- Desencadenadores CLR son un tipo especial de desencadenadores de auditoría que permiten una técnica de desencadenamiento para ser usada directamente dentro de lenguajes .NET, y soportan SQL Server 2008 y superiores
- Desencadenadores de logon, se gatillan cuando un evento LOGON de SQL Server ocurre. Puede ser usado para rastrear autenticaciones exitosas y controlar y hacer auditoría a las sesiones del servidor.
Hay un amplio rango de casos de uso donde los desencadenadores pueden ser usados, y son especialmente útiles para aplicar políticas de negocios. Por ejemplo, algunos negocios pueden tener una regla que, cuando ellos obtienen un nuevo cliente, este cliente tiene que ser asignado a un consultor de la compañía. Cuando esta regla de negocios es traducida a las bases de datos, significa que cada vez que un nuevo cliente es añadido a la base de datos (usando la operación INSERT), un desencadenador puede ser usado para asignar el cliente al consultor, quien tiene el más bajo número de clientes o no está actualmente ocupado, o lo que sea que las políticas de negocio requieran.
En este artículo nos enfocaremos en sus capacidades de auditoría de datos. Lo que los desencadenadores de auditoría deben proveer básicamente mientras los datos de auditoría cambian son respuestas a las siguientes preguntas:
- 1. ¿Quién cambió los datos?
- 2. ¿Cuál era la fecha y hora cuando ocurrió el cambio?
- 3. ¿Qué software de cliente fue usado para acceder a los datos?
- 4. En el caso de que el requerimiento fuera una modificación de datos (la sentencia UPDATE), ¿cuál era el valor de los datos previamente y después del cambio?
Significa que cuando un desencadenador es gatillado, toda esta información tiene que ser recolectada desde una base de datos o una instancia de SQL Server y almacenada en un repositorio de auditoría.
Por ejemplo, un desencadenador gatillado cuando un registro es insertado en la tabla Currency.Sales es creado usando el siguiente SQL:
CREATE TRIGGER Sales.tr_i_AUDIT_Currency ON Sales.Currency FOR INSERT NOT FOR REPLICATION AS
Entonces, el desencadenador debe insertar el nombre de la computadora, el nombre de la persona que insertó el registro y el nombre de la aplicación usada para insertar el registro, donde INSERT fue ejecutado. Los desencadenadores apalancan funciones SQL Server integradas para obtener estos valores:
- HOST_NAME () retorna el nombre de la estación de trabajo
- APP_NAME() retorna un nombre de la aplicación en la sesión actual
- SUSER_NAME() – retorna el nombre de usuario
- GETDATE() – retorna la marca de tiempo del sistema para la base de datos a la que actualmente se está conectado
Estos valores son almacenados en una tabla predefinida usada como un repositorio de datos de auditoría:
INSERT INTO AdventureWorks2012.dbo.AUDIT_LOG_TRANSACTIONS ( TABLE_NAME, TABLE_SCHEMA, AUDIT_ACTION_ID, HOST_NAME, APP_NAME, MODIFIED_BY, MODIFIED_DATE, [DATABASE] ) VALUES( 'Currency', 'Sales', 2, -- ACTION ID For INSERT CASE WHEN LEN(HOST_NAME()) < 1 THEN ' ' ELSE HOST_NAME() END, CASE WHEN LEN(APP_NAME()) < 1 THEN ' ' ELSE APP_NAME() END, SUSER_SNAME(), GETDATE(), 'AdventureWorks2012' );
Organizando desencadenadores de auditoria
Como los desencadenadores de auditoría son básicamente parte de una instancia de SQL Server de producción, se gatillan y trabajan en tiempo real con cambios actuales en la base de datos. Esto, por la naturaleza de los desencadenadores, significa un mayor uso de recursos de SQL Server, lo que conlleva algo de planificación.
Los desencadenadores de auditoría deberían estar organizados en una manera eficiente para hacer auditoría a suficientes cambios y aun así no causar costos adicionales innecesarios de SQL Server. Esto puede ser logrado a través de una planificación cuidadosa y el análisis de acciones precias de los usuarios de la base de datos. Cualquier auditoría innecesaria debería ser evadida para simplificar la creación de desencadenadores y el proceso de mantenimiento así como para prevenir una utilización innecesaria de los recursos de SQL Server. Tristemente, SQL Server no provee ningún medio visual para ayudar con la organización y la vista general de los desencadenadores de auditoría. Eventualmente, Object Explorer de SQL Server Management Studio puede ser usado para una vista general de los desencadenadores en una forma de árbol.
Ventajas y desventajas de usar desencadenadores de auditoría
Hay muchas ventajas si los desencadenadores son usados para hacer auditoría a cambios en la base de datos:
- Usar desencadenadores de auditoría provee un desarrollo de aplicación más rápido porque éstos están escritos en una base de datos; por lo tanto, no hay necesidad de codificar acciones de desencadenadores por cada aplicación que los usa
- Una vez definidos, los desencadenadores serán reusados por cualquier aplicación que modifique los datos de la base de datos, por tanto reforzando las reglas de negocios definidas
- Si una política de datos de negocios cambia, es suficiente modificar el desencadenador de auditoría apropiado en lugar de cambiar todas las aplicaciones
- El desempeño del ambiente cliente/servidor no es afectado (no hay impacto en el tráfico de red) mientras la auditoría se realiza localmente en el servidor de base de datos
Por otro lado, un impacto significativo en el desempeño de SQL Server puede ocurrir, ya que los desencadenadores de auditoría pueden utilizar recursos igualmente como un usuario realizó una operación. Esto es la principal desventaja de los desencadenadores – el costo adicional para SQL Server. Por ejemplo, hacer auditoría de un cambio en un campo de registro adicionalmente realizará al menos un cambio más mientras se guardan los datos capturados.
Guardando la información capturada
Las regulaciones de cumplimiento requieren información de auditoría fiable y legible para humanos a disposición. La práctica más común es usar SQL Server como un almacenamiento. De esta manera, técnicas similares (por ejemplo, T-SQL) son usadas para auditar, almacenar y mantener información capturada. Por otra parte, la información auditada es grabada en un formulario de tablas/bases de datos SQL Server, así que los beneficios que SQL Server provee para cualquier información almacenada están disponibles para manejar los datos capturados también.
Por supuesto, en algunos casos particulares, otros formatos para guardar datos capturados son usados. Por ejemplo, si no se requiere consultas de datos adicionales, los formatos de archivo estándar son usados – como XLSX o PDF. Esto ciertamente puede proveer reportes de auditoría de acceso fácil, pero cualquier procesamiento adicional o consulta puede no ser posible.
Haciendo auditoría a desencadenadores y ApexSQL Trigger
ApexSQL Trigger es una herramienta de auditoría de SQL Server 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 afectados, cuándo fue hecho, así como la información acerca del login SQL, la aplicación y anfitrión usados para hacer el cambio. Almacena toda la información capturada en un repositorio central y la exporta en formatos amistosos para la impresión.
La tecnología que ApexSQL Trigger usa para realizar auditorías está basada en los desencadenadores. Estos son creados usando su interface visual, sin ninguna necesidad de conocimientos de T-SQL. Simplemente, después de conectarse a una base de datos, la herramienta lista todas las tablas de usuario existentes en las tablas de usuario de la base de datos con su estado de auditoría actual.
La cuadrícula claramente muestra qué tabla está bajo auditoría y para qué operación – INSERT, UPDATE, DELETE o todas ellas. Adicionalmente, la fila Filter puede ser usada para reducir la lista. La columna Prepared indica si hay una selección apropiada de columnas bajo auditoría para una tabla particular. Los detalles acerca de las columnas y su estado de auditoría son mostrados en el panel Fields:
Usando esta vista general, los desencadenadores de auditoría pueden ser fácilmente creados, modificados y removidos. Al finalizar la selección de tablas y sus filas para ser auditadas para una operación particular, simplemente use la opción Create en la barra de herramientas de la aplicación principal, y los desencadenadores de auditoría serán creados.
ApexSQL Trigger le permite revisar y editar el script de creación del desencadenador previo a su ejecución. Además de la posibilidad de modificar el script que genera los desencadenadores, es también posible personalizar la plantilla que ApexSQL Trigger usa para generarlos.
Se pueden conseguir mejoras adicionales usando Watches y Lookups.
Cuando son creados, los desencadenadores de auditoría pueden ser deshabilitados, habilitados o eliminados por selección o todos al mismo tiempo usando el módulo de administración de desencadenadores. Esto tampoco requiere scripting manual:
Cuando se trata de presentar datos capturados, el módulo de reportes integrado provee un filtrado detallado de los datos capturados mientras que, al mismo tiempo, puede exportar reportes como un archivo CSV, TXT, o PDF.
Finalmente, tratemos el tema de grabar datos capturados usando desencadenadores de auditoría. ApexSQL Trigger almacena información capturada en dos tablas adicionales:
- La tabla AUDIT_LOG_DATA es usada para almacenar información acerca de datos que fueron cambiados – el valor de los datos iniciales previo a un cambio y el valor de los datos luego de que el cambio ha ocurrido
- La tabla AUDIT_LOG_TRANSACTIONS almacena la información acerca de la transacción como el nombre de usuario, el nombre de la aplicación, el nombre y fecha de la tabla y el tiempo cuando la transacción fue consolidada.
Ambas tablas son creadas dentro de cada base de datos bajo auditoría. Esto puede ser personalizado de modo que todos los datos capturados de todas las bases de datos auditadas sean grabados dentro de una base de datos separada.
Así que, dado que la información capturada es grabada en una base de datos SQL Server, no sólo está disponible a través del módulo de reportes y sus exportaciones, sino que también es una fuente de consultas adicionales vía scripts SQL de los usuarios.
Traductor: Daniel Calbimonte
junio 4, 2015
