SQL Server Aplicado

Primer Semestre 2010Rocío Contreras Aguila

Renombrando Base de Datos Para quitar una base de datos, utilice DROP

DATABASE.

Para cambiar el nombre de una base de datos, utilice sp_renamedb, pero recuerde que para esto la base de datos a renombrar tiene que tener activa la opción “single user”.

Renombrando Base de Datos Si desea comprobar el empleo de esta

sentencia realice la siguiente secuencia de instrucciones desde el Analizador deConsultas: verifique la base de datos Prueba1 (creada la

clase pasada) no este seleccionada en el Administrador Empresarial, para asegurarse haga clic izquierdo en Databases,

luego ingrese al Analizador de Consultas con una cuenta de administrador (Windows authentication) o con la cuenta sa

Renombrando Base de Datos/* Comprueba la presencia de Prueba1 */Use MasterGOSelect name From SysDatabasesGO

Renombrando Base de Datos El resultado sería:

Name master tempdb model msdb pubs Northwind Prueba3 Prueba1 Prueba2

Renombrando Base de Datos/* Para renombrar active la opción Single

User en la Base de datos a renombrar */Sp_DBOption ‘Prueba3’, ‘Single User’,

‘True’GO El resultado sería:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The database is now single usuario.

Renombrando Base de Datos/* En este instante la base de datos puede

ser renombrada */Sp_RenameDB ‘Prueba3’, ‘NuevoNombre’GO

Renombrando Base de Datos /* Compruebando el correcto

renombrado, luego retire la opción single usuario de la base de datos */

Select Name From SysDatabasesGOSp_DBOption 'NuevoNombre', 'Single

Usuario', 'True'GO

Empleo de Comandos DDLL (Data Definition Language) Las tablas son objetos compuestos por una

estructura (conjunto de columnas) que almacenan información interrelacionada (filas) acerca de algún objeto en general.

Las tablas se definen para los objetos críticos de una base de datos, por ejemplo CLIENTES y su estructura estaría conformada por cada uno de los atributos que se requieran de los clientes para poder obtener información de ellos, como por ejemplo: nombres, direcciones, teléfonos, celular, ruc, etc.

Empleo de Comandos DDLL (Data Definition Language) Cada uno de estos atributos tiene un tipo de

dato definido y además la tabla debe permitir asegurar que cada código de producto es único en la misma, para asegurarse de no almacenar la información del mismo cliente dos veces.

Las tablas suelen estar relacionadas entre sí, para facilitar el hecho de consultas entre múltiples tablas.

Empleo de Comandos DDLL (Data Definition Language) Podemos distinguir los siguientes tipos de

tablas:

Tablas del Sistema Tablas del Usuario

Tablas del Sistema La información usada por SQL Server y sus

componentes son almacenadas en tablas especiales denominadas como tablas del sistema.

Estas tablas no deben alterarse directamente por el usuario Si desea obtener información almacenada en las tablas del sistema debe usar: Información de la vista esquema (schema view). Procedimientos Almacenados de sistema. Instrucciones Transact-SQL y funciones. SQL-DMO. Catálogo de funciones API.

Tablas del Sistema Las tablas del sistema almacenan

información, llamada Metadata, acerca del sistema y de los objetos de las bases de datos. Todas las tablas del sistema comienzan con el prefijo SYS.

Ejemplo:SELECT * FROM SYSUSUARIOS

Tablas del Usuario Permanentes

Son las tablas donde se almacena la información que los usuarios utilizan para sus operaciones. Esta información existirá hasta que se elimine explícitamente.

Temporales

Estas son tablas similares a las permanentes que se graban en tempdb, y son eliminadas automáticamente cuando ya no son usadas.

Hay dos tipos de tablas temporales, locales y globales, difieren una de la otra en susnombres, su visibilidad y su ámbito de vida.

Tablas del Usuario Tablas Temporales Locales. El primer

carácter del nombre de #, su visibilidad es solamente para la conexión actual del usuario y son eliminadas cuando el usuario se desconecta.

Tablas Temporales Globales. Su nombre comienza con ##, su visibilidad es para cualquier usuario, y son eliminadas luego que todos los usuarios que la referencian se desconectan del SQL Server.

Creación de tablas Cuando se crea una tabla debe asignarle un

nombre a la misma, un nombre a cada columna además de un tipo de datos y de ser necesaria una longitud.

