Tutorial: Ownership Chains and Context Switching
Se aplica a:
SQL ServerAzure SQL Managed Instance
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.
Nota:
Para ejecutar el código de este tutorial, debe estar configurada la seguridad de modo mixto y la base de datos AdventureWorks2022 debe estar instalada. Para obtener más información sobre la seguridad de modo mixto, consulte 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 AdventureWorks2022 . 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.
Requisitos previos
Para llevar a cabo este tutorial necesita tener SQL Server Management Studio, acceso a un servidor que ejecute SQL Server y una base de datos AdventureWorks2022.
- Instale SQL Server Management Studio.
- Instale SQL Server 2017 Developer Edition.
- Descargue bases de datos de ejemplo AdventureWorks.
Para obtener instrucciones sobre cómo restaurar una base de datos en SQL Server Management Studio, vea Restauración de una base de datos.
1. Configurar el entorno
Use SQL Server Management Studio y el código siguiente para abrir la base de datos AdventureWorks2022 y use la instrucción CURRENT_USER de Transact-SQL para comprobar que el usuario dbo se muestra como contexto.
USE AdventureWorks2022;
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
Para obtener más información sobre la instrucción CURRENT_USER, consulte CURRENT_USER (Transact-SQL).
Use este código como usuario dbo para crear dos usuarios en el servidor y en la base de datos AdventureWorks2022.
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 sobre la instrucción CREATE USER, consulte CREATE USER (Transact-SQL). Para obtener más información sobre la instrucción CREATE LOGIN, consulte 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 e INSERT ) en los objetos que contiene. TestManagerUser también tiene la capacidad de crear procedimientos almacenados.
/* Change owner of the Purchasing Schema to TestManagerUser */
ALTER AUTHORIZATION
ON SCHEMA::Purchasing
TO TestManagerUser;
GO
GRANT CREATE PROCEDURE
TO TestManagerUser
WITH GRANT OPTION;
GO
Para obtener más información sobre la instrucción GRANT, consulte GRANT (Transact-SQL). Para obtener más información sobre los procedimientos almacenados, vea Procedimientos almacenados (motor de base de datos). Para ver un póster de todos los permisos del motor de base de datos, consulte https://aka.ms/sql-permissions-poster.
2. Crear un procedimiento almacenado para obtener acceso a los datos
Para cambiar el contexto dentro de una base de datos, use la instrucción EXECUTE AS. 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'
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
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 sobre la instrucción REVERT, consulte 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 AdventureWorks2022 aparte del inicio de sesión y los derechos asignados al rol de base de datos pública. 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
El error que se devuelve:
Msg 229, Level 14, State 5, Line 6
The SELECT permission was denied on the object 'PurchaseOrderHeader', database 'AdventureWorks2022', schema 'Purchasing'.
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 dboy, 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 AdventureWorks2022;
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 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 AS a
INNER JOIN Purchasing.PurchaseOrderDetail AS 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 AS a
INNER JOIN sys.objects AS 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
Consulte también
Centro de seguridad para el Motor de base de datos de SQL Server y Azure SQL Database
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de