Teoría BBDD

Jul

2024

Auditoría en SQL Server: ¿Cómo configurarlas?

En nuestro anterior post hablamos sobre SQL Server Profiler y sus trazas y comentábamos que uno de sus usos puede ser el de auditoría ya que registra todos los eventos sobre nuestros servidores SQL Server. Sin embargo su elevado consumo de recursos no lo hace la solución más ideal para esta función. Y es que, SQL Server implementa una auditoría nativa mucho más potente con menor coste de recursos que la hacen la herramienta ideal. Configurar auditorías en SQL Server no solo nos va a ayudar a supervisar la actividad y los cambios, sino que también es un proceso clave en nuestra estrategia de protección de datos y cumplimiento normativo. En este artículo, voy a tratar de explicarte cómo puedes configurar eficazmente estas auditorías, optimizando cada aspecto para obtener el máximo beneficio.

¿Qué es la Auditoría de SQL Server?

La auditoría de SQL Server es un mecanismo que permite registrar y rastrear actividades y eventos dentro de nuestras instancias de SQL Server. La auditoría puede ser configurada para capturar una variedad de eventos, desde inicios de sesión hasta cambios en la configuración del servidor. Con estos registros, podemos realizar un seguimiento detallado de quién hizo qué y cuándo, lo que es vital para la seguridad y el cumplimiento normativo.

Tipos de Auditoría de SQL Server

SQL Server proporciona dos enfoques principales para la auditoría: la auditoría de instancia y la auditoría de base de datos. Cada uno tiene sus características específicas y se aplica en diferentes contextos según los requisitos de seguridad y cumplimiento por lo que no son excluyentes.

La auditoría de instancia se enfoca en eventos que afectan a toda la instancia de SQL Server, no solo a bases de datos individuales. Es ideal para capturar eventos que tienen un impacto global en el servidor y para mantener una vista general sobre la actividad de toda la instancia. Su uso principal es registrar el cumplimiento de políticas de seguridad como la gestión de accesos y el control de cambios en la configuración del servidor o de la instancia. Para ello, captura eventos que afectan a toda la instancia, como inicios de sesión, cambios en la configuración del servidor, y operaciones de mantenimiento. También permite registrar eventos de alto nivel que impactan el funcionamiento de la instancia.

La auditoría de base de datos se centra en eventos que ocurren dentro de una base de datos específica. Es ideal para capturar eventos relacionados con las operaciones de datos y la estructura de la base de datos, proporcionando un nivel de detalle más granular. Como hemos dicho, captura eventos a nivel de base de datos, como modificaciones en los datos, cambios en los objetos, y accesos a datos y nos permite definir qué operaciones se auditan en tablas, esquemas o procedimientos almacenados específicos. Se usa principalmente para registrar operaciones como inserciones, actualizaciones y eliminaciones, es decir, control de cambios y para monitorizar y registrar el acceso a datos sensibles o críticos dentro de una base de datos.

Auditoría de Instancia en SQL Server

Ya hemos visto que la auditoría a nivel de instancia nos permite capturar eventos que afectan a toda la instancia de SQL Server, independientemente de las bases de datos individuales. Este enfoque es útil para registrar eventos que ocurren a nivel de servidor, como cambios en la configuración del servidor o inicios de sesión.

Pasos para Configurar una Auditoría de Instancia

Primero, debemos definir una auditoría que especificará qué eventos se registrarán y cómo se almacenarán los resultados. Esto se hace mediante el SQL Server Management Studio (SSMS) o mediante Transact-SQL (T-SQL).

Usando SSMS:

En SSMS, navegamos a Seguridad > Auditorías.

Hacemos clic derecho en Auditorías y seleccionamos Nueva Auditoría.

En la ventana de propiedades, configuramos la ubicación del archivo de auditoría, que puede ser un archivo de registro, un archivo de eventos o un registro de la aplicación.

Establecemos las opciones necesarias, como el tamaño máximo del archivo y la política de retención.

Usando T-SQL:

USE [master]
GO
CREATE SERVER AUDIT [Audit-20240720-104102]
TO FILE 
( FILEPATH = N'C:\AuditLogs\'
 ,MAXSIZE = 1 GB
 ,MAX_FILES = 10
 ,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)
GO

Una vez creada la auditoría, debemos definir qué eventos específicos deseamos capturar. Esto se hace mediante la creación de especificaciones de auditoría.

Usando SSMS:

Navegamos a Seguridad > Especificaciones de Auditoría.

Hacemos clic derecho en Especificaciones de Auditoría y seleccionamos Nueva Especificación de Auditoría.

Seleccionamos la auditoría previamente creada y definimos los eventos que deseamos capturar, como Inicios de sesión o Cambios en la configuración.

Usando T-SQL:

USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20240720-104510]
FOR SERVER AUDIT [Audit-20240720-104102]
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (FAILED_LOGIN_GROUP)
GO

Finalmente, habilitamos tanto la auditoría como las especificaciones para comenzar a capturar los eventos configurados desde SSMS con clic derecho del ratón sobre los objetos y habilitar.

Usando T-SQL:

ALTER SERVER AUDIT [Audit-20240720-104102] WITH (STATE = ON)
GO
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20240720-104510] WITH (STATE = ON)

Auditoría de Base de Datos en SQL Server

A diferencia de la anterior, la auditoría a nivel de base de datos se centra en registrar eventos que ocurren dentro de una base de datos específica. Este nivel de detalle es fundamental para monitorear actividades relacionadas con el contenido y los objetos de la base de datos.

Pasos para Configurar una Auditoría de Base de Datos

Al igual que con la auditoría a nivel de instancia, primero debemos crear una auditoría que especifique dónde se almacenarán los registros. Esta auditoría se crea a nivel de instancia y podemos usar la misma que teníamos antes, en la base de datos solo vamos a crear las especificaciones.

Usando SSMS:

Navegamos a la base de datos en Seguridad > Especificaciones de Auditoría.

Hacemos clic derecho en Especificaciones de Auditoría y seleccionamos Nueva Especificación de Auditoría.

Configuramos los eventos específicos, como operaciones de datos o cambios en los objetos.

Usando T-SQL:

USE [AdventureWorks2022]
GO
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20240720-105338]
FOR SERVER AUDIT [Audit-20240720-104102]
ADD (UPDATE ON DATABASE::[AdventureWorks2022] BY [public]),
ADD (INSERT ON DATABASE::[AdventureWorks2022] BY [public]),
ADD (DELETE ON DATABASE::[AdventureWorks2022] BY [public])
GO

Igual que antes, activamos la auditoría y las especificaciones para comenzar a registrar los eventos.

