Sql dinamico y contexto de seguridad - Sql Server 2008

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

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

——————————————————————
– Sql dinamico , contexto de seguridad
——————————————————————
USE Pruebas
GO

– Creamos un nuevo login
CREATE LOGIN Ricardo WITH PASSWORD =’Passw@rd’
GO

– Creamos el usuario en la base de datos
CREATE USER Ricardo
GO

– Creamos una nueva tabla
CREATE TABLE dbo.Clientes (Id_Cliente INT IDENTITY, Nombre VARCHAR(80))
GO

INSERT INTO dbo.Clientes VALUES(‘JUAN AVILA’)
INSERT INTO dbo.Clientes VALUES(‘ALDO LADERAS’)
INSERT INTO dbo.Clientes VALUES(‘VICTOR MORENO’)

– Creamos un SP que utiliza la tabla Clienetes
CREATE PROC dbo.SPClientesSelect AS
 SELECT * FROM dbo.Clientes
GO

– Creamos otro SP que utiliza la tabla pero con SqlDinamico
CREATE PROC dbo.SPClienetesSelectDinamico AS
 DECLARE @SqlDinamico NVARCHAR(50)
 SET @SqlDinamico = N’SELECT * FROM dbo.Clientes’
 EXECUTE SP_EXECUTESQL @SqlDinamico
GO

– Ricardo solo tiene permisos a los SP
GRANT EXECUTE ON dbo.SPClientesSelect TO Ricardo
GRANT EXECUTE ON dbo.SPClienetesSelectDinamico TO Ricardo
GO

– Entramos como Ricardo
EXECUTE AS LOGIN = ‘Ricardo’

SELECT ()SUSER_SNAME
SELECT * FROM dbo.Clientes – No funciona por carecer de permisos
EXEC dbo.SPClientesSelect – Funciona
EXEC dbo.SPClienetesSelectDinamico – No funciona por carecer por ser SqlDinamico
REVERT – Revertimos el login
SELECT SUSER_SNAME()

——————————————————————
– Sql dinamico y contexto de seguridad
– Cambiamos el SP del SqlDinamico para que ejecute en otro contexto de seguridad
——————————————————————

ALTER PROC dbo.SPClienetesSelectDinamico
WITH EXECUTE AS OWNER
AS

DECLARE @SqlDinamico NVARCHAR(50)
SET @SqlDinamico = N’SELECT * FROM dbo.Clientes’

EXECUTE SP_EXECUTESQL @SqlDinamico
GO

– Volvemos a probar
EXECUTE AS LOGIN = ‘Ricardo’

SELECT SUSER_SNAME()
SELECT * FROM dbo.Clientes  – No funciona por carecer de permisos
EXEC dbo.SPClientesSelect  – Funciona
EXEC dbo.SPClienetesSelectDinamico – Funciona

REVERT – Revertimos el login
SELECT SUSER_SNAME()

About these ads