sábado, 31 de diciembre de 2016

Conceptos básicos de bases de datos relacionales

Una base de datos, contiene una o más tablas de información. Las filas de una tabla son llamadas registros, y las columnas de una tabla se denominan campos o atributos. Una base de datos que sólo contiene una tabla se llama una base de datos plana. Una base de datos que contiene dos o más tablas relacionadas entre sí, constituye una base de datos relacional. 
Imagina que eres el responsable de un hospital. Podrías utilizar una sola tabla (una base de datos plana) para realizar su seguimiento tal y como se muestra en la siguiente tabla.

Conceptos básicos de bases de datos relacionales

sábado, 17 de diciembre de 2016

Tareas típicas de un DBA

Estas son las tareas típicas que deberá realizar en algún momento en su día a día un administrador de bases de datos (DBA):

 Archivar datos

Los datos crecen con el tiempo y puede ser costoso almacenarlos y difícil gestionar. Muchos datos tienden a dar problemas de rendimiento. Como DBA, se debe supervisar el tamaño de los datos y su crecimiento, y determinar la mejor manera de almacenarlos. En algunos archivos de datos que rara vez se utilizan puede ir a otra base de datos o servidor, o incluso se pueden eliminar datos que ya no se necesitan. A menudo, las opciones son limitadas, como la política de la empresa y las regulaciones del gobierno pueden restringir cómo y dónde se almacenan los datos. 

Tareas típicas de un DBA


sábado, 3 de diciembre de 2016

SQL Server: Los 10 secretos de un experto en SQL Server


El mantenimiento de un entorno de SQL Server es una tarea compleja. Aquí están las 10 mejores formas para optimizar su administración.
Muchas empresas han reducido sus departamentos de  TIC en los últimos años. Muchos administradores de bases de datos (DBA) han acabado con la responsabilidad de gestionar un gran número de bases de datos SQL Server. Peor aún, a menudo no hay un DBA real. Alguien está etiquetado como el DBA a tiempo parcial. En algunos casos, el DBA termina de bombero, extinguiendo fuegos, pasando de una crisis a otra. Este tipo de ambiente es difícil e insostenible. A nadie le gusta estar bajo un estrés constante.
Una forma de salir de este tipo de situación es invertir un poco de tiempo en racionalizar el  entorno SQL Server para que sea más fácil de comprender y manejar. Aquí están las 10 mejores maneras para que un DBA de SQL Server tome el control y reduzca el potencial global de las crisis que se produzcan. La lista está ordenada en tareas de menor a mayor importancia.

SQL Server: Los 10 secretos de un experto en SQL Server

sábado, 19 de noviembre de 2016

Curso online de Administración de Bases de Datos SQL Server

Dejo aquí un curso online con varios conceptos sobre bases de datos SQL Server, el curso va desde los conceptos básicos hasta el nuevo paradigma del BigData. La mayor parte del curso son entradas de este blog pero aquellos conceptos que aún no se han desarrollado en el blog tienen enlaces a las fuentes más fiables posibles, como Wikipedia o las páginas de referencia de Microsoft ® SQL Server. A medida que lo vaya desarrollando iré ampliando los enlaces.

Curso de Administración de Bases de Datos SQL Server


Aquí, algunos libros interesantes sobre diseño de bases de datos y programación

Conceptos básicos


Herramientas de administración


Transact SQL 


Objetos de Base de Datos

Procedimientos Almacenados (Stored Procedures)


Utilidades con Procedimientos Almacenados


Monitorización y rendimiento 


Nuevos paradigmas de almacenamiento



sábado, 12 de noviembre de 2016

SQL Server. El registro de transacciones (Transaction Log)

El  registro de transacciones (transaction log) el transaction Iog  es una entidad de datos utilizada por SQL Server para almacenar la historia de las transacciones por las que ha pasado una base de datos.

SQL Server. El registro de transacciones (Transaction Log)

sábado, 5 de noviembre de 2016

Planes de ejecución con SQL Server V, planes de ejecución en XML

Trabajar con los planes de ejecución XML


El almacenamiento de los planes en XML abre varias posibilidades. En primer lugar, es muy fácil hacer una copia de un plan para compartirlo. Esta es la verdadera fuerza de tener a nuestra disposición XML para los planes de ejecución, podemos usar el lenguaje XQuery para ejecutar consultas directamente contra el plan de ejecución y en los planes en la caché.

Obtención de los planes reales y estimados en XML 


Con el fin de convertir a XML el plan estimado se puede activar o desactivar con:

SET SHOWPLAN_XML ON
...
SET SHOWPLAN_XML OFF

El comando SHOWPLAN_XML no es una instrucción esencialmente una vez se activa estos parámetros al ejecutar una sentencia SQL en lugar de ejecutarla recoge información del plan de ejecución en forma de documento XML. Una vez más, es importante poner SHOWPLAN_XML a OFF tan pronto como se haya terminado de recoger la información del plan, para permitir que las instrucciones SQL se ejecuten según lo previsto.

Para ver la versión XML del plan real:

SET ON STATISTICS XML
...
SET OFF STATISTICS XML

Una vez más, vamos a ver el mismo plan de ejecución como se evaluó con el plan de texto.

SET SHOWPLAN_XML ON;
GO
SELECT *
FROM [dbo] [NombreTabla].;
SET SHOWPLAN_XML OFF;
GO


Esta instrucción mostrará algo parecido a esto


planes de ejecución en XML

sábado, 29 de octubre de 2016

Big Data de Microsoft

¿Qué es Big Data?



