Sql Dinamico y seguridad en SQL2005

Maxi Accotto — Thu, 08 Nov 2007 23:56:00 GMT

SQL-Dinámico y seguridad dentro de SQLServer 2005

El SQL-dinámico es una técnica la cual nos permite ejecutar sentencias TSQL de forma dinámica, para poder hacer uso de esta técnica es necesario utilizar o bien el store procedure sp_executesql o sino EXEC(@str).

Este articulo no tiene como objetivo entrar en detalle del SQL-dinámico ya que hay otros escritos al respecto como por ejemplo el siguiente link el cual recomiendo leer

http://www.hayes.ch/sql/sql_dinamico.html

Aquí nos concentraremos en la seguridad y los cambios que hay en SQL2005 al respecto.

Una de las mayores contras que tiene el uso de SQL-Dinámico es que si lo ponemos dentro de un Store Procedure el usuario que ejecute tal Store no solo deberá tener permisos de execute sino que también deberá tener permisos sobre los objetos que haga referencia el SQL-Dinámico.

Para poder ser más específicos veamos un ejemplo al respecto

USE MASTER

CREATE DATABASE TEST1

USE TEST1

-- CREAMOS UN NUEVO LOGIN

CREATE LOGIN FEDERICO WITH PASSWORD ='PASSW@RD'

USE TEST1

-- CREAMOS EL USUARIO EN LA BASE DE DATOS

CREATE USER FEDERICO

-- CREAMOS UNA TABLA

CREATE TABLE DBO.EMPLEADOS (ID INT IDENTITY, NAME VARCHAR(50))

-- CREAMOS UN STORE QUE CONSUME LA TABLA EMPLEADOS

CREATE PROC DBO.USP_EMPLEADOS_GET_ALL AS

SELECT * FROM DBO.EMPLEADOS

-- CREAMOS OTRO STORE QUE CONSUME LA TABLA PERO CON SQLDINAMICO

CREATE PROC DBO.USP_EMPLEADOS_GET_ALL2 AS

DECLARE @N NVARCHAR(50)

SET @N = N'SELECT * FROM DBO.EMPLEADOS'

EXECUTE SP_EXECUTESQL @N

-- FEDERICO SOLO TIENE PERMISOS A LOS STORES

GRANT EXECUTE ON DBO.USP_EMPLEADOS_GET_ALL TO FEDERICO

GRANT EXECUTE ON DBO.USP_EMPLEADOS_GET_ALL2 TO FEDERICO

-- ENTRAMOS COMO FEDERICO

EXECUTE AS LOGIN = 'FEDERICO'

SELECT SUSER_SNAME()

SELECT * FROM DBO.EMPLEADOS -- FALLA PORQUE NO TENEMOS PERMISO

EXEC DBO.USP_EMPLEADOS_GET_ALL -- FUNCIONA

EXEC DBO.USP_EMPLEADOS_GET_ALL2 -- FALLA POR EL SQL-DINAMICO

REVERT -- REVERTIMOS EL LOGIN

Como se ha podido observar en el ejemplo el segundo Store ha fallado cuando el usuario Federico lo invoco ya que no tenia permisos sobre los objetos internos del mismo, en este caso la tabla "Empleados".

Esto atenta contra el buen uso de un Store procedure ya que una de sus virtudes es aislar a los usuarios del acceso directo a los objetos, con lo cual si deseamos en este caso usar SQL-Dinámico deberíamos darle permisos de Select a la tabla correspondiente.

Esto funciona así en 2000 y no hay solución al respecto, por lo cual yo no recomiendo mucho el uso de SQL-Dinámico y de usarlo tener en claro lo que está sucediendo.

¿Que cambios hay en 2005?

SqlServer 2005 permite dentro de un objeto como el Store Procedure indicarle el contexto de ejecución, esto es totalmente nuevo y se indica con la sentencia WITH EXECUTE AS (Caller, Owner o Username)

Caller es utilizado por defecto y representa a quien llama (como vimos en el ejemplo 1)
Owner indica que se impersonificara como el usuario propietario del objeto, en este caso como entre con la cuenta de SA entonces estamos impersonificando con ella.
User_name nos permite indicar con que login deseamos que se impersonifique

El siguiente ejemplo modifica el Store con SQL Dinámico pero utilizando Execute AS

-- CAMBIOS EL STORE DEL SQL DINAMO PARA QUE SE EJECUTE EN

-- OTRO CONTEXTO DE SEGURIDAD

ALTER PROC DBO.USP_EMPLEADOS_GET_ALL2

WITH EXECUTE AS OWNER

DECLARE @N NVARCHAR(50)

SET @N = N'SELECT * FROM DBO.EMPLEADOS'

EXECUTE SP_EXECUTESQL @N

-- VOLVEMOS A PROBAR

EXECUTE AS LOGIN = 'FEDERICO'

SELECT SUSER_SNAME()

SELECT * FROM DBO.EMPLEADOS -- FALLA PORQUE NO TENEMOS PERMISO

EXEC DBO.USP_EMPLEADOS_GET_ALL

EXEC DBO.USP_EMPLEADOS_GET_ALL2

REVERT

Aquí se ve claramente que ahora Federico no necesito permisos sobre la tabla "Empleados" para poder hacer uso del SQL-Dinámico.

Con esto lo que logramos es seguir manteniendo una de las enormes virtudes que tienen los Stores Procedures que es el aislamiento con respecto a los objetos base.

No confundir el Execute AS del Store con el luego utilizado en el query para dentro del management Studio impersonificarnos como Federico sin la necesidad entrar a otra ventana.

Estos ejemplos deben ser probados desde SQL2005, si desea probar el primer ejemplo desde SQL2000 deberá sacar la sentencia Execute AS y probar la seguridad entrando con el correspondiente login y password.

Bueno espero les sea de utilidad y espero sus comentarios.

Nos vemos!

BLOG DE MAXI ACCOTTO : Seguridad

Sql Dinamico y seguridad en SQL2005