Usando T-SQL:

ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20240720-105338] WITH (STATE = ON);

Auditoría vs SQL Server Profiler

Como vimos en el pasado post, SQL Server Profiler es otra herramienta que, aunque no se usa para auditorías a largo plazo, sigue siendo relevante para capturar eventos en tiempo real y para el análisis detallado de sesiones y transacciones. Vamos a comparar esta herramienta con las auditorías de SQL Server en términos de capacidades y usos.

Alcance y propósito

SQL Server Profiler captura eventos en tiempo real para el análisis detallado de sesiones y transacciones. Es ideal para depurar problemas y monitorear el rendimiento en tiempo real ya que proporciona una vista instantánea de la actividad de la base de datos. Por el contrario, las auditorías de SQL Server registran eventos a lo largo del tiempo, permitiendo un seguimiento extensivo y cumplimiento normativo. Son más adecuadas para el cumplimiento de regulaciones y para proporcionar informes detallados sobre eventos históricos ya que capturan eventos críticos y permiten su almacenamiento en archivos o registros para un análisis posterior.

Persistencia

Los datos capturados por Profiler son temporales y se almacenan en memoria mientras se realiza el seguimiento. Aunque se puede salvar un archivo de traza, este no está diseñado para almacenamiento a largo plazo o para el cumplimiento normativo. Las auditorías de SQL Server sin embargo si almacenan los eventos en archivos o registros, lo que permite un almacenamiento prolongado y una revisión a largo plazo. Además facilitan la conservación de datos históricos necesarios para el cumplimiento regulatorio.

Rendimiento

Como ya vimos también, SQL Server Profiler puede impactar el rendimiento del servidor durante la captura de eventos debido a la sobrecarga de recursos. lo que no lo hace la herramienta ideal para sesiones largas y poco específicas donde el rendimiento es crítico. En este sentido, las auditorías de SQL Server tienen un menor impacto en el rendimiento, especialmente cuando se configuran para capturar solo eventos esenciales. Además nos permiten ajustar la granularidad de la auditoría para minimizar la sobrecarga.

Usabilidad

SQL Server Profiler nos ofrece una interfaz gráfica para configurar y visualizar eventos en tiempo real pero requiere de una comprensión avanzada para interpretar los eventos capturados. Las auditorías de SQL Server que configuramos a través de SSMS o T-SQL, proporcionan una forma estructurada y más amigable de registrar eventos para que puedan ser consumidos por auditores y técnicos de ciberseguridad. Las especificaciones de auditoría permiten un control preciso sobre qué eventos se registran y cómo se almacenan.

Conclusión

Configurar auditorías en SQL Server, tanto a nivel de instancia como de base de datos, es fundamental para mantener un control exhaustivo sobre nuestras bases de datos y garantizar la seguridad y el cumplimiento. A través de la correcta configuración de auditorías y especificaciones, podemos registrar eventos críticos y analizar el acceso y las modificaciones a nuestros datos. Aunque el proceso puede parecer complejo, seguir estos pasos nos permite implementar una estrategia de auditoría efectiva que proporciona una visión detallada y precisa de la actividad en nuestras instancias y bases de datos. Al final, una auditoría bien configurada es una herramienta poderosa que fortalece nuestra postura de seguridad y facilita el cumplimiento normativo.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta

Jul

2024

Anti-Patterns : Nuestro mayor enemigo.

Como DBAs, lo más normal va a ser encontrarnos con consultas que, aunque devuelven el resultado esperado, no están optimizadas y pueden llevar a problemas de rendimiento muy serios. Estos patrones de diseño ineficaces son conocidos como «Query Anti-Patterns» y por muy bien diseñada y optimizada que esté tu base de datos van a dilapidar su rendimiento. Vamos a explorar algunos de los Anti-Patterns más comunes y cómo evitarlos para asegurarnos de que nuestras consultas SQL sean lo más eficientes posible.

Anti-Patterns Comunes y Cómo Evitarlos

La optimización de consultas SQL es una mezcla de arte y ciencia. La habilidad para identificar y corregir anti-patterns en nuestras consultas puede marcar la diferencia entre una base de datos que funciona sin problemas y una que causa dolores de cabeza constantes. Aunque algunos anti-patterns pueden parecer inofensivos a primera vista, su impacto acumulativo, cuando se repiten mucho o se solapan puede ser devastador. En este artículo, vamos a ver varios de estos anti-patterns con ejemplos concretos y soluciones prácticas.

El más común: Select *

Uno de los anti-patterns más comunes y dañinos es el uso de SELECT *. Al seleccionar todas las columnas de una tabla, no solo estamos recuperando datos innecesarios, sino que también podemos estar comprometiendo la seguridad y el rendimiento de nuestra consulta. Este problema se agrava si estamos ante una tabla de un modelo tabular optimizada con índices columnares pues perderemos su gran potencial propio de su diseño, la capacidad de leer solo las columnas necesarias.

Ejemplo:

SELECT * 
FROM Employees 
WHERE Department = 'IT';

Solución:

Especificar sólo las columnas necesarias, de esta manera, reducimos la cantidad de datos transferidos y hacemos que nuestra consulta sea más clara y manejable. Además podremos aprovecharnos de índices que cubran completamente nuestras consultas.

SELECT EmployeeID, FirstName, LastName
FROM Employees 
WHERE Department = 'IT';

Funciones en las Columnas

Usar funciones en las columnas dentro de las cláusulas WHERE es otro anti-pattern que puede degradar significativamente el rendimiento. Las funciones en las columnas evitan que SQL Server use índices, resultando en escaneos de tabla completos. Es lo que se conoce como problemas de sargabilidad de los índices, y consiste en que, al usar un filtro de búsqueda que no se puede aplicar a todos los niveles de la estructura B-tree (árbol invertido), el motor de base de datos tiene que recorrer completamente el nivel hoja buscando coincidencias.

Ejemplo:

SELECT * 
FROM Orders 
WHERE YEAR(OrderDate) = 2023;

Solución:

Reescribir la consulta para evitar la función en la columna. Con esta modificación, permitimos que SQL Server utilice índices sobre OrderDate, mejorando notablemente el rendimiento.

SELECT * FROM Orders WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01';

Subconsultas Correlacionadas en el WHERE

Las subconsultas correlacionadas dentro de una cláusula WHERE pueden ser extremadamente costosas, ya que la subconsulta se ejecuta una vez por cada fila en la tabla externa.

Ejemplo:

SELECT * 
FROM Employees e 
WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE Department = e.Department);

Solución:

