En más de una ocasión se nos ha preguntado cómo podemos crear una auditoría sobre los datos leídos en SQL Server. Esta necesidad surge especialmente cuando tenemos un entorno con unos requisitos de protección de datos elevada, como puede ser un centro hospitalario. Claramente es una necesidad que existe y actualmente SQL Server no ofrece una forma de auditoría adecuada para ello como sí ofrece para otras operaciones (INSERT, UPDATE, DELETE).
Que no esté implementada esta funcionalidad no significa que esta necesidad sea desconocida por Microsoft. En 2013 Microsoft Research presentó un whitepaper de investigación realizado sobre una implementación real de esta característica sobre SQL Server basada en triggers de SELECT, con benchmarks, etc. Desgraciadamente esa funcionalidad parece que no se ha implementado aún en el producto.
Si consideráis que sería útil tener esta funcionalidad en SQL Server podéis votarla aquí: https://feedback.azure.com/forums/908035-sql-server/suggestions/41742163-select-triggers-audit-sensitive-data-reads
Si queréis leer el whitepaper al que hago referencia, que es bastante interesante como la mayoría de Microsoft Research, podéis encontrarlo aquí: https://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.396.9974&rep=rep1&type=pdf o en este enlace local: select_triggers_whitepaper
A día de hoy, sin esta funcionalidad ni otra similar disponible, la forma de implementar esta auditoría más recomendada por nuestra parte pasa por el uso de procedimientos almacenados para el acceso a esta información delicada. Es decir, debemos añadir manualmente un control de auditoría que nos almacene los registros a devolver al cliente antes de devolverlos al cliente.
Por ejemplo, imaginemos que queremos auditar los accesos a la tabla Person.Person de Adventureworks2017. En ese caso lo que normalmente propondríamos sería algún tipo de procedimiento Person.GetPerson (@BusinessEntityID int) que devolviera la información requerida previo registro en una tabla de auditoría de dicho acceso. Es decir, encapsularíamos una consulta similar a la siguiente, parametrizándola y añadiendo el código de auditoría:
Vamos a comenzar creando una tabla de auditoría dentro de un esquema llamado Audit que contenga todas las columnas de la tabla más algunas extras que queremos añadir a la auditoría:
Una vez tenemos la tabla de auditoría pasaríamos a crear un procedimiento que nos permita obtener los datos que necesitamos de Person.Person. Imaginemos que solo necesitamos tres de las columnas de la tabla en este caso y que vamos a buscar por su BusinesEntityID directamente. En ese caso podríamos tener un procedimiento como el siguiente, que se apoya en una tabla temporal para almacenar las filas necesarias:
Si lanzamos el procedimiento podemos ver como se inserta en la tabla de auditoría y se devuelven los resultados esperados:
Una posible optimización sobre este proceso sería utilizar la cláusula output para devolver los resultados a la vez que insertamos en la tabla de auditoría, con lo que pasaríamos de tres statements a solamente dos:
Es más, podríamos hacer una tercera versión que en un único statement se guarde en la auditoría y a su vez se devuelvan los datos necesarios, un todo en uno, evitando el uso de la tabla temporal:
También podríamos crear un GetAllPerson que devuelva todos los datos sin filtrar por identificador y queden a su vez auditados en bloque:
El principal problema de esta aproximación, y la razón por la que lo ideal sería que SQL Server soportara este tipo de auditorías, es que esta forma de trabajo no es compatible con muchos ORM que necesitan tener un acceso directo al modelo.
Quizás pensemos que podemos forzar el uso de algún tipo de vistas, pero eso no permitirá una auditoría correcta. Es decir, podemos usar un TVF (Table Valued Function) como interfaz intermedio entre la vista y la tabla haciendo algún tipo de “hacking” para ejecutar procedimientos desde funciones (es algo que el motor intenta impedir) pero realmente no es la forma adecuada de hacer las cosas y no es recomendable su uso.
Como se entenderá mejor con un ejemplo a lo que me estoy refiriendo, voy a crear una TVF y una vista para la tabla Person que muestre esta mala práctica. Para ello primero, dependiendo de la configuración y versión de la instancia, deberemos habilitar las consultas distribuidas adhoc:
Una vez hecho esto, crearemos la TVF y la vista:
Ahora vamos a truncar la tabla de auditoría y a comprobar cómo llamando a la TVF o a la vista se registran en la tabla las mismas filas que se consultan si no aplicamos ningún filtro:
Sin embargo si aplicamos algún tipo de filtrado sobre la vista o la TVF, el funcionamiento ya no es el correcto en ninguno de los dos casos, ya que auditaremos como si hubiésemos accedido a todos los registros cuando realmente solamente hemos accedido a 58 de ellos. Además el rendimiento será mucho peor ya que cada vez accedemos a los casi 20 mil registros existentes:
Otra posible aproximación sería intentar crear una auditoría del propio protocolo TDS. Esta aproximación es bastante más “hardcore” y casi seguro que no podría llegar a ningún entorno productivo real. Es decir, podríamos capturar con WireShark/Network Monitor u otra herramienta similar los paquetes de la comunicación con SQL Server. Si no están encriptados, podríamos obtener qué datos teníamos “de vuelta” en los paquetes TDS de una petición concreta. El uso de estas herramientas no suele ser aceptable en un entorno de producción y además en muchos casos la encriptación del canal de comunicaciones es necesaria.
Existen soluciones de terceros que implementan “proxies” o “cachés” para SQL Server. Estas soluciones se instalan y simulan una conexión contra SQL Server u otros motores añadiendo funcionalidades extras, como redirección de conexiones, caché, aplicación de expresiones regulares sobre las consultas, etc. Estas sí podrían funcionar con SSL, aunque realmente lo que estarían implementando sería una especie de SSL man-in-the-middle attack y que solo funcionará si confiamos ciegamente en el certificado del servidor, si una entidad certificadora falsa ha sido introducida en el almacen de certificados de confianza del cliente, etc.
Vamos a mostrar un ejemplo de estas herramientas instalando Heimdall Data (https://www.heimdalldata.com/overview/)
No vamos a entrar en muchas de las funcionalidades interesantes que provee, simplemente nos centraremos en una funcionalidad llamada Audit Trails: https://blog.heimdalldata.com/2017/12/11/heimdall-data-privacy-compliance/
Realizaremos una instalación básica de la herramienta, en este caso sobre Ubuntu 20.04:
Lanzaremos el comando siguiente y automáticamente se detectará la versión de nuestra distribución y se descargarán los paquetes correspondientes y sus dependencias:
Una vez instalado por defecto el portal de administración escuchará en el puerto 8087 por lo que es posible que tengamos que habilitar dicho puerto en el firewall. También habilitaremos el 1433 si queremos usarlo como puerto proxy para SQL Server:
Una vez conectemos el login por defecto es “admin” y el password “heimdall” (recomendable cambiarlo, obviamente):
A continuación crearemos una base de datos virtual, que será el proxy a nuestro SQL Server:
Configuraremos primero el data source al que queremos acceder:
Y utilizaremos los valores por defecto para el resto de opciones:
Una vez finalizada la configuración conectaremos a la IP del servidor Ubuntu/Heimdall (192.168.1.6) y al puerto 1433 y nos redirigirá la conexión a la 192.168.1.12 al mismo puerto 1433. Una vez conectados podemos lanzar la consulta sobre AdventureWorks2017 y vemos que realmente funciona sin problemas:
Sin embargo con la configuración por defecto no tenemos disponible la monitorización PCI/HIPPA por lo que necesitamos activar la caché necesariamente:
Desde el apartado de logs podemos ver las consultas realizadas:
Y si obtenemos los logs completos podemos encontrar los valores que se devolvieron al cliente:
Lanzaremos algunas pruebas más con los BusinesEntityID 2 y 3 y comprobamos que también tenemos registrados los valores leídos en el log:
Por tanto este tipo de herramientas que actúan de proxy entre el servidor SQL Server y el cliente si son capaces de almacenar los valores devueltos al cliente y por tanto podrían satisfacer estas necesidades de auditoría de lectura.
En conclusión, la auditoría de las operaciones de lectura es compleja debido a que no tenemos “ayuda” por parte de SQL Server para realizar dicha labor. Debido a ello ahora mismo la opción con menor impacto de rendimiento pasa por forzar los accesos a la información a auditar vía procedimiento almacenado mientras que las opciones más transparentes para las aplicaciones (sin cambio de código) pasan por el uso de software de terceros, que intercepte las operaciones y las almacenen en logs de auditoría. Esperemos que en un futuro, bien sea mediante triggers SELECT o bien mediante algún tipo de auditoría de resultsets, sea posible añadir este tipo de auditorías de lectura de forma nativa en SQL Server.
Máster en Business Intelligence & Advanced Analytics
Conviértete en un año en un experto en BI con un seguimiento personalizado de los mentores y MVPs de SolidQ y con el nuevo temario del máster en Business Intelligence & Advanced Analytics, introduciendo Modern Data Warehouse, analítica y visualización avanzada.
Más Información
