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
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 GO
CREATE DATABASE TEST1 GO
USE TEST1 GO
-- CREAMOS UN NUEVO LOGIN
CREATE
LOGIN FEDERICO WITH PASSWORD ='PASSW@RD' GO
USE TEST1 GO
-- CREAMOS EL USUARIO EN LA BASE DE DATOS
CREATE USER FEDERICO GO
-- CREAMOS UNA TABLA
CREATE
TABLE DBO.EMPLEADOS (ID INT IDENTITY, NAME VARCHAR(50)) GO
-- CREAMOS UN STORE QUE CONSUME LA TABLA EMPLEADOS
CREATE
PROC DBO.USP_EMPLEADOS_GET_ALL ASSELECT * FROM DBO.EMPLEADOS GO
-- 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 GO
-- FEDERICO SOLO TIENE PERMISOS A LOS STORES
GRANT
EXECUTE ON DBO.USP_EMPLEADOS_GET_ALL TO FEDERICOGRANT EXECUTE ON DBO.USP_EMPLEADOS_GET_ALL2 TO FEDERICO GO
-- 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 AS DECLARE @N NVARCHAR(50) SET @N = N'SELECT * FROM DBO.EMPLEADOS'EXECUTE SP_EXECUTESQL @N GO
-- 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!
Filtrar por APML
Subscribir