Utilizar un JOIN o CROSS APPLY para evitar la subconsulta correlacionada. Aunque puede haber excepciones en función del tamaño de las tablas, esta aproximación es generalmente más eficiente, ya que la subconsulta se ejecuta una sola vez y los resultados se unen a la tabla principal reduciendo drásticamente el número de lecturas en disco.

SELECT e.* 
FROM Employees e
JOIN (
    SELECT Department, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY Department
) d ON e.Department = d.Department
WHERE e.Salary > d.AvgSalary;

Subconsultas en el SELECT

Las subconsultas en la cláusula SELECT pueden causar problemas similares a las subconsultas en WHERE, ya que se ejecutan por cada fila de la tabla principal.

Ejemplo:

SELECT 
    EmployeeID,
    (SELECT DepartmentName FROM Departments WHERE DepartmentID = Employees.DepartmentID) AS DepartmentName
FROM Employees;

Solución:

Usar un JOIN para incluir la información necesaria. De esta forma, la subconsulta se elimina y la consulta puede beneficiarse de una menor cantidad de lecturas.

SELECT 
    e.EmployeeID, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;

UNION en Lugar de UNION ALL

El uso de UNION en lugar de UNION ALL puede resultar en un rendimiento deficiente, ya que UNION elimina duplicados, lo cual requiere una operación adicional de ordenación y comparación para lo que es necesario cargar todos los datos en memoria. Es común encontrarse con consultas con UNION por pereza y no escribir los 4 carácteres extra pero, si no es estrictamente necesario nunca es una buena idea.

Ejemplo:

SELECT EmployeeID FROM CurrentEmployees
UNION
SELECT EmployeeID FROM FormerEmployees;

Solución:

Si estamos seguros de que no hay duplicados, usar UNION ALL. De esta manera, evitamos el trabajo adicional de eliminar duplicados y mejoramos la eficiencia de la consulta.

SELECT EmployeeID FROM CurrentEmployees
UNION ALL
SELECT EmployeeID FROM FormerEmployees;

Conversiones Implícitas

Las conversiones implícitas ocurren cuando SQL Server necesita convertir los tipos de datos de una columna o variable para que coincidan. Esto puede tener un impacto negativo en el rendimiento, especialmente cuando involucra columnas indexadas, ya que puede evitar que los índices se utilicen de manera eficiente.

Ejemplo:

SELECT * FROM Orders WHERE OrderID = ‘12345’;

En este ejemplo, si OrderID es un entero y estamos comparándolo con una cadena, SQL Server tendrá que convertir OrderID a una cadena para realizar la comparación, lo que puede evitar el uso de índices.

Solución:

Asegurarse de que los tipos de datos coincidan. De esta manera, evitamos la conversión implícita y permitimos que SQL Server utilice los índices de manera eficiente.

SELECT * FROM Orders WHERE OrderID = 12345;

Localizando Anti-Patterns con X-Events (ojo, esto ya es muy friki)

Los eventos extendidos son una herramienta muy poderosa en nuestra búsqueda de problemas. Gracias a su capacidad de capturar, en tiempo real, consultas que cumplan unos requisitos establecidos y, a una novedad introducida en SQL Server 2022 como es el evento query_antipattern, nos van a permitir localizar estas consultas mal diseñadas de una manera sencilla. O eso dice la teoría. Os dejo por aquí el script que he usado yo para generar esta sesión de x-events:

USE [master]
GO 
IF EXISTS (SELECT * FROM sys.dm_xe_sessions WHERE NAME = 'query_antipattern') 
      DROP event session [query_antipattern] ON server
GO 
CREATE EVENT SESSION [query_antipattern] ON SERVER  
     ADD EVENT sqlserver.query_antipattern (   
        ACTION(sqlserver.client_app_name,sqlserver.plan_handle, 
               sqlserver.query_hash,sqlserver.query_plan_hash,
               sqlserver.sql_text)
          ) ADD TARGET package0.ring_buffer(SET max_memory=(500)) 
GO
ALTER EVENT SESSION query_antipattern ON SERVER STATE = START
GO

Probando los Anti-Patterns X-Events

Ahora que ya hemos visto la teoría vamos a volver al mundo real. Podemos localizar los anti-patterns detectados por este evento extendido si miramos en la DMV sys.dm_xe_map_values que registra los distintos tipos de eventos.

Como ves, hay 5 antipatrones, el quinto es el único que coincide con alguno de los que hemos visto nosotros en este artículo. Sobre el resto no hay más documentación, Microsoft ha decidido sacar esta novedad en X-Events pero no lo ha documentado.

En mis pruebas, he conseguido generar una consulta que active el primero de los tipos de antipatrón, el LargeIn, pero os voy a ser sinceros, no ha sido fácil. Para lograrlo he escrito una consulta con un filtro where CustomerID IN y a continuación le he pasado 2500 parámetros separados por comas. Con menos parámetros no he conseguido hacer saltar la alerta. El antipatrón de conversiones que impiden el Seek sí que es relativamente sencillo verlo y salta ante cualquier consulta con un error de conversión.

Sobre el resto no os puedo decir más, llevo más de una semana con este artículo escrito, buscando información y haciendo pruebas pero no lo he conseguido ver. En algún sitio he visto que han conseguido el fallo por un antipatrón LargeNumberOfOrInPredicate con las pruebas que a mi me han dado el LargeIn pero yo no lo he podido reproducir. Igual que ellos no pudieron reproducir el LargeIn. Sobre el resto de tipos de Anti-Patterns no he conseguido más información. No sé qué significa Max en este contexto. En el caso de NonOptimalOrLogic he llegado a pensar que es un problema con las lógicas OR pero no he conseguido reproducirlo ni con 21.500 OR en una misma consulta. Es más, ni con 21.500 OR en una misma consulta mostró ninguno de los otros Anti-Patterns.

Conclusión

Identificar, resolver y evitar estos anti-patterns en nuestras consultas T-SQL puede suponer una mejora significativa en el rendimiento de nuestras bases de datos. Es crucial siempre revisar y optimizar las consultas en ejecución, especialmente en sistemas críticos donde el rendimiento es esencial.Al aplicar buenas prácticas y evitar estos anti-patterns, no solo mejoramos la eficiencia de nuestras consultas, sino que también contribuimos a la estabilidad a largo plazo de nuestras aplicaciones.

Jul

2024

Modos de Recuperación: Guía Completa

Uno de los aspectos más cruciales de las bases de datos SQL Server y, a menudo, complejos para los que se están iniciando son los modos de recuperación. En este artículo, voy a tratar de profundizar en los detalles de los modos de recuperación de SQL Server, detallando sus características, usos y mejores prácticas para optimizar el rendimiento y la seguridad de nuestras bases de datos. Espero que tanto si estáis empezando a trabajar con bases de datos como si sois ya experimentados podáis aprender algo nuevo.