"Big Data" es un término para la recolección de conjuntos de datos tan grandes y complejos que no pueden ser fácilmente manejados por tecnologías tradicionales de almacenamiento de datos. Big Data es el mundo de datos que existe fuera del almacén de datos tradicional. Es generado por dispositivos; blogs, redes sociales; aplicaciones móviles; clickstreams; ATM, RFID y sensores; tiempo meteorológico, tráfico, y cotizaciones de los mercados; y mucho más. Big Data es des-estructurado, no filtrado y no relacional. Big Data no se genera por las operaciones propias de la empresa.

Big Data es valioso para el negocio, ya que aporta datos a la empresa de su entorno en el mundo en el que opera, compite, y vende. Big Data ofrece la oportunidad a la empresa de obtener datos externos en tiempo real para mejorar, optimizar y mover el negocio hacia nuevos objetivos.
Big Data es alto volumen, alta velocidad y / o activos de gran variedad de información que requieren nuevas formas de procesamiento de cualquier tipo de dato, permitir la toma de decisiones mejorada, así como la visión y la optimización de procesos. 

Los escenarios comunes para Big Data


La popularidad de los grandes volúmenes de datos se basa predominantemente en una oleada de nuevos escenarios, fuentes de datos y oportunidades para integrar los datos no relacionales desde fuera de la empresa en su análisis de negocio.

Big Data de Microsoft

sábado, 22 de octubre de 2016

Planes de ejecución con SQL Server. IV Planes de ejecución de texto

Los planes de ejecución gráficos son muy útiles porque son muy fáciles de leer. Sin embargo, gran parte de los datos de los operadores no son inmediatamente visibles, para ver el conjunto completo tenemos que echar un vistazo a la ventana de propiedades. Pero podemos ver el plan completo conplanes de ejecución de  XML.

Antes de XML se utilizaban los planes de texto, estos actualmente están es desuso pero veremos algunas instrucciones por compatibilidad. Las nuevas versiones de SQL Server sólo se proporcionarán planes de texto en formato XML.

Para ver el plan de ejecución estimado de texto, sólo hay que escribir el comando. 

SET SHOWPLAN_ALL ON;

Esta instrucción pone en modo on a SQL Server para recopilar la información de ejecución para todas las sentencias SQL posteriores, pero esas declaraciones en realidad no se han ejecutado aun. Por lo tanto, tenemos el plan estimado. Es muy importante poner  SHOWPLAN_ALL  a OFF después de que capturar la información que se necesita. Si se nos olvidamos de esto no se ejecutarán las instrucciones SQL server.

A partir de este momento cualquier sentencia que ejecutemos mostrará su plan estimado de ejecución.


Para desactivarlo basta con ejecutar el comando 

SET SHOWPLAN_ALL OFF;


Obtener el plan textual real 

Para obtener el plan de texto real ejecutamos la sentencia

SET STATISTICS PROFILE ON


A partir de este momento cualquier sentencia que ejecutemos vendrá acompañada de su plan real de ejecución.
Para desactivarlo 

SET STATISTICS PROFILE OFF

Interpretación de los planes de texto 


Cuando hemos ejecutado una select sencilla con el plan textual activa nos ha mostrado algo de este tipo.

Planes de ejecución de texto

sábado, 15 de octubre de 2016

El Data Warehousing en la actualidad

El Data Warehouse es un marco para gestionar mejor, entender y sacar provecho de los datos generados por el negocio. El almacén de datos tradicional tiraba de  los datos en un "depósito de la verdad", esquema impulsado para el análisis y la presentación de informes, y funcionó muy bien durante muchos años. Sin embargo, el mundo de los datos está evolucionando rápidamente en formas que están transformando la industria y las empresas están considerando nuevos enfoques de inteligencia empresarial (BI).
El data Warehouse tradicional está siendo presionado por el creciente peso del volumen explosivo de datos, la variedad de tipos de datos y la velocidad de procesamiento en tiempo real así también de cómo estos los datos se están utilizando para crecer y operar el negocio. 

El almacenamiento de datos ha alcanzado el punto de inflexión más importante desde su creación. La empresa moderna necesita una arquitectura lógica que pueda escalar sin problemas para satisfacer estas demandas de volumen con una potencia de procesamiento en tiempo real y capacidad de manejar cualquier tipo de datos para conectar rápidamente el negocio con información valiosa. Esto significa que el data warehouse tradicional tiene que evolucionar hacia un data warehouse moderno.
El  data warehouse tradicional fue diseñado para ser un repositorio central de todos los datos de una empresa. Los datos dispares de sistemas transaccionales como  ERP, CRM y aplicaciones de línea de negocio debían ser adaptadas para extraer los datos (ETL) e introducirlo en el almacén dentro de un esquema relacional global. La estructura de datos era predecible y el procesamiento y presentación de informes de calidad optimizada. Sin embargo para la preparación de consultas se necesitaba gran medida el apoyo de tecnologías de la información TI basadas en el procesamiento por lotes programados.
La llegada de  la Web 2.0 aumentó significativamente los datos relacionados con la empresa generados a través de comercio electrónico, registros web, marketing en buscadores, y otras fuentes. Las Empresas ampliaron las operaciones de ETL para compensar las nuevas fuentes de datos, en última instancia, también  expandieron el modelo.
Sin embargo, incluso con estas complejidades crecientes, el valor de negocio principal del almacén de datos tradicional era la capacidad de realizar un análisis histórico y la presentación de informes procedentes de una fuente confiable y completa de datos.

El Data Warehousing en la actualidad

sábado, 8 de octubre de 2016

Planes de ejecución con SQL Server III, planes de ejecución gráficos

Los planes de ejecución nos ayudarán a escribir código SQL eficiente, solucionar problemas de SQL o monitorear e informar sobre nuestros sistemas.
Aquí se pueden consultar las dos partes previas:

Planes de ejecución con SQL Server I

Planes de ejecución con SQL Server II


Permisos requeridos para ver los planes de ejecución


Para generar planes de ejecución de consultas es necesario tener los permisos necesarios dentro de la base de datos. El administrador de base de datos tendrá generalmente todos los permisos pero para dar permisos a cualquier usuario se puede ejecutar.

GRANT SHOWPLAN TO [nombre de usuario]
Esta acción permitirá a un usuario ver los planes de ejecución para la base de datos.


Trabajar con los planes de ejecución gráficos


Comenzaremos con una de las consultas más simples posibles,

SELECT * FROM dbo.tbNombretabla

Ver el plan estimado

Vamos a ver el plan de ejecución estimado en forma gráfica que ha generado el optimizador de consultas.
Es posible averiguar la estimación del plan más óptimo calculada por el optimizador de una delas siguientes maneras, primero hay que seleccionar la consulta a analizar y después:

• Hacer clic en el icono plan de ejecución estimado en la barra de herramientas.


Planes de ejecución con SQL Server


sábado, 1 de octubre de 2016

Administración de la seguridad con SQL Server: IV Permisos de acceso a objetos

Permisos de acceso a objetos


El último filtro en el proceso de seguridad es la posibilidad de habilitar permisos para manejar cada uno de los objetos de una base de datos de SQL Server. Una vez que un login id tiene acceso a una base de datos mediante un username, todavía puede definirse qué sentencias podrá ejecutar y sobre qué objetos de la base de datos.

Existen tres tipos de permisos: de objeto, de sentencia e implícitos

-Permisos de objeto permiten utilizar y modificar los objetos existentes. Por ejemplo, se podrá dar permiso para añadir o eliminar una tabla, o para realizar sobre ella una consulta.

- Permisos de sentencia permiten ejecutarlas, sin limitar  ese permiso de ejecución a un objeto en particular. Suelen referirse a sentencias de creación o eliminación de objetos.

- Permisos implícitos. Son los que se da a los miembros de las funciones del servidor preestablecidas (como la sysadmin), y a los propietarios de bases de datos. Este tipo de usuarios tienen permisos no modificables, que son los que entran en la categoría de implícitos.


Arquitectura de permisos


Quién tiene permisos y quién puede otorgarlos


Por defecto, tras la creación de la base de datos sólo el propietario de la base de datos (los miembros de db_owner y los agrupados bajo el usuario dbo) tienen permisos para crear objetos en la misma. Esta facultad no suele transmitirse a otros usuarios, pues se supone que éstos son los únicos con permisos para modificar su estructura.
Una vez se crea un objeto en la base de datos, tan sólo el creador del objeto y los administradores (sysadmin) tienen permisos para manipulado. Por defecto, el administrador posee todos los permisos para todos los objetos.
El comando DENY hace que un usuario no tenga permiso para un acceso a un objeto. El comando REVOKE sólo afecta al usuario o función en que se ejecute, permitiendo que otros comandos GRANT en otras entidades que incluyan al mismo usuario puedan deshacer su efecto.


La tabla sysprotects


Cada vez que se otorga un permiso, se añade una fila a la tabla de sistema sysprotects, presente en todas las bases de datos. Esta tabla contiene las siguientes informaciones:

Id: ID del objeto para el que se otorga permiso.
Uid: ID del usuario o función al que se otorga permiso.
Action: Permiso que se otorga.

Este último puede ser:

REFERENCES
SELECT
INSERT
DELETE
UPDATE
CREATE TABLE
CREATE DATABASE
CREATE VIEW
CREATE PROCEDURE
EXECUTE
BACKUP/RESTORE DATABASE
CREATE DEFAULT
BACKUP/RESTORE LOG
CREATE RULE

Protecttype: Tipo de protección ante la posibilidad de que el permiso pueda transmitirse.
Column: Indica las columnas a las que se aplica el permiso.
Grantor: Identificador del usuario que ejecutó la sentencia GRANT o DENY que motivó que se añadiese la fila de la tabla.


Permisos a objetos


Hay dos formas de dar permisos de acceso a datos, una de ellas es hacerlo a través de permitir, revocar o denegar el trabajo con cada uno de los objetos. Esto permite dar permisos a tablas, columnas de tabla, vistas, etc. Los permisos pueden darse a través del Administrador Corporativo a través de la ejecución de procedimientos almacenados del sistema. Es posible dar permisos para ejecutar las siguientes sentencias sobre cada uno de los objetos.


Permisos para tablas

SELECT
UPDATE
DELETE
INSERT
DRI
(Declarative Referential Integrity)
DRI permite crear un objeto que tenga relaciones de integridad referencial con el objeto al que damos permisos.


Permisos para columnas


SELECT
UPDATE


Permisos para vistas


SELECT
UPDATE
DELETE
INSERT


Permisos para procedimientos almacenados


 
EXECUTE


Dar permisos a objetos con el Administrador Corporativo


Para dar, denegar o retirar permisos a objetos desde el Administrador Corporativo. Situarse sobre la base de datos en la que se encuentra el objeto.
Situarse sobre el objeto, sea una tabla, una vista o un procedimiento almacenado. Sobre el objeto pulsar botón derecho del ratón lo que muestra el un menú, pulsar en propiedades, esto nos abre un cuadro de diálogo con varias  pestañas, entre ellas la de permisos la cual nos muestra los tipos de  permiso que tiene el objeto.
Permisos de acceso a objetos

sábado, 24 de septiembre de 2016

Planes de ejecución con SQL Server II

Planes estimado y real de ejecución


