viernes, 25 de agosto de 2017

cross database ownership chaining



En esta entrada exploraremos una característica muy interesante de SQL Server que permite brincar de una base de datos a otra con permisos mínimos para el usuario.

Para que esto sea posible será necesario que ambas bases de datos tengan el mismo propietario.

Vamos a crear dos bases de datos, cada una con una tabla muy sencilla.

USE master
GO
CREATE DATABASE DatabaseA
GO
CREATE DATABASE DatabaseB
GO
USE DatabaseA
GO
CREATE TABLE TableA(
    Valor VARCHAR(10)
)
GO
USE DatabaseB
GO
CREATE TABLE TableB(
    Valor VARCHAR(10)
)



Ahora vamos a crear un procedimiento almacenado que nos permita insertar un registro en TableA de DatabaseA y que en el mismo procedimiento se inserte un registro en TableB de DatabaseB.

USE DatabaseA
GO
CREATE PROC pAgregarValor(
    @Valor VARCHAR(10)
)
AS
BEGIN
    BEGIN TRAN
    BEGIN TRY
        INSERT INTO TableA (Valor) VALUES (@Valor)
        INSERT INTO DatabaseB.dbo.TableB (Valor) VALUES (@Valor)

        COMMIT
    END TRY
    BEGIN CATCH
        ROLLBACK

        DECLARE @Error VARCHAR(4000) = ERROR_MESSAGE()
        RAISERROR (@Error, 16, 1)
    END CATCH
END


Crearemos un login y le daremos acceso a DatabaseA, también dándole permiso de ejecución sobre el procedimiento almacenado que acabamos de crear.

USE master
GO
CREATE LOGIN LoginA WITH PASSWORD = 'password'
GO
USE DatabaseA
GO
CREATE USER LoginA FOR LOGIN LoginA
GO
GRANT EXEC ON pAgregarValor TO LoginA


Si nos conectamos con el login que acabamos de crear e intentamos ejecutar el procedimiento almacenado en DatabaseA nos va a marcar el siguiente error:



El login por lo menos debe tener acceso a DatabaseB.   Vamos a darle un usuario al login en esa base de datos para que pueda ingresar.    Aquí hay que notar que lo único que tendrá permitido es entrar a la base de datos y ejecutar las tareas que tenga permitidas el rol "public".

USE DatabaseB
GO
CREATE USER LoginA FOR LOGIN LoginA


Si volvemos a ejecutar el procedimiento almacenado en DatabaseA, el mensaje de error cambiará debido a que el usuario ya puede entrar a DatabaseB pero no tiene permiso de inserción sobre TableB.


He aquí donde utilizaremos la opción "cross database ownership chaining" de SQL Server.    Al darle permiso de ejecución en un procedimiento almacenado de DatabaseA que realice una modificación en DatabaseB, el usuario tendrá la autorización porque ambas bases de datos tienen el mismo propietario.

Vamos a habilitar la opción cross database ownership chaining.

sp_configure 'cross db ownership chaining', 1
GO
RECONFIGURE
GO
sp_configure 'cross db ownership chaining'


Si ejecutamos nuestro procedimiento almacenado de nuevo, veremos que la inserción resultó exitosa.



Notarás que no es necesario darle permiso de inserción a LoginA sobre TableB de DatabaseB, tuvo permiso porque el propietario de ambas bases de datos es el mismo y cross database ownership chaining está habilitado.

Espero esta entrada te haya gustado y te ayude a mejorar en tu trabajo.   Te espero en la siguiente, ¡saludos!


miércoles, 12 de julio de 2017

ORDER BY



Durante la semana pasada estaba de visita con un cliente y me encontré con un tema que resulta muy interesante, los conjuntos de resultados ordenados.

Cuando uno ejecuta una sentencia en SQL Server sin utilizar ORDER BY, los resultados son contemplados como conjuntos de datos, éstos no tienen un orden bien definido, son simplemente conjuntos donde sus datos satisfacen las características de los predicados utilizados en las sentencias.

Si utilizas el ORDER BY, entonces lo que estás solicitando es conocido como CURSOR.   Es importante no confundirlos con los objetos que nos permiten lecturas fila a fila.

Cuando la combinación de las columnas que aparecen en el criterio de ordenamiento no aseguran una combinación única, el orden del resultado no está totalmente asegurado, esto se debe a que varias formas de ordenar el mismo resultado cumplirían con los criterios de ordenamiento.

Para explicar mejor este punto vamos a realizar un ejercicio bastante sencillo pero que ayudará a entender mejor este tema tan interesante.

Vamos a conectarnos a una base de datos que tengas de pruebas (yo usaré AdventureWorks2014), crearemos una tabla de prueba y la llenaremos con datos aleatorios con el siguiente query.

IF NOT OBJECT_ID('dbo.TestOrderTORAB') IS NULL
    DROP TABLE dbo.TestOrderTORAB
GO
CREATE TABLE dbo.TestOrderTORAB(
    Id INT IDENTITY(1, 1),
    Nombre VARCHAR(32) NOT NULL,
    Color VARCHAR(8) NOT NULL,
    CONSTRAINT pkTestOrderTORAB PRIMARY KEY (Id)
)
GO
DECLARE @i INT = 1
WHILE @i <= 500
BEGIN
    INSERT INTO dbo.TestOrderTORAB (Nombre, Color) VALUES (REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), 'Amarillo')
    INSERT INTO dbo.TestOrderTORAB (Nombre, Color) VALUES (REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), 'Blanco')
    INSERT INTO dbo.TestOrderTORAB (Nombre, Color) VALUES (REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), 'Rojo')
    INSERT INTO dbo.TestOrderTORAB (Nombre, Color) VALUES (REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', ''), 'Azul')

    SET @i += 1
END


En una nueva conexión (ventana) ejecuta la siguiente sentencia.    Es importante mencionar que tu resultado y mi resultado serán muy diferentes, esto es debido a que la tabla fue poblada con información aleatoria.   Aquí lo importante es que veas en tu resultado las filas que forman parte de él.

SELECT TOP 10 Id, Nombre, Color FROM dbo.TestOrderTORAB ORDER BY Color


Supongamos que por tareas de optimización se vio la necesidad de crear un índice sobre la tabla TestOrderTORAB.    Ejecuta este query en otra ventana para que no pierdas el resultado de la sentencia que anteriormente ejecutamos.

CREATE INDEX ixTestOrderTORAB ON dbo.TestOrderTORAB (Color, Nombre)

Si abrimos otra ventana y volvemos a ejecutar la misma sentencia que obtiene los 10 primeros productos ordenados por color, ¡veremos que es diferente el resultado!


Esto a pesar de que es el mismo query y que los datos de la tabla no han sido modificados.    ¿Cuál es la razón?, que el plan de ejecución con el que se despachó el query cambió de uno a otro y que la condición de tomar los primeros 10 productos ordenados por color sigue siendo cumplida con un conjunto de resultados diferente.


Ambos resultados son correctos, y la razón es que ambos cumplen con devolver los primeros 10 elementos que SQL Server encontró ordenándolos por nombre.

Si deseamos que esto no suceda, es necesario incluir una columna que ayude a que sea única la combinación de valores de las columnas utilizadas en el ORDER BY, a esta columna se le conoce como tiebreaker.

Vamos a eliminar el índice con la siguiente sentencia:

DROP INDEX ixTestOrderTORAB ON dbo.TestOrderTORAB

Modificamos el query para incluir la columna id como tiebreaker y ejecutamos para observar los resultados:

SELECT TOP 10 Id, Nombre, Color FROM dbo.TestOrderTORAB ORDER BY Color, Id


Creamos de nueva cuenta el índice con la siguiente sentencia:

CREATE INDEX ixTestOrderTORAB ON dbo.TestOrderTORAB (Color, Id, Nombre)

Ejecutamos el mismo query (el que tiene id como tiebreaker) en otra ventana y veremos que el resultado es el mismo.   No importa que se haya creado un índice, el resultado no fue modificado y la razón es que la combinación de valores de las columnas utilizadas en el ORDER BY es única.

Si comparamos los planes de ejecución de ambas sentencias (antes del índice vs después del índice) veremos que cambió, lo cual es totalmente deseable cuando uno genera índices, que éstos sean contemplados por el optimizador de consultas para mejorar el rendimiento.

En conclusión, es muy recomendable incluir una columna tiebreaker en caso de que la combinación de valores de las columnas utilizadas en un ORDER BY no sean únicos.

Espero esta entrada te haya gustado y te ayude a mejorar en tu trabajo.   Te espero en la siguiente, ¡saludos!