En mi artículo anterior os hablé de cómo construir SQL dinámico de forma segura usando sp_executesql, y cómo evitar riesgos como el SQL Injection. Sin embargo, hay un aspecto igual de crítico que no se suele tener en cuenta: el contexto de seguridad desde el que se ejecuta el código dinámico y su impacto en los permisos.
Muchos desarrolladores se sorprenden cuando un procedimiento almacenado que funciona con SQL «normal» deja de funcionar al pasar a SQL dinámico, a pesar de tener los mismos permisos. El motivo está en cómo SQL Server maneja los permisos de ejecución implícitos y qué ocurre cuando usamos sp_executesql. En este artículo voy a tratar de explicarlo paso a paso.
Permisos implícitos al ejecutar un procedimiento almacenado
Cuando concedemos a un usuario permiso para ejecutar un procedimiento almacenado, por ejemplo:
GRANT EXECUTE ON dbo.usp_ConsultaVentas TO app_user;Ese usuario puede ejecutar el procedimiento sin necesidad de tener permisos directos sobre las tablas internas que use dicho procedimiento. Es decir, aunque no tenga SELECT sobre Sales.SalesOrderHeader, si el procedimiento ejecuta esta consulta:
SELECT * FROM Sales.SalesOrderHeader;… el usuario podrá obtener los datos. Esto ocurre porque el contexto de ejecución del procedimiento es el del propietario, y si el procedimiento y las tablas tienen el mismo dueño, SQL Server permite ese acceso mediante el mecanismo de ownership chaining (encadenamiento de propiedad). Este comportamiento es clave para encapsular la lógica de negocio sin exponer directamente las tablas subyacentes.
¿Qué ocurre con los permisos cuando usamos SQL dinámico?
Aquí viene la trampa. Si dentro del procedimiento usamos SQL dinámico con sp_executesql como:
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM Sales.SalesOrderHeader';
EXEC sp_executesql @sql;… entonces se rompe la cadena de propiedad, y SQL Server evalúa los permisos como si el usuario estuviera ejecutando directamente la consulta, no como si formara parte del procedimiento. En otras palabras: aunque el usuario tenga permiso de ejecución sobre el procedimiento, necesitará permisos explícitos de SELECT sobre la tabla referenciada en el SQL dinámico.
Esto puede llevar a errores difíciles de diagnosticar si no se comprende cómo funciona el contexto de seguridad.
Demostración del problema de permisos
Imaginemos este escenario:
- Usuario app_user
- Procedimiento dbo.usp_Informe
- Tabla Sales.SalesOrderHeader
- Escenario app_user tiene EXECUTE sobre usp_Informe, pero no SELECT sobre la tabla
- Código del procedimiento:
CREATE PROCEDURE dbo.usp_Informe
AS
BEGIN
-- Esto funcionará
SELECT COUNT(*) FROM Sales.SalesOrderHeader;
-- Esto lanzará error si el usuario no tiene SELECT explícito
DECLARE @sql NVARCHAR(MAX) = 'SELECT COUNT(*) FROM Sales.SalesOrderHeader';
EXEC sp_executesql @sql;
ENDEl primer SELECT se ejecuta correctamente gracias al ownership chaining. El segundo da error:
Msg 229, Level 14, State 5: The SELECT permission was denied on the object...Cuándo se rompe y cuándo no
Para que se mantenga el ownership chaining, se deben cumplir dos condiciones:
- Los objetos deben pertenecer al mismo propietario (normalmente dbo)
- La consulta no debe usar SQL dinámico
- En cuanto usamos EXEC, sp_executesql o EXECUTE AS con un contexto diferente, se interrumpe esa cadena y SQL Server valida los permisos del usuario directamente.
¿Cómo se soluciona el problema de permisos?
Existen varias estrategias según el contexto, pero las más comunes son estas:
1. Firmar el procedimiento con un certificado
Es la solución más profesional. Se firma el procedimiento con un certificado que tenga los permisos necesarios sobre las tablas, y el usuario ejecuta el procedimiento sin tener permisos directos. Requiere más trabajo, pero es la solución más segura y escalable, especialmente en entornos regulados o críticos.
2. Conceder permisos explícitos sobre las tablas
Es la opción más directa pero rompe el aislamiento que buscamos al encapsular la lógica dentro de procedimientos. Puede ser válido en entornos internos o controlados.
GRANT SELECT ON Sales.SalesOrderHeader TO app_user;Pero en ese caso ya no protegemos las tablas detrás de la lógica del procedimiento.
3. Evitar usar SQL dinámico innecesariamente
Si el acceso a los datos no requiere construir partes dinámicas (columnas, nombres de tabla, filtros condicionales avanzados), es mejor mantener el SQL como texto plano. Así se conserva el contexto de permisos del procedimiento.
En ocasiones, usamos SQL dinámico cuando lo que realmente necesitamos es ejecutar una u otra consulta en función de un parámetro de entrada del procedimiento. En estos casos es mejor crear una lógica con IF que códigos dinámicos.
Por ejemplo, este procedimiento con código SQL dinámico:
CREATE PROCEDURE sp_ConsultaDinamica
@TIPO INT
AS
BEGIN
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM '+
CASE @TIPO
WHEN 1 THEN 'Clientes'
WHEN 2 THEN 'Proveedores'
WHEN 3 THEN 'Productos'
END;
EXEC sp_executesql @sql, N'@TIPO INT', @TIPO;
END
GO… lo podríamos sustituir por este:
CREATE PROCEDURE sp_ConsultaDinamica
@TIPO INT
AS
BEGIN
IF @TIPO = 1
SELECT * FROM Clientes;
IF @TIPO = 2
SELECT * FROM Proveedores;
IF @TIPO = 3
SELECT * FROM Productos;
END
GOConclusión
El uso de SQL dinámico no es solo una cuestión de sintaxis o seguridad frente a inyecciones. También tiene implicaciones directas en el modelo de permisos y seguridad de SQL Server. Es importante entender que al usar sp_executesql, el procedimiento pierde la protección que le daba la cadena de propiedad, y el motor evalúa los permisos como si se tratase de una ejecución independiente. Este comportamiento puede ser confuso si no se conoce, pero una vez lo interiorizamos, se convierte en una herramienta poderosa para diseñar arquitecturas seguras y mantenibles. Si estamos diseñando procedimientos que deben proteger las tablas subyacentes, debemos considerar firmar con certificados, controlar cuidadosamente los permisos, o bien evitar SQL dinámico cuando sea posible.
En el artículo anterior explicamos cómo generar SQL dinámico de forma segura desde el punto de vista sintáctico y de rendimiento. Pero ahora, sabemos que también hay que hacerlo de forma segura desde el punto de vista del contexto de ejecución.
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 la próxima!