¿Qué son los Modos de Recuperación en SQL Server?

Empecemos por el principio, en SQL Server, los modos de recuperación determinan cómo se gestiona el log de transacciones y cómo se pueden restaurar las bases de datos tras un fallo. Comprender estos modos es esencial para garantizar la integridad de los datos y minimizar el tiempo de inactividad en caso de incidentes. En este sentido, vamos a poder trabajar con tres modos distintos, simple, full (completo) o bulk-logged (registro masivo).

Tipos de Modos de Recuperación

Como acabamos de ver existen tres modos principales de recuperación en SQL Server: Simple, Full y Bulk-Logged. Cada uno de estos modos tiene sus propias características y escenarios de uso.

Modo de Recuperación Simple

El modo de recuperación simple es el más básico de los tres. En este modo, el espacio utilizado por el registro de transacciones se reutiliza automáticamente después de cada punto de control (checkpoint), lo que significa que no se requiere una gestión detallada del log.

Esto tiene una serie de ventajas como la simplicidad de no requerir una administración exhaustiva del log de transacciones o un menor tamaño del log al reutilizar el espacio. Siempre se mantiene el tamaño del log relativamente pequeño mientras no hagamos transacciones descomunales. Sin embargo, también tiene sus contras. Como desventajas nos vamos a encontrar con una menor capacidad de copias de seguridad ya que solo vamos a poder hacer copias completas o diferenciales. Esto, por supuesto, se traduce en una menor capacidad de recuperación a la hora de necesitar una restauración. En estos casos es probable que suframos una mayor pérdida de datos. En caso de fallo, los datos desde el último respaldo hasta el momento del fallo se perderán, esto es así siempre pero, en el caso de otros modos de recuperación con copias de logs, este tiempo suele ser menor.

Como habrás podido comprobar este modo es ideal para bases de datos que no necesitan un alto grado de recuperación de datos, como bases de datos de desarrollo o pruebas o entornos de producción sin gran cantidad de cambios o sin mucha criticidad.

Modo de Recuperación Full

El modo de recuperación completo, el modo por defecto de todas las nuevas bases de datos de SQL Server y Azure, ofrece el mayor nivel de protección para los datos. En este modo, cada transacción se registra completamente, y se puede realizar una restauración a cualquier punto en el tiempo. Para lograr esto el log de transacciones almacena las transacciones sin borrarlas incluso cuando han terminado y solo se borran cuando ya han sido salvadas en una copia de seguridad de log.

Como ventajas a este comportamiento podemos encontrar una capacidad de copias y por tanto de recuperación más granular. Una base de datos en modo de recuperación Full nos va a permitir la restauración de la base de datos a un punto concreto en el tiempo, minimizando la pérdida de datos. Por supuesto esta mayor capacidad de copias tiene otra ventaja añadida y es la flexibilidad a la hora de diseñar nuestra solución de backups. Al soportar backups completos, diferenciales y de log de transacciones vamos a poder adaptar nuestro plan de copias a nuestros procesos de manera que no interfieran en el rendimiento.

No todo es tan bonito, claro, a cambio tendremos una gestión de los log más compleja que requerirá más cuidado y vigilancia por nuestra parte para evitar que crezca demasiado.

Esto puede ser más complicado de administrar debido a la necesidad de realizar backups de log regulares.

Como ves, este modo es el indicado para bases de datos de producción críticas donde la pérdida de datos debe ser mínima y disponen de un administrador de base de datos que diseña y supervisa el plan de copias de seguridad para evitar incidencias.

Modo de Recuperación por Bulk-Logged

El modo de recuperación de registro masivo, es una opción intermedia entre el Modo Simple y el Modo Completo. Está diseñado para optimizar las operaciones masivas de carga de datos. Este modo minimiza el uso del espacio del log de transacciones cuando se ejecutan operaciones masivas como BULK INSERT, SELECT INTO o CREATE INDEX. Funciona de manera similar al Modo de Recuperación Completo, con la excepción de que los registros de transacciones se registran mínimamente durante las operaciones masivas. Esta forma de registro mínimo ayuda a mantener el log más pequeño al no registrar tanta información.

Gracias a estas optimizaciones, mejora la eficiencia en las operaciones masivas como la carga de datos con BULK INSERT que serán más rápidas y generarán menos registros en el log. Todo esto lo consigue sin perder la flexibilidad ya que ofrece algunas capacidades de recuperación punto en el tiempo, similares al modo full. Es importante remarcar ese algunas capacidades ya que cuando estamos haciendo una operación de carga masiva de datos no vamos a tener registros en el log de transacciones de esa operación lo que va a afectar a la posibilidad de recuperar en ese punto en el tiempo en concreto. Además, aún requiere una gestión adecuada del log de transacciones igual que el modo full.

A tener en cuenta

A pesar de que las recuperaciones punto en el tiempo pueden realizarse en algunas situaciones, si la base de datos se daña mientras se realiza una operación de carga masiva, solo se puede recuperar hasta el último backup del log de transacciones creado antes de la operación masiva. Si no se realizan operaciones de carga masiva mientras se utiliza este modo, entonces se puede realizar una restauración punto en el tiempo de manera similar al modo de recuperación completo. Para que podamos minimizar la pérdida de datos al realizar operaciones de carga masiva, es recomendable realizar un backup del log de transacciones justo antes de la operación masiva y otro inmediatamente después de que la operación haya finalizado. De esta manera, se puede realizar una recuperación punto en el tiempo utilizando los backups del log de transacciones tomados antes y después de la operación de carga masiva.

Elección del Modo de Recuperación Adecuado

La elección del modo de recuperación adecuado depende de varios factores, incluyendo los requisitos de recuperación de datos, la frecuencia de cambios en los datos y la capacidad de gestionar los backups del log, como ya hemos comentado anteriormente.

Requisitos de Recuperación: Si necesitamos la capacidad de recuperar la base de datos hasta un punto específico en el tiempo, el modo full es imprescindible. Si la pérdida de algunos datos es aceptable, el modo simple puede ser suficiente y nos va a simplificar mucho la tarea.
Frecuencia de Cambios: Las bases de datos con cambios frecuentes pueden beneficiarse del modo completo, mientras que aquellas con menos cambios pueden utilizar el modo simple o el Modo en Bulk-Logged.
Capacidad de Gestión: La capacidad de gestionar backups regulares y el tamaño del log de transacciones también influye en la elección. El modo completo requiere una mayor gestión, mientras que el modo simple es más fácil de manejar.

Mejores Prácticas para la Gestión de Modos de Recuperación

Como con todos los temas importantes, existen una serie de buenas prácticas que no debemos descuidar. En el caso de los modos de recuperación estas recomendaciones pasan por:

Realizar Backups Regulares: Independientemente del modo de recuperación, es crucial realizar backups completos de manera regular. Estos se podrán complementar con otros backups como diferenciales o log en función de las necesidades y del modo de recuperación elegido.
Monitorizar el Tamaño del Log: Especialmente en el modo full y Bulk-Logged, es importante monitorear y gestionar el tamaño del log de transacciones para evitar que crezca descontroladamente.
Probar Restauraciones: Realizar pruebas de restauración periódicas para asegurar que los backups son funcionales y los datos se pueden recuperar según lo planeado. Esto, que muchas veces se pasa por alto pues un backup no testeado puede dejarnos tirados cuando más lo necesitemos.
Documentar Procedimientos: Mantener una documentación detallada de los procedimientos de backup y restauración para asegurar una respuesta rápida y efectiva en caso de fallo es clave, como ya vimos en el post sobre la recuperación ante desastres.

¿Qué modo estoy usando? ¿Cómo lo cambio?

Existen varios métodos para averiguar el modo de recuperación actual de una base de datos y poder cambiarlo. El primero y más sencillo es a través de la interfaz gráfica del SSMS. Para ello haremos clic derecho sobre una base de datos y abriremos sus propiedades. Ya en las propiedades nos dirigiremos a las opciones para encontrar el modo de recuperación actual y poder cambiarlo si lo deseamos.

Otra forma de verlo es consultando la vista de sistema sys.databases donde vamos a encontrar una columna llamada recovery_mode_desc con esta información.

SELECT
   name,
   recovery_model_desc
FROM sys.Databases

Si deseamos modificar el modo de recuperación por código T-SQL usaremos estas sintaxis para el modo simple, full o bulk-logged respectivamente

USE master
GO
ALTER DATABASE NombreBaseDatos SET RECOVERY SIMPLE
GO

USE master 
GO
ALTER DATABASE NombreBaseDatos SET RECOVERY FULL
GO

USE master
GO
ALTER DATABASE NombreBaseDatos SET RECOVERY BULK_LOGGED
GO

Conclusión

Los modos de recuperación en SQL Server son un aspecto clave en la estrategia de administración de bases de datos. Elegir el modo adecuado y gestionarlo de manera efectiva pueden marcar la diferencia entre una recuperación exitosa y una pérdida de datos importante. Comprender las ventajas y desventajas de cada modo y aplicar las mejores prácticas nos asegurará la integridad y disponibilidad de nuestros datos, garantizando así un rendimiento óptimo y una respuesta eficiente ante cualquier incidente.

Jul

2024

NULL, un dato especial que debemos saber tratar

Cuando hablamos de datos, uno de los conceptos más interesantes y a menudo malinterpretados es el tratamiento del valor NULL. Nosotros, como DBAs, debemos comprender en profundidad cómo manejar estos valores especiales para asegurar la integridad y eficiencia de nuestras bases de datos. En este artículo, quiero profundizar en cómo SQL Server y las bases de datos SQL de Azure tratan los valores nulos, sus implicaciones y las mejores prácticas para su gestión. Por último compararemos este comportamiento con otros sistemas de gestión de bases de datos (SGBD) como PostgreSQL, MySQL y Oracle.

¿Qué significa NULL?

En el estándar SQL, NULL representa un valor desconocido o no aplicable. A diferencia de otros lenguajes de programación o de fórmulas, NULL no es igual a cero ni a una cadena vacía, en resumen, es simplemente la ausencia de un valor. Esta distinción es crucial, debemos interiorizarla y no olvidarla jamás, hazle un hueco en tu cerebro junto al recuerdo del gol de Iniesta en el minuto 116 de la final del Mundial de Sudáfrica 2010. Y no digo esto porque si, este comportamiento afecta a la forma en que las consultas se construyen y ejecutan. En entornos como SQL Server y Azure SQL, manejar correctamente los valores nulos puede marcar la diferencia entre una base de datos coherente y una llena de inconsistencias.

Tratamiento del valor NULL en SQL Server y Azure SQL

Lo primero que debemos recordar es que NULL no representa ningún valor y como tal no se va a comportar como un valor típico. En SQL Server, cualquier comparación lógica con NULL (por ejemplo, =, <, >, etc.) dará como resultado otro NULL. Esto se debe a que, al ser un valor desconocido, no se puede determinar con certeza si es igual o diferente a otro valor. Tenemos que pensar en que a la lógica de verdadero o falso (sí o no) se le va a sumar una tercera posibilidad, desconocido. Desconocido no es ni verdadero ni falso, ni sí ni no. Por esta razón, debemos utilizar el operador IS NULL o IS NOT NULL para verificar su existencia.

Efectos de NULL en las consultas

Cuando escribimos consultas, el manejo de los nulos es crucial. Vamos a verlo con un ejemplo, supongamos que tenemos una tabla Empleados y queremos encontrar a los que no tienen un jefe asignado. Podriamos pensar que esta consulta va a funcionar:

SELECT * FROM Empleados WHERE JefeID = NULL;

Sin embargo, esta consulta no devolverá ningún resultado, porque JefeID = NULL siempre es falso. La consulta correcta es:

SELECT * FROM Empleados WHERE JefeID IS NULL;

Funciones de manejo de NULL

Para poder salvar estas limitaciones SQL Server y Azure SQL ofrecen varias funciones para tratar los valores nulos de manera efectiva. Algunas de las más útiles incluyen:

ISNULL(): Esta función reemplaza NULL con un valor especificado. Por ejemplo, para mostrar «N/A» en lugar de NULL en un campo de teléfono, usaríamos:

SELECT Nombre, ISNULL(Telefono, 'N/A') AS Telefono FROM Empleados;

COALESCE(): Hemos visto que ISNULL() es una función que acepta dos valores y devuelve el primero que no sea NULL. Coalesce es igual, pero acepta múltiples argumentos y devuelve el primero que no sea NULL. Esto es especialmente útil cuando trabajamos con varias columnas que podrían contener valores nulos:

SELECT Nombre, COALESCE(Telefono1, Telefono2, Telefono3, 'N/A') AS Telefono FROM Empleados;

Tratamiento de NULL en funciones agregadas

