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.

auditar-datos-sql-server-implementacion

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:

 

select FirstName,MiddleName,LastName from Person.Person
where BusinessEntityID=1

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:

CREATE SCHEMA Audit
GO
CREATE TABLE Audit.[Person](
AuditDatetime datetime default getdate(),
OriginalLogin sysname default original_login(),
UserName sysname default user_name(),
[BusinessEntityID] [int] NULL,
[PersonType] [nchar](2) NULL,
[NameStyle] [dbo].[NameStyle] NULL,
[Title] [nvarchar](8) NULL,
[FirstName] [dbo].[Name] NULL,
[MiddleName] [dbo].[Name] NULL,
[LastName] [dbo].[Name] NULL,
[Suffix] [nvarchar](10) NULL,
[EmailPromotion] [int] NULL,
[AdditionalContactInfo] [xml] NULL,
[Demographics] [xml] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NULL,
[ModifiedDate] [datetime] NULL
)
GO

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:

CREATE PROCEDURE Person.GetPerson(@BusinessEntityID int)
AS
BEGIN
 
— Original query into #temp
select FirstName,MiddleName,LastName into #temp from Person.Person
where BusinessEntityID=@BusinessEntityID
 
— Insert into #temp and output columns
insert into Audit.Person (FirstName,MiddleName,LastName)
SELECT FirstName,MiddleName,LastName from #temp
 
— Return the data
SELECT * from #temp
 
END

Si lanzamos el procedimiento podemos ver como se inserta en la tabla de auditoría y se devuelven los resultados esperados:

exec Person.GetPerson 1
select * from Audit.Person
auditar-datos-sql-server-results
auditar-datos-sql-server-final-results

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:

CREATE PROCEDURE Person.GetPerson2(@BusinessEntityID int)
AS
BEGIN
 
— Original query into #temp
select FirstName,MiddleName,LastName into #temp from Person.Person
where BusinessEntityID=@BusinessEntityID
 
— Insert into #temp and output the required columns
insert into Audit.Person (FirstName,MiddleName,LastName)
OUTPUT inserted.FirstName,inserted.MiddleName,inserted.LastName
SELECT FirstName,MiddleName,LastName from #temp
 
END

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:

CREATE PROCEDURE Person.GetPerson3(@BusinessEntityID int)
AS
BEGIN
 
— Original query direct into audit table and output the required columns all in one
insert into Audit.Person (FirstName,MiddleName,LastName)
OUTPUT inserted.FirstName,inserted.MiddleName,inserted.LastName
select FirstName,MiddleName,LastName from Person.Person
where BusinessEntityID=@BusinessEntityID
 
END

También podríamos crear un GetAllPerson que devuelva todos los datos sin filtrar por identificador y queden a su vez auditados en bloque:

CREATE PROCEDURE Person.GetAllPerson
AS
BEGIN
 
— Original query into audit table and output columns all in one
insert into Audit.Person (FirstName,MiddleName,LastName)
OUTPUT inserted.FirstName,inserted.MiddleName,inserted.LastName
select FirstName,MiddleName,LastName from Person.Person
 
END
Auditando datos leídos en SQL Server
auditar-datos-sql-server-audit-date-time

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:

 

exec sp_configure ‘Ad Hoc Distributed Queries’,1
reconfigure

 

Una vez hecho esto, crearemos la TVF y la vista:

 
 
 
CREATE function Person.GetAllPerson2()
RETURNS @person TABLE (
FirstName Name,
MiddleName Name,
LastName Name
)
AS
BEGIN
 
INSERT INTO @person
SELECT * from OPENROWSET(‘MSDASQL’,‘DRIVER={SQL Server}; SERVER=localhost; Trusted_Connection=yes’, ‘EXEC AdventureWorks2017.Person.GetAllPerson’);
RETURN;
END;
 
CREATE view Person.VGetAllPerson
AS
SELECT * from OPENROWSET(‘MSDASQL’,‘DRIVER={SQL Server}; SERVER=localhost; Trusted_Connection=yes’, ‘EXEC AdventureWorks2017.Person.GetAllPerson’);

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:

 
 
 
truncate table Audit.Person
select count(*) from Person.GetAllPerson2()
select count(*) from Audit.Person
select count(*) from Person.Person
 

 

auditar-datos-sql-server-results- column

 

truncate table Audit.Person
select count(*) from Person.VGetAllPerson
select count(*) from Audit.Person
select count(*) from Person.Person

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:

 

 
 
 
truncate table Audit.Person
select count(*) from Person.GetAllPerson2() where FirstName=‘John’
select count(*) from Audit.Person
select count(*) from Person.Person where FirstName=‘John’
 
truncate table Audit.Person
select count(*) from Person.VGetAllPerson where FirstName=‘John’
select count(*) from Audit.Person
select count(*) from Person.Person where FirstName=‘John’
 
Auditando datos leídos en SQL Server

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/)

auditar-datos-sql-server-results-application

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/

auditar-datos-sql-server-results-audit-trails

Realizaremos una instalación básica de la herramienta, en este caso sobre Ubuntu 20.04:

Realizaremos uauditar-datos-sql-server-results-ubuntuna 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:

sudo bash -c ‘bash <(curl -s http://s3.heimdalldata.com/hdinstall.sh)’

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:

ufw allow 8087
ufw allow 1433

Una vez conectemos el login por defecto es “admin” y el password “heimdall” (recomendable cambiarlo, obviamente):

Auditando datos leídos en SQL Server

A continuación crearemos una base de datos virtual, que será el proxy a nuestro SQL Server:

auditar-datos-sql-server-status

Configuraremos primero el data source al que queremos acceder:

Auditando datos leídos en SQL Server

Y utilizaremos los valores por defecto para el resto de opciones:

Auditando datos leídos en SQL Server

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:

Auditando datos leídos en SQL Server

Sin embargo con la configuración por defecto no tenemos disponible la monitorización PCI/HIPPA por lo que necesitamos activar la caché necesariamente:

auditar-datos-sql-server-data-source-caching

Desde el apartado de logs podemos ver las consultas realizadas:

auditar-datos-sql-server-data-source-log

Y si obtenemos los logs completos podemos encontrar los valores que se devolvieron al cliente:

auditar-datos-sql-server-data-source-log-complete
Auditando datos leídos en SQL Server

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:

auditar-datos-sql-server-data-cliente-Terri
auditar-datos-sql-server-data-BusinessEntityID
auditar-datos-sql-server-data-cliente-roberto
auditar-datos-sql-server-data-BusinessEntityID2

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
1 Shares:
Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

You May Also Like