Como se mencionó en la primera parte de Planes de Ejecución con SQL Server, hay dos tipos distintos de plan de ejecución. En primer lugar, existe el plan que representa la salida del optimizador. Es el plan de ejecución estimado. Corresponde con una primera estimación del optimizador y no representan la ejecución real cuando se ejecute la consulta física.
El siguiente es el plan que representa la salida real de la ejecución de la consulta. Este tipo de plan es el plan de ejecución real y representa lo que realmente ocurrió cuando se ejecutó la consulta.


Planes de ejecución con SQL Server

sábado, 10 de septiembre de 2016

Administración de la seguridad con SQL Server: III Gestión de la seguridad

Gestión de la seguridad


Aquí se describen las técnicas y mecanismos para implementar los mecanismos de seguridad. 


Administración de la seguridad con SQL Server 1ª Parte


Identificadores de inicio de sesión: Creación


Crear inicios de sesión, sólo será necesario si vamos a utilizar autenticación de SQL Server.  Cuando se añade el nuevo usuario, SQL Server escribe una nueva fila en la tabla syslogins de la base de datos master. Esta tarea puede llevarse a cabo utilizando comandos SQL o a través del Administrador Corporativo.


Creación de identificadores mediante el Administrador Corporativo


Para añadir un identificador de inicio de sesión desde el Administrador Corporativo, seleccionamos la carpeta seguridad y dentro de esta, inicios de sesión que deseemos crearlo, y pulsamos botón derecho del ratón y elegimos Nuevo inicio de sesión.
Nuevo inicio de sesión

 
 A continuación, especificamos la información de autenticación del identificador a crear.

nuevo inicio de sesión, SQL Server


 
La primera página de este cuadro de diálogo multipágina nos permite rellenar los siguientes datos:
- Nombre del identificador.
- Tipo de autenticación: Debe marcarse autenticación de SQL Server.
- La contraseña para el identificador.
- La base de datos por defecto para el identificador, en la que se colocará por defecto al usuario.
- El idioma predeterminado para el inicio de sesión.
En la segunda página,  Roles del servidor permite especificar en qué funciones de servidor preestablecidas se incluirá el identificador de inicio de sesión que va a crearse. 
roles del servidor SQL Server

 
En la página Asignación de usuarios, podremos indicar a qué bases de datos tendrá acceso el identificador de inicio de sesión  y al usuario de bases de datos a la que se asociará.
Asignación usuarios SQL Server

 
La siguiente pestaña es la de elementos protegibles, aquí podemos dar o denegar 

elementos protegibles SQL Server



Finalmente en la carpeta Estado podemos conceder o denegar permiso de conexión al motor de base de datos y habilitar o deshabilitar el inicio de sesión.
 

Creación de inicios de sesión a través de comandos


Podemos crear inicios de sesión ejecutando el procedimiento almacenado sp_addlogin.

sp_addlogin  identificador [, contraseña [, password [, base de datos por defecto, [, lenguaje [, id numérico [, opción de encriptación  ]]]]]]

Algunas consideraciones sobre este procedimiento almacenado.

- Puede omitirse la contraseña.
- Si no se especifica la base de datos por defecto, se considerará la master como tal.
- El identificador numérico tiene sentido cuando existe un usuario en la tabla sysusers de una base de datos cuyo id apunte a un identificador de inicio de sesión inexistente, desee crear un nuevo inicio de sesión con el id del usuario de base de datos en cuestión.
- El parámetro opción de encriptación permite especificar si la contraseña se almacena o no encriptada. Si se omite, la contraseña se encriptará. Si ponemos como parámetro skip_encription, la contraseña no se encriptará.


Identificadores de inicio de sesión: Modificación y eliminación


Mediante el Administrador Corporativo


Para modificar las propiedades de un identificador de inicio de sesión desde el Administrador Corporativo, seleccionamos el inicio de sesión y con el botón derecho del ratón elegimos propiedades.

modificar inicio de sesión
Esto nos abre de nuevo las pantallas explicadas anteriormente con lo que podemos modificar los parámetros elegidos. Si elegimos eliminar eliminaremos el inicio de sesión.


Modificación y eliminación de inicios de sesión a través de comandos SQL


La información de configuración de un identificador de inicio de sesión puede modificarse con los siguientes procedimientos almacenados.
Base de datos por defecto: sp_defaultdb
Facilita al sistema el nombre de la base de datos a la que un identificador de inicio de sesión accederá al entrar en el sistema.
La sintaxis de uso es:

sp_defaultdb nombre de usuario, base de datos

Idioma por defecto: sp_defaultlanguage
Este procedimiento almacenado facilita al sistema el lenguaje con el que el identificador accederá al iniciar una sesión en el sistema. Su sintaxis es:

sp_defaultlanguage ‘nombre de usuario’ [, ‘lenguaje’]

Cambio de contraseña: sp_password
Este procedimiento almacenado permite cambiar la contraseña de acceso al servidor de un identificador de inicio de sesión.

La sintaxis de uso es:

sp_password ‘contraseña antigua’, ‘contraseña nueva’, [,’usuario’]

Para eliminar un identificador de acceso utilizaremos el procedimiento almacenado sp_droplogin. La ejecución del procedimiento eliminará la línea correspondiente al identificador, en la tabla syslogins. Sólo el administrador del sistema puede ejecutar este procedimiento almacenado.

sp_droplogin nombre de usuario


Usuarios de bases de datos: Creación de usuarios de bases de datos


Crear usuarios de bases de datos significa, en realidad, habilitar identificadores de inicio de sesión para su acceso a una o varias bases de datos.
En función del modo de autenticación del usuario, esto significa habilitar el acceso a una cuenta o grupo de Windows o a un identificador de acceso de SQL Server.


A través del Administrador Corporativo


Posicionados sobre la base de datos, abrimos la carpeta seguridad y debajo de esta aparece la de usuarios, sobre esta carpeta botón derecho del ratón Nuevo usuario.
Nuevo usuario BBDD SQL Server


Esto abre un cuadro de diálogo con varias pestañas. En la primera podremos especificar:
- Tipo de Usuario,  a escoger entre los que ya están dados de alta para el acceso al servidor.
- Nombre de usuario. El nombre que deseamos asignar al usuario en la base de datos.
- Nombre de inicio de sesión. Que debe elegir entre los que ya estén creados
- Esquema predeterminado. También se elegirá entre los ya creados.

usuario de base de datos SQL Server


 
En el resto de pestañas podemos elegir las funciones de la base de datos para las que se da acceso o a las que se desea que pertenezca.


Añadiendo usuarios mediante comandos SQL


El procedimiento almacenado de SQL Server  permite habilitar a un identificador de acceso o a una cuenta Windows para su acceso a una base de datos:

sp_grantdbaccess o sp_adduser.
sp_grantdbaccess id inicio sesión [,’nombre de usuario’]

id inicio sesión es un identificador de inicio de SQL Server o una cuenta de Windows. Si se omite el nombre de usuario, el usuario en la base de datos utilizará la misma denominación que el identificador de inicio de sesión.


Eliminación de usuarios mediante el Administrador Corporativo


Para eliminar un usuario en el Administrador Corporativo basta con hacer clic en el botón derecho del ratón situados encima del usuario y eliminar.

eliminar usuario de Base de Datos SQL Server


 

Eliminación de usuarios mediante comandos


Para ello se utiliza el método sp_revokedbaccess que elimina un usuario de una base de datos.

sp_revokedbaccess 'usuario'

No pueden eliminarse los usuarios dbo ni el information_schema ni el guest en la base  de datos master  ni tempdb.


Obteniendo información sobre usuarios


Es posible obtener información sobre los usuarios existentes en una base de datos mediante el procedimiento almacenado sp_helpuser.

sp_helpuser [ ‘nombre de usuario’ ]

Si se omite el nombre del usuario se obtendrá información sobre todos los usuarios de la base de datos.


Roles de base de datos (database roles)


Es posible agrupar a los usuarios para otorgarles conjuntamente permisos de acceso a base de datos utilizando los Roles de base de datos. Un rol de base de datos representa un tipo de usuarios, desde el punto de vista de las labores que pueden realizar, al que se asignarán los permisos necesarios para llevar a cabo esa tarea.
Este Procedimiento simplifica la gestión al administrador de los aspectos de seguridad, pues permite otorgar conjuntamente permisos a usuarios de perfil similar. Lo más lógico es crear primero los roles de base de datos, y añadir posteriormente los usuarios particulares que se adapten a ese grupo. Existen roles de bases de datos predefinidos.


Gestión de roles de bases de datos: Mediante el Administrador Corporativo


En el Administrador Corporativo, para crear un rol de base a datos de usuario deberemos acceder a la base de datos en cuestión y desplegando la carpeta Seguridad y debajo de ella nos colocamos en la carpeta Roles y con el botón derecho del ratón elegimos Nuevo, esto nos da la opción de elegir entre nuevo rol de base de datos o de aplicación. Elegimos rol de base de datos.
Roles Base de datos SQL Server


Esto nos abre un cuadro de diálogo con tres pestañas.
rol de base de datos SLQ Server


En el cuadro de diálogo podremos especificar
El nombre del rol, el propietario y los esquemas propiedad del rol.
En las otras pestañas al igual que hicimos anteriormente podemos elegir los elementos de la base de datos a los que se proporcionan permisos. Los esquemas a los que pertenecen, y el tipo de permiso que se proporciona.

propiedades de rol de la base de datos SQL Server


Creación de Roles de bases de datos mediante comandos SQL


El procedimiento almacenado que permite añadir un nuevo rol una base de datos es sp_addrole.

sp_addrole 'nombre de rol’ [ 'propietario']

El parámetro propietario permite especificar el usuario de la base de datos propietaria del rol que podrá, por tanto, modificarla. Por defecto será dbo.

sp_addrole 'db_owner’


Adición de usuarios a un rol de servidor


El procedimiento almacenado que permite añadir usuarios a un rol de base de datos es sp_addrolemember:

sp_addrolemember 'nombre función'  [, id de inicio de sesión]


Un ejemplo de uso: sp_addrolemember 'Especialistas', 'Conchi'


Información sobre las funciones existentes


Es posible obtener información sobre los roles existentes mediante el procedimiento almacenado sp_helprole.

sp_helprole  [ nombre de función ]

sábado, 3 de septiembre de 2016

Planes de ejecución con SQL Server I

Introducción


A la hora de gestionar las bases de datos el DBA  siempre se plantea el mismo tipo de preguntas:

• ¿Por qué es esta consulta está funcionando tan lentamente?

• Está SQL Server utilizando el índice que he creado?

• ¿Por qué SQL Server no utiliza el índice que he creado?

• ¿Por qué esta consulta se ejecuta más rápido que esta otra?

• Y así sucesivamente.


Planes de ejecución con SQL Server



La respuesta correcta probablemente sea diferente en cada caso, pero siempre hay que hacerse la misma pregunta: ¿He consultado el plan de ejecución?
Los planes de ejecución muestran lo que está pasando detrás de cada actuación de SQL Server. Esto proporciona una gran cantidad de información acerca de cómo SQL Server está ejecutando las consultas, incluyendo los siguientes puntos.

sábado, 27 de agosto de 2016

Administración de la seguridad con SQL Server: II Modos de seguridad

Modos de seguridad en SQL Server



Hay dos modos de seguridad en SQL Server: El modo de Autenticación Integrada con Windows   y el Modo de Autenticación Mixta.
El modo de Autenticación Integrada permite a los usuarios que posean una cuenta en Windows acceder a SQL Server sin necesidad de validar su acceso en el servidor de bases de datos. Este modo aprovecha la integración existente entre Windows y el servidor de bases de datos. 
Además de esta técnica, SQL Server puede ser responsable de manejar y mantener las cuentas de acceso al servidor, de manera que un individuo que intente acceder a SQL Server deberá proporcionar un nombre de usuario y una palabra de paso que le validará mediante una tabla de sistema. Esta autenticación es llamada SQL Server Authentication.

Administración de la seguridad con SQL Server: II Modos de seguridad


sábado, 20 de agosto de 2016

Microsoft Azure ® Bases de Datos elásticas

Introducción

Las bases de datos elásticas son una nueva característica de Azure.  Las bases de datos elásticas son útiles cuando se tienen varias bases de datos y se desea controlar y gestionar todas al mismo tiempo. 
Hay algunas bases de datos que consumen importantes recursos y otras consumen pocos recursos. Cuando se tienen varias bases de datos es difícil determinar el nivel de cada base de datos. Con bases de datos elásticas, se pueden obtener estadísticas para todas las bases de datos al mismo tiempo. 
En el Portal de Microsoft Azure, hemos creado 3 bases de datos SQL Azure. 

Microsoft Azure ® Bases de Datos elásticas

lunes, 15 de agosto de 2016

Problemas y soluciones de seguridad con SQL Server

Seguridad física


A la hora de definir la seguridad de un sistema SQL server la primera consideración obvia es la seguridad física de la máquina servidora SQL, a menudo se pasa por alto. No se trata simplemente si la máquina puede ser robada o no, sino también de la disponibilidad del acceso físico al sistema de almacenamiento de los archivos de base de datos, los discos de backup, o cualesquier servidor que aloje copias redundantes de la base de datos. Sólo aquellas personas con una necesidad real de tocar físicamente estos sistemas deben tener acceso a ellos; cualquier persona que necesite acceso temporal debe ir acompañado y supervisado.

Problemas y soluciones de seguridad con SQL Server

Una consideración menos obvia es la seguridad son los terminales de las personas que tienen acceso o altos privilegios en SQL Server. Si alguien tiene acceso al administrador de sistemas de SQL Server y deja su escritorio desbloqueado, toda la seguridad no va a evitar que alguien fuera de supervisión acceda a datos potencialmente sensibles. Un problema más insidioso sería si alguien utilizó el escritorio para cambiar alguna instancia de datos o algún dato de forma inadvertida o intencionada y no somos capaces de darnos cuenta del cambio.

Seguridad de red


A pesar de que los servidores pueden ser físicamente inaccesibles, lo más probable es que estén conectados a una red de algún tipo. Podría ser una empresa aislada con una LAN sin conexiones externas, o podría ser una conexión directa a Internet. No importa cuál sea la situación, hay algunas cosas que hay que considerar:

Hay que asegurarse de que el servidor de Windows tiene configurada la seguridad de red adecuada y decidir qué protocolos de red se implementarán para permitir o desactivar los accesos que no sean necesarios.
Asegúrarse que haya un cortafuegos y configurarlo para permitir el acceso a SQL Server. Decidir si desea cifrar las conexiones a SQL Server y configurar el acceso adecuadamente.

Si se utiliza Kerberosregistrar un nombre principal de servidor. Decidir si desea utilizar el servicio Explorador de SQL Server para ayudar a los clientes a encontrar las instancias de SQL Server instaladas, y decidir si desean ocultar algunos casos. Ocultar una instancia implica que las aplicaciones cliente y los usuarios tendrán que conocer los detalles de la conexión de la instancia de SQL Server, esto no impide que la gente busque instancias de SQL Server.


Minimizar las oportunidades de ataque


Cuantos más servicios y funciones que estén habilitadas hay más oprtunidades para atacar el sistema. Con SQL Server 2005 se puso en estado  "off por defecto" por lo que las características de acceso con implicaciones de seguridad están desactivados por defecto y sólo serán habilitadas por el DBA cuando sea necesario. (Este proceso de habilitar y deshabilitar los servicios que comúnmente se llama surface area configuration.) 

Un buen ejemplo de función que es posible que se desee deshabilitar es xp_cmdshell, que proporciona una manera de ejecutar comandos desde el sistema Windows dentro del contexto de una instancia de SQL Server. Si un intruso dispone de privilegios elevados puede comprometer la instancia de SQL Server, también se puede utilizar xp_cmdshell para obtener acceso al sistema de Windows.

Hay una serie de métodos para establecer la surface area configuration.  el Administrador de configuración de SQL Server para la configuración de servicios y protocolos de red. Tambien podemos establecer esta configuración con el procedimiento almacenado sp_configure. A modo de ejemplo, este código desactivará la función xp_cmdshell:

-- Permitir opciones avanzadas para ser cambiados
EXEC sp_configure 'show advanced options', 1;
GO
-- Para actualizar el valor configurado actualmente para - opciones avanzadas
RECONFIGURE;
GO
-- Para deshabilitar xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0;
GO
-- Para actualizar el valor configurado actualmente para esta - función
RECONFIGURE;
GO

Para más información se puede consultar 


Cuentas de servicio 


