Chuỗi quyền sở hữu và chuyển đổi context (ngữ cảnh)

Hướng dẫn này sử dụng một kịch bản để minh họa cho các khái niệm bảo mật SQL Server liên quan đến các chuỗi quyền sở hữu và chuyển đổi ngữ cảnh người dùng.

 Lưu ý

Để chạy code trong hướng dẫn này, bạn phải có cả hai chế độ bảo mật hỗn hợp được cấu hình và cơ sở dữ liệu AdventureWorks2017 được cài đặt. Để biết thêm thông tin về bảo mật Chế độ hỗn hợp, hãy xem Choose an Authentication Mode.

Scenario (kịch bản)

Trong kịch bản này, hai người dùng cần tài khoản để truy cập dữ liệu đơn đặt hàng được lưu trữ trong cơ sở dữ liệu AdventureWorks2017. Các yêu cầu như sau:

  • Tài khoản đầu tiên (TestManagerUser) phải có thể xem được tất cả các chi tiết trong mỗi đơn đặt hàng.
  • Tài khoản thứ hai (TestEmployeeUser) phải có thể xem được số đơn đặt hàng, ngày đặt hàng, ngày giao hàng, số ID sản phẩm và các mục đã đặt và nhận trên mỗi đơn hàng, theo số đơn đặt hàng, đối với các mục đã nhận được một phần lô hàng.
  • Tất cả các tài khoản khác phải giữ quyền hiện tại của chúng.
    Để đáp ứng các yêu cầu của kịch bản này, ví dụ này được chia thành bốn phần thể hiện các khái niệm về chuỗi quyền sở hữu và chuyển đổi ngữ cảnh:
  1. Cấu hình môi trường.
  2. Tạo một thủ tục lưu trữ để truy cập dữ liệu theo đơn đặt hàng.
  3. Truy cập dữ liệu thông qua các thủ tục lưu trữ.
  4. Đặt lại môi trường.

Mỗi block code trong ví dụ này được giải thích trong dòng. Để sao chép ví dụ hoàn chỉnh, hãy xem Ví dụ hoàn chỉnh ở cuối hướng dẫn này.

Điều kiện tiên quyết

Để hoàn thành hướng dẫn này, bạn cần SQL Server Management Studio, truy cập vào máy chủ đang chạy SQL Server và cơ sở dữ liệu AdventureWorks.

Để được hướng dẫn khôi phục cơ sở dữ liệu trong SQL Server Management Studio, hãy xem Khôi phục cơ sở dữ liệu.

1. Cấu hình môi trường

Sử dụng SQL Server Management Studio và đoạn code sau để mở cơ sở dữ liệu AdventureWorks2017 và sử dụng câu lệnhCURRENT_USERTransact-SQL để kiểm tra xem người dùng dbo có được hiển thị như ngữ cảnh không.

USE AdventureWorks2017;
GO
SELECT CURRENT_USER AS 'Current User Name';
GO

Để biết thêm thông tin về câu lệnh CURRENT_USER, xem CURRENT_USER (Transact-SQL) .

Sử dụng code này làm người dùng dbo để tạo hai người dùng trên máy chủ và trong cơ sở dữ liệu AdventureWorks2017.


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

Để biết thêm thông tin về câu lệnh CREATE USER, xem CREATE USER (Transact-SQL) . Để biết thêm thông tin về câu lệnh CREATE LOGIN, xem CREATE LOGIN (Transact-SQL).

Sử dụng code sau đây để thay đổi quyền sở hữu lược đồ Purchasingthành tài khoảnTestManagerUser. Điều này cho phép tài khoản đó sử dụng tất cả câu lệnh Data Manipulation Language (Ngôn ngữ thao tác dữ liệu – DML) (chẳng hạn như quyền SELECTvà INSERT) truy cập trên các đối tượng mà nó chứa. TestManagerUsercũng được cấp khả năng tạo các thủ tục lưu trữ.