Las funciones agregadas como SUM(), AVG(), MAX(), y MIN() directamente ignoran los valores nulos en sus cálculos. Esto es importante para evitar resultados inesperados. Por ejemplo, si queremos calcular el salario promedio de nuestros empleados, SQL Server excluirá automáticamente los valores nulos de la columna y podremos hacer esta consulta simple sin miedo a errores o resultados inesperados:

SELECT AVG(Salario) FROM Empleados;

Tratamiento de NULL en Dynamic Data Masking (DDM)

DDM nos permite enmascarar la información sensible de nuestra base de datos y, en ocasiones, la propia ausencia de información es sensible en sí. Sin embargo, y esto es algo mejorable a mi parecer, DDM no nos permite ocultar valores NULL sensibles en nuestras tablas. Es decir, DDM solo enmascara datos conocidos, mostrando un valor NULL real a los usuarios sin privilegios igual que a los que sí tienen permiso de desenmascarar.

Implicaciones de NULL en índices y claves

Al definir índices y claves, debemos tener en cuenta cómo los valores nulos afectan a estas estructuras. Por ejemplo, en SQL Server, un índice único permite múltiples valores nulos, lo cual puede ser útil pero también peligroso si no se entiende y maneja correctamente. Veamos ahora

Claves Primarias

Las claves primarias (PK) no admiten valores nulos. Esto es lógico, una clave primaria debe identificar de manera única cada fila en una tabla, y un valor nulo, al ser desconocido, no puede cumplir con esta restricción de unicidad. Por lo tanto, al diseñar nuestras tablas, debemos asegurarnos de que las columnas definidas como clave primaria siempre contengan valores no nulos. Realmente, SQL Server hará esto por nosotros y solo nos dejará definir como campos clave de una PK aquellas columnas definidas como NOT NULL, es decir, que no admiten valores nulos.

CREATE TABLE Empleados ( 
EmpleadoID INT NOT NULL PRIMARY KEY,
Nombre NVARCHAR(50),
JefeID INT NULL
);

En el ejemplo anterior, EmpleadoID no puede contener valores nulos, garantizando así la unicidad y la identificabilidad de cada registro. Gracias a esa restricción podemos definir la columna como clave primaria.

Claves Foráneas

Las claves foráneas (FK), por el contrario, sí pueden admitir valores nulos. Un valor nulo en una clave foránea indica que no hay una relación con la tabla referenciada. Esto puede ser útil para representar situaciones donde una relación no es obligatoria. Por ejemplo, si un empleado no tiene un jefe designado, el campo JefeID puede ser NULL.

ALTER TABLE Empleados ADD CONSTRAINT FK_Empleados_Jefe FOREIGN KEY (JefeID) REFERENCES Empleados(EmpleadoID);

En este caso, JefeID puede ser nulo, lo que indica que un empleado no tiene jefe.

Mejores prácticas para manejar NULL

Para mantener la integridad y eficiencia de nuestras bases de datos, debemos seguir algunas mejores prácticas en el uso y control de nulos. Siempre que sea posible, definiremos valores por defecto en nuestras columnas para evitar NULL. Por ejemplo, en lugar de permitir NULL en una columna de estado, podríamos definir «Desconocido» como valor por defecto. Los valores por defecto son aquellos que se van a poner en nuestra base de datos cuando insertamos un registro y no especificamos manualmente otra cosa. Por ejemplo:

ALTER TABLE Empleados ADD CONSTRAINT DF_Estado DEFAULT 'Desconocido' FOR Estado;

No solo los nulos pueden ser un problema, también es posible que un dato técnicamente válido no tenga sentido de negocio. Para estos casos podemos utilizar restricciones CHECK y NOT NULL para asegurarnos de que nuestras columnas contengan siempre valores válidos. Por ejemplo, la siguiente restricción se asegura de que el salario de un empleado siempre sea un número mayor que 0. Como hemos visto antes, esta restricción también invalidará los valores NULL de forma intrínseca.

ALTER TABLE Empleados ADD CONSTRAINT CK_Salario CHECK (Salario > 0);

Por último, debemos documentar claramente cuándo y por qué permitimos nulos en nuestras tablas y asegurarnos de que todos los desarrolladores sigan las mismas buenas prácticas a la hora de crear tablas.

Comparación con otros SGBD

Como este tema es importante vamos a ver el comportamiento de NULL cuando trabajamos con otros de los principales motores de base de datos presentes en el mercado.

PostgreSQL maneja los valores nulos de manera similar a SQL Server. Al igual que en SQL Server, las comparaciones directas con NULL siempre devuelven NULL, y se utilizan IS NULL y IS NOT NULL para verificar la existencia de valores NULL. PostgreSQL también soporta funciones como COALESCE() y NULLIF() para manejar NULL. Una diferencia notable es que PostgreSQL permite definir restricciones más avanzadas usando expresiones booleanas, lo que puede ser útil para controles más complejos sobre los valores NULL.
MySQL, el manejo de nulos es también bastante similar. Las comparaciones directas con NULL resultan en NULL, y se usan IS NULL y IS NOT NULL para las verificaciones. MySQL proporciona las funciones IFNULL() y COALESCE() para manejar valores NULL. Un aspecto distintivo de MySQL es que, en ciertos modos de SQL, puede comportarse de manera más laxa con los valores nulos en índices únicos, permitiendo múltiples entradas NULL, lo cual es similar a SQL Server.
Oracle maneja los nulos de manera muy coherente con el estándar SQL. Las comparaciones con NULL devuelven NULL, y se utilizan IS NULL y IS NOT NULL. Oracle también ofrece funciones como NVL() (que es equivalente a ISNULL() en SQL Server) y COALESCE(). Una característica única de Oracle es su uso de NULLS FIRST y NULLS LAST en las cláusulas ORDER BY, permitiendo un control más granular sobre el orden de los valores NULL en los resultados de las consultas.

Comparación con DAX

Ya hemos hablado de DAX (Data Analysis Expressions) en el blog. DAX es un lenguaje de fórmulas utilizado en Power BI, Analysis Services y Power Pivot para realizar cálculos y consultas de datos. DAX tiene su propia forma de manejar valores nulos, conocida como BLANK. En DAX, el equivalente de NULL es BLANK. El manejo de valores BLANK en DAX difiere de los NULL en SQL en varios aspectos clave

Comparaciones

En DAX, una comparación directa con BLANK no devuelve BLANK. En lugar de eso, BLANK es tratado como 0 (cero) en comparaciones numéricas y como una cadena vacía en comparaciones de texto. Por ejemplo:

IF([Valor] = BLANK(), "Es nulo", "No es nulo")

Funciones de manejo de BLANK

DAX proporciona varias funciones para manejar valores BLANK, incluyendo BLANK(), ISBLANK(), y COALESCE(). La función COALESCE() en DAX es similar a su homóloga en SQL, devolviendo el primer valor que no sea BLANK.

COALESCE([Columna1], [Columna2], 0)

Impacto en cálculos agregados

Las funciones agregadas en DAX, como SUM(), AVERAGE(), MAX(), y MIN(), manejan los valores BLANK de manera similar a SQL, ignorándolos en los cálculos. Sin embargo, es importante saber que en DAX, un BLANK en una medida puede tener implicaciones diferentes dependiendo del contexto del cálculo. Supongamos que tenemos una tabla de ventas en Power BI y queremos calcular el total de ventas, tratando los valores nulos en la columna Cantidad como ceros:

TotalVentas := SUMX(Ventas, COALESCE(Ventas[Cantidad], 0))

En este ejemplo, la función SUMX recorre cada fila en la tabla Ventas y utiliza COALESCE para tratar los valores BLANK en la columna Cantidad como 0.

Diferencias clave DAX vs SQL

Como hemos visto el tratamiento de los valores nulos en DAX difiere del de SQL. Podríamos resumir las diferencias así:

Manejo en comparaciones: Mientras que en SQL, NULL no se puede comparar directamente y cualquier comparación devuelve NULL, en DAX el valor BLANK se trata como 0 o una cadena vacía, dependiendo del contexto.
Funciones específicas: DAX tiene funciones específicas para trabajar con BLANK, y estas funciones son esenciales para manejar datos en contextos de análisis y cálculos complejos.
Contexto de cálculo: En DAX, el contexto de cálculo puede afectar cómo se manejan los valores BLANK, especialmente en modelos de datos complejos donde se utilizan múltiples tablas y relaciones.

Conclusión

El manejo de valores nulos es una característica fundamental cuando trabajamos con datos, ya sea en SQL Server, Azure SQL, otros SGBD como PostgreSQL, MySQL y Oracle o con herramientas de BI como Power BI. Aunque las diferencias en el comportamiento son sutiles, cada SGBD ofrece herramientas y funciones específicas para tratar con valores NULL de manera eficiente.

Comprender estas diferencias y mejores prácticas es imprescindible para diseñar y gestionar nuestras bases de datos de manera más efectiva, asegurando la integridad y eficiencia de nuestros sistemas.

Jun

2024

DB_CHAINING: Una configuración de seguridad peligrosa

Continuamos con el tema del pasado lunes donde hablábamos de la configuración trustworthy de SQL Server. Una configuración de seguridad con unos riesgos de seguridad añadidos que es muy importante que conozcamos. Como os decía antes de irme por las ramas, hoy continuamos con ese tema y es el turno de la opción de configuración db_chaining, una configuración también de seguridad que nos va a permitir simplificar mucho los permisos, no sin riesgos claro. Quédate hasta el final que te voy a contar todos los secretos.

¿Qué es DB_Chaining?

DB_Chaining es una opción de configuración en SQL Server que, cuando está habilitada, permite a los objetos de una base de datos acceder a los objetos de otra base de datos que tienen el mismo propietario. Esto puede ser útil en situaciones donde las bases de datos necesitan compartir información, pero también plantea ciertos riesgos de seguridad que deben ser considerados cuidadosamente.

Normalmente, si un usuario tiene permiso para ejecutar un procedimiento almacenado, y ese procedimiento tiene referencias a otros objetos en la base de datos, el usuario necesita tener permisos para esos objetos también. Esto es el comportamiento por defecto de SQL Server y las bases de datos de Azure SQL ya que db_chaining por defecto está deshabilitado. Sin embargo, cuando DB_Chaining está habilitado, SQL Server trata las cadenas de propiedad de los objetos de la base de datos de manera diferente.Si el procedimiento almacenado y el objeto al que hace referencia tienen el mismo propietario, SQL Server permite que el procedimiento acceda al objeto sin comprobar los permisos del usuario para ese objeto.

Esto que acabamos de ver puede ser útil en situaciones donde se desea simplificar la administración de permisos. Por ejemplo, si tienes varias bases de datos que necesitan compartir información, puedes tener procedimientos almacenados en una base de datos que leen o escriben en tablas en otra base de datos. Sin DB_Chaining, tendrías que dar a los usuarios permisos para las tablas en ambas bases de datos. Con DB_Chaining, puedes dar a los usuarios permisos para ejecutar los procedimientos almacenados, y los procedimientos pueden acceder a las tablas sin que los usuarios tengan permisos directos para ellas.

Consideraciones de Seguridad

Aunque DB_Chaining puede ser útil, también plantea riesgos de seguridad. Cuando DB_Chaining está habilitado, un usuario que tiene permiso para ejecutar un procedimiento almacenado puede potencialmente acceder a cualquier objeto en la base de datos que tenga el mismo propietario que el procedimiento. Si no se gestiona correctamente, esto podría permitir a los usuarios acceder a información a la que no deberían tener acceso.

Por lo tanto, antes de habilitar DB_Chaining, es importante entender completamente sus implicaciones de seguridad y asegurarse de que todas las bases de datos y objetos estén correctamente asegurados. Además, y esto es recomendación personal más que buenas prácticas, no os recomiendo habilitarlo para bases de datos donde los usuarios tengan permisos de control (para crear modificar los objetos) ya que podrían crear una vista para leer datos que no deberían o, peor aún, un procedimiento almacenado para editar los datos.

Conclusión

La configuración de DB_Chaining en SQL Server es una herramienta poderosa que puede simplificar la administración de permisos y facilitar el intercambio de información entre bases de datos. Sin embargo, también plantea riesgos de seguridad que deben ser cuidadosamente considerados y gestionados. Como siempre, la clave para usar DB_Chaining de manera efectiva y segura es entender completamente cómo funciona y seguir las mejores prácticas de seguridad de la información.

Jun

2024

TRUSTWORTHY: La opción que no debes habilitar

Como DBAs de SQL Server tenemos que ser verdaderos expertos en las configuraciones de las bases y datos y comprender a la perfección todas las implicaciones de cada una de estas opciones configurables. Una de las opciones con las que más cuidado hay que tener es la propiedad TRUSTWORTHY (confiable en nuestro idioma). Si lleváis tiempo en esto de la administración de bases de datos es probable que en varias ocasiones os hayáis visto envueltos en debates sobre el uso de esta opción. Trustworthy es una configuración de la base de datos que afecta directamente a la seguridad, sobre todo en lo que a ejecución de código se refiere. Su configuración es clave en el comportamiento de todos los objetos de base de datos que se ejecuten con “WITH EXECUTE AS” y en los componentes CLR marcados como EXTERNAL_ACCESS o UNSAFE.

¿Para qué sirve Trustworthy?

Ahora que ya sabemos que trustworthy es una configuración de base de datos vamos a ver qué es lo que hace. Cuando habilitamos esta configuración (por defecto está deshabilitada) lo que estamos haciendo es decirle a SQL Server que confíe en los objetos de esa base de datos en lo que a seguridad (autenticación y autorización) se refiere. Como hemos visto, esto afecta a los objetos (funciones y procedimientos) que usan EXECUTE AS y a los módulos CLR. Veamos uno a uno estos casos.

Objetos con EXECUTE AS

Cuando trustworthy está habilitado, los módulos T-SQL que usen la opción execute as podrán ejecutarse impersonándose con cualquier otro usuario. Si estos otros usuarios tienen asociado un login con permisos elevados o con usuarios con permisos sobre otras bases de datos vamos a poder acceder a todas las funcionalidades que tendría ese login.

Por ejemplo, imaginad que tenemos dos bases de datos: ventas y contabilidad. Tenemos tres usuarios: vendedor, contable y gerente. Supongamos que el usuario vendedor tiene sólo acceso a ventas, el usuario contable tiene sólo acceso a contabilidad y el usuario gerente tiene acceso a las dos bases de datos. Gracias a tener la propiedad trustworthy habilitada el usuario vendedor va a poder actuar bajo el contexto de seguridad del usuario Gerente y así acceder a datos de contabilidad con sus mismos permisos. Lo mismo para el usuario contable en la otra base de datos.

Módulos CLR

Para las integraciones de CLR (Common Language Runtime) la propiedad trustworthy va a permitir ejecutar ensamblados marcados como EXTERNAL ACCESS o UNSAFE y, gracias a ello, acceder a recursos externos del sistema o de la red que de otra manera no estarían permitidos en este contexto de seguridad.

El peligro de trustworthy

Ahora es cuando viene la historia de terror. Tener trustworthy habilitado en nuestra base de datos puede ser un vector para una escalada de privilegios. Os lo voy a demostrar. Para esta demo vamos a crear una base de datos llamada TrustyDB y vamos a habilitar la opción trustworthy. Vamos a crear un login llamado LoginTrusty. Después pondremos como propietario de nuestra base de datos al usuario “sa”, esto es una buena práctica. Por último vamos a crear un usuario UsurioTrusty para nuestro LoginTrusty y darle permisos de db_owner sobre la base de datos.

USE [master]
GO
CREATE DATABASE [TrustyDB]
GO
ALTER DATABASE TrustyDB SET TRUSTWORTHY ON
GO
CREATE LOGIN [LoginTrusty] WITH PASSWORD = 'EstoEsUnaContraseñaSegura'
GO

USE [TrustyDB]
GO
ALTER AUTHORIZATION ON DATABASE::[TrustyDB] TO [sa]
GO
CREATE USER [UsuarioTrusty] FROM LOGIN [LoginTrusty]
GO
ALTER ROLE db_owner ADD MEMBER UsuarioTrusty
GO

En este momento nuestro login tiene un usuario con permisos de db_owner sobre la base de datos pero no tiene permisos elevados a nivel de servidor. Tampoco es el propietario de la base de datos. Sin embargo, gracias a la propiedad trustworthy va a poder crear un procedimiento almacenado que se ejecute bajo el contexto de seguridad del propietario de la base de datos (recordad que es el sa) y añadirse al rol de servidor sysadmin.

EXECUTE AS USER = 'UsuarioTrusty'
GO
CREATE PROCEDURE PiratillaProc
WITH EXECUTE AS OWNER
AS
ALTER SERVER ROLE sysadmin ADD MEMBER UsuarioTrusty 
GO

EXECUTE PiratillaProc; 
DROP PROCEDURE PiratillaProc;

Acabamos de ver un ejemplo en el que un usuario ha logrado escalar privilegios hasta convertirse en sysadmin. Este es uno de los problemas de habilitar trustworthy junto con el que ya habíamos comentado antes de acceder a datos de otras bases de datos sobre las que no tienes permisos. Otros problemas están relacionados con el uso de CLR ya que va a permitir acceder a información contenida en el sistema operativo (fuera del servidor de base de datos) o incluso de la red, interacciones que no estarían permitidas sin esta propiedad. La propiedad de trustworthy está pensada para eludir medidas de seguridad como la forma de módulos CLR o el uso de certificados para la elevación de privilegios controlada sobre todo en entornos de prueba o desarrollo.

Buenas prácticas con Trustworthy

Como has podido ver, habilitar trustworthy conlleva un riesgo alto de seguridad por lo que la primera recomendación sería habilitarlo con precaución y sólo cuando sea imprescindible. Revisa muy bien los requisitos de cada escenario y determina si es o no necesario habilitarlo. Intenta una alternativa siempre que sea posible, por ejemplo para el caso de CLR la firma de código y uso de certificados te permitirá una gestión más segura de los permisos. Si no te queda más remedio que habilitar trustworthy refuerza tus controles de acceso, sigue una política de permisos mínimos para los usuarios y habilita auditorías para detectar cambios en los permisos y así como acceso a datos críticos.

Como activar Trustworthy

Realmente ya lo hemos visto en el ejemplo de arriba, podemos activar la propiedad trustworthy en las bases de datos que necesitemos con el siguiente código:

ALTER DATABASE NombreBaseDeDatos SET TRUSTWORTHY ON

Una cosa importante que debemos tener muy en cuenta es que esta propiedad no solo está desactivada por defecto para todas las bases de datos sino que SQL Server no puede confiar en una base de datos nueva por defecto. Por este motivo, aunque tengamos la propiedad habilitada siempre que restauremos la base de datos desde un backup o separemos y adjuntemos (detach y attach) la base de datos con trustworthy habilitado esta propiedad va a estar deshabilitada y tendremos que ser nosotros quien la habilite manualmente. Esto va a pasar, sea el mismo servidor o uno distinto.

Conclusión

En resumen, habilitar la configuración de trustworthy puede ser indispensable para algunas funcionalidades de nuestro proyecto, pero si no es nuestro caso debemos evitar su uso para evitar riesgos de seguridad innecesarios. Ahora que ya comprendes su funcionamiento y los riesgos de habilitarlo seguro que te lo vas a pensar dos veces antes de concederles esa petición al usuario que te pide habilitarlo. Si no te queda más remedio recuerda las buenas prácticas y las precauciones que hemos visto en este artículo.

Teoría BBDD

NO TE PIERDAS NADA

Colabora con nosotros

Entradas recientes

Etiquetas

Comentarios recientes