SQL Server se ejecuta como uno o más servicios de Windows, y cada servicio tiene que tener una cuenta de Windows que se utiliza para ejecutarlo. La cuenta debe tener acceso a varios recursos en el sistema Windows (tales como la red y varios directorios del sistema de archivos). Lo más recomendable es que la cuenta tenga los privilegios mínimos posibles necesarios para permitir que SQL Server funcione correctamente. Esto es parte de lo que se llama el principio de mínimo privilegio, que establece que un sistema puede hacerse más seguro a través de la concesión de un usuario o proceso sólo con los permisos requeridos y nada más.

Como tal, una cuenta de servicio de SQL Server no debe ser una cuenta de altos privilegios (como administrador local) porque si SQL Server se ve comprometido, existe la posibilidad de que el sistema Windows también esté en peligro. Si SQL Server requiere el acceso a otros recursos de dominio, se debe crear una nueva cuenta de usuario de dominio con los privilegios mínimos y accesos requiera de recursos. 


Restricción de uso de privilegios del administrador


La mayor exposición a entradas inadecuadas en el sistema se produce con el usuario sa o la función de servidor sysadmin. Hay algunas buenas prácticas a seguir.

Reducir al mínimo el número de personas que tienen acceso a la cuenta sa y restringir el número de miembros de la función sysadmin de modo que sólo aquellas personas que realmente necesiten privilegios de administrador de sistemas los tiengan. No revelar la contraseña de sa a cualquiera que necesite acceso temporal a SQL Server o a un usuario de SQL que quiera realizar alguna tarea rápida. En estas situaciones, es mejor crear una nueva sesión de SQL y concederle privilegios para lo que se requiera.

Es mejor tener personas como miembros de la función sysadmin en lugar de utilizar la cuenta sa. De esta forma, se puede quitar de inicio de sesión de un usuario sin tener que cambiar la contraseña de la cuenta sa. Si hay que hacerse cargo de un servidor viejo y no se sabe quien tuvo acceso a él antes, cambiar la contraseña de sa.

Una cuestión que provoca el debate es si se debe quitar el grupo de Windows BUILTIN / a los administradores de la función sysadmin (se agrega de forma predeterminada). Puede darse el caso de que un administrador de Windows sea capaz de consultar las  bases de datos.  Si se decide quitarlo, hay que tener cuidado. En un entorno agrupado si no se toman las medidas correctas, puede que el servidor de SQL Server no se inicie. 

Para aquellos que tengan acceso al administrador de sistemas, animarlos a no iniciar sesión con privilegios elevados a menos que sea absolutamente necesario. Una buena práctica es  dar a cada uno usuario dos inicios de sesión, uno con privilegios y otro no.  Y utilizar la cuenta con menos privilegios de forma predeterminada ayudando de este modo a minimizar la posibilidad de errores, también reduce la probabilidad de que un terminal de Windows desbloqueado tenga una ventana con privilegios de administrador de sistemas abiertos en él.

Evitar tener aplicaciones que requieran privilegios de administrador de sistemas. Por desgracia, esta es una práctica común e inevitable con algunas aplicaciones, pero se debe evitar esta práctica en aplicaciones de producción propia, es necesario advertir a los desarrolladores que realicen aplicaciones que requieran los mínimos privilegios para acceder al servidor.

Autenticación 


Hay dos modos de autenticación disponibles: Autenticación de Windows y de modo mixto (que es la autenticación de Windows en combinación con la autenticación de SQL Server).

Autenticación de Windows utiliza cuentas de red / dominio validar la cuenta de Windows que se utiliza para conectarse a SQL Server. Si se selecciona esta opción durante la instalación, la cuenta sa se crea con una contraseña generada al azar, pero efectivamente deshabilitada. Inmediatamente después de la instalación, se debe cambiar la contraseña de sa a una contraseña fuerte y segura.

La autenticación de SQL Server se basa en que cada usuario tiene una cuenta de SQL Server definida y la contraseña almacenada en SQL Server. Y, por supuesto, esto significa que la cuenta de sa está habilitada y debe tener una contraseña definida. Con la autenticación de SQL Server, los usuarios tienen al menos dos nombres de usuario y contraseñas (uno para la red y otro para SQL Server). En general se recomienda que sólo se utilice la autenticación de Windows siempre que sea posible, ya que es una solución más segura. 

Si se usa la autenticación de SQL, todos los usuarios deben tener una contraseña, y que sea suficientemente compleja como para ser segura. Esto es especialmente importante para las cuentas de altos privilegios, como sa y los miembros de la función sysadmin. 

Autorización


Uno de los principios de protección de los sistemas es utilizar el principio del mínimo privilegio. Un usuario en una base de datos no debe ser capaz de acceder a los datos de otra base de datos. El propietario de un conjunto de tablas no debe ser capaz de entrar en las tablas de otro usuario. Los usuarios sólo deben ser capaces de acceder a los datos que se supone tienen acceso, e incluso entonces sólo deben ser capaces de realizar las acciones requeridas en los datos (por ejemplo, SELECT, pero no actualizar o borrar).

Todo esto se puede lograr dentro de SQL Server mediante un sistema integral, permisos jerárquicos donde los usuarios o roles (llamados principales) conceden o deniegan ciertos permisos específicos sobre determinados recursos (llamados elementos protegibles) como un objeto, esquema o base de datos. Una visión general de la jerarquía de permisos de SQL Server se ilustra en la imagen. 

Permisos SQL Server




Esto también implica que se siga principio del mínimo privilegio. Por ejemplo, no hacer que todos los desarrolladores accedan a la función db_owner de la base de datos. Restringir los permisos públicos y sólo conceder permisos en los niveles más bajos (usuario o rol) para minimizar el acceso directo. 