/* 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

Để biết thêm thông tin về câu lệnh GRANT, hãy xem GRANT (Transact-SQL) . Để biết thêm thông tin về các thủ tục lưu trữ, hãy xem Stored Procedures (Database Engine). Đối với một poster của tất cả các quyền Database Engine, xem https://aka.ms/sql-permissions-poster.

2. Tạo một thủ tục lưu trữ để truy cập dữ liệu

Để chuyển đổi ngữ cảnh bên trong một cơ sở dữ liệu, sử dụng câu lệnh EXECUTE AS. EXECUTE AS yêu cầu quyền IMPERSONATE.

Sử dụng câu lệnh EXECUTE AStrong đoạn code sau để thay đổi ngữ cảnhTestManagerUservà tạo một thủ tục lưu trữ chỉ hiển thị dữ liệu được yêu cầu bởiTestEmployeeUser. Để đáp ứng các yêu cầu, thủ tục lưu trữ chấp nhận một biến cho số đơn đặt hàng và không hiển thị thông tin tài chính, và mệnh đề WHERE giới hạn kết quả cho một phần lô hàng.


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

Hiện tại TestEmployeeUserkhông có quyền truy cập vào bất kỳ đối tượng cơ sở dữ liệu nào. Mã sau đây (vẫn còn trong ngữ cảnhTestManagerUser) cấp cho tài khoản người dùng khả năng truy vấn thông tin base-table thông qua thủ tục lưu trữ.


GRANT EXECUTE
ON OBJECT::Purchasing.usp_ShowWaitingItems
TO TestEmployeeUser;
GO

Thủ tục lưu trữ là một phần của lược đồ Purchasing, mặc dù không có lược đồ nào được chỉ định rõ ràng, bởi vìTestManagerUserđược gán theo mặc định cho lược đồ PurchasingBạn có thể sử dụng thông tin danh mục hệ thống để định vị đối tượng, như được hiển thị trong đoạn code sau.


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

Với phần này của ví dụ đã hoàn thành, code chuyển ngữ cảnh (context) trở lại dbo bằng cách sử dụng câu lệnhREVERT.


REVERT;
GO

Để biết thêm thông tin về câu lệnh REVERT, xem REVERT (Transact-SQL).

3. Truy cập dữ liệu thông qua thủ tục lưu trữ

TestEmployeeUserkhông có quyền đối với các đối tượng cơ sở dữ liệu AdventureWorks2012 ngoài đăng nhập và các quyền được gán cho vai trò cơ sở dữ liệu công khai. Code sau trả về lỗi khiTestEmployeeUsercố gắng truy cập các base-table (bảng cơ sở)


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

Lỗi được trả về:


Msg 229, Level 14, State 5, Line 6
The SELECT permission was denied on the object 'PurchaseOrderHeader', database 'AdventureWorks2017', schema 'Purchasing'.

Bởi vì các đối tượng được tham chiếu bởi thủ tục lưu trữ đã tạo trong phần cuối cùng được sở hữu bởi TestManagerUser bằng quyền sở hữu lược đồ Purchasing, TestEmployeeUser có thể truy cập vào các bảng cơ sở thông qua các thủ tục lưu trữ. Code sau, vẫn sử dụng context TestEmployeeUser, chuyển đơn đặt hàng 952 làm tham số.


EXEC Purchasing.usp_ShowWaitingItems 952
GO

4. Đặt lại môi trường

Đoạn code sau sử dụng câu lệnh REVERTđể trả về ngữ cảnh của tài khoản hiện tại dbo, và sau đó đặt lại môi trường.


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

Ví dụ hoàn chỉnh

Phần này hiển thị code ví dụ hoàn chỉnh.

Chú thích

Code này không bao gồm hai lỗi dự kiến chứng minh sự bất khả thi của TestEmployeeUser để chọn từ các bảng cơ sở.


/*
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 AdventureWorks2017;
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

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *