Download Diapositiva 1 - Apuntes DUOC / FrontPage

SQL Server Aplicado
Primer Semestre 2010
Rocí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 Master
GO
Select name From SysDatabases
GO
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 SysDatabases
GO
Sp_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 TransactSQL:

CREATE USER <new user name> FOR LOGIN <login name> ; GO
Ejemplo
use Master
CREATE DATABASE CEMENTERIO
GO
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.