Buscar este blog

martes, 21 de junio de 2016

Usa el esquema en tus consultas o rompe tu código sin darte cuenta

Hace ya algún tiempo contábamos en otra entrada de este blog la importancia que tiene nombrar correctamente a los objetos de una base de datos a la hora de realizar las consultas: siempre debemos usar el formato esquema.nombre.

Los motivos quedaban claros entonces: evitar problemas de rendimiento en la compilación de las consultas debidos al proceso de resolución de nombres y evitar errores si el esquema por defecto de un usuario es distinto a "dbo" o "NULL".

Si bien estos errores pueden ocasionar algún quebradero de cabeza, su resolución es bastante sencilla, tal y como se explica en el artículo anterior. Sin embargo, añadimos ahora un nuevo caso que podría ser más problemático, ya que podemos generar un error que pasase inadvertido, devolviendo registros incorrectos en nuestras consultas.

Cuando trabajamos con más de un esquema -cosa que deberíamos hacer no sólo por motivos de organización de nuestros objetos, sino también para aplicar permisos sobre ellos más ágilmente- es especialmente importante prestar atención en la definición de vistas y procedimientos almacenados.

En ellos, deberemos poner siempre el esquema y el nombre de los objetos a los que hagamos referencia, si no queremos exponer nuestro código a futuros problemas.

Abocados al cataclismo


Supongamos que en nuestra base de datos usamos esquemas, en concreto "dbo" y "web". En el esquema dbo tenemos dos tablas "Cliente" y "Pedido", en el esquema web añadimos ahora la vista "PedidosCliente" e insertamos algunos registros en las tablas a modo de prueba. El script completo para crear los objetos y rellenar las tablas es el siguiente:

Una vez tenemos los objetos creados, ejecutamos una simple consulta sobre la vista:

Que nos devolverá tres resultados: Dos pedidos para Juan y uno para María.

Ahora bien, imaginemos que solamente para los clientes web necesitamos guardar más información, por ejemplo su password. Como tenemos clientes tradicionales y web, decidimos añadir una segunda tabla Cliente, esta vez en el esquema web, que hará referencia a la tabla dbo.Cliente por el Codigo y extenderá la información de sus registros con la columna Password, para aquellos que vengan a través de la web. Así:


Empieza la batalla de los esquemas


A estas alturas ya podemos empezar a tener dudas sobre a qué tabla referencia nuestra vista web.PedidosCliente. Recordemos que habíamos referenciado a las tablas Pedido y Cliente sin usar el esquema en la definición de la vista. Así pues, ¿la vista seguirá usando dbo.Clientes como hasta ahora, o bien pasará a referenciar la nueva tabla web.Clientes?

Una primera comprobación que podemos hacer es, en el SQL Server Management Studio, hacer clic derecho sobre la vista y en el menú contextual seleccionar "Ver Dependencias".

SQL Server nos dice que la vista sigue dependiendo de dbo.Cliente
Parece que sigue usando la tabla dbo.Cliente. Para estar doblemente seguros, podemos consultar también la definición de la vista  -igualmente con clic derecho sobre ella- y ver cómo está definida. Efectivamente, SQL Server parece estar seguro, es sobre dbo.Cliente:

SELECT        P.Numero, P.FechaPedido, C.Nombre
FROM            dbo.Pedido AS P INNER JOIN
                         dbo.Cliente AS C ON P.CodigoCliente = C.Codigo

SQL Server Management Studio no dice la verdad


A estas alturas podemos asumir que nuestra vista seguirá funcionando como si nada hubiera pasado. Para comprobarlo, ejecutemos la misma consulta del principio de esta entrada sobre la vista. Debería devolver los mismos tres resultados, ¿verdad? Veámoslo:

Msg 207, Level 16, State 1, Procedure PedidosCliente, Line 3 [Batch Start Line 44]El nombre de columna 'Nombre' no es válido.Msg 4413, Level 16, State 1, Line 46No se pudo usar la vista o función 'web.PedidosCliente' debido a errores de enlace.

¡Oh! ¡Oh! Hemos roto nuestra vista y no nos hemos dado cuenta hasta que hemos ejecutado una consulta sobre ella.

Sin embargo, esta es la buena noticia dentro de la gravedad del problema. Ha ocurrido un error y SQL Server nos lo ha notificado. Este error se produce porque la vista está definida dentro del esquema web. Al estar dentro de este esquema, pese a que el Management Studio nos insista en que la vista hace referencia a la tabla dbo.Cliente, no es así: al crear la tabla web.Cliente, la vista hace referencia a ella, porque está en su mismo esquema. Como esta tabla no tiene la columna Nombre, se produce el error.

El mismo error lo podríamos obtener si recreásemos la vista o la intentásemos modificar sin alterar la definición.

Aún puede ser peor


Sí, podría serlo. Si nuestra nueva tabla web.Cliente también tuviese un campo Nombre -imaginemos que queremos poner nombre de usuario web, pero llamamos a la columna Nombre- no se produciría ningún error. Simplemente cambiaría el conjunto de resultados devuelto, circunstancia que podría pasar inadvertida hasta que, por ejemplo, un usuario detectase la anomalía. Por ejemplo:

¡Oooooh! ¡Ooooooh! ¿Ningún resultado? ¿Qué ha pasado con nuestros tres pedidos de Juan y María? Fácil: la consulta sobre la vista ha funcionado, pero haciendo referencia a la tabla web.Cliente, donde no tenemos ningún registro. Así que la vista nos devuelve eso: ningún registro.

La solución


Es sencilla: Nombrar a los objetos por su nombre completo, que incluye esquema.nombre. Si en la definición de la vista original simplemente hubiésemos puesto dbo.Cliente y dbo.Pedido, no habría forma de haberla roto, ni con error ni inadvertidamente. Así:


Recuérdalo, llama a las cosas por su nombre, o podrás tener problemas que, por muy poco probables que parezcan, ocurrirán en el momento más inoportuno. Siempre es mejor no dejarlo en manos del azar.

Con SCHEMABINDING esto no pasaría


Efectivamente, hay otra solución. SQL Server nos permite definir nuestras vistas (y funciones) con la opción SCHEMABINDING. Esta opción hace que no se pueda cambiar la definición de los objetos a los que hace referencia la vista (las tablas Pedido y Cliente en nuestro caso). Además, tal y como explica la MSDN, se nos obliga a usar el esquema siempre cuando definimos una vista con WITH SCHEMABINDING.

Cuando se utiliza SCHEMABINDING, select_statement debe incluir los nombres de dos partes (schema.object) de las tablas, vistas o funciones definidas por el usuario a las que se hace referencia.
Por ejemplo, si intentamos definir nuestra vista web.PedidosCliente así:
Recibiremos el siguiente error:

Msg 4512, Level 16, State 3, Procedure PedidosCliente, Line 4 [Batch Start Line 27]No se puede enlazar a esquema vista 'web.PedidosCliente' porque el nombre 'Pedido' no es válido para enlazar a esquema. Los nombres deben constar de dos partes y los objetos no pueden hacer referencia a sí mismos.

lunes, 9 de noviembre de 2015

UNION vs UNION ALL: Entiende las diferencias

SQL Server ofrece el operador UNION para, según la MSDN, "combinar los resultados de dos o más consultas en un solo conjunto de resultados que incluye todas las filas que pertenecen a las consultas de la unión."

Para que el motor de base de datos pueda combinar el resultado de distintas consultas, éstas deben respetar el número y orden de las columnas implicadas. Asimismo, las columnas equivalentes en posición deben serlo también compatibles en tipos de datos.

El operador UNION presenta un modificador o argumento: ALL. Éste se escribe a continuación de UNION, así: UNION ALL. Cuando se especifica, SQL Server devuelve todas las filas de todas las consultas combinadas, incluyendo posibles duplicados.

Ahí radica justamente la diferencia entre UNION y UNION ALL: El operador de unión elimina las filas duplicadas cuando combina los resultados de las consultas implicadas. Sin embargo, con el argumento ALL, los duplicados se mantienen.


¿Cuándo uso UNION y cuándo UNION ALL?

El primer criterio de selección está claro: si no nos importa que haya registro repetidos o los necesitamos, entonces debemos usar UNION ALL. Si queremos asegurarnos de que no haya registros repetidos, usaremos UNION.


¿Y qué pasa si estoy seguro de que no hay registros repetidos?

En ese caso, por normal general, será mucho más eficiente usar UNION ALL, ya que le ahorramos a SQL Server la necesidad de comprobar y descartar posibles duplicados. Así que, salvo excepciones, salvo que explícitamente queramos eliminar duplicados, será más eficiente para SQL Server el uso de UNION ALL.

miércoles, 28 de enero de 2015

Cómo averiguar el propietario (Owner) de una base de datos en SQL Server

En ocasiones necesitamos conocer quién es el propietario de una base de datos. Normalmente podemos acceder a esta información a través del SQL Server Management Studio, consultando las propiedades de la base de datos.

Para ello, buscamos la base de datos que queramos consultar en el árbol de bases de datos del Object Explorer y hacemos clic derecho sobre ella. A continuación, elegimos la última opción del menú contextual que nos aparece: Propiedades. Veremos esta ventana:

Propiedades de una base de datos, que incluyen el propietario (en amarillo)
Propiedades de una base de datos, que incluyen el propietario (en amarillo)

Sin embargo, en ocasiones no tenemos acceso a esta ventana de propiedades o, simplemente, queremos consultar el propietario de todas las bases de datos de un servidor. Para ello disponemos del siguiente script T-SQL, que nos proporcionará la información deseada:

La primera columna nos dirá el propietario de la base de datos (en concreto, su login o nombre de usuario). A partir de este script, podemos filtrar por base de datos, mediante el campo "name":

O podemos conocer todas las bases de datos cuyo propietario sea un login concreto, aplicando de nuevo al campo "owner_sid" la función suser_sname:

lunes, 27 de octubre de 2014

Notas del SQL Saturday Barcelona (#338): Conocimiento compartido

Volvemos de Barcelona con la sensación de haber sido partícipes de una experiencia enriquecedora: el primer SQL Saturday celebrado en España ha sido todo un éxito.

La encomiable labor organizativa del grupo PASS España -encabezados por Rubén Pertusa-, la generosidad a la hora de compartir su conocimiento de los expertos venidos de diversos países para realizar sus excelentes ponencias y el entusiasmo de todos los profesionales registrados, allí reunidos durante más de 10 horas de sesiones, con un gran afán por compartir y aprender han hecho del SQL Saturday Barcelona un evento del que salimos con un bagaje positivo y muchas ganas de repetir.

Como bien expuso Fernando G. Guerrero, CEO de SolidQ -uno de los sponsors del evento-, "la curiosidad es lo que nos mueve". El apetito por aprender, las ganas de compartir experiencias y conocer a quienes también las viven son el motor de los SQL Saturday. Nada más acertado que el lema de "Share what you know, learn what you don't" (Comparte lo que sabes, aprende lo que no).
Nuestro sábado dio comienzo poco después de las ocho y media de la mañana. Tras el pertinente registro, llegaba la presentación del evento.

La primera sensación fue de sorpresa, pues el número de profesionales inscrito, dispuestos a sacrificar su tiempo libre para no perderse la cita -a centenares de kilómetros de su hogar, en muchos casos-, era elevado.

Tras la bienvenida en uno de los salones de actos del IQS (lugar donde se desarrollaría la jornada), comenzaban las sesiones. Distribuidas entre tres salas, las rutas a elegir eran claras: Business Intelligence, Big Data o DBA (y developers, añadiríamos).

Escogimos, por supuesto, la que más se ajustaba a nuestro perfil, la tercera de ellas. Comenzamos la mañana aprendiendo muchas cosas nuevas sobre SQL Server 2014, de la mano de Enrique Catalá.
No entraremos en detalles técnicos, puesto que no es el propósito de esta entrada en la bitácora, pero las Transacciones de Durabilidad Diferida enseguida captaron nuestra atención. Ya había merecido la pena el esfuerzo realizado por estar presente.

La segunda sesión, también en manos de Enrique, nos dejó con ganas de más. El tiempo destinado a explicar los planes de ejecución -que nos explican qué hace el motor de bases de datos para devolvernos lo que le pedimos- fue a todas luces escaso. Es un tema que da para muchas horas, y que conviene conocer a la perfección. Por cierto, Enrique sólo lo mencionó de pasada porque se quedó sin tiempo para más: si has de verte las caras con planes de ejecución, no dudes en usar SQL Sentry Plan Explorer.

Llegó el tiempo para el primer descanso. Un café y la primera oportunidad para conversar con compañeros de fatigas. Allí conocimos a Eduardo Burillo, alicantino él, con el que fue un placer compartir inquietudes.

Dos sesiones más antes de comer: En la primera, Sergey Olontsev nos habló de una utopía en "Cómo sacar provecho de In-Memory OLTP (Hekaton) en nuestras aplicaciones". Imposible. No se puede. Parece que el afán por competir con otras soluciones para Big Data ha provocado el parto prematuro de Hekaton. SQL Server 2014 salió mucho antes de que esta tecnología estuviese madura. Si es que algún día lo está. Habrá que estar pendientes de su evolución en próximas ediciones. Por ahora solamente alguna aplicación de nuevo cuño y con finalidad muy específica podría sacarle partido sin sufrir alguna se sus casi infinitas restricciones. Eso, y el rayo de luz final que supuso conocer la existencia de una alternativa en memoria a las tablas temporales y variables tipo tabla.

Llegaba el turno después para Davide Mauro, hablándonos de un tema en el que somos más bien profanos: "Cómo elegir el hardware adecuado para SQL Server". Apuntamos algunas cosas interesantes, como las herramientas TPC (Transaction Processing Performance Council) para banco de pruebas. Las usaremos.

Tras la hora de la comida, aún restaban tres sesiones más. Descubrimos el potencial de Power BI, de la mano Rodney Landrum como antesala a otra de las sesiones más interesantes del día, de la mano del divertido alemán Uwe Ricken. Su charla sobre lo que ocurre en las entrañas de SQL Server -esto es, a nivel de páginas de datos- cada vez que modificamos datos mediante instrucciones INSERT, UPDATE y DELETE fue realmente reveladora. No hubo tiempo para completarla, pero nos sumergió de lleno en lo que ocurre cuando ejecutamos dichas sentencias.

Finalizamos el día con la sesión más relajada del sábado. Alberto López Grande nos propuso una reflexión, desarrollada en un agradable debate colectivo, sobre el día a día del DBA, centrada en el desafío que supone mantener los servidores en las versiones más recientes para entornos productivos.

Las interesantes intervenciones de los allí presentes reflejó a la perfección el propósito y espíritu de los SQL Saturday: participar, compartir, debatir. En resumen, aprender. SQL Saturdays que, esperamos, se instauren y se conviertan en costumbre, no sólo en Barcelona, sino en cualquier ciudad donde los profesionales que trabajamos con SQL Server queramos seguir compartiendo conocimiento.

viernes, 4 de julio de 2014

Humor y SQL Server: inyección SQL y cómo evitarla (no llames a tu hijo DROP TABLE)

El portal xkcd publica esta viñeta sobre inyección de código en SQL Server:

Inyección en SQL Server
Los peligros de llamar a tu hijo DROP TABLE
Tratado con gran sentido del humor, esta cómica situación nos pone en alerta frente a la inyección de código SQL Server, que puede suponer una vulnerabilidad seria para nuestras bases de datos.

Una aplicación que no compruebe los datos que en ella se introducen a través de aplicaciones externas está expuesta al desastre. En el cómic se introduce una instrucción DROP TABLE en medio del supuesto nombre de un niño inscrito en un colegio. Al introducir el falso nombre en el sistema informático del centro, el resultado es catastrófico: la tabla de estudiantes es eliminada por completo.

La Wikipedia presenta un ejemplo similar para demostrar en qué consiste la inyección SQL Server, así como diversas soluciones a adoptar, dependiendo del lenguaje de programación de la aplicación:
"Por ejemplo, asumiendo que el siguiente código reside en una aplicación web y que existe un parámetro "nombreUsuario" que contiene el nombre de usuario a consultar, una inyección SQL se podría provocar de la siguiente forma:
El código SQL original y vulnerable es:
consulta := "SELECT * FROM usuarios WHERE nombre = '" + nombreUsuario + "';"
Si el operador escribe un nombre, por ejemplo "Alicia", nada anormal sucederá, la aplicación generaría una sentencia SQL similar a la siguiente, que es perfectamente correcta, en donde se seleccionarían todos los registros con el nombre "Alicia" en la base de datos:
SELECT * FROM usuarios WHERE nombre = 'Alicia';
Pero si un operador malintencionado escribe como nombre de usuario a consultar:
"Alicia'; DROP TABLE usuarios; SELECT * FROM datos WHERE nombre LIKE '%"
(sin las comillas externas), se generaría la siguiente consulta SQL, (el color verde es lo que pretende el programador, el azul es el dato, y el rojo, el código SQL inyectado):
SELECT * FROM usuarios WHERE nombre = 'Alicia'; DROP TABLE usuarios; SELECT * FROM datos WHERE nombre LIKE '%DROP TABLE usuarios;SELECT * FROM datos WHERE nombre LIKE '%';
En la base de datos se ejecutaría la consulta en el orden dado, se seleccionarían todos los registros con el nombre 'Alicia', se borraría la tabla 'usuarios' y finalmente se seleccionaría toda la tabla "datos", que no debería estar disponible para los usuarios web comunes."

jueves, 3 de julio de 2014

¡Nos vemos en el SQLSaturday Barcelona!

¡Por primera vez llegan a España los famosos eventos SQLSaturday! Será en Barcelona, el próximo sábado 25 de octubre de 2014.

Ya me he registrado en el evento. Será una buena oportunidad para conocer a otros profesionales de SQL Server y compartir experiencias y conocimiento, todo ello mientras aprendemos de los mejores expertos, incluidos algunos MVP y MCM de Microsoft, en las diferentes sesiones programadas.
SQLSaturday es un evento gratuito de un día de duración para profesionales y futuros profesionales relacionados con SQL Server, Big Data y Business Intelligence; organizado por la Asociación de Profesionales de SQL Server (PASS). Se han celebrado más de 300 eventos SQLSaturday alrededor de todo el mundo (el de Barcelona será el 338) y ésta va a ser la primera edición en España. El plazo de registro lleva abierto algunos días, en los que ya se han inscrito más de 70 asistentes.
Tal y como afirma la web del evento, dentro de la multitud de temas que se van a tratar en las sesiones, podremos encontrar:

  • Motor relacional (optimización, bloqueos, índices...)
  • Alta disponibilidad
  • SQL Server 2014
  • Big Data (HDinsight, Pig, Hive...)
  • Power BI
  • Integration Services, Analysis Services, Reporting 
  • Powershell 
  • ...
Habrá sesiones en inglés y en castellano. Los ponentes han ido enviando sus propuestas de sesiones, que aparecen listadas en la web del evento. En próximas fechas se publicará el calendario final del evento, para que cada asistente se programe aquéllas a las que tiene más interés en asistir.

Además, el evento favorece el networking entre profesionales de SQL Server, facilitando un lugar común en el que conocer al resto de asistentes mediante sus perfiles de LinkedId o sus cuentas de Twitter.

viernes, 23 de mayo de 2014

Haz bien tus consultas: Llama a las cosas por su nombre (y esquema)

La MSDN nos explica cual es la forma correcta de referenciar a los objetos en tus consultas, y es la siguiente:

Esquema.Nombre

¿Por qué? Hay diversos motivos y, aunque parezca algo trivial, pueden evitarnos acceder a objetos incorrectos, errores en consultas para usuarios de la base de datos distintos al que creó el objeto e incluso problemas de rendimiento.

Resolución de nombres

Cuando ejecutamos una consulta, el servidor SQL realiza un proceso llamado resolución de nombres (name resolution, en inglés) para identificar los objetos referidos en ella.

Si identificamos cada objeto por su nombre completo (fully qualified name), SQL sabrá determinar inmediatamente a qué objeto nos referimos y si existe o no. Por el contrario, si solamente especificamos su nombre, el motor de bases de datos tendrá que determinar a qué objeto nos referimos. Para ello usará el default schema del usuario conectado.

Esquema por defecto o Default schema

Cada usuario de una base de datos puede tener un esquema por defecto (desde SQL Server 2008 R2, también los grupos de Windows. Esto es importante, como veremos a continuación). Si para un usuario no está definido el esquema por defecto, se asume que éste es "dbo".

Para comprobar el esquema por defecto que tienen los usuarios de nuestra base de datos, podemos ejecutar la siguiente consulta:

Cuando SQL tiene que resolver un nombre de objeto sin esquema, primero busca en el esquema por defecto del usuario actual. Si no lo encuentra ahí, entonces lo buscará en el esquema dbo. Si tampoco estuviera ahí, la consulta fallaría.

Es conveniente que el esquema por defecto de todos los usuarios y grupos sea dbo o no esté definido (NULL), salvo que haya razones muy concretas para que no sea así.

Cuando el esquema por defecto de un usuario es otro, se dan los problemas descritos a continuación.

Problemas relacionados con el esquema por defecto

  • Si un usuario tiene un esquema por defecto que no es dbo, cuando cree nuevos objetos en la base de datos, estos se llamarán esquema_usuario.nombre_objeto. Si cualquier otro usuario con un esquema por defecto distinto hace una consulta sobre dicho objeto sin especificar su esquema, la consulta fallará.
  • Si se crea un objeto con el esquema dbo, pero un usuario con el esquema por defecto distinto de dbo hace una consulta se hace sin especificar el esquema, SQL buscará el objeto primero en su esquema por defecto y después en dbo (name resolution). Esto puede producir problemas de rendimiento. Si bien estos problemas no serían dramáticos, se han medido que entre el 10% y el 15% del proceso de ejecución de una consulta puede ser debido a la resolución de nombres.
Los esquemas existen desde SQL Server 2005 y ofrecen multitud de posibilidades de organización y control de seguridad sobre nuestros objetos. Sin embargo, aunque no los uses y todos tus objetos estén en el esquema dbo, que es el por defecto, es posible que el esquema por defecto de alguno de tus usuarios no sea dbo.

Para comprobar qué usuarios no tienen por defecto el esquema "dbo", puedes ejecutar el siguiente script:

La MSDN explica muy bien todo este proceso en su sección "How to Refer to Objects" (como referirse a objetos) del artículo -en inglés- "SQL Server Best Practices - Implementation of Database Object" (Prácticas recomendadas en SQL Server - Implementación de Objetos de Base de Datos):
Es siempre una buena práctica referirse a los objetos de la base de datos por el nombre de esquema y el nombre de objeto separados por un punto.
...
El proceso [de resolución de nombres] puede mejorarse usando el nombre completo o configurando correctamente el esquema por defecto del usuario, eliminando un proceso innecesario de comprobación de propiedad. Esto puede mejorar considerablemente el rendimiento en sistemas utilizados intensivamente.

Problema adicional con grupos de usuarios de Windows en versiones anteriores a SQL Server 2012

Si asignas permisos a tus bases de datos a grupos de usuarios de Windows en versiones de SQL Server anteriores a 2012, cuando los usuarios de dichos grupos accedan a la base de datos, SQL Server les creará un usuario cuyo esquema por defecto será su login de Windows.

Esto hace que el problema descrito de resolución de nombres se agrave especialmente en este tipo de entornos con versiones de SQL Server 2005 o 2008, en los que el administrador de bases de datos gestione los permisos de esta manera.

Por ejemplo: Si el usuario MIDOMINIO\Usuario1 pertenece al grupo MIDOMINIO\Grupo1 y el Usuario1 se conecte por primera vez a una base de datos para la que tenga permisos asignados a través del Grupo1, SQL Server le asignará como esquema por defecto el valor "MIDOMINIO\Usuario1".

A partir de este momento, cada objeto que cree el Usuario1 sin definir el esquema correctamente se llamará de la forma:

MIDOMINIO\Usuario1.NombreObjeto.

Y todas las consultas que haga el usuario sin especificar el esquema en los nombres de objeto penalizarán el rendimiento de SQL Server. Por ejemplo:

Además, si otros usuarios realizan esta misma consulta, fallará, ya que SQL Server no será capaz de encontrar el objeto si no se especifica el nombre de esquema.

Conclusiones

  • Si eres DBA: Asegúrate que los usuarios de tus bases de datos tengan asignado como esquema por defecto dbo o NULL.
  • Si eres programador: Escribe todas tus consultas haciendo referencia a los objetos por su nombre completo, de la forma nombre_de_esquema.nombre_de_objeto