SQL Server

Hoy vamos a hablar de una de las características más interesantes y novedosas de SQL Server y Azure SQL: el libro de contabilidad o Ledger. Es una forma de garantizar la integridad, la inmutabilidad y el orden cronológico de los datos almacenados en una base de datos relacional, utilizando conceptos similares a los de la tecnología blockchain.

¿Qué es el libro de contabilidad? 

El libro de contabilidad permite crear tablas especiales que registran las operaciones realizadas sobre los datos, incluyendo información sobre el usuario, la fecha y hora, el tipo de operación y un hash criptográfico que enlaza cada registro con el anterior, formando una cadena inalterable. Además, cada registro del libro de contabilidad tiene una firma digital que se genera con una clave asimétrica, lo que impide que se pueda modificar o eliminar sin dejar rastro. De esta forma, se puede auditar y verificar la historia completa de los datos, detectar cualquier intento de manipulación o fraude, y cumplir con las normativas de seguridad y privacidad más exigentes.

Es una característica disponible tanto en SQL Server como en Azure SQL, aunque con algunas diferencias. En SQL Server, requiere la edición Enterprise o Developer y la versión 2019 o superior. En Azure SQL, está disponible en todas las ediciones y versiones, pero solo para las bases de datos individuales, no para las bases de datos elásticas o los grupos de servidores administrados. Además, en Azure SQL se ofrece un servicio adicional llamado Azure SQL Ledger, que proporciona una capa adicional de seguridad y confianza al almacenar los hashes del libro de contabilidad en un servicio externo e independiente, basado en la tecnología blockchain.

¿Para qué sirve el libro de contabilidad? 

El libro de contabilidad es una solución ideal para escenarios en los que se requiere un alto nivel de confianza y transparencia en los datos, como por ejemplo:

– Contabilidad financiera: se puede llevar un registro fiable y verificable de todas las transacciones realizadas por una empresa o una entidad bancaria, evitando errores, duplicidades o fraudes.
– Gestión de inventarios: se puede controlar el movimiento y el estado de los productos o activos a lo largo de la cadena de suministro, desde su origen hasta su destino final, asegurando su trazabilidad y calidad.
– Certificación digital: se puede emitir y validar certificados digitales que acrediten la identidad, la propiedad o la autorización de una persona o una entidad, utilizando ledger como fuente de verdad única e inmutable.
– Votación electrónica: se puede garantizar la integridad y el secreto del voto, así como el recuento y la publicación de los resultados, utilizando el ledger como sistema de registro seguro y transparente.

¿Cómo se usa el libro de contabilidad? 

Lo primero que tenemos que hacer es habilitarlo para la tabla o la base de datos que queremos proteger. Esto se puede hacer mediante el asistente gráfico de SQL Server Management Studio o mediante instrucciones T-SQL. Por ejemplo, para habilitarlo para una tabla llamada Clientes, podemos usar el siguiente código:

ALTER TABLE Clientes
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Clientes_Ledger));

Con esto, se crea una tabla de libro de contabilidad llamada Clientes_Ledger, que tendrá la misma estructura que la tabla Clientes, más dos columnas adicionales: SysStartTime y SysEndTime, que indican el inicio y el fin de la vigencia de cada registro. También se crea un índice clusterizado sobre estas dos columnas para optimizar las consultas. Una vez habilitado, cada vez que insertemos, actualicemos o eliminemos un registro en la tabla Clientes, se generará automáticamente un registro correspondiente en la tabla Clientes_Ledger, con la información y la firma digital de la operación. Para consultar el libro de contabilidad, podemos usar la cláusula FOR SYSTEM_TIME en las instrucciones SELECT. Por ejemplo, para ver el historial completo de la tabla Clientes, podemos usar el siguiente código:

SELECT * FROM Clientes
    FOR SYSTEM_TIME ALL;

Con esto, obtenemos todos los registros que han existido en la tabla Clientes desde que se habilitó el libro de contabilidad, incluyendo los actuales y los borrados. También podemos filtrar por un rango temporal específico, usando las opciones AS OF, FROM TO o BETWEEN AND. Por ejemplo, para ver los registros que existían en la tabla Clientes el 1 de enero de 2024 a las 10:00 a.m., podemos usar el siguiente código:

SELECT * FROM Clientes
    FOR SYSTEM_TIME AS OF '2024-01-01 10:00:00';

Libro de contabilidad a nivel base de datos

También se puede habilitar a nivel de base de datos, lo que implica que se creará una tabla de libro de contabilidad para cada tabla existente y futura en la base de datos. Una vez habilitado para una base de datos habilitado no se podrán crear en ella tablas con el libro de contabilidad deshabilitado en esa base de datos. Para activarlo, podemos usar el siguiente código:

ALTER DATABASE MiBaseDeDatos
    SET LEDGER ON;

Para deshabilitar el libro de contabilidad, tanto a nivel de tabla como de base de datos, podemos usar el mismo código pero cambiando ON por OFF. Sin embargo, hay que tener en cuenta que al deshabilitarlo se pierde todo el historial acumulado hasta ese momento, por lo que es recomendable hacer una copia de seguridad antes. 

También se puede utilizar la función sys.fn_ledger_chain_integrity para comprobar la integridad del libro de contabilidad, comparando los valores almacenados en la columna sys_lsn con los valores calculados a partir del contenido y el hash de los registros. Esta función devuelve un valor booleano que indica si está intacto o si ha sido alterado.

¿Qué ventajas tiene? 

El libro de contabilidad tiene varias ventajas frente a otros métodos tradicionales de auditoría o registro de cambios, como por ejemplo:

– Fácil de implementar y administrar, ya que no requiere crear triggers ni tablas auxiliares.
– Más seguro y confiable, ya que usa criptografía y firmas digitales para garantizar la autenticidad e integridad de los datos.
– Es más eficiente y escalable, ya que usa técnicas de compresión y particionamiento para optimizar el almacenamiento y el rendimiento.
– Compatible y portable, ya que se basa en estándares abiertos y se puede usar tanto en SQL Server como en Azure SQL.

¿Qué inconvenientes tiene el libro de contabilidad?

El libro de contabilidad también tiene algunas limitaciones que hay que tener en cuenta, como por ejemplo:

– No se puede modificar o eliminar el historial del libro de contabilidad, salvo que se deshabilite la funcionalidad por completo.
– Es incompatible con tablas que tengan FILESTREAM o columnas de tipo XML, CLR, HIERARCHYID o GEOGRAPHY.
– No se puede usar el libro de contabilidad con tablas que tengan índices espaciales, columnstore o full-text.
– Es incompatible con tablas que tengan restricciones de tipo CHECK, FOREIGN KEY o UNIQUE.
– No se puede usar el libro de contabilidad con tablas que tengan columnas calculadas o generadas.

Conclusión

El libro de contabilidad es una característica muy potente y útil que permite aprovechar las ventajas de la tecnología blockchain en el ámbito de las bases de datos relacionales, ofreciendo un nivel de integridad, inmutabilidad y transparencia de los datos sin precedentes. Espero que os haya gustado este artículo y que os animéis a probar el libro de contabilidad en vuestros proyectos. Si os han quedado dudas o queréis aportar algo podéis hacerlo en los comentarios, en Twitter o en mi mail.

Estos últimos días hemos estado viendo cómo cifrar y enmascarar datos confidenciales en SQL Server y cómo clasificar los datos en función de su tipología y confidencialidad. Hemos tenido mucha teoría sobre el tema así que hoy vamos con unas aplicaciones prácticas algo distintas que no se suelen ver pero que me parecen muy interesantes. En concreto vamos a tratar de combinar los conceptos de DDM con el resultado de nuestra clasificación de datos.

Clasificación de los datos.

Data Classification es una herramienta que nos permite etiquetar los datos sensibles o confidenciales de nuestras tablas y columnas, asignándoles un nivel de sensibilidad y un tipo de información. De esta forma, podemos identificar fácilmente qué datos requieren una mayor protección y cómo gestionarlos adecuadamente.

Como ejemplo vamos a usar la base de datos AdventureWorks2022, El asistente de clasificación de datos nos ha revelado que la base de datos tiene 32 columnas confidenciales según el RGPD así que las clasificamos como tal.

