Tema 28.docx

Tema 28. SQL Server: características principales. Creación de bases de datos. Seguridad lógica: usuarios, permisos y roles. Seguridad física. Backup. Instalación y configuración. Microsoft SQL Server es un sistema de gestión de base de datos relacional (RDBMS) desarrollado por Microsoft. Su princip...

Tema 28. SQL Server: características principales. Creación de bases de datos. Seguridad lógica: usuarios, permisos y roles. Seguridad física. Backup. Instalación y configuración. Microsoft SQL Server es un sistema de gestión de base de datos relacional (RDBMS) desarrollado por Microsoft. Su principal lenguaje de consulta es Transact-SQL, una aplicación de las normas Structured Query Language (SQL). Características de Microsoft SQL Server: - SQL Server se ofrece en dos ediciones principales, las dos de pago: - Enterprise Edition: para aplicaciones que necesitan rendimiento en memoria, seguridad y alta disponibilidad críticos. - Standard Edition: ofrece funciones de base de datos completas para aplicaciones y datos de nivel medio. - SQL Server ha estado tradicionalmente disponible solo para [sistemas operativos](https://es.wikipedia.org/wiki/Sistema_operativo) [Windows](https://es.wikipedia.org/wiki/Windows) de Microsoft, pero desde 2016 está disponible para [GNU/Linux](https://es.wikipedia.org/wiki/GNU/Linux). - Soporte de transacciones. - Soporta procedimientos almacenados. - Incluye también un potente entorno integrado de administración (SQL Server Management Studio) muy potente. - Permite trabajar en modo cliente-servidor, donde la información y datos se alojan en el servidor y las terminales o clientes de la red sólo acceden a la información. **Creación de bases de datos** Se Necesita el permiso CREATE DATABASE en la base de datos master, o bien los permisos CREATE ANY DATABASE o ALTER ANY DATABASE. Para la creación de una base de datos se tienen dos opciones: - A través de SQL Server Management Studio - Transact-SQL En línea de comandos utilizando la instrucción de "Create database NombreBaseDatos". En esta instrucción se puede especificar: - Fichero principal de datos (solo se permite uno) con sus características como pueden ser: nombre del archivo, tamaño, tamaño máximo, factor de crecimiento,... Los archivos de datos contienen datos y otros objetos, como tablas, índices, procedimientos almacenados y vistas. - Ficheros LOG ON, que son los ficheros de registro de transacciones de la base de datos. Se puede también especificar sus características. Los archivos de registro contienen la información necesaria para recuperar todas las transacciones de la base de datos. Después se puede especificar una gran variedad de opciones. **Seguridad lógica** [Usuarios] En SQL Server nos encontramos con tres niveles o capas en los cuales podemos gestionar la seguridad: 1. Nivel de servidor: en él podemos gestionar quién tiene acceso al servidor y quién no. Para tener acceso al servidor se debe tener un inicio de sesión (login) asignado, y a éste se le asigna los roles o funciones que puede realizar sobre el servidor. 2. Nivel de base de datos. Para que un login tenga acceso a una base de datos, tenemos que crear en ella un usuario (user). Se debe crear un usuario en cada una de las bases de datos a las que queramos que acceda un login. 3. Nivel de objeto: que un usuario tenga acceso a una base de datos no quiere decir que tenga acceso a todo su contenido, ni a cada uno de los objetos que la componen. Para que esto ocurra tendremos que irle concediendo o denegando permisos sobre cada uno de los objetos que la componen. Para crear un inicio de sesión: CREATE LOGIN nombre\_login Para crear usuarios de bases de datos: Si se omite FOR LOGIN, el nuevo usuario de base de datos se asignará al inicio de sesión de SQL Server con el mismo nombre. La cláusula WITHOUT LOGIN crea un usuario que no se asigna a ningún inicio de sesión de SQL Server. Un esquema es una colección de objetos de la BBDD cuyo propietario puede ser una entidad de seguridad de nivel de base de datos (un usuario de base de datos, un rol de base de datos o un rol de aplicación). Los objetos creados dentro de un esquema son propiedad del esquema. La propiedad de los objetos incluidos en el esquema puede transferirse a cualquier entidad de seguridad de nivel de base de datos, pero el propietario del esquema siempre mantiene el permiso CONTROL en los objetos del esquema. El espacio de nombres de los objetos tendrá la siguiente forma: servidor.basededatos.esquema.objeto El esquema dbo es el esquema predeterminado de cada base de datos y pertenece a la cuenta de usuario dbo. Los usuarios creados con el comando CREATE USER de Transact-SQL tendrán como esquema predeterminado dbo sino se indica un esquema. Eso no significa que esos usuarios tengan los mismos permisos que el usuario dbo. Los permisos sobre los objetos del esquema o sobre el esquema completo se tendrán que otorgar para cada usuario. [Permisos] El sistema de permisos o privilegios del SGDB se encarga de controlar las operaciones que se pueden realizar sobre el servidor, sobre las bases de datos, sobre los esquemas o sobre los objetos de la base de datos por parte de las diferentes entidades de seguridad. Por tanto, es posible, gestionar la asignación de privilegios a nivel del servidor, de la base de datos, del esquema o directamente a nivel del objeto. Los derechos se heredan de forma jerárquica. SQL Server gestiona los privilegios con tres tipos de palabras claves: - GRANT: para otorgar permisos: - Permisos de servidor: se podrán otorgar a un rol de servidor o a un inicio de sesión (login). Ejemplos: ALTER ANY DATABASE, CREATE ANY DATABASE, SHUTDOWN,... - Permisos de Base de datos, permisos de esquemas y los permisos de objetos y los permisos de aplicación: se podrán otorgar a un usuario de la base de datos, a un rol de la base de datos o a un rol de aplicación. - Permisos de bases de datos BACKUP DATABASE, CREATE DATABASE, CREATE ROLE, CREATE TABLE,..), se otorgarán permisos sobre la base de datos. - Permisos de esquema sobre el esquema (CONTROL, INSERT, UPDATE, SELECT,...). - Permisos de objetos (DELETE, INSERT, EXECUTE, SELECT, UPDATE) sobre los objetos de la base de datos. Y todos ellos se podrán otorgar con WITH GRANT OPTION para permitir a esas entidades de seguridad otorgar esos mismos permisos sobre otras entidades de seguridad. Permisos de Servidor: GRANT permission \[ ,\...n \] TO \ \[ ,\...n \] \[ WITH GRANT OPTION \] \[ AS \ \] Permisos de Base de datos: GRANT \ \[ ,\...n \] TO \ \[ ,\...n \] \[ WITH GRANT OPTION \] \[ AS \ \] Permisos de esquema: GRANT permission \[ ,\...n \] ON SCHEMA :: schema\_name TO \ \[ ,\...n \] \[ WITH GRANT OPTION \] \[ AS granting\_principal \] Permisos de Objeto: GRANT \ \[ ,\...n \] ON \[ OBJECT :: \]\[ schema\_name. \] object\_name \[ ( column\_name \[ ,\...n \] ) \] TO \ \[ ,\...n \] \[ WITH GRANT OPTION \] \[ AS \ \] - REVOKE: para quitar permisos previamente otorgados con GRANT. - DENY: para prohibir permisos, aunque estos estuvieran otorgados previamente. [Roles] Los roles son los conjuntos de permisos. Existen a tres niveles distintos: - [Rol de servidor]: para ayudar a administrar los permisos de un servidor.  Los permisos de servidor se organizan jerárquicamente. Los permisos que mantienen estos roles de nivel de servidor se pueden propagar a los permisos de base de datos. Existen dos tipos de roles en el nivel de servidor: - Roles fijos de servidor: están predefinidos en el servidor. Por ejemplo: - sysadmin: Pueden realizar cualquier actividad en el servidor. - serveradmin: Pueden cambiar las opciones de configuración de todo el servidor y apagar el servidor. - Roles de servidor: Se puede crear roles de servidor definidos por el usuario y agregarles permisos de nivel de servidor. Para crear un rol, se utilizará: CREATE SERVER ROLE nombre\_rol Para añadir un miembro a un rol de servidor: ALTER SERVER ROLE nombre\_rol ADD MEMBER entidad\_seguridad\_nivel\_servidor; entidad\_seguridad\_nivel\_servidor = {inicio de sesion \| rol de servidor} Para borrarlo con DROP MEMBER. - [Rol de base de datos]: se aplican sobre toda la base de datos. Existen dos tipos de roles en el nivel de base de datos: - Los roles fijos de base de datos que están predefinidos en la base de datos - db\_owner: Puede realizar todas las actividades de configuración y mantenimiento en la base de datos y también pueden eliminar la base de datos en SQL Server. - Los roles de base de datos definidos por el usuario y que el usuario puede crear. Se crea el rol: CREATE ROLE nombre\_rol; Para añadir un miembro a un rol de base de datos: ALTER ROLE nombre\_rol ADD MEMBER {usuario de dase de datos \| rol de base de datos}; Para borrarlo con DROP MEMBER. - [Rol de aplicación]: permite que los usuarios puedan acceder a ciertos objetos de la base de datos a través de una aplicación externa. Los roles de aplicación no contienen miembros. Siempre es preferible asignar los permisos a los roles y posteriormente asignar los roles a los usuarios. Con una estructura como esta, la adición y la modificación de permisos o de usuarios son más sencillas. **Seguridad física** Las copias de seguridad y la restauración utilizándolas son la única forma de proteger los datos de nuestra base de datos SQL Server. Tipos de copias de seguridad (backups): - [Completa]: Este es el tipo de copia de seguridad más común que incluye todo, incorporando objetos, datos de tablas del sistema y transacciones que ocurren durante la copia de seguridad. Con una copia de seguridad completa, se puede restaurar su base de datos al estado original en el que realizó la copia de seguridad. A medida que la base de datos aumenta de tamaño, las copias de seguridad completas requieren más tiempo para finalizar y más espacio de almacenamiento. Para las bases de datos grandes, será recomendable complementar las copias de seguridad completas con una serie de copias de seguridad diferenciales. - [Diferencial]: se basa en la copia de seguridad de datos completa anterior más reciente. Una copia de seguridad diferencial registra solo los datos que han cambiado después de la última copia de seguridad completa. La copia de seguridad completa en la que se basa una diferencial se denomina base de la diferencial. La creación de una copia de seguridad diferencial es mucho más rápida que crear una copia de seguridad completa. Esto facilita la realización de copias de seguridad frecuentes, lo que reduce el riesgo de pérdida de datos. No obstante, antes de restaurar una copia de seguridad diferencial, debe restaurar su base. A medida que se incrementa el tamaño de las copias de seguridad diferenciales, la restauración de una copia de seguridad diferencial aumentará significativamente el tiempo necesario para restaurar una base de datos. Se recomienda que realice una nueva copia de seguridad completa a intervalos definidos para establecer una nueva base diferencial para los datos. Por ejemplo, cada semana podría realizar una copia de seguridad completa de toda la base de datos (es decir, una copia de seguridad completa de la base de datos) seguida de una serie de copias de seguridad diferenciales de la base de datos realizadas periódicamente durante la semana. - [Registro de transacciones]. Este tipo de copia de seguridad realizará una copia adicional de seguridad de todas las transacciones que se hayan producido desde la última copia de seguridad completa. Esto capturará toda la información de la transacción, tanto DML como también DDL, que haya ocurrido en la base de datos. Con una copia de seguridad del registro de transacciones, se puede restaurar una base de datos a un punto particular en el tiempo. Para hacer una copia de seguridad de una base de datos con éxito, la ubicación del archivo y la configuración que se desee es solo un primer paso. Una vez logrado el mismo se, querrá poder replicar este proceso una y otra vez, automáticamente y en horario y a tiempo. Programar copias de seguridad automatizadas es fundamental para garantizar la continuidad de la base de datos y reducir el esfuerzo manual requerido para lograrlo. Para hacer copias de seguridad podremos utilizar: - SQL Server Management Studio (SSMS) - Transact-SQL (T-SQL) - SQL Server Agent: Es un servicio que se ejecuta en SQL Server y que permite programar tareas automatizadas, como la realización de copias de seguridad. Con Transact-SQL (T-SQL) [Completa] Para hacer copia de seguridad completa se utiliza la sentencia: BACKUP DATABASE y especifique lo siguiente: - El nombre de la base de datos de la que se va a realizar una copia de seguridad. - El dispositivo de copia de seguridad en el que se escribe la copia de seguridad de base de datos completa. Para realizar la restauración: RESTORE DATABASE especificando el dispositivo de copia de seguridad desde el que se restaurará la copia de seguridad completa de la base de datos y se utilizará la cláusula NORECOVERY si va a aplicar una copia de seguridad del registro de transacciones o una copia de seguridad diferencial de la base de datos después de restaurar la copia de seguridad completa de la base de datos. Si no se realiza con RECOVERY. Las operaciones de RESTORE pueden ir con: - RECOVERY: Indica a la operación de restauración que revierta las transacciones no confirmadas. Después del proceso de recuperación, la base de datos está preparada para ser utilizada. - NORECOVERY: Indica a la operación de restauración que no revierta las transacciones no confirmadas. [Diferencial] Para hacer la copia diferencial: BACKUP DATABASE con la cláusula WITH DIFFERENTIAL: BACKUP DATABASE database\_name TO \ WITH DIFFERENTIAL Para la restauración, después de haber realizado la restauración completa con RESTORE DATABASE y la cláusula NORECOVERY se debe recuperar la última copia que se hizo diferencial. Se ejecuta RESTORE DATABASE especificando el dispositivo de copia de seguridad desde el que se restaurará la copia de seguridad diferencial de la base de datos y con la cláusula NORECOVERY, si dispone de copias de seguridad del registro de transacciones que deban aplicarse después de que se restaure la copia de seguridad diferencial de la base de datos. En caso contrario, especifique la cláusula RECOVERY. [Registro de transacciones] Para realizar la copia se utiliza: BACKUP LOG por cada fichero log que tengamos: Las copias de seguridad deben restaurarse en el mismo orden en el que se crearon. La recuperación se realizará: Por cada copia del registro, se hace un RESTORE LOG, especificando el dispositivo desde donde se recuperará la copia y la cláusula NORECOVERY para los registros intermedios RESTORE LOG database\_name FROM \ WITH NORECOVERY; cuando se trate del último registro se puede aplicar la cláusula RECOVERY RESTORE LOG \ FROM \ WITH RECOVERY; pero también se puede aplicar con NORECOVERY pero luego hacer una RECOVER DATABASE con la cláusula RECOVERY: RESTORE LOG \ FROM \ WITH NORECOVERY; RESTORE DATABASE \ WITH RECOVERY; Nos tenemos que asegurar de colocar las copias de seguridad de las bases de datos en una ubicación física o un dispositivo independiente de los archivos de la base de datos. Si la unidad física que almacena las bases de datos no funciona correctamente o se bloquea, la capacidad de recuperación depende de la capacidad de acceder a la unidad o el dispositivo remoto independiente que almacenó las copias de seguridad para realizar una restauración. **Instalación y configuración de SQL Server** Puede variar según la versión, la edición de SQL Server y la plataforma donde se desea instalar. Pasos previos a la instalación: - Revisar las características admitidas en las distintas ediciones y versiones de SQL Server para determinar cuáles son las que mejor se adaptan a sus necesidades. - Revisar los requisitos de instalación de hardware y software para SQL Server. - Seleccionar el tipo de licencia entre las dos que ofrece SQL Server. Instalación: Existen diferentes posibilidades: - Instalación de SQL Server mediante la GUI del Asistente para instalación que se inicia desde setup.exe: el Asistente para la instalación proporciona un único árbol de características para la instalación de todos los componentes de SQL Server para que no tenga que instalarlos individualmente.  - Instalación de los componentes por separado: Motor de base de datos de SQL Server, Replicación de SQL Server... - Instalación de SQL Server desde el símbolo del sistema: Para realizar la instalación desde el símbolo del sistema, abra un símbolo del sistema administrativo y navegue hasta la ubicación donde se encuentra setup.exe setup.exe /\[Option\] /\[Option\] = {value} Final del formulario Principio del formulario Principio del formulario Final del formulario Principio del formulario Final del formulario