SEGURIDAD Y PROTECCION DE UN MOTOR DE BASE DE DATOS

Motor de base de datos de SQL ServerSQL Server 2008

El siguiente aporte se construyó con la finalidad de ayudar a todas las personas que deseen aprender SEGURIDAD Y PROTECCION DE UN MOTOR DE BASE DE DATOS en SQL ya que no encontré navegando algo que me convenciera espero que les ayude si hay sugerencias escribirme que no tengo duda de poder resolverlo.

Se tratara los siguientes puntos:

Seguridad Amenazas Vulnerabilidad Identidad y control de acceso Implementación segura

El Motor de base de datos es el servicio principal para almacenar, procesar y proteger datos. El Motor de base de datos proporciona acceso controlado y procesamiento de transacciones rápido para cumplir con los requisitos de las aplicaciones consumidoras de datos más exigentes de su empresa.Use Motor de base de datos para crear bases de datos relacionales para el procesamiento de transacciones en línea o datos de procesamiento analítico en línea. Esto incluye la creación de tablas para almacenar datos y objetos de base de datos (p.ej., índices, vistas y procedimientos almacenados) para ver, administrar y proteger datos. Puede usar SQL Server Management Studio para administrar los objetos de bases de datos y SQL Server Profiler para capturar eventos de servidor.

Información general sobre seguridad

3.- Mitigación de amenazas y vulnerabilidades (motor de base de datos)Aunque SQL Server incluye varios mecanismos de seguridad, todo sistema tiene características que se podrían aprovechar con fines malintencionados. Esta página contiene vínculos que le ayudarán a encontrar la información que necesita en relación con las amenazas y vulnerabilidades en SQL Server Database Engine (Motor de base de datos de SQL Server) y cómo puede eliminarlas.

Matriz de amenazas y vulnerabilidad (motor de base de datos)

Aunque SQL Server incluye varios mecanismos de seguridad, todo sistema tiene características que se podrían aprovechar con fines malintencionados. Cualquier característica que exponga datos u otra información puede constituir un riesgo si se implementa incorrectamente.

Aunque cualquier característica podría representar un riesgo, no todos los riesgos son iguales. Algunos requieren un cambio de procedimiento, otros de configuración y algunos de código. En las tablas siguientes se explican los riesgos y los pasos proactivos que se pueden dar para disminuirlos.

Amenazas y vulnerabilidades de los procesos

Amenaza o vulnerabilidad Definición Mitigación

Directivas de seguridad

Una directiva de seguridad es un registro de los procesos y procedimientos que debe seguir una organización para evitar, realizar un seguimiento y responder a las amenazas de seguridad. Contiene directivas relacionadas con el acceso

Cree, revise, distribuya y mantenga una directiva de seguridad efectiva. Para obtener más información acerca de cómo crear una directiva de seguridad, vea Proteger SQL Server.

adecuado a los sistemas, la aplicación de revisiones y los firewalls, así como mecanismos de prevención antivirus.

Principio de "privilegios mínimos"

Según el principio de "privilegios mínimos", un sistema sólo debería permitir el nivel de acceso necesario a un objeto protegible. Además, el acceso sólo debería estar habilitado para quienes tienen una necesidad directa y sólo durante un tiempo especificado. Las aplicaciones pueden estar codificadas para proporcionar más acceso del necesario y las cuentas podrían tener demasiado acceso.

Revise e implemente la seguridad de acuerdo con el principio de privilegios mínimos. Para obtener más información sobre cómo desarrollar aplicaciones que utilizan los conceptos de privilegios mínimos, vea el tema relativo a procedimientos recomendados en un entorno con privilegios mínimos en MSDN.

Boletines de seguridad

Microsoft publica información de seguridad tan pronto como se comprueba y se prueba en distintas plataformas. Las organizaciones que no están al corriente de estos boletines ponen en peligro sus sistemas al no implementar las instrucciones de seguridad adecuadas.

Revise y haga un seguimiento de los boletines de seguridad de SQL Server. Para obtener más información, vea la búsqueda de boletines de seguridad de Microsoft en TechNet.

Amenazas y vulnerabilidades de la plataforma

Amenaza o vulnerabilidad Definición Mitigación

El sistema no está actualizado (no se han aplicado las actualizaciones de software)

Microsoft publica actualizaciones de software para mejorar la seguridad de SQL Server. Si no se siguen o se aplican estas actualizaciones de software, el sistema será más vulnerable a los ataques.

Revise y aplique todos los Service Packs y todas las revisiones en cuanto estén disponibles. Para obtener más información, consulte la página de descargas en SQL Server TechCenter.

Vulnerabilidades de seguridad de los puertos de red

La red es la principal vía de acceso para los ataques contra SQL Server. Si los puertos estándar están abiertos a Internet, se favorecerán los ataques.

Utilice un firewall en el servidor si está expuesto a Internet y utilice la herramienta Administrador de configuración de SQL Server para establecer la configuración de la red. Considere también la posibilidad de

utilizar SSL (Capa de sockets seguros) para aumentar aún más la seguridad. Para obtener más información acerca de los firewalls y SQL Server, vea Cómo configurar Firewall de Windows para el acceso al motor de base de datos. Para obtener más información acerca de cómo cambiar la configuración de la red, vea Administrador de configuración de SQL Server. Para obtener más información acerca de cómo utilizar SSL en SQL Server, vea Cifrar conexiones a SQL Server.

Configuración incorrecta de las cuentas de servicio

Las cuentas de servicio de SQL Server suelen tener más acceso a la plataforma o a la red del que necesitan.

Las cuentas de servicio de SQL Server deberían funcionar bajo el principio de privilegios mínimos y deberían tener contraseñas seguras. Para obtener más información acerca de las cuentas de servicio, vea Configurar cuentas de servicio de Windows. Para obtener más información acerca de las contraseñas, vea Contraseñas seguras.

Área expuesta demasiado grande

Las características y capacidades de SQL Server pueden estar expuestas cuando no es necesario.

Use el Administrador de configuración de SQL Server y la Administración basada en directiva para controlar las características y los demás componentes. Para obtener más información, vea Descripción de la configuración del área expuesta.

Procedimientos almacenados innecesarios habilitados

Algunos procedimientos almacenados extendidos permiten el acceso al sistema operativo o al registro.

No habilite los procedimientos almacenados que permiten el acceso al sistema operativo o al registro si no es completamente necesario. Para obtener más información, vea Descripción de la configuración del área expuesta.

Amenazas y vulnerabilidades de autenticación

Amenaza o vulnerabilidad Definición Mitigación

Contraseñas no seguras

Las contraseñas sencillas están expuestas a los ataques por fuerza bruta o de diccionario.

Utilice siempre contraseñas seguras y complejas. Para obtener más información, vea Contraseñas seguras. También vea las opciones CHECK_POLICY y

CHECK_EXPIRATION en las instrucciones CREATE LOGIN (Transact-SQL) y ALTER LOGIN (Transact-SQL).

Cuentas de usuario sin auditar

Los usuarios (entidades de seguridad) a menudo cambian de puesto o dejan la organización. Si no se cambia el acceso a una cuenta de usuario, se puede seguir teniendo acceso al sistema con el nivel de permisos anterior.

Las cuentas de usuario se deberían auditar con frecuencia para asegurarse de que se tiene habilitado el acceso adecuado a los servidores y los objetos de las bases de datos. Para obtener más información acerca de cómo auditar el acceso a SQL Server, vea Supervisar los registros de errores.

Amenazas y vulnerabilidades de programación

Amenaza o vulnerabilidad Definición Mitigación

Inyección de código SQL

Consiste en incrustar una consulta malintencionada en una legítima.

Para obtener más información acerca de cómo actuar ante los ataques por inyección de código SQL, vea Inyección de código SQL.

Contraseñas incrustadas

Algunas aplicaciones guardan las cadenas de conexión en el programa o en archivos de configuración.

No guarde las contraseñas ni la información confidencial de conexión en un programa, en el registro ni en un archivo de configuración. Para obtener más información, vea Directiva de contraseñas.

Amenazas y vulnerabilidades de acceso a los datos

Amenaza o vulnerabilidad Definición Mitigación

Cifrado aplicado incorrectamente

El cifrado ofusca los datos o la información de conexión en SQL Server. No cifrar los datos cuando es necesario o hacerlo cuando no lo es, supone un riesgo y una complejidad innecesarios.

Conozca e implemente correctamente el cifrado en SQL Server. Para obtener más información, vea Cifrado de SQL Server.

Certificados aplicados incorrectamente

Los certificados son un mecanismo para comprobar la autenticación. SQL Server puede utilizar los certificados para

Conozca e implemente correctamente los certificados de SQL Server. Para obtener más información, vea Certificados y claves asimétricas de

diversos propósitos, desde las conexiones a los datos. El uso inadecuado de los certificados autofirmados y los períodos de validación extendidos reducen la eficacia de la seguridad global.

SQL Server.

Claves de SQL Server sin copias de seguridad

Una instancia de SQL Server y las bases de datos que contiene pueden tener claves que se utilizan para distintos propósitos de seguridad. Esto incluye el cifrado.

Se deberían hacer copias de seguridad de las claves del servidor (también conocidas como claves maestras de servicio) y de las claves de las bases de datos, y guardarlas en lugar seguro. También se deberían cambiar periódicamente. Para obtener más información, vea SQL Server y claves de cifrado de base de datos (motor de base de datos).

Inyección de código SQLLa inyección de código SQL es un ataque en el cual se inserta código malicioso en las cadenas que posteriormente se pasan a una instancia de SQL Server para su análisis y ejecución. Todos los procedimientos que generan instrucciones SQL deben revisarse en busca de vulnerabilidades de inyección de código, ya que SQL Server ejecutará todas las consultas recibidas que sean válidas desde el punto de vista sintáctico. Un atacante cualificado y con determinación puede manipular incluso os datos con parámetros.

La forma principal de inyección de código SQL consiste en la inserción directa de código en variables especificadas por el usuario que se concatenan con comandos SQL y se ejecutan. Existe un ataque menos directo que inyecta código dañino en cadenas que están destinadas a almacenarse en una tabla o como metadatos. Cuando las cadenas almacenadas se concatenan posteriormente en un comando SQL dinámico, se ejecuta el código dañino.

El proceso de inyección consiste en finalizar prematuramente una cadena de texto y anexar un nuevo comando. Como el comando insertado puede contener cadenas adicionales que se hayan anexado al mismo antes de su ejecución, el atacante pone fin a la cadena inyectada con una marca de comentario "--". El texto situado a continuación se omite en tiempo de ejecución.

En el siguiente script se muestra una sencilla inyección de código SQL. El script crea una consulta SQL concatenando cadenas no modificables con una cadena especificada por el usuario:

var Shipcity;ShipCity = Request.form ("ShipCity");

var sql = "select * from OrdersTable where ShipCity = '" + ShipCity + "'";

Se le pide al usuario que escriba el nombre de una ciudad. Si el usuario especifica Redmond, la consulta ensamblada por el script presenta un aspecto similar al siguiente:

SELECT * FROM OrdersTable WHERE ShipCity = 'Redmond'

Sin embargo, suponga que el usuario especificase lo siguiente:

Redmond'; drop table OrdersTable--

En este caso, la siguiente consulta la ensambla el script:

SELECT * FROM OrdersTable WHERE ShipCity = 'Redmond';drop table OrdersTable--'

El punto y coma (;) denota el final de una consulta y el principio de otra. El guión doble (--) indica que el resto de la línea actual es un comentario y debe omitirse. Si el código modificado es sintácticamente correcto, el servidor lo ejecutará. Cuando SQL Server procese esta instrucción, SQL Server seleccionará en primer lugar todos los registros de OrdersTable donde ShipCity sea Redmond. A continuación, SQL Server quitará OrdersTable.

Siempre y cuando el código SQL inyectado sea sintácticamente correcto, no será posible detectar alteraciones mediante programación. Por ello, debe validar todos los datos especificados por el usuario y revisar cuidadosamente el código que ejecute comandos SQL construidos en el servidor que utilice. Las prácticas recomendadas de codificación se describen en las siguientes secciones de este tema.

4.- Identidad y control de acceso (motor de base de datos)

SQL Server incluye varios métodos y herramientas que permiten configurar la seguridad para que los usuarios, los servicios y las otras cuentas puedan tener acceso al sistema. Esta página contiene vínculos

que le ayudarán a encontrar la información que necesita para trabajar con entidades de seguridad (usuarios y cuentas de inicio de sesión), funciones (grupos de entidades de seguridad), objetos que pueden protegerse (protegibles) y permisos.

Entidades de seguridad (motor de base de datos)

Las entidades de seguridad son entidades que pueden solicitar recursos de SQL Server. Igual que otros componentes del modelo de autorización de SQL Server, las entidades de seguridad se pueden organizar en jerarquías. 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).

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 deSQL 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

Inicio de sesión sa de SQL Server

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. Es un cambio de comportamiento con respecto a versiones anteriores de SQL Server.

Función public de base de datos

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.

INFORMATION_SCHEMA y sys

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.

Inicios de sesión de SQL Server basados en certificados

Las entidades de seguridad de servidor con nombres incluidos entre signos de número dobles (##) son exclusivamente para uso interno del sistema. Las siguientes entidades de seguridad se crean a partir de certificados cuando se instala SQL Server y no deben eliminarse.

• ##MS_SQLResourceSigningCertificate##• ##MS_SQLReplicationSigningCertificate##• ##MS_SQLAuthenticatorCertificate##• ##MS_AgentSigningCertificate##• ##MS_PolicyEventProcessingLogin##• ##MS_PolicySigningCertificate##• ##MS_PolicyTsqlExecutionLogin##

Cliente y servidor de base de datos

Por definición, un cliente y un servidor de base de datos son entidades de seguridad y se pueden proteger. Estas entidades se pueden autenticar mutuamente antes de establecer una conexión de red segura. SQL Server admite el protocolo de autenticación Kerberos, que define cómo interactúan los clientes con un servicio de autenticación de red.

Para obtener más información acerca de la implementación en SQL Server de la compatibilidad con Kerberos, vea Autenticación Kerberos y SQL Server.

Funciones de nivel de servidor

Para administrar con facilidad los permisos en el servidor, SQL Server proporciona varias funciones, que son entidades de seguridad que agrupan a otras entidades de seguridad. Las funciones son como los grupos del sistema operativo Microsoft Windows.

Las funciones fijas de servidor se proporcionan por comodidad y por motivos de compatibilidad con versiones anteriores. Se recomienda asignar permisos más concretos siempre que sea posible.

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.

Puede agregar inicios de sesión de SQL Server, cuentas de Windows y grupos de Windows a las funciones de nivel de servidor. Cada miembro de una función fija de servidor puede agregar otros inicios de sesión a esa misma función.

En la tabla siguiente se muestran las funciones de nivel de servidor y sus capacidades.

Nombre de la función de nivel de servidor

Descripción

sysadmin Los miembros de la función fija de servidor sysadmin pueden realizar cualquier actividad en el servidor.

serveradmin Los miembros de la función fija de servidor serveradmin pueden cambiar las opciones de configuración en el servidor y cerrar el servidor.

securityadmin Los miembros de la función fija de servidor securityadmin administran los inicios de sesión y sus propiedades. Administran los permisos de servidor GRANT, DENY y REVOKE. También administran los permisos de base de datos GRANT, DENY y REVOKE. Asimismo, pueden restablecer contraseñas para inicios de sesión de SQL Server.

Nota de seguridadLa capacidad de conceder acceso al Database Engine (Motor de base de datos) y configurar permisos de usuario permite a la administración de seguridad asignar la mayoría de los permisos de servidor. La función securityadmin se debe tratar como equivalente a la función sysadmin.

processadmin Los miembros de la función fija de servidor processadmin pueden

finalizar los procesos que se ejecutan en una instancia de SQL Server.Setupadmin Los miembros de la función fija de servidor setupadmin pueden agregar

y quitar los servidores vinculados.Bulkadmin Los miembros de la función fija de servidor bulkadmin pueden ejecutar

la instrucción BULK INSERT.Diskadmin La función fija de servidor diskadmin se utiliza para administrar

archivos de disco.Dbcreator Los miembros de la función fija de servidor dbcreator pueden crear,

modificar, quitar y restaurar cualquier base de datos.public Cada inicio de sesión de SQL Server pertenece a la función de servidor

public. Cuando a una entidad de seguridad de servidor no se le han concedido ni denegado permisos específicos en un objeto que puede protegerse, el usuario hereda los permisos concedidos a la función public en ese objeto. Asigne solamente permisos públicos en cualquier objeto cuando desee que el objeto esté disponible para todos los usuarios.

Para obtener información específica sobre los permisos de las funciones de nivel de servidor, vea Permisos de las funciones fijas de servidor (motor de base de datos).

Trabajar con funciones de nivel de servidor

En la tabla siguiente se explican los comandos, las vistas y las funciones que se utilizan para trabajar con funciones de nivel de servidor.

Característica Tipo Descripción

sp_helpsrvrole (Transact-SQL) Metadatos Devuelve una lista de funciones de nivel de servidor.

sp_helpsrvrolemember (Transact-SQL) Metadatos

Devuelve información acerca de los miembros de una función de nivel de servidor.

sp_srvrolepermission (Transact-SQL) Metadatos Muestra los permisos de una función de nivel

de servidor.

IS_SRVROLEMEMBER (Transact-SQL) Metadatos

Indica si un inicio de sesión de SQL Server es miembro de la función de nivel de servidor especificada.

sys.server_role_members (Transact-SQL) Metadatos Devuelve una fila por cada miembro de cada

función de nivel de servidor.sp_addsrvrolemember (Transact-SQL) Comando Agrega un inicio de sesión como miembro de

una función de nivel de servidor.sp_dropsrvrolemember (Transact-SQL)

Comando Quita un inicio de sesión de SQL Server o un usuario o grupo de Windows de una función

de nivel de servidor.

Funciones en el nivel de base de datos

Para administrar con facilidad los permisos en las bases de datos, SQL Server proporciona varias funciones, que son las entidades de seguridad que agrupan a otras entidades de seguridad. Son como los grupos del sistema operativo Microsoft Windows. Las funciones del nivel de base de datos se aplican a toda la base de datos en lo que respecta a su ámbito de permisos.

Existen dos tipos de funciones de nivel de base de datos en SQL Server: las funciones fijas de base de datos, que están predefinidas en la base de datos, y las funciones flexibles de base de datos, que pueden crearse.

Las funciones de base de datos fijas se definen en el nivel de base de datos y existen en cada una de ellas. Los miembros de las funciones de base de datos db_owner y db_securityadmin pueden administrar los miembros de las funciones de base de datos fijas. Sin embargo, sólo los miembros de la función de base de datos db_owner pueden agregar miembros a la función de base de datos fija db_owner. Hay también algunas funciones fijas de base de datos con fines especiales en la base de datos msdb.

Puede agregar cualquier cuenta de la base de datos y otras funciones de SQL Server a las funciones del nivel de base de datos. Cada miembro de una función de base de datos fija puede agregar otros inicios de sesión a esa misma función.

ImportanteNo agregue funciones flexibles de la base de datos como miembros de funciones fijas. Esto podría habilitar un aumento de privilegios no deseado.

En la tabla siguiente se muestran las funciones fijas del nivel de base de datos y sus capacidades. Estas funciones existen en todas las bases de datos.

Nombre de función de nivel de base de datos

Descripción

db_owner Los miembros de la función de base de datos fija db_owner pueden realizar todas las actividades de configuración y mantenimiento en la base de datos y también pueden quitar la base de datos.

db_securityadmin Los miembros de la función de base de datos fija db_securityadmin pueden modificar la pertenencia a funciones y administrar permisos. Si se agregan entidades de seguridad a esta función, podría habilitarse un aumento de privilegios no deseado.

db_accessadmin Los miembros de la función de base de datos fija db_accessadmin pueden agregar o quitar el acceso a la base de datos para inicios de sesión de Windows, grupos de Windows e inicios de sesión de SQL Server.

db_backupoperator Los miembros de la función de base de datos fija db_backupoperator pueden crear copias de seguridad de la base de datos.

db_ddladmin Los miembros de la función de base de datos fija db_ddladmin pueden ejecutar cualquier comando del lenguaje de definición de datos (DDL) en una base de datos.

db_datawriter Los miembros de la función de base de datos fija db_datawriter pueden agregar, eliminar o cambiar datos en todas las tablas de usuario.

db_datareader Los miembros de la función de base de datos fija db_datareader pueden leer todos los datos de todas las tablas de usuario.

db_denydatawriter Los miembros de la función de base de datos fija db_denydatawriter no pueden agregar, modificar ni eliminar datos de tablas de usuario de una base de datos.

db_denydatareader Los miembros de la función de base de datos fija db_denydatareader no pueden leer datos de las tablas de usuario dentro de una base de

Funciones de msdb

La base de datos msdb contiene las funciones con fines especiales que se muestran en la tabla siguiente.

Nombre de función de msdb Descripción

db_ssisadmin

db_ssisoperator

db_ssisltduser

Los miembros de estas funciones de base de datos pueden administrar y utilizar SSIS. Las instancias de SQL Server que se actualizan desde una versión anterior podrían contener una versión anterior de la función cuya denominación se realizaba utilizando Servicios de transformación de datos (DTS) en lugar de SSIS. Para obtener más información, vea Usar funciones de Integration Services.

dc_admin

dc_operator

dc_proxy

Los miembros de estas funciones de base de datos pueden administrar y utilizar el recopilador de datos. Para obtener más información, vea Seguridad del recolección de datos.

PolicyAdministratorRole Los miembros de la función de base de datos db_PolicyAdministratorRole pueden realizar todas las actividades de mantenimiento y configuración en las condiciones y directivas de Administración basada en directiva. Para obtener más información, vea Administrar servidores mediante administración basada en directivas.

ServerGroupAdministratorRole

ServerGroupReaderRole

Los miembros de estas funciones de la base de datos pueden administrar y utilizar grupos de servidores registrados. Para obtener más información, vea Crear grupos de servidores.

Trabajar con funciones del nivel de base de datos

En la tabla siguiente se explican los comandos, las vistas y las funciones que se utilizan para trabajar con funciones del nivel de base de datos.

Característica Tipo Descripción

sp_helpdbfixedrole (Transact-SQL)

MetadatosDevuelve la lista de las funciones de base de datos fijas.

sp_dbfixedrolepermission (Transact-SQL)

MetadatosMuestra los permisos de una función de base de datos fija.

sp_helprole (Transact-SQL) MetadatosDevuelve información acerca de las funciones de la base de datos actual.

sp_helprolemember (Transact-SQL)

MetadatosDevuelve información acerca de los miembros de una función de base de datos actual.

sys.database_role_members (Transact-SQL)

MetadatosDevuelve una fila por cada miembro de cada función de base de datos.

IS_MEMBER (Transact-SQL) MetadatosIndica si el usuario actual es miembro del grupo de Microsoft Windows o de la función de base de datos de SQL Server especificados.

CREATE ROLE (Transact-SQL) ComandoCrea una nueva función de base de datos en la base de datos actual.

ALTER ROLE (Transact-SQL) Comando Cambia el nombre de una función de base de datos.

DROP ROLE (Transact-SQL) Comando Quita una función de base de datos.

sp_addrole (Transact-SQL) ComandoCrea una nueva función de base de datos en la base de datos actual.

sp_droprole (Transact-SQL) Comando Quita una función de base de datos de la base de

datos actual.

sp_addrolemember (Transact-SQL)

Comando

Agrega un usuario de base de datos, una función de base de datos, un inicio de sesión de Windows o un grupo de Windows a una función de base de datos en la base de datos actual.

sp_droprolemember (Transact-SQL)

ComandoQuita una cuenta de seguridad de una función de SQL Server de la base de datos actual.

Función pública de la base de datos

Todos los usuarios de una base de datos pertenecen a la función pública de la base de datos. Cuando a un usuario no se le han concedido ni denegado permisos específicos para un objeto que puede protegerse, el usuario hereda los permisos concedidos a la función pública para ese objeto.

Credenciales (motor de base de datos)Una credencial es un registro que contiene la información de autenticación (credenciales) necesaria para conectarse a un recurso situado fuera de SQL Server. Esta información la utiliza SQL Server internamente. La mayoría de las credenciales incluyen un nombre de usuario y una contraseña de Windows.

La información almacenada en una credencial permite al usuario que se ha conectado a SQL Server mediante la autenticación de SQL Server obtener acceso a recursos situados fuera de la instancia del servidor. Cuando el recurso externo es Windows, el usuario se autentica como el usuario de Windows especificado en la credencial. Se puede asignar una única credencial a varios inicios de sesión de SQL Server. Sin embargo, un inicio de sesión de SQL Server sólo se puede asignar a una credencial.

Las credenciales del sistema se crean de forma automática y se asocian a extremos específicos. Los nombres de las credenciales del sistema comienzan por dos signos de número (##).

Sintaxis

CREATE CREDENTIAL credential_name WITH IDENTITY = 'identity_name' [ , SECRET = 'secret' ] [ FOR CRYPTOGRAPHIC PROVIDER cryptographic_provider_name ]

/*credential_name

Especifica el nombre de la credencial que se va a crear. credential_name no puede comenzar por el signo de número (#). Las credenciales del sistema comienzan por ##. IDENTITY ='identity_name'

Especifica el nombre de la cuenta que se utilizará para conectarse fuera del servidor. SECRET ='secret'

Especifica el secreto necesario para la autenticación de salida. Esta cláusula es opcional.FOR CRYPTOGRAPHIC PROVIDER cryptographic_provider_name*/

Ejemplos

--En el ejemplo siguiente se crea la credencial denominada AlterEgo.-- La credencial contiene el usuario de Windows JoelPC y una contraseña.

CREATE CREDENTIAL AlterEgo WITH IDENTITY = 'Mary5', SECRET = '<EnterStrongPasswordHere>';GO

--En el ejemplo siguiente se utiliza una cuenta creada previamente denominada --User1OnEKM en un módulo EKM a través de las herramientas de administración --de EKM, con un tipo de cuenta y una contraseña básicos. La cuenta sysadmin --del servidor crea una credencial que se utiliza para conectar a la cuenta --de EKM y la asigna a la cuenta User1 de SQL Server:

CREATE CREDENTIAL CredentialForEKMWITH IDENTITY='User1OnEKM', SECRET='<EnterStrongPasswordHere>' FOR CRYPTOGRAPHIC PROVIDER MyEKMProvider;GO/* Modify the login to assign the cryptographic provider credential */ALTER LOGIN User1ADD CREDENTIAL CredentialForEKM;/* Modify the login to assign a non cryptographic provider credential */ ALTER LOGIN User1WITH CREDENTIAL = AlterEgo;GO

ALTER CREDENTIAL (Transact-SQL)

Sintaxis

ALTER CREDENTIAL credential_name WITH IDENTITY = 'identity_name' [ , SECRET = 'secret' ]

/*Argumentos

credential_name

Especifica el nombre de la credencial que se va a modificar.*/IDENTITY ='identity_name'

-- Especifica el nombre de la cuenta que se va a usar al conectarse fuera del servidor.SECRET ='secret'

-- Especifica el secreto requerido para la autenticación de salida. secret es opcional.

Ejemplos

/*A. Cambiar la contraseña de una credencial

En el siguiente ejemplo se cambia el secreto almacenado en una credencial denominada Saddles. La credencial contiene el inicio de sesión de Windows RettigB y su contraseña. La nueva contraseña se agrega a la credencial mediante la cláusula SECRET.*/

ALTER CREDENTIAL Saddles WITH IDENTITY = 'RettigB', SECRET = 'sdrlk8$40-dksli87nNN8';GO

/*B. Quitar la contraseña de una credencial

En el ejemplo siguiente se quita la contraseña de una credencialdenominada Frames. La credencial contiene el inicio de sesión deWindows Aboulrus8 y una contraseña. Después de ejecutar la instrucción, la credencial tendrá una contraseña NULL porque no se especifica la opción SECRET.*/

ALTER CREDENTIAL Frames WITH IDENTITY = 'Aboulrus8';GO

DROP CREDENTIAL (Transact-SQL)

Sintaxis

DROP CREDENTIAL credential_name

/*Argumentos

credential_name

Se trata del nombre de la credencial que se va a quitar del servidor.*/

Ejemplos

--En el ejemplo siguiente se quita la credencial denominada Saddles.

DROP CREDENTIAL Saddles;GO

Asegurables

Los asegurables son los recursos cuyo acceso es regulado por el sistema de autorización del SQL Server Database Engine (Motor de base de datos de SQL Server). Algunos asegurables pueden estar incluidos en otros, con lo que se crean jerarquías anidadas denominadas "ámbitos" que a su vez se pueden asegurar. Los ámbitos asegurables son servidor, base de datos y esquema.

Servidor

Incluye los asegurables siguientes:

Extremo Inicio de sesión Base de datos

Ámbito de Securable: Base de datos

Incluye los asegurables siguientes:

Usuario Función Función de aplicación Ensamblado Tipo del mensaje Ruta Servicio Enlace de servicio remoto Catálogo de texto Certificado Clave asimétrica Clave simétrica Contrato Esquema

Ámbito de Securable: Esquema

Incluye los asegurables siguientes:

Tipo Colección de esquemas XML Objeto

Objetos

Los elementos siguientes son miembros de la clase de objeto:

Agregado Restricción Función Procedimiento Cola Estadística Sinónimo Tabla Vista

Esquemas (motor de base de datos)Un esquema es un contenedor que contiene tablas, vistas, procedimientos, etc. Se encuentra dentro de una base de datos, que a su vez está dentro de un servidor. Estas

entidades se acomodan como cajas anidadas. El servidor es la caja más externa y el esquema la más interna. Contiene todos los asegurables que se mencionan a continuación. Pero no puede contener otra caja.

Asegurable que debe estar dentro de un esquema ClaseTipo TYPEColección de esquemas XML XML SCHEMA COLLECTIONTabla OBJECTVista OBJECTProcedimiento OBJECTFunción OBJECTAgregado OBJECTRestricción OBJECTSinónimo OBJECTCola OBJECTEstadística OBJECT

Todos los asegurables de un esquema específico deben tener un nombre exclusivo. El nombre completo de un asegurable contenido por un esquema incluye el nombre del esquema que lo contiene. Por lo tanto, un esquema es también un espacio de nombres.

Proteger archivos de datos y de registro

El SQL Server establece los permisos de acceso a archivos en los archivos de datos físicos y de registro de cada base de datos en cuentas específicas. Estos permisos impiden que se alteren los archivos si se encuentran en un directorio con permisos abiertos. Por ejemplo, si no se han establecido los permisos y para los permisos del sistema operativo en el directorio de la base de datos se ha seleccionado Control total para todos, cualquier cuenta que tenga acceso a ese directorio podrá eliminar o modificar los archivos de la base de datos aunque no tenga permisos de SQL Server para modificar la base de datos.

Los permisos de acceso a archivos se establecen durante cualquiera de las siguientes operaciones de base de datos: crear, adjuntar, separar, modificar para agregar un nuevo archivo, realizar copias de seguridad o restaurar.

Cadenas de propiedadCuando varios objetos de base de datos obtienen acceso unos a otros de forma secuencial, la secuencia se denomina cadena. Aunque estas cadenas no existen de manera

independiente, cuando SQL Server recorre los eslabones de una cadena, SQL Server evalúa los permisos de los objetos que la componen de manera distinta a si se estuviese obteniendo acceso a los objetos por separado. Estas diferencias tienen implicaciones importantes en lo que se refiere a administrar la seguridad.

Las cadenas de propiedad permiten administrar el acceso a varios objetos, por ejemplo, a varias tablas, estableciendo permisos en un objeto, como una vista. El encadenamiento de propiedad también ofrece una pequeña mejora en el rendimiento en los escenarios que permiten la omisión de comprobaciones de permisos.

Cómo se comprueban los permisos en una cadena

Cuando se obtiene acceso a un objeto mediante una cadena, SQL Server primero compara al propietario del objeto con el propietario del objeto de llamada. Este es el eslabón anterior de la cadena. Si ambos objetos tienen el mismo propietario, los permisos del objeto de referencia no se evalúan.

Ejemplo de encadenamiento de propiedad

En la siguiente ilustración, la vista July2003 es propiedad de Mary. Ella le ha concedido a Alex permisos en la vista. Él no tiene ningún otro permiso en los objetos de base de datos de esta instancia. ¿Qué ocurre cuando Alex selecciona la vista?

Fojura 11111111111111111

1. Alex ejecuta SELECT * en la vista July2003. SQL Server comprueba los permisos de la vista y confirma que Alex tiene permiso para seleccionarla.

2. La vista July2003 requiere información de la vista SalesXZ. SQL Server comprueba la propiedad de la vista SalesXZ. Debido a que esta vista tiene el mismo propietario (Mary) que la vista que la llama, los permisos de la vista SalesXZ no se comprueban. Se devuelve la información requerida.

3. La vista SalesXZ requiere información de la vista InvoicesXZ. SQL Server comprueba la propiedad de la vista InvoicesXZ. Debido a que esta vista tiene el mismo propietario que el objeto anterior, los permisos de la vista InvoicesXZ no se comprueban. Se devuelve la información requerida. Hasta este momento, todos los elementos de la secuencia tienen un único propietario (Mary). Esto se denomina cadena de propiedad continua.

4. La vista InvoicesXZ requiere información de la vista AcctAgeXZ. SQL Server comprueba la propiedad de la vista AcctAgeXZ. Como el propietario de esta vista es diferente del propietario del objeto anterior (Sam, y no Mary), se recupera toda la información sobre permisos de esta vista. Si la vista AcctAgeXZ tiene permisos que permiten que Alex tenga acceso, se devolverá información.

5. La vista AcctAgeXZ requiere información de la tabla ExpenseXZ. SQL Server comprueba la propiedad de la tabla ExpenseXZ. Como el propietario de esta tabla es diferente del propietario del objeto anterior (Joe, y no Sam), se recupera toda la información sobre permisos de esta tabla. Si la tabla ExpenseXZ tiene permisos que permiten que Alex tenga acceso, se devuelve información.

6. Cuando la vista July2003 intenta recuperar información de la tabla ProjectionsXZ, el servidor primero hace comprobaciones para ver si está habilitado el encadenamiento entre las bases de datos Database 1 y Database 2. Si el encadenamiento entre bases de datos está habilitado, el servidor comprobará la propiedad de la tabla ProjectionsXZ. Debido a que esta vista tiene el mismo propietario que la vista de llamada (Mary), los permisos de esta tabla no se comprueban. Se devuelve la información solicitada

Encadenamiento de propiedad entre bases de datos

SQL Server se puede configurar para permitir el encadenamiento de propiedad entre bases de datos específicas o entre todas las bases de datos de una única instancia de SQL Server. De manera predeterminada, el encadenamiento de propiedad entre bases de datos está deshabilitado y no se debe habilitar a menos que sea específicamente necesario.

Posibles amenazas

Las cadenas de propiedad resultan muy útiles a la hora de administrar los permisos de una base de datos, pero asumen que los propietarios de objetos preverán todas las consecuencias de las decisiones que tomen para conceder permisos de un elemento protegible. En la ilustración anterior, Mary es la propietaria de la mayoría de los objetos subyacentes de la vista July 2003. Debido a que Mary tiene el derecho de hacer que los objetos de los que es propietaria se encuentren accesibles para otros usuarios, SQL Server asume que, si Mary concede el acceso a la primera vista de una cadena, ha tomado la decisión consciente de compartir las vistas y las tablas a las que hace referencia. En la vida real, es posible que

esto no sea una suposición válida. Las bases de datos de producción son mucho más complejas que la de la ilustración, y los permisos que regulan el acceso a las mismas en muy pocas ocasiones se asignan correctamente a las estructuras administrativas de las organizaciones que las utilizan.

Es importante comprender que los miembros de funciones de la base de datos con amplios privilegios pueden utilizar el encadenamiento de propiedad entre bases de datos para obtener acceso a objetos de bases de datos externas a las suyas. Por ejemplo, si el encadenamiento de propiedad entre bases de datos se habilita entre la base de datos A y la B, un miembro de la función fija de base de datos db_owner de cualquiera de las dos bases de datos puede suplantar su identidad y entrar en la otra. El proceso es simple: Diane (un miembro de db_owner en la base de datos A) crea un usuario Stuart en la base de datos A. Stuart ya existe como usuario en la base de datos B. A continuación, Diane crea un objeto (propiedad de Stuart) en la base de datos A que llama a cualquier objeto propiedad de Stuart de la base de datos B. Como ambos objetos (el que llama y al que llama) pertenecen al mismo usuario, los permisos del objeto de la base de datos B no se comprobarán cuando Diane obtenga acceso a la misma mediante el objeto que ella ha creado.

Permisos (motor de base de datos)

Todos los protegibles de SQL Server tienen permisos asociados que se pueden conceder a una entidad de seguridad. Este tema proporciona la siguiente información:

Convenciones de nomenclatura de permisos Permisos relacionados con elementos protegibles específicos Permisos de SQL Server Algoritmo de comprobación de permiso

Resumen del algoritmo de comprobación de permiso

Comprobar los permisos puede ser complejo. El algoritmo de comprobación de permiso incluye la superposición de la pertenencia a grupos y el encadenamiento de propiedad, tanto el permiso explícito como el implícito, y puede ser afectado por los permisos en las clases

protegibles y que contienen la entidad que se puede proteger. El proceso general del algoritmo es reunir todos los permisos pertinentes. Si no se encuentra ningún bloqueo DENY, el algoritmo busca un permiso GRANT que proporcione el acceso suficiente. El algoritmo contiene tres elementos esenciales, el contexto de seguridad, el espacio del permiso y el permiso requerido.

Contexto de seguridad

Es el grupo de entidades de seguridad al que contribuyen los permisos para la comprobación de acceso. Son los permisos que están relacionados con el inicio de sesión actual o el usuario, a menos que el contexto de seguridad se cambiara a otro inicio de sesión o usuario utilizando la instrucción EXECUTE AS. El contexto de seguridad incluye las entidades de seguridad siguientes:

o El inicio de sesióno El usuarioo Pertenecientes a la funcióno Pertenecientes al grupo Windowso Si se utiliza la firma de módulo, cualquier cuenta de inicio de sesión o de

usuario da cuenta del certificado utilizado para firmar el módulo que el usuario está ejecutando actualmente, así como de los pertenecientes a la función asociados de ese entidad de seguridad.

Espacio del permiso

Es la entidad que hay que proteger y todas las clases a proteger que contiene la entidad a proteger. Por ejemplo, una tabla (una entidad a proteger) está contenida en la clase de esquema a proteger y en la clase de base de datos a proteger. El acceso puede verse afectado por permisos de nivel de tabla, esquema, base de datos y servidor. Para obtener más información, vea Jerarquía de permisos (motor de base de datos).

Permiso necesario

El tipo de permiso que se necesita. Por ejemplo, INSERT, UPDATE, DELETE, SELECT, EXECUTE, ALTER, CONTROL, etc.

El acceso puede requerir varios permisos, como en los ejemplos siguientes:

o Un procedimiento almacenado puede requerir el permiso EXECUTE sobre el procedimiento almacenado y el permiso INSERT sobre varias tablas a las que hace referencia el procedimiento almacenado.

o Una vista de administración dinámica puede requerir los permisos VIEW SERVER STATE y SELECT sobre la vista.

Ejemplos

/*Los siguientes ejemplos muestran cómo recuperar la información sobre permisos.A. Devolviendo la lista completa de los permisos que pueden concederse.

La instrucción siguiente devuelve todo los permisos de Database Engine (Motor de base de datos) utilizando la función fn_builtin_permissions. Para obtener más información, vea sys.fn_builtin_permissions (Transact-SQL).*/

SELECT * FROM fn_builtin_permissions(default);GO

/*B. Devolviendo los permisos de una clase de objetos concreta

También puede utilizar la función fn_builtin_permissions para ver todos los permisos que están disponibles para una categoría protegible. El ejemplo siguiente devuelve permisos de ensamblados.*/

SELECT * FROM fn_builtin_permissions('assembly');GO

/*C. Devolviendo los permisos de un objeto concedidos a la entidad de seguridad que se ejecuta

Puede utilizar fn_my_permissions para devolver una lista de los permisos efectivos que son retenidos por el entidad de seguridad de la llamada sobre un elemento específico que se puede proteger. Para obtener más información, vea fn_my_permissions (Transact-SQL). El ejemplo siguiente devuelve los permisos de un objeto denominado Orders55.*/

SELECT * FROM fn_my_permissions('Orders55', 'object');GO

/*D. Devolviendo los permisos aplicables a un objeto especificado

El ejemplo siguiente devuelve los permisos aplicables a un objeto denominado Yttrium. Observe que la función integrada OBJECT_ID se utiliza para recuperar el identificador del objeto Yttrium.*/

SELECT * FROM sys.database_permissions WHERE major_id = OBJECT_ID('Yttrium');

GO

5.- Desarrollo seguro (motor de base de datos)SQL Server tiene varios mecanismos para mejorar la seguridad del código. Estos mecanismos ayudan a los administradores y los programadores a implementar un entorno seguro para el código.

Firma de módulos (Motor de base de datos)

SQL Server 2008 introdujo la capacidad de firmar módulos en la base de datos como, por ejemplo, procedimientos almacenados, funciones, desencadenadores o ensamblados. Tenga en cuenta que no se pueden firmar los desencadenadores del lenguaje de definición de datos (DDL). Una firma digital es un resumen de datos cifrados con una clave privada del firmante. La clave privada garantiza que la firma digital sea única para su portador o propietario.

Para firmar datos, el firmante resume los datos, los cifra con una clave privada y adjunta el valor resumido y cifrado a los datos. Para verificar la firma, el comprobador utiliza la clave pública del firmante para descifrar el valor resumido y cifrado que se adjunta a los datos. A continuación, el comprobador compara el valor resumido y descifrado con el valor resumido que se ha calculado en los datos complementarios. Es importante que tanto el firmante como el comprobador usen la misma función hash para resumir los datos.

Advertencia

La firma de módulos sólo se debe utilizar para conceder permisos, nunca para denegarlos o revocarlos.

Escenario

Suponga que el acceso a la vista sys.sysprocesses debe controlarse a través del procedimiento almacenado usp_sysprocesses. Los usuarios sólo pueden tener acceso a la información de sys.sysprocesses cuando pasan por el procedimiento usp_sysprocesses. Dado que los objetos de usp_sysprocesses y sys.sysprocesses tienen diferentes propietarios, no se aplica el encadenamiento de propiedad.

Ejemplo

La siguiente script se crea un certificado a partir de un par de claves y se asigna a un nuevo inicio de sesión. Primero se debe crear un par de claves de prueba mediante la herramienta MakeCert

que se incluye con el SDK de .NET Framework. A continuación, se conceden los permisos de selección para la vista sys.sysproceses al inicio de sesión asociado al certificado. Se crea el procedimiento almacenado administrado usp_sysprocesses en la nueva base de datos y se firma con el certificado. Se crea la función SysProcRole y se le concede permisos de ejecución en el procedimiento almacenado usp_sysprocesses. Se crea un usuario de prueba y se agrega a la función SysProcRole. El usuario de prueba realiza una instrucción SELECT en sys.sysprocess y, a continuación, ejecuta el procedimiento almacenado usp_sysprocesses para comparación. Después, la script limpia el entorno de prueba.

use mastergo

-- Create a test database.CREATE DATABASE db_Demogo

-- Create a certificate on the server. A test key pair can be created-- using the MakeCert tool that ships with the .NET Framework SDK.CREATE CERTIFICATE SysProcCert FROM FILE = 'e:\programming\testCert.cer'go

-- Create a login and map it to the certificate.CREATE LOGIN login_SysProcCert FROM CERTIFICATE SysProcCertGo

-- Revoke the connect permission.REVOKE CONNECT SQL FROM login_SysProcCert ;go -- Grant the certificate, through the login, permission to select from sys.sysprocesses view.GRANT VIEW SERVER STATE TO login_SysProcCertgo

-- Create a test login.CREATE LOGIN bob WITH PASSWORD = '<enterStrongPasswordHere>'go

-- Connect to the test database.use db_Demogo

-- Create the master key for the test database (used to protect -- private keys and certificates in the database).CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<enterStrongPasswordHere>'

-- Create a certificate from a private key.CREATE CERTIFICATE SysProcCert FROM FILE = 'e:\programming\testCert.cer'WITH PRIVATE KEY(FILE = 'e:\programming\testCert.pvk', DECRYPTION BY PASSWORD= '<enterStrongPasswordHere>', ENCRYPTION BY PASSWORD='<enterStrongPasswordHere>')

go

-- Create the assembly on the server. The assembly DLL must be signed.CREATE ASSEMBLY SysStoredProceduresFROM 'E:\programming\SysStoredProcs.dll'WITH PERMISSION_SET = SAFEgo

-- Create the managed stored procedure on the server.CREATE PROCEDURE usp_sysprocessesAS EXTERNAL NAME SysStoredProcedures.StoredProcedures.usp_sysprocessesgo

-- Add the signature to the stored procedure.ADD SIGNATURE TO [dbo].[usp_sysprocesses] BY CERTIFICATE SysProcCert WITH PASSWORD = '<enterStrongPasswordHere>'go

-- Create a role.CREATE ROLE SysProcRolego

-- Create a test userCREATE USER bobgo

-- Add the test user to the role.EXEC sp_addrolemember 'SysProcRole', 'bob'go

-- Grant execute permissions on the stored procedure to the new role.GRANT EXECUTE ON [dbo].[usp_sysprocesses] TO SysProcRolego -- Connect as the test user.EXECUTE AS LOGIN = 'bob'use db_Demogo -- User only has permission to see their own processes.SELECT * FROM sys.sysprocessesgo

-- Execute the stored procedure, which has been signed.exec usp_sysprocessesgo

-- REVERTREVERT------------------------------------------------------------------------ Cleanup

use db_Demogo

use master

go

DROP DATABASE db_Demogo

DROP login login_SysProcCertDROP login bobgo

DROP CERTIFICATE SysProcCertgo

A continuación se muestra el código fuente para el procedimiento almacenado usp_sysprocesses, que realiza una instrucción SELECT en la vista sys.sysprocesses. El ensamblado debe firmarse cuando se genera.

Imports SystemImports System.Data.SqlClientImports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures<Microsoft.SqlServer.Server.SqlProcedure()>_ Public Shared Sub usp_sysprocesses() Using connection As New SqlConnection("context connection=true") connection.Open()

Dim command As New SqlCommand("SELECT * FROM sys.sysprocesses", connection) SqlContext.Pipe.ExecuteAndSend(command) End Using End Sub End Class

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. 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. 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 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.

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.

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

En el ejemplo siguiente, Peter Connelly, un administrador de red de AdventureWorks , desea crear una cuenta de inicio de sesión de SQL Server para un nuevo empleado, Jinghao Liuhas. El inicio de sesión de SQL Server de Peter no necesita el mismo permiso en el servidor necesario para crear inicios de sesión de SQL Server, pero tiene el permiso IMPERSONATE para adventure-works\dan1, un inicio de sesión de SQL Server que dispone del permiso de servidor requerido. Cuando Peter se conecta a SQL Server, el contexto de ejecución de la sesión se deriva de su inicio de sesión de SQL Server. Para crear un inicio de sesión de SQL Server, Peter asume temporalmente el contexto de ejecución de adventure-works\dan1. Después crea el inicio de sesión. Finalmente, renuncia a los permisos que ha asumido.

-- Switch execution context to the adventure-works\dan1 login account.EXECUTE AS LOGIN = 'adventure-works\dan1';-- Create the new login account.CREATE LOGIN Jinghao1 WITH PASSWORD = '3KHJ6dhx(0xVYsdf';-- Revert to the previous execution context.

REVERT;

Cambio explícito de contexto en la base de datos

Para cambiar el contexto en la base de datos, utilice la instrucción EXECUTE AS USER = 'user_name'. El nombre del usuario debe existir como entidad de seguridad en sys.database_principals y el usuario que llama a la instrucción debe contar con permisos IMPERSONATE en el nombre de usuario especificado.

El ámbito de la suplantación, cuando el contexto de ejecución se establece en las bases de datos, es el siguiente:

El token de usuario de user_name se autentica en la instancia de SQL Server y es válido en la base de datos actual. Para obtener información sobre la ampliación de la suplantación de usuarios más allá del ámbito de la base de datos actual, vea Extender la suplantación de la base de datos mediante EXECUTE AS.

Se aplican los permisos de base de datos y la pertenencia a funciones de user_name de la base de datos actual. No se aplican los permisos de servidor concedidos de forma explícita a identidades del token de usuario ni mediante pertenencia a funciones.

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

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.

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.

-- EXECUTE AS USER = 'dan1';-- Create a table in dan1's default schemaCREATE TABLE t_NewTable( data nvarchar(100) );go-- Revert to the previous execution context.REVERTgo;

Cambio implícito de contexto

El contexto de ejecución de un módulo, como un procedimiento almacenado, un desencadenador, un cola o un función definida por el usuario, se puede cambiar de forma implícita mediante la especificación de un nombre de usuario o de inicio de sesión en una cláusula EXECUTE AS de la definición del módulo.

Si se especifica el contexto en que se ejecuta el módulo, se puede controlar la cuenta de usuario que utiliza SQL Server para validar permisos en los objetos a los que hace referencia el módulo. Así se consigue una flexibilidad y un control adicionales en la administración de permisos de la cadena de objetos que existe entre los módulos definidos por el usuario y los objetos a los que hacen referencia estos módulos. Los permisos pueden concederse a usuarios únicamente para el propio módulo, sin tener que concederles permisos explícitos para los objetos a los que se hace referencia. Sólo el usuario al que suplanta el módulo necesita disponer de permisos para los objetos a los que dicho módulo tiene acceso.

El nivel de suplantación se determina según el tipo de módulo en que se define la suplantación.

La suplantación en el servidor se puede definir en el siguiente elemento:

Desencadenadores DDL

El ámbito de suplantación en el servidor es el mismo que el definido anteriormente en "Cambio explícito de contexto en el servidor".

La suplantación en la base de datos se puede definir en los siguiente elementos:

Desencadenadores DML Colas Procedimientos almacenados Funciones definidas por el usuario El ámbito de suplantación en la base de datos es el mismo que el definido

anteriormente en "Cambio explícito de contexto en la base de datos". Para obtener más información sobre el cambio implícito de contexto, vea Usar

EXECUTE AS en módulos.

Ejemplo

En el siguiente ejemplo, Mary es la propietaria de la tabla MyTable. Desea que el usuario Scott pueda truncar la tabla, pero Scott no dispone de permisos directos para ella. Por tanto, Mary crea el procedimiento almacenado dbo.usp_TruncateMyTable y concede a Scott permisos EXECUTE para el procedimiento. Cuando Scott ejecuta el procedimiento almacenado, Database Engine (Motor de base de datos) comprueba los permisos para

truncar la tabla como si fuera la propia Mary la que estuviera ejecutando el procedimiento almacenado. Puesto que ella es la propietaria de la tabla, la instrucción se realiza correctamente a pesar de que Scott no dispone de permisos directos en la propia tabla.

CREATE PROCEDURE dbo.usp_TruncateMyTableWITH EXECUTE AS SELF

AS TRUNCATE TABLE MyDB..MyTable;

Ejemplo de Cadenas de propiedad y cambio de contexto

Para ejecutar el código que se deja mas abajo, la seguridad de modo mixto debe estar configurada y la base de datos AdventureWorks debe estar instalada.

Escenario

En este escenario, dos usuarios necesitan cuentas para obtener acceso a los datos de los pedidos de compra almacenados en la base de datos AdventureWorks. Los requisitos son los siguientes:

La primera cuenta (TestManagerUser) debe poder ver todos los detalles de cada pedido de compra.

La segunda cuenta (TestEmployeeUser) debe poder ver el número de los pedidos de compra, la fecha de los pedidos, la fecha de envío, los números de identificación de los productos y los elementos pedidos y recibidos en cada pedido de compra, ordenados por número de pedido de compra, correspondientes a los elementos para los que se han recibido envíos parciales.

El resto de cuentas deben conservar sus permisos.

Para cumplir los requisitos de este escenario, el ejemplo se ha dividido en cuatro partes que describen los conceptos de cadenas de propiedad y cambio de contexto:

1. Configuración del entorno.

2. Creación de un procedimiento almacenado para obtener acceso a datos por pedido de compra.

3. Acceso a los datos mediante un procedimiento almacenado. 4. Restablecimiento del entorno.

1. Configurar el entorno

Use SQL Server Management Studio y el código siguiente para abrir la base de datos AdventureWorks y use la instrucción CURRENT_USER de Transact-SQL para comprobar que el usuario dbo se muestra como contexto.

USE AdventureWorks;GO

SELECT CURRENT_USER AS 'Current User Name';

GO

2222222222222222

Use este código como usuario dbo para crear dos usuarios en el servidor y en la base de datos AdventureWorks.

CREATE LOGIN TestManagerUser WITH PASSWORD = '340$Uuxwp7Mcxo7Khx';GOCREATE USER TestManagerUser FOR LOGIN TestManagerUser WITH DEFAULT_SCHEMA = Purchasing;GO

CREATE LOGIN TestEmployeeUser WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';GOCREATE USER TestEmployeeUser FOR LOGIN TestEmployeeUser;

GO

333333333333333333333333333

Use el código siguiente para cambiar la propiedad del esquema Purchasing a la cuenta TestManagerUser. Esto permite que dicha cuenta use todo el acceso a las instrucciones del lenguaje de manipulación de datos (DML) (por ejemplo, los permisos SELECT y INSERT) en los objetos que contiene. Puesto que no se incluyen los permisos de lenguaje de definición de datos (DDL), a TestManagerUser se le conceden explícitamente derechos en las tablas PurchaseOrderHeader y PurchaseOrderDetail además de la capacidad de crear procedimientos almacenados.

/* Change owner of the Purchasing Schema to TestManagerUser */ALTER AUTHORIZATION ON SCHEMA::Purchasing TO TestManagerUser;GO

/* Grant permissions to TestManagerUser on these objects with GRANT option */GRANT ALL ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderHeader TO TestManagerUser WITH GRANT OPTION;GOGRANT ALL ON OBJECT::AdventureWorks.Purchasing.PurchaseOrderDetail TO TestManagerUser WITH GRANT OPTION;GO/* Note: DML works fine with Schema owner, but not DDL. */GRANT CREATE PROCEDURE TO TestManagerUser WITH GRANT OPTION;

GO

2. Crear un procedimiento almacenado para obtener acceso a los datos

Existen dos modos de permitir a un usuario que cambie los contextos en una base de datos: mediante SETUSER o EXECUTE AS. Para usar la instrucción SETUSER, el autor de la llamada debe ser miembro de la función fija de servidor sysadmin o ser la cuenta dbo. EXECUTE AS requiere permisos IMPERSONATE.

Use la instrucción EXECUTE AS en el código siguiente para cambiar el contexto a TestManagerUser y crear un procedimiento almacenado que muestre únicamente los datos exigidos por TestEmployeeUser. Para cumplir los requisitos, el procedimiento almacenado acepta una variable para el número del pedido de compra y no muestra la información financiera, y la cláusula WHERE limita los resultados a los envíos parciales.

EXECUTE AS LOGIN = 'TestManagerUser'GOSELECT CURRENT_USER AS 'Current User Name';GO

444444444444444444444444444444444444444

/* Note: The user that calls the EXECUTE AS statement must have IMPERSONATE permissions on the target principal */CREATE PROCEDURE usp_ShowWaitingItems @ProductID intASBEGIN SELECT a.PurchaseOrderID, a.OrderDate, a.ShipDate , b.ProductID, b.OrderQty, b.ReceivedQty FROM Purchasing.PurchaseOrderHeader a INNER JOIN Purchasing.PurchaseOrderDetail b ON a.PurchaseOrderID = b.PurchaseOrderID WHERE b.OrderQty > b.ReceivedQty AND @ProductID = b.ProductID ORDER BY b.ProductID ASCEND

GO

5555555555555555555555555555555555555555555555555555

En estos momentos, TestEmployeeUser no tiene acceso a ningún objeto de la base de datos. El código siguiente (todavía en el contexto de TestManagerUser) permite que la cuenta de usuario consulte la información de tabla base mediante el procedimiento almacenado.GRANT EXECUTE ON OBJECT::Purchasing.usp_ShowWaitingItems TO TestEmployeeUser;

GO

El procedimiento almacenado forma parte del esquema de Purchasing, aunque no se especifica explícitamente ningún esquema, porque TestManagerUser se asigna de forma predeterminada al esquema Purchasing. La información del catálogo del sistema se puede utilizar para buscar objetos, tal y como se muestra en el código siguiente.SELECT a.name AS 'Schema' , b.name AS 'Object Name' , b.type AS 'Object Type'FROM sys.schemas a INNER JOIN sys.objects b ON a.schema_id = b.schema_id WHERE b.name = 'usp_ShowWaitingItems';

GO

77777777

Una vez finalizada esta sección del ejemplo, el código vuelve a cambiar el contexto a dbo mediante la instrucción REVERT.REVERT;

GO

88888888

3. Obtener acceso a los datos mediante el procedimiento almacenado

TestEmployeeUser no tiene ningún permiso en los objetos de la base de datos AdventureWorks aparte del inicio de sesión y los derechos asignados a la función de base de datos public. El código siguiente devuelve un error cuando TestEmployeeUser intenta obtener acceso a las tablas base.EXECUTE AS LOGIN = 'TestEmployeeUser'GOSELECT CURRENT_USER AS 'Current User Name';

GO

99999

/* This won't work */SELECT *FROM Purchasing.PurchaseOrderHeader;GOSELECT *FROM Purchasing.PurchaseOrderDetail;

GO

Puesto que los objetos a los que hace referencia el procedimiento almacenado, creados en la última sección, pertenecen a TestManagerUser en virtud de la propiedad de esquema Purchasing, TestEmployeeUser puede tener acceso a las tablas base mediante el procedimiento almacenado. El código siguiente, que todavía usa el contexto TestEmployeeUser, pasa el pedido de compra 952 como un parámetro.EXEC Purchasing.usp_ShowWaitingItems 952

GO

1010101010101010101

4. Restablecer el entorno

El código siguiente usa el comando REVERT para devolver el contexto de la cuenta actual a dbo y, a continuación, restablece el entorno.REVERT;GOALTER AUTHORIZATION ON SCHEMA::Purchasing TO dbo;GODROP PROCEDURE Purchasing.usp_ShowWaitingItemsGODROP USER TestEmployeeUser;GODROP USER TestManagerUser;GODROP LOGIN TestEmployeeUser;GODROP LOGIN TestManagerUser;

GO

1111111111