Para permitir que más permisos separados y una mejor separación de roles dentro de una base de datos, SQL Server introdujo la separación de esquemas de usuario, donde los esquemas son independientes de los usuarios de bases de datos y son sólo contenedores de objetos. Esto permite una mayor precisión para la gestión de permisos. Por ejemplo puede crearse un esquema que tienga permisos de control para desarrolladores de bases de datos. Se pueden hacer que les permita crear, modificar y eliminar todos los objetos dentro de los esquemas que controlan pero no tienen permisos implícitos a cualquier otro esquema dentro de la base de datos y ya no tienen derechos de db_owner para el desarrollo de bases de datos. Además, la separación de esquemas de usuario permite a los usuarios de bases de datos darles de baja sin tener que recodificar todos los objetos relacionados con el usuario dado de baja.  

Otra forma de evitar que los usuarios hagan cosas no permitidas es no permitir el acceso directo a sus tablas base. Esto puede hacerse proporcionando procedimientos y funciones que se utilizan para encapsular, controlar y aislar operaciones como las actualizaciones y eliminaciones almacenados, y proporcionando vistas que permiten controlar y seleccionar los datos óptimos.


Uno de los métodos más comunes de obtener acceso no autorizado a los datos es utilizar un ataque de inyección SQL. La inyección de SQL puede tomar muchas formas, pero el enfoque principal es aprovechar  el código que utiliza cadenas construidas de forma dinámica e "inyectar" código inesperado en la query. Por ejemplo, el siguiente ataque de inyección se aprovecha de la lógica mal escrita para validar la entrada de usuario para engañar a SQL Server y obligarlo a aceptar la entrada al incluir caracteres de escape en la cadena de entrada. Aunque artificial, este ejemplo pone de relieve lo que puede suceder cuando el código se construye dinámicamente usando cadenas de entrada no es validadas a fondo:

DECLARE @ password VARCHAR (20);
DECLARE @input VARCHAR (20);
DECLARE @ExecStr VARCHAR (1000);

SELECT @ password = 'Mi_Password';

-- Asume que la aplicación obtiene de entrada 'OR' '='
SELECT @input = '' 'O' '' '=' '';

SELECT @ExecStr = 'SI' '' + @ password + '' 'LIKE' '' + @input + '' 'Imprimir' 'Contraseña Aceptada' '';

EXEC (@ExecStr);
GO

Si ejecuta este código, se imprimirá la frase "Contraseña aceptada" a pesar de que la entrada del usuario claramente no coincide con la cadena de contraseña. La entrada del usuario contenía una secuencia de escape que cambió la lógica de SQL porque la entrada no fue debidamente analizada y controlada. La inyección SQL no debería ser un problema para una aplicación bien escrita y hay algunos trucos específicos (como el uso de identificadores delimitados por comillas). Pero si se hereda una aplicación antigua, Se debe probar específicamente para ver si es vulnerable a ataques de inyección SQL. 

Recuperación de desastres


Los problemas de seguridad pueden ocurrir en muchos niveles. Existen algunas preocupaciones cuando la recuperación de desastres implica la conmutación a otro servidor SQL Server pero no se ha tenido la precaución de replicar los logins de sesión en el otro servidor por lo que los usuarios pierden el acceso, también hay un problema cuando surge la necesidad de restaurar una base de datos que contiene los datos cifrados.

En el primer caso, los problemas se producen cuando los inicios de sesión necesarios para acceder a la base de datos no se han duplicado en el servidor replicado por error, por ejemplo, cuando se utiliza el trasvase de registros. Si en la base de datos replicada la aplicación intenta conectarse a través de una entrada específica que no existe en el servidor replicado, la aplicación recibirá un error "acceso fallido". Los inicios de sesión son parte del sistema de aplicaciones y deben ser definidos en la instancia de la base de datos.

En el segundo caso, los problemas se producen cuando la copia de seguridad de base de datos contiene datos cifrados y la clave de cifrado (o teclas) que se utiliza para cifrar los datos no fueron replicados o no están disponibles en la instancia de SQL Server desde donde se está restaurando la base de datos. El mejor caso es si sólo una parte de los datos en la base de datos está encriptada por lo que sólo el subconjunto de datos encriptados no se podrá acceder. El peor escenario es que la totalidad de la base de datos esté cifrada. En ese caso, si el certificado de servidor utiliza para proteger la clave de cifrado de base de datos no fue replicado o no está disponible, la totalidad base de datos no puede ser restaurada y devolverá los siguientes errores:

No se puede encontrar el certificado del servidor con la huella digital

Uno de los objetivos de cifrar una base de datos es precisamente que no se podrá copiar y restaurar para evitar robos de datos, pero si se desea restaurar una base de datos cifrada,  entonces se debe disponer del certificado de servidor o si no,  se pierden los datos.

Revisión de cuentas


Una de las cosas más importantes que se debe hacer para mejorar la seguridad de un sistema es la implementación de una auditoría. Con esto, se sabrá quién está haciendo qué. 
Como mínimo, se deben auditar los intentos fallidos y exitosos de manera que se puede saber si, por ejemplo, cinco intentos de acceso fallidos fueron seguidos por un éxito.  De este modo es posible saber cuando alguien está tratando de entrar en la instancia de SQL Server (y con el que inicio de sesión). La figura muestra la configuración de auditoría de inicio de sesión a través del cuadro de diálogo Propiedades del servidor en SQL Server 2014.
Sobre El servidor en el explorador de objetos pulsamos el botón derecho del ratón y elegimos Propiedades 

Seguridad de SQL Server



Seguridad SQL Server


Y elegimos la carpeta Seguridad.


Aquí hay más información sobre cómo auditar los inicios de sesión. 
Related Posts Plugin for WordPress, Blogger...