Enmascarando datos confidenciales

Ya tenemos los datos clasificados, pero eso no es suficiente. También tenemos que asegurarnos de que solo las personas autorizadas puedan acceder a ello. Para ello, podemos usar dos funcionalidades muy potentes de SQL Server: Always Encrypted y el enmascaramiento dinámico de datos (DDM). DDM es una función de SQL Server que te permite ocultar o enmascarar los datos sensibles cuando los consultas, sin modificar los datos reales en la base de datos. De esta forma, puedes limitar la exposición de tus datos a usuarios no autorizados o aplicaciones externas. Vamos a usar esta para el ejemplo.

Localizando las columnas afectadas

Nuestra idea es construir una consulta SQL que genere el enmascaramiento DDM para las consultas confidenciales. Lo primero que necesitaremos será localizar las columnas que tenemos que enmascarar con una consulta SQL. Por suerte para nosotros esta consulta está directamente disponible en la documentación

SELECT 
    schema_name(O.schema_id) AS schema_name,
    O.NAME AS table_name,
    C.NAME AS column_name,
    information_type,
	label,
	rank,
	rank_desc
FROM sys.sensitivity_classifications sc
    JOIN sys.objects O
    ON  sc.major_id = O.object_id
	JOIN sys.columns C 
    ON  sc.major_id = C.object_id  AND sc.minor_id = C.column_id

Enmascarando las consultas con DDM

Con la consulta anterior, ya podremos crear una consulta dinámica para aplicar reglas de enmascaramiento a todas las columnas en función de su tipo:

WITH ConfidentialData AS (
SELECT 
    schema_name(O.schema_id) AS schema_name,
    O.NAME AS table_name,
    C.NAME AS column_name,
    information_type,
	label,
	rank,
	rank_desc
FROM sys.sensitivity_classifications sc
	JOIN sys.objects O ON  sc.major_id = O.object_id
	JOIN sys.columns C ON  sc.major_id = C.object_id  AND sc.minor_id = C.column_id)

SELECT 
	'ALTER TABLE ' + QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) + ' ALTER COLUMN ' + QUOTENAME(column_name) + ' ADD MASKED WITH (FUNCTION = ' 
	+ CASE
		WHEN information_type = 'Name'			THEN '''partial(1,"xxxx",0)'''
		WHEN information_type = 'Contact Info'	THEN '''default()'''
		WHEN information_type = 'Credentials'	THEN '''default()'''
		WHEN information_type = 'Credit Card'	THEN '''default()'''
		WHEN information_type = 'Date Of Birth'	THEN '''default()'''
		WHEN information_type = 'Financial'		THEN '''default()'''
		WHEN information_type = 'National ID'	THEN '''default()'''
	END + ');' AS DDL,*
FROM ConfidentialData

Resultado

Ahora toda persona que carezca del permiso unmask no podrá ver los datos confidenciales y verá las máscaras que hemos asignado. Os dejo un ejemplo:

Auditar lecturas de datos confidenciales

Es probable que, por temas regulatorios, se nos pida llevar un control sobre los accesos a datos confidenciales. Para ello podemos combinar la auditoría de SQL Server con las columnas que hemos clasificado con el asistente de clasificación de datos. 

La auditoría nos permite crear un registro detallado de todas las operaciones que se realizan sobre los datos clasificados, incluyendo el usuario, la aplicación, la fecha, la hora, el tipo de acción y los datos afectados. Así, podemos monitorizar el uso de los datos sensibles y detectar posibles amenazas o fugas de información.

Para crear una auditoría de lecturas sobre los datos clasificados, tenemos que seguir estos pasos:

1. Crear un grupo de auditoría en el nivel del servidor, especificando el destino donde se almacenarán los registros (un archivo, una tabla o el registro de eventos de Windows).
2. Crear una especificación de auditoría en el nivel de la base de datos, asociándola al grupo de auditoría creado y seleccionando las tablas o columnas que queremos auditar (por ejemplo, las que tienen datos clasificados).
3. Habilitar el grupo de auditoría y las especificaciones de auditoría creadas.

Como habrás podido notar los pasos son los mismos que para una auditoría normal, no tienes que hacer nada más. SQL Server reconocerá las columnas clasificadas como confidenciales y mostrará en la auditoría el tipo de información a la que se ha accedido de esta manera:

Conclusión

Hemos visto un par de formas de ir más allá en la protección de nuestros datos confidenciales pero no son las únicas. Podemos combinar el uso de las vistas del catálogo de datos clasificados con nuestras propias consultas para hacer todo lo que nos podamos imaginar. Podríamos crear una consulta que cree automáticamente Always Encrypted para una mayor seguridad por ejemplo. A partir de aquí el límite lo ponéis vosotros. Como siempre os digo para cualquier duda os dejo a vuestra disposición los comentarios, mi Twitter o mi mail.

En el post de ayer vimos cómo cifrar o enmascarar los datos en SQL Server pero, ¿qué datos debemos proteger? Eso es justo lo que vamos a ver hoy, en este artículo te voy a explicar cómo funciona Data Classification, cómo puedes usarla para clasificar tus datos y cómo puedes detectar los datos clasificados en tu base de datos. El objetivo es que al final de este post seamos capaces de aumentar el nivel de protección de nuestros datos. ¡Vamos allá!

¿Qué es Data Classification?

Data Classification es una función de SQL Server que te permite asignar etiquetas de sensibilidad y confidencialidad a tus columnas de datos. Estas etiquetas te ayudan a identificar qué datos son más críticos y qué medidas de seguridad debes aplicar para protegerlos. Por ejemplo, puedes etiquetar una columna que contiene números de tarjeta de crédito como «Alta sensibilidad» y «Confidencial», o una columna que contiene nombres de clientes como «Baja sensibilidad» y «Público».

¿Para qué sirve Data Classification?

Data Classification tiene varios beneficios para tu base de datos, principalmente nos ayuda a mejorar la seguridad. Podemos aplicar políticas de acceso, encriptación, auditoría y retención basadas en las etiquetas de tus datos. Por otro lado, nos va a permitir cumplir con las normativas de protección de datos, como el RGPD, que exigen que clasifiques tus datos según su sensibilidad y confidencialidad, y que informes a tus usuarios sobre el uso que haces de sus datos. Por último, facilita el análisis y la visualización de los datos, ya que podremos filtrar, agrupar y ordenar nuestros datos según sus etiquetas.

¿Cómo clasificar tus datos con Data Classification?

Para clasificar tus datos con Data Classification, tienes que seguir estos pasos:

1. Abre el Explorador de objetos de SQL Server Management Studio (SSMS) y conecta con tu servidor de SQL Server.
2. Expande la carpeta Bases de datos y selecciona la base de datos que quieres clasificar.
3. Haz clic derecho sobre la base de datos y selecciona Tareas > Clasificar datos.
4. Se abrirá el asistente de Data Classification, que te guiará por el proceso de clasificación.
5. En la primera página del asistente, haz clic en Siguiente.
6. En la segunda página del asistente, verás una lista de las tablas y columnas de tu base de datos, con las recomendaciones automáticas de clasificación basadas en los tipos y nombres de los datos. Puedes aceptar las recomendaciones o modificarlas según tus criterios. Para modificar una recomendación, haz clic sobre la fila correspondiente y selecciona la sensibilidad y la confidencialidad que quieras asignar. También puedes añadir o eliminar columnas del proceso de clasificación usando los botones Agregar o Eliminar.
7. Cuando hayas terminado de clasificar tus columnas, haz clic en Siguiente.
8. En la tercera página del asistente, verás un resumen de la clasificación que has realizado, con el número total de columnas clasificadas y el nivel medio de sensibilidad y confidencialidad. También podrás generar un informe en formato PDF o CSV con los detalles de la clasificación. Haz clic en Finalizar para aplicar la clasificación a tu base de datos.

¿Cómo detectar los datos clasificados en tu base de datos?

Una vez que hayamos clasificado nuestros datos con Data Classification, tendremos varias opciones para detectarlos. 

Asistente de Data Classification

Si abres el asistente de Data Classification, podrás ver las columnas que ya has clasificado y modificarlas si lo deseas. También nos ofrece la posibilidad de generar un informe.

Vistas del sistema

SQL Server almacena la información sobre la clasificación en el catálogo del sistema, en las vista sys.sensitivity_classifications. Podemos consultar esta vista usando consultas para obtener los detalles sobre la clasificación.

Propiedades de las columnas

Si seleccionas una columna clasificada en el Explorador de objetos de SSMS y abres el panel Propiedades, podrás ver la sensibilidad y la confidencialidad que le has asignado en la pestaña de propiedades extendidas.

Conclusión

En este artículo te he mostrado cómo puedes clasificar, detectar e integrar tus datos de SQL Server usando la función Data Classification. Esta función nos permite asignar etiquetas de sensibilidad y confidencialidad a nuestras columnas de datos. Espero que te haya resultado interesante, en el próximo artículo vamos a ver los siguientes pasos una vez que tenemos clasificados los datos. Si tienes alguna duda o comentario, puedes dejarlo abajo, en Twitter o en mi mail. ¡Hasta la próxima!

El otro día, hablando de preguntas en entrevistas de trabajo para DBA, comentamos el tema del cifrado de datos en SQL Server. Como es un tema muy interesante y que a veces genera dudas vamos a verlo en profundidad. 

Antes de empezar, una aclaración. Mi profesor de ciberseguridad siempre decía que se dice CIFRAR, la palabra ENCRIPTAR nunca ha existido en el castellano. La gente usa encriptar por una mala traducción del inglés encrypt. Aunque lo cierto es que el uso de encriptar está tan extendido que en 2022 la RAE lo incluyó en las últimas versiones del diccionario (junto a almóndiga y cocreta), la forma correcta de decirlo sigue siendo CIFRAR. Al fin y al cabo no vamos a meter nada en ninguna cripta, ¿verdad?.

¿Qué es el cifrado y que tipos existen?

El cifrado es un proceso que transforma la información en un formato ilegible para quienes no tienen la clave para descifrarla. De esta forma, podemos evitar que personas no autorizadas accedan a nuestros datos sensibles o confidenciales.

Existen dos escenarios principales donde el cifrado es importante: en tránsito y en espera. El cifrado en tránsito es la protección de los datos mientras se transmiten por la red, por ejemplo, entre el cliente y el servidor, o entre dos servidores. Sin embargo, cuando decimos cifrado en espera nos referimos a la protección de los datos mientras se almacenan, por ejemplo, en un disco duro o en la nube.

El cifrado en SQL Server

SQL Server nos ofrece varias opciones para implementar el cifrado en ambos escenarios. Veamos algunas de ellas:

TLS para cifrado en tránsito

Para el cifrado en tránsito, podemos usar el protocolo TLS (Transport Layer Security), que crea un canal seguro entre dos partes que se comunican por la red. SQL Server soporta TLS desde la versión 2005 y lo podemos habilitar mediante la configuración del servidor y del cliente. También podemos usar certificados digitales para autenticar las partes y garantizar la integridad de los datos. Los certificados son documentos electrónicos que contienen información sobre la identidad y la clave pública de una entidad. SQL Server nos permite crear, importar y administrar certificados mediante el uso de cláusulas Transact-SQL o mediante el uso de herramientas gráficas como el Administrador de configuración de SQL Server o el Explorador de objetos de SQL Server Management Studio.

TDE para cifrado en espera de bases de datos 

Para el cifrado en espera, podemos usar el TDE (Transparent Data Encryption), que cifra los archivos de datos, los logs de registro y las copias de seguridad de las bases de datos. SQL Server soporta TDE desde la versión 2008 y lo podemos habilitar mediante la creación de una clave de cifrado de base de datos y una clave maestra de base de datos. El TDE no afecta al rendimiento ni al diseño de las aplicaciones, ya que el cifrado y el descifrado se realizan de forma transparente.

TDE se puede configurar con EKM (Extensible Key Management), que nos permite usar proveedores externos para almacenar y administrar las claves de cifrado. El EKM nos ofrece mayor seguridad y flexibilidad para gestionar las claves.

Always Encrypted para cifrado en espera de columnas

Otra opción para el cifrado en espera es Always Encrypted, que nos permite cifrar los datos sensibles dentro de las columnas de las tablas, tanto en el servidor como en el cliente. SQL Server soporta Always Encrypted desde la versión 2016 y lo podemos habilitar mediante la configuración del driver del cliente y la creación de una clave maestra columnar y una clave columnar. El Always Encrypted nos permite separar las claves de los datos, lo que implica que ni siquiera nosotros como DBAs podremos ver los datos sin autorización.

Enmascaramiento de datos

Además del cifrado, otra forma de proteger nuestros datos es el enmascaramiento. SQL Server ofrece,desde la versión 2016, una opción llamada DDM (Dynamic Data Masking) que nos permite ocultar o modificar los datos sensibles cuando se muestran a los usuarios. Esta opción nunca va a alterar los datos reales almacenados en la base de datos. El DDM nos permite limitar la exposición de los datos según si el usuario tiene o no un permiso. En concreto los usuarios sysadmin o con el permiso UNMASK leerán los datos tal como están guardados en la base de datos y el resto verá los datos enmascarados

Es importante destacar que el DDM no es lo mismo que el cifrado, ya que el DDM no cambia los datos reales, sino solo su presentación. Por lo tanto, el DDM no es suficiente para garantizar la seguridad de los datos, sino que debe usarse junto con otras medidas como el cifrado o el control de acceso.

¿Cómo funciona DDM?

DDM funciona mediante la definición de máscaras o reglas que se aplican a las columnas que contienen los datos que queremos proteger. Estas máscaras pueden ser de diferentes tipos, como por ejemplo:

– Máscara parcial: oculta parte del valor original, dejando visible solo algunos caracteres. Por ejemplo, podemos mostrar solo los últimos cuatro dígitos de un número de teléfono o de una tarjeta de crédito.
– Máscara por defecto: reemplaza el valor original por un valor fijo o predeterminado. Por ejemplo, podemos mostrar un asterisco (*) en lugar del nombre completo de una persona.
– Máscara aleatoria: genera un valor aleatorio dentro de un rango especificado. Por ejemplo, podemos mostrar una fecha aleatoria entre el año 2000 y el 2020 en lugar de la fecha real de nacimiento de una persona.
– Máscara personalizada: permite definir una expresión o una función que se encarga de generar el valor enmascarado. Por ejemplo, podemos mostrar el nombre de una ciudad al azar en lugar del nombre real de la ciudad donde vive una persona.

Para implementar el DDM en SQL Server, solo tenemos que usar la instrucción ALTER TABLE y especificar el tipo de máscara y el valor que queremos aplicar a cada columna. Por ejemplo, si tenemos una tabla llamada Clientes con las columnas Nombre, Apellido, Teléfono y Ciudad, podemos definir las siguientes máscaras:

ALTER TABLE Clientes
ALTER COLUMN Nombre ADD MASKED WITH (FUNCTION = 'default()')
ALTER COLUMN Apellido ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)')
ALTER COLUMN Teléfono ADD MASKED WITH (FUNCTION = 'partial(0,"XXX",4)')
ALTER COLUMN Ciudad ADD MASKED WITH (FUNCTION = 'random(1,10)')

De esta forma, cuando consultemos la tabla Clientes, veremos algo así:

Conclusión

Hemos visto algunos ejemplos de los tipos de cifrado y protección que podemos usar con SQL Server. Aunque existen otras opciones como el cifrado estático de columnas son opciones antiguas que prácticamente no se usan. Como habéis podido ver, el cifrado y el DDM son herramientas muy útiles y poderosas para proteger nuestros datos y cumplir con las normativas de privacidad y seguridad. Cada opción tiene sus ventajas y desventajas, dependiendo del nivel de seguridad y rendimiento que se busque. Espero os haya gustado este post y que lo podáis poner en práctica. Como siempre dejo a vuestra disposición los comentarios, mi cuenta de Twitter y mi mail para cualquier duda.