자습서: 소유권 체인 및 컨텍스트 전환
이 자습서에서는 시나리오를 통해 소유권 체인 및 사용자 컨텍스트 전환과 관련된 SQL Server 보안 개념을 설명합니다. 소유권 체인에 대한 자세한 내용은 소유권 체인을 참조하십시오. 컨텍스트 전환 방법은 컨텍스트 전환(데이터베이스 엔진)을 참조하십시오.
[!참고]
이 자습서의 코드를 실행하려면 혼합 모드 보안을 구성하고 AdventureWorks 데이터베이스를 설치해야 합니다. 혼합 모드 보안에 대한 자세한 내용은 인증 모드 선택를 참조하십시오.
시나리오
이 시나리오에서는 두 사용자에게 AdventureWorks 데이터베이스에 저장된 구매 주문 데이터에 액세스할 수 있는 계정이 필요합니다. 요구 사항은 다음과 같습니다.
첫 번째 계정(TestManagerUser)은 전체 구매 주문의 세부 사항을 모두 볼 수 있어야 합니다.
두 번째 계정(TestEmployeeUser)은 부분 운송을 받은 항목에 대해 구매 주문 번호, 주문 날짜, 운송 날짜, 제품 ID 번호, 구매 주문별로 주문하고 받은 항목, 구매 주문 번호별로 주문하고 받은 항목을 볼 수 있어야 합니다.
다른 모든 계정은 현재의 해당 사용 권한을 유지해야 합니다.
이 시나리오의 요구 사항을 만족시키기 위해 이 예제는 소유권 체인 및 컨텍스트 전환의 개념을 설명하는 네 부분으로 나뉘어 있습니다.
환경 구성
구매 주문별로 데이터에 액세스하는 저장 프로시저 만들기
저장 프로시저를 통해 데이터 액세스
환경 다시 설정
이 예제의 각 코드 블록에 대한 설명도 함께 나와 있습니다. 전체 예제를 복사하려면 이 자습서 끝에 있는 전체 예제를 참조하십시오.
1. 환경 구성
SQL Server Management Studio 및 다음 코드를 사용하여 AdventureWorks 데이터베이스를 열고 CURRENT_USER Transact-SQL 문을 사용하여 dbo 사용자가 컨텍스트로 표시되는지 확인합니다.
USE AdventureWorks;
GO
SELECT CURRENT_USER AS 'Current User Name';
GO
CURRENT_USER 문에 대한 자세한 내용은 CURRENT_USER(Transact-SQL)를 참조하십시오.
다음 코드를 dbo 사용자로 사용하여 서버와 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
CREATE USER 문에 대한 자세한 내용은 CREATE USER(Transact-SQL)를 참조하십시오. CREATE LOGIN 문에 대한 자세한 내용은 CREATE LOGIN(Transact-SQL)을 참조하십시오.
다음 코드를 사용하여 Purchasing 스키마의 소유권을 TestManagerUser 계정이 가지도록 변경합니다. 이렇게 하면 해당 계정이 자신이 포함하는 개체에서 모든 DML(데이터 조작 언어) 문 액세스 권한(예:SELECT 및 INSERT 권한)을 사용할 수 있습니다. 여기에는 DDL(데이터 정의 언어) 권한이 포함되지 않기 때문에 TestManagerUser에게는 PurchaseOrderHeader 및 PurchaseOrderDetail 테이블에 대한 권한뿐만 아니라 저장 프로시저를 만들 수 있는 능력도 명시적으로 부여됩니다.
/* 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
GRANT 문에 대한 자세한 내용은 GRANT(Transact-SQL)를 참조하십시오. 데이터베이스 스키마에 대한 자세한 내용은 스키마(데이터베이스 엔진)를 참조하십시오. 저장 프로시저에 대한 자세한 내용은 저장 프로시저(데이터베이스 엔진)를 참조하십시오.
2. 데이터에 액세스하는 저장 프로시저 만들기
SETUSER 또는 EXECUTE AS를 사용하여 사용자가 데이터베이스 내에서 컨텍스트를 전환하도록 할 수 있습니다. SETUSER 문을 사용하려면 호출자가 sysadmin 고정 서버 역할의 멤버이거나 dbo 계정이어야 합니다. EXECUTE AS를 사용하려면 IMPERSONATE 권한이 있어야 합니다. 이러한 개념에 대한 자세한 내용은 EXECUTE AS와 SETUSER를 참조하십시오.
다음 코드에서 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
TestManagerUser가 기본적으로 Purchasing 스키마에 할당되므로 스키마가 명시적으로 지정되지 않더라도 저장 프로시저는 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
시스템 카탈로그에 대한 자세한 내용은 SQL Server 시스템 카탈로그 쿼리를 참조하십시오.
예제의 이 섹션을 완료하면 코드가 REVERT 문을 사용하여 컨텍스트를 다시 dbo로 전환합니다.
REVERT;
GO
REVERT 문에 대한 자세한 내용은 REVERT(Transact-SQL)를 참조하십시오.
3. 저장 프로시저를 통해 데이터 액세스
TestEmployeeUser에게는 public 데이터베이스 역할에 할당된 로그인 및 권한 이외에 AdventureWorks 데이터베이스 개체에 대한 사용 권한이 없습니다. 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
Purchasing 스키마 소유권으로 인해 TestManagerUser가 마지막 섹션에서 생성된 저장 프로시저에서 참조하는 개체를 소유하므로 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 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