Adicional a las características antes mencionadas, SQL Server nos brinda la posibilidad de implementar columnas calculadas, definiéndolas como fórmulas.

Los nombres de las columnas deben ser únicos en la tabla

Creación de tablas Consideraciones al crear tablas

billones de tablas por base de datos 1024 columnas por tabla 8060 es el tamaño máximo de registro (sin

considerar datos image, text y ntext) Al momento de definir una columna se puede

especificar si la columna soporta o no valores NULL.

Creación de tablas Para crear tablas debe utilizar la sentencia

CREATE TABLE, cuya sintaxis es la siguiente:

CREATE TABLE <Nombre de Tabla>( Nom_Columna1 Tipo_de_Dato [NULL l

NOT NULL],Nom_Columna2 Tipo_de_Dato [NULL l NOT

NULL],Nom_Columna3 As formula ...)GO

Creación de tablas También puede crear sus tablas desde el

Administrador Empresarial, para ello extienda la carpeta Tablas de la base de datos donde creará la tabla, haga clic derecho y seleccione Nueva Tabla.

Ejercicio 1 Poblar la Base de datos CEMENTERIO, con al

menos 3 registros por tabla.

Entidades de seguridad (motor de base de datos)

• Las entidades de seguridad son entidades que pueden solicitar recursos de SQL Server.

• El ámbito de influencia de una entidad de seguridad depende del ámbito de su definición: Windows, servidor o base de datos; y de si la entidad de seguridad es indivisible o es una colección.

• Un Inicio de sesión de Windows es un ejemplo de entidad de seguridad indivisible y un Grupo de Windows es un ejemplo de una del tipo colección.

• Toda entidad de seguridad tiene un identificador de seguridad (SID).

Tipos de Entidad• Entidades de seguridad a nivel de Windows– Inicio de sesión del dominio de Windows

– Inicio de sesión local de Windows

• Entidad de seguridad de SQL Server– Inicio de sesión de SQL Server

• Entidades de seguridad a nivel de bases de datos– Usuario de base de datos

– Función de base de datos

– Función de aplicación

Usuarios

• El inicio de sesión sa de SQL Server es una entidad de seguridad del servidor. Se crea de forma predeterminada cuando se instala una instancia. – En SQL Server 2005 y SQL Server 2008, la base de datos

predeterminada de sa es master.

• Todos los usuarios de una base de datos pertenecen a la función public de la base de datos. Cuando a un usuario no se le han concedido ni denegado permisos de un elemento que puede protegerse, el usuario hereda los permisos de ese elemento concedidos a public.

• Todas las bases de datos incluyen dos entidades que aparecen como usuarios en las vistas de catálogo: INFORMATION_SCHEMA y sys. SQL Server necesita estas dos entidades. No son entidades de seguridad y no se pueden modificar ni quitar.

Usuarios• El usuario de una base de datos es una entidad de

seguridad de la base de datos. Cada usuario de una base de datos es miembro de la función public.

• Cuando se crea una base de datos, ésta incluye de forma predeterminada un usuario guest. Los permisos concedidos al usuario guest se aplican a todos los usuarios que no disponen de una cuenta en la base de datos.

• No se puede quitar el usuario guest, pero se puede deshabilitar revocando su permiso CONNECT. El permiso CONNECT se puede revocar ejecutando REVOKE CONNECT FROM GUEST dentro de cualquier base de datos que no sea master ni tempdb.

Usuario guest

Cuando se crea una base de datos, ésta incluye de forma predeterminada un usuario guest. Los permisos concedidos al usuario guest se aplican a todos los usuarios que no disponen de una cuenta en la base de datos.

No se puede quitar el usuario guest, pero se puede deshabilitar revocando su permiso CONNECT. El permiso CONNECT se puede revocar ejecutando REVOKE CONNECT FROM GUEST dentro de cualquier base de datos que no sea master ni tempdb.

Funciones de aplicación Una función de aplicación es una entidad de

seguridad de base de datos que permite que una aplicación se ejecute con sus propios permisos de usuario.

Puede utilizar las funciones de aplicación para permitir el acceso a datos específicos únicamente a aquellos usuarios que se conecten a través de una aplicación concreta.

Funciones de aplicación A diferencia de las funciones de base de

datos, las funciones de aplicación no contienen miembros y están inactivas de manera predeterminada.

Las funciones de aplicación funcionan con ambos modos de autenticación.

Funciones de aplicación Las funciones de aplicación se habilitan empleando

sp_setapprole, que requiere una contraseña.

Debido a que las funciones de aplicación son una entidad de seguridad de la base de datos, sólo pueden obtener acceso a otras bases de datos mediante los permisos que se conceden en dichas bases de datos a guest.

Por tanto, cualquier base de datos en la que se haya deshabilitado guest no será accesible para las funciones de aplicación de otras bases de datos.

Descripción del cambio de contexto El contexto de ejecución está determinado por

el usuario o el inicio de sesión conectado a la sesión o que ejecuta (llama) un módulo.

Este contexto establece la identidad con la que se comprueban los permisos para ejecutar instrucciones o realizar acciones.

Descripción del cambio de contexto En SQL Server, el contexto de ejecución se

puede cambiar a otro usuario o inicio de sesión si se ejecuta la instrucción EXECUTE AS, o bien si se especifica la cláusula EXECUTE AS en un módulo.

Después del cambio de contexto, SQL Server comprueba los permisos con el inicio de sesión y el usuario de dicha cuenta, en lugar de hacerlo con la persona que llama al módulo o a la instrucción EXECUTE AS.

Descripción del cambio de contexto El inicio de sesión de SQL Server o del usuario

de la base de datos se suplanta durante el resto de la sesión o de la ejecución del módulo, o bien hasta que el cambio de contexto se revierta de forma explícita.

Cambio explícito de contexto El contexto de ejecución de una sesión o un módulo

se puede cambiar de forma explícita si se especifica un nombre de usuario o de inicio de sesión en una instrucción EXECUTE AS. La suplantación sigue activa hasta que se produce uno de los siguientes eventos:

Se quita la sesión.

Se cambia el contexto a otro inicio de sesión o usuario.

El contexto se revierte al contexto de ejecución anterior.

Cambio explícito de contexto en el servidor Para cambiar el contexto de ejecución en el

servidor, utilice la instrucción EXECUTE AS LOGIN = 'login_name'.

El nombre de inicio de sesión debe ser visible como entidad de seguridad principal en sys.server_principals y el usuario que llama a la instrucción debe contar con el permiso IMPERSONATE en el nombre de inicio de sesión especificado.

Cambio explícito de contexto en el servidor El ámbito de la suplantación, cuando el

contexto de ejecución se establece en el servidor, es el siguiente: El token de inicio de sesión de login_name se

autentica en la instancia de SQL Server y es válido en dicha instancia.

Se aplican los permisos de servidor y la pertenencia a funciones de login_name.

Cambio explícito de contexto en el servidor Utilice la instrucción REVERT para volver al

contexto anterior. El usuario que llama a la instrucción REVERT debe estar incluido en la misma base de datos donde se ha producido la suplantación.

Ejemplo (by msdn.microsoft.com) En el siguiente ejemplo, François Ajenstat,

administrador de bases de datos de Adventure Works Cycles, desea ejecutar la instrucción DBCC CHECKDB en la base de datos AdventureWorksDW, pero no dispone de permisos en el nivel de base de datos para realizar la operación. Sin embargo, sí dispone de permisos IMPERSONATE en el usuario dan1, una cuenta que incluye el permiso necesario.

Ejemplo Cuando François se conecta a la base de

datos AdventureWorksDW, el contexto de ejecución se asigna a su token de seguridad de usuario. Los permisos para ejecutar instrucciones se comprueban en las entidades de seguridad principales y secundarias del token del usuario. Puesto que no dispone de los permisos necesarios para ejecutar la instrucción DBCC CHECKDB, ejecuta las instrucciones siguientes.

Ejemplo

Descripción del contexto de ejecución El contexto de ejecución está determinado por

el usuario o el inicio de sesión que está conectado a la sesión o que está ejecutando (llamando) un módulo.

Establece la identidad para la que se comprueban los permisos para ejecutar instrucciones o realizar acciones.

Descripción del contexto de ejecución El contexto de ejecución está representado

por un par de tokens de seguridad: un token de inicio de sesión y un token de usuario.

Los tokens identifican las entidades de seguridad primaria y secundaria para las que se comprueban los permisos y el origen utilizado para autenticar el token.

Descripción del contexto de ejecución Un inicio de sesión que se conecta a una

instancia de SQL Server tiene un token de inicio de sesión y uno o más tokens de usuario en función del número de bases de datos a la que tiene acceso la cuenta.

Tokens de seguridad de usuario e inicio de sesión

Un token de seguridad para un usuario o un inicio de sesión está formado por: Una entidad de seguridad de servidor o base de

datos como identidad primaria

Una o varias entidades de seguridad como identidades secundarias

Cero o más autenticadores

Los privilegios y permisos de las identidades primaria y secundaria

Tokens Las entidades de seguridad son los individuos,

grupos y procesos que pueden solicitar los recursos de SQL Server.

Las entidades de seguridad se clasifican por su ámbito de influencia: nivel de Windows, nivel de SQL Server o nivel de base de datos.

Tokens Los autenticadores son entidades de

seguridad, certificados o claves asimétricas que avalan la autenticidad del token.

A menudo, el autenticador de un token es la instancia de SQL Server.

Tokens Un token de inicio de sesión tiene validez en

toda la instancia de SQL Server. Contiene las identidades primaria y secundaria para las que se comprueban los permisos de nivel de servidor y cualquier permiso de nivel de base de datos asociado a estas identidades.

La identidad primaria es el propio inicio de sesión. La identidad secundaria incluye permisos heredados de reglas y grupos.

Tokens Un token de usuario sólo es válido para una

base de datos específica. Contiene las identidades primarias y secundarias para las que se comprueban los permisos de nivel de base de datos.

La identidad primaria es el propio usuario de base de datos. La identidad secundaria incluye permisos heredados de funciones de bases de datos.

Tokens Los tokens de usuario no contienen miembros

de función de servidor y no respetan los permisos de nivel de servidor para las identidades del token incluidas las que se conceden a la función public de nivel de servidor.

Tokens Si se crea explícitamente una cuenta de inicio

de sesión o usuario de SQL Server, el Id. de inicio de sesión o usuario creado para esa cuenta se utiliza como la identidad primaria en el token de inicio de sesión o usuario.

Los miembros de la función fija de servidor sysadmin siempre utilizan dbo como identidad primaria de su token de usuario.

Tokens Cuando una entidad de seguridad tiene

acceso implícito a una instancia de SQL Server o acceso a una base de datos mediante los permisos CONTROL SERVER, la identidad primaria del token de inicio de sesión es la función public predeterminada.

La identidad primaria del token de usuario es public

Ejemplo SELECT principal_id, sid, name, type, usage

FROM dbo.sys.login_token;

Creación de Usuarios Asumimos que existe el correspondiente inicio

de sesión de SQL Server. (explicado en diapositivas superiores)

En SQL Server Management Studio, abra el Explorador de objetos y expanda la carpeta Bases de datos.

Expanda la base de datos en la que se va a crear el usuario de la misma. Haga clic con el botón secundario en la carpeta Seguridad, seleccione Nuevo y, a

continuación, haga clic en Usuario. En la página General, escriba un nombre para el usuario en el cuadro Nombre de

usuario. En el cuadro Nombre de inicio de sesión, escriba el nombre de un inicio de

sesión de SQL Server para asignarlo al usuario de la base de datos. Haga clic en Aceptar.

Creación de Usuarios Para crear un usuario de base de datos mediante

Transact-SQL En el Editor de consultas, conéctese a la base de

datos en la que se va a crear el usuario de la base de datos; para ello, ejecute el siguiente comando Transact-SQL:

USE <database name> GO

Cree el usuario ejecutando el siguiente comando de Transact-SQL: CREATE USER <new user name> FOR LOGIN <login

name> ; GO

Ejemplouse Master CREATE DATABASE CEMENTERIOGO exec sp_addlogin 'usrPrueba', 'usrPruebaPWD' GO Use CEMENTERIO exec sp_grantdbaccess 'usrPrueba' GO exec sp_addrolemember

'db_owner','usrPrueba‘ GO

Funciones de nivel de servidor Para administrar con facilidad los permisos en

el servidor, SQL Server proporciona varias funciones, que son las entidades de seguridad que agrupan a otras entidades de seguridad. Las funciones son como los grupos del sistema operativo Microsoft Windows.

Las funciones de nivel de servidor también se denominan funciones fijas de servidor porque no se pueden crear nuevas funciones de nivel de servidor. Las funciones de nivel de servidor se aplican a todo el servidor en lo que respecta a su ámbito de permisos.

Funciones de nivel de servidor

Ejercicio Cree un usuario con privilegios, he intente

iniciar sesion con él en el SQL.