자습서: 소유권 체인 및 컨텍스트 전환

아티클
07/12/2024

적용 대상: SQL Server Azure SQL Managed Instance

이 자습서에서는 시나리오를 사용하여 소유권 체인 및 사용자 컨텍스트 전환과 관련된 SQL Server 보안 개념을 예시로 설명합니다.

참고 항목

이 자습서의 코드를 실행하려면 혼합 모드 보안이 구성되어 있고 AdventureWorks2022 데이터베이스가 모두 설치되어 있어야 합니다. 혼합 모드 보안에 대한 자세한 내용은 인증 모드 선택을 참조하세요.

시나리오

이 시나리오에서는 AdventureWorks2022 데이터베이스에 저장된 구매 주문 데이터에 액세스하려면 두 명의 사용자에게 계정이 필요합니다. 요구 사항은 다음과 같습니다.

첫 번째 계정(TestManagerUser)은 전체 구매 주문의 세부 사항을 모두 볼 수 있어야 합니다.
두 번째 계정(TestEmployeeUser)은 부분 배송이 접수된 품목에 대해 구매 주문 번호, 주문 날짜, 배송 날짜, 제품 ID 번호, 구매 주문 번호별로 주문 및 접수된 품목을 확인할 수 있어야 합니다.
다른 모든 계정은 현재의 해당 사용 권한을 유지해야 합니다.
이 시나리오의 요구 사항을 만족시키기 위해 이 예시는 소유권 체인 및 컨텍스트 전환의 개념을 설명하는 네 부분으로 나뉘어 있습니다.

환경 구성.
구매 주문별 데이터에 액세스하기 위한 저장 프로시저 생성.
저장 프로시저를 통해 데이터에 액세스.
환경 다시 설정

이 예시의 각 코드 블록은 줄 단위로 설명되어 있습니다. 전체 예시를 복사하려면 이 자습서 끝에 있는 전체 예시를 참조하세요.

필수 조건

이 자습서를 완료하려면 SQL Server Management Studio, SQL Server를 실행하는 서버에 대한 액세스 및 AdventureWorks2022 데이터베이스가 필요합니다.

SQL Server Management Studio를 설치합니다.
SQL Server 2017 Developer Edition을 설치합니다.
AdventureWorks 샘플 데이터베이스를 다운로드합니다.

SQL Server Management Studio에서 데이터베이스를 복원하는 방법에 대한 지침은 데이터베이스 복원을 참조하세요.

1. 환경 구성

SQL Server Management Studio와 다음 코드를 사용하여 AdventureWorks2022 데이터베이스를 열고 CURRENT_USER Transact-SQL 문을 사용하여 dbo 사용자가 컨텍스트로 표시되는지 확인합니다.

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

CURRENT_USER 문에 대한 자세한 내용은 CURRENT_USER(Transact-SQL)를 참조하세요.

이 코드를 dbo 사용자로 사용하여 서버와 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

CREATE USER 문에 대한 자세한 내용은 CREATE USER(Transact-SQL)를 참조하세요. CREATE LOGIN 문에 대한 자세한 내용은 CREATE LOGIN(Transact-SQL)을 참조하세요.

다음 코드를 사용하여 Purchasing 스키마의 소유권을 TestManagerUser 계정이 가지도록 변경합니다. 이렇게 하면 해당 계정에서 포함된 개체에 대한 모든 DML(데이터 조작 언어) 문 액세스(예: SELECT 및 INSERT 권한)를 사용할 수 있습니다. 또한 TestManagerUser에는 저장 프로시저를 만들 수 있는 권한이 부여됩니다.

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

GRANT 문에 대한 자세한 내용은 GRANT(Transact-SQL)를 참조하세요. 저장 프로시저에 대한 자세한 내용은 저장 프로시저(데이터베이스 엔진)를 참조하세요. 모든 데이터베이스 엔진 사용 권한에 대한 포스터는 https://aka.ms/sql-permissions-poster을(를) 참조하세요.

2. 데이터에 액세스하는 저장 프로시저 만들기

데이터베이스 내에서 컨텍스트를 전환하려면 EXECUTE AS 문을 사용합니다. EXECUTE AS를 사용하려면 IMPERSONATE 권한이 있어야 합니다.

다음 코드에서 EXECUTE AS 문을 사용하여 컨텍스트를 TestManagerUser(으)로 변경하고 TestEmployeeUser에 필요한 데이터만 표시하는 저장 프로시저를 만듭니다. 요구 사항을 충족하기 위해 저장 프로시저는 구매 주문 번호에 대해 하나의 변수를 받아들이고 재무 정보를 표시하지 않으며, WHERE 절은 결과를 부분 배송으로 제한합니다.

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

현재 TestEmployeeUser에는 데이터베이스 개체에 대한 액세스 권한이 없습니다. 다음 코드(여전히 TestManagerUser 컨텍스트에 있음)는 사용자 계정에 저장 프로시저를 통해 기본 테이블 정보를 쿼리할 수 있는 권한을 부여합니다.

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

Purchasing 가 기본적으로 TestManagerUser 스키마에 할당되므로 스키마가 명시적으로 지정되지 않더라도 저장 프로시저는 Purchasing 스키마의 일부가 됩니다. 다음 코드에서와 같이 시스템 카탈로그 정보를 사용하여 개체를 찾을 수 있습니다.

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

예시의 이 섹션이 완료되면 이 코드는 REVERT 문을 사용하여 컨텍스트를 dbo로 다시 전환합니다.

REVERT;  
GO

REVERT 문에 대한 자세한 내용은 REVERT(Transact-SQL)를 참조하세요.

3. 저장 프로시저를 통해 데이터 액세스

TestEmployeeUser에는 로그인 및 공용 데이터베이스 역할에 할당된 권한 외에는 AdventureWorks2022 데이터베이스 개체에 대한 권한이 없습니다. 다음 코드는 TestEmployeeUser이(가) 기본 테이블에 액세스하려고 할 때 오류를 반환합니다.

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

반환되는 오류는 다음과 같습니다.

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

마지막 섹션에서 만든 저장 프로시저에서 참조하는 개체는 TestManagerUser 스키마 소유권에 따라 Purchasing이(가) 소유하므로 TestEmployeeUser은(는) 저장 프로시저를 통해 기본 테이블에 액세스할 수 있습니다. 다음 코드는 여전히 TestEmployeeUser 컨텍스트를 사용하여 구매 주문 952를 매개 변수로 전달합니다.

EXEC Purchasing.usp_ShowWaitingItems 952  
GO

4. 환경 초기화

다음 코드는 REVERT 명령을 사용하여 현재 계정의 컨텍스트를 dbo(으)로 반환한 다음 환경을 초기화합니다.

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

완성된 예시

이 섹션에서는 전체 예시 코드를 표시합니다.

참고 항목

이 코드에는 TestEmployeeUser 가 기본 테이블에서 데이터를 선택할 수 없음을 보여 주는 두 가지 예상 오류가 포함되어 있지 않습니다.

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

참고 항목

SQL Server 데이터베이스 엔진 및 Azure SQL 데이터베이스용 Security Center

다음을 통해 공유

자습서: 소유권 체인 및 컨텍스트 전환

시나리오

필수 조건

1. 환경 구성

2. 데이터에 액세스하는 저장 프로시저 만들기

3. 저장 프로시저를 통해 데이터 액세스

4. 환경 초기화

완성된 예시

참고 항목

피드백

추가 리소스