Tutorial: Cadenas de propiedad y cambio de contexto
En este tutorial se usa un escenario para ilustrar los conceptos de seguridad de SQL Server relacionados con las cadenas de propiedad y el cambio de contexto de usuario. Para obtener más información acerca de las cadenas de propiedad, vea Cadenas de propiedad. Para obtener más información sobre el cambio de contexto, vea Cambio de contexto (motor de base de datos).
[!NOTA]
Para ejecutar el código de este tutorial, la seguridad de modo mixto debe estar configurada y la base de datos AdventureWorks debe estar instalada. Para obtener más información acerca de la seguridad de modo mixto, vea Elegir un modo de autenticación.
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:
Configuración del entorno.
Creación de un procedimiento almacenado para obtener acceso a datos por pedido de compra.
Acceso a los datos mediante un procedimiento almacenado.
Restablecimiento del entorno.
Cada bloque de código incluido en este ejemplo se describe en línea. Para copiar el ejemplo completo, vea Ejemplo completo al final de este tutorial.
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
Para obtener más información acerca de la instrucción CURRENT_USER, vea CURRENT_USER (Transact-SQL).
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';
GO
CREATE USER TestManagerUser
FOR LOGIN TestManagerUser
WITH DEFAULT_SCHEMA = Purchasing;
GO
CREATE LOGIN TestEmployeeUser
WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';
GO
CREATE USER TestEmployeeUser
FOR LOGIN TestEmployeeUser;
GO
Para obtener más información acerca de la instrucción CREATE USER , vea CREATE USER (Transact-SQL). Para obtener más información acerca de la instrucción CREATE LOGIN, vea CREATE LOGIN (Transact-SQL).
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;
GO
GRANT 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
Para obtener más información acerca de la instrucción GRANT, vea GRANT (Transact-SQL). Para obtener más información acerca de los esquemas de base de datos, vea Esquemas (motor de base de datos). Para obtener más información acerca de los procedimientos almacenados, vea Procedimientos almacenados (motor de base de datos).
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. Para obtener más información acerca de estos conceptos, vea EXECUTE AS frente a SETUSER.
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'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* Note: The user that calls the EXECUTE AS statement must have IMPERSONATE permissions on the target principal */
CREATE PROCEDURE usp_ShowWaitingItems @ProductID int
AS
BEGIN
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 ASC
END
GO
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
Para obtener más información acerca de los catálogos del sistema, vea Consultar el catálogo del sistema de SQL Server.
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
Para obtener más información acerca de la instrucción REVERT, vea REVERT (Transact-SQL).
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'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* This won't work */
SELECT *
FROM Purchasing.PurchaseOrderHeader;
GO
SELECT *
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
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;
GO
ALTER AUTHORIZATION
ON SCHEMA::Purchasing TO dbo;
GO
DROP PROCEDURE Purchasing.usp_ShowWaitingItems
GO
DROP USER TestEmployeeUser;
GO
DROP USER TestManagerUser;
GO
DROP LOGIN TestEmployeeUser;
GO
DROP LOGIN TestManagerUser;
GO
Ejemplo completo
En esta sección se muestra el código de ejemplo completo.
[!NOTA]
Este código no incluye los dos errores esperados que demuestran la incapacidad de TestEmployeeUser para seleccionar en las tablas base.
/*
Script: UserContextTutorial.sql
Author: Microsoft
Last Updated: Books Online
Conditions: Execute as DBO or sysadmin in the AdventureWorks database
Section 1: Configure the Environment
*/
USE AdventureWorks;
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* Create server and database users */
CREATE LOGIN TestManagerUser
WITH PASSWORD = '340$Uuxwp7Mcxo7Khx';
GO
CREATE USER TestManagerUser
FOR LOGIN TestManagerUser
WITH DEFAULT_SCHEMA = Purchasing;
GO
CREATE LOGIN TestEmployeeUser
WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';
GO
CREATE USER TestEmployeeUser
FOR LOGIN TestEmployeeUser;
GO
/* 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;
GO
GRANT 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
/*
Section 2: Switch Context and Create Objects
*/
EXECUTE AS LOGIN = 'TestManagerUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
/* Note: The user that calls the EXECUTE AS statement must have IMPERSONATE permissions on the target principal */
CREATE PROCEDURE usp_ShowWaitingItems @ProductID int
AS
BEGIN
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 ASC
END
GO
/* Give the employee the ability to run the procedure */
GRANT EXECUTE
ON OBJECT::Purchasing.usp_ShowWaitingItems
TO TestEmployeeUser;
GO
/* Notice that the stored procedure is located in the Purchasing
schema. This also demonstrates system catalogs */
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
/* Go back to being the dbo user */
REVERT;
GO
/*
Section 3: Switch Context and Observe Security
*/
EXECUTE AS LOGIN = 'TestEmployeeUser'
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
EXEC Purchasing.usp_ShowWaitingItems 952
GO
/*
Section 4: Clean Up Example
*/
REVERT;
GO
ALTER AUTHORIZATION
ON SCHEMA::Purchasing TO dbo;
GO
DROP PROCEDURE Purchasing.usp_ShowWaitingItems
GO
DROP USER TestEmployeeUser;
GO
DROP USER TestManagerUser;
GO
DROP LOGIN TestEmployeeUser;
GO
DROP LOGIN TestManagerUser;
GO
Vea también