다음을 통해 공유

자습서: 인증서를 사용하여 저장 프로시저 서명

  • 아티클

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

이 자습서에서는 SQL Server에서 생성한 인증서를 사용하여 저장 프로시저에 서명하는 방법에 대해 설명합니다.

참고 항목

이 자습서의 코드를 실행하려면 혼합 모드 보안이 구성되어 있고 AdventureWorks2022 데이터베이스가 모두 설치되어 있어야 합니다.

인증서를 사용하여 저장 프로시저에 서명하는 것은 저장 프로시저에 대한 권한이 필요하지만 사용자에게 해당 권한을 명시적으로 부여하고 싶지 않을 때 유용합니다. EXECUTE AS 문을 사용하거나 다른 방법으로 이 태스크를 수행할 수도 있지만 인증서를 사용하면 추적을 통해 저장 프로시저를 원래 호출한 사용자를 찾을 수 있습니다. 특히 보안 또는 DDL(데이터 정의 언어) 작업 중에 높은 수준의 감사를 제공합니다.

마스터 데이터베이스에 인증서를 만들어 서버 수준 권한을 허용하거나 사용자 데이터베이스에 인증서를 만들어 데이터베이스 수준 권한을 허용할 수 있습니다. 이 시나리오에서는 기본 테이블에 대한 권한이 없는 사용자가 AdventureWorks2022 데이터베이스의 저장 프로시저에 액세스해야 하며, 개체 액세스 추적을 감사하려고 합니다. 다른 소유권 체인 방법을 사용하는 대신 기본 개체에 대한 권한이 없는 서버 및 데이터베이스 사용자 계정과 테이블 및 저장 프로시저에 대한 권한이 있는 데이터베이스 사용자 계정을 만들 수 있습니다. 저장 프로시저와 두 번째 데이터베이스 사용자 계정은 모두 인증서로 보호됩니다. 두 번째 데이터베이스 계정에는 모든 개체에 대한 액세스 권한이 있고, 첫 번째 데이터베이스 사용자 계정에는 저장 프로시저에 대한 액세스 권한이 부여됩니다.

이 시나리오에서는 먼저 데이터베이스 인증서, 저장 프로시저 및 사용자를 만든 다음 다음 단계에 따라 프로세스를 테스트합니다.

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

필수 조건

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

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

1. 환경 구성

예시의 초기 컨텍스트를 설정하려면 SQL Server Management Studio에서 새 쿼리를 열고 다음 코드를 실행하여 AdventureWorks2022 데이터베이스를 엽니다. 이 코드는 데이터베이스 컨텍스트를 AdventureWorks2022(으)로 변경하고 비밀번호를 사용하여 새 서버 로그인 및 데이터베이스 사용자 계정(TestCreditRatingUser)을 만듭니다.

USE AdventureWorks2022;  
GO  
-- Set up a login for the test user  
CREATE LOGIN TestCreditRatingUser  
   WITH PASSWORD = 'ASDECd2439587y'  
GO  
CREATE USER TestCreditRatingUser  
FOR LOGIN TestCreditRatingUser;  
GO

CREATE USER 문에 대한 자세한 내용은 사용자 만들기(Transact-SQL)를 참조하세요. CREATE LOGIN 문에 대한 자세한 내용은 로그인 만들기(Transact-SQL)를 참조하세요.

2. 인증서 만들기

마스터 데이터베이스를 컨텍스트로 사용하거나 사용자 데이터베이스를 사용하거나 둘 다 사용하여 서버에서 인증서를 만들 수 있습니다. 인증서 보안을 위한 여러 옵션이 있습니다. 인증서에 대한 자세한 내용은 CREATE CERTIFICATE(Transact-SQL)를 참조하세요.

다음 코드를 실행하여 데이터베이스 인증서를 만들고 암호를 사용하여 이 인증서에 보안을 설정합니다.

CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/31/2022';  -- Error 3701 will occur if this date is not in the future
GO

3. 인증서를 사용하여 저장 프로시저 만들기 및 서명

다음 코드를 사용하여 Vendor 데이터베이스 스키마의 Purchasing 테이블에서 데이터를 선택하는 저장 프로시저를 만들어 신용 등급이 1인 회사만 여기에 액세스할 수 있도록 제한할 수 있습니다. 저장 프로시저의 첫 번째 섹션에는 저장 프로시저를 실행하는 사용자 계정의 컨텍스트가 표시되며, 이는 개념만 설명하기 위한 것입니다. 요구 사항을 충족할 필요는 없습니다.

CREATE PROCEDURE TestCreditRatingSP  
AS  
BEGIN  
   -- Show who is running the stored procedure  
   SELECT SYSTEM_USER 'system Login'  
   , USER AS 'Database Login'  
   , NAME AS 'Context'  
   , TYPE  
   , USAGE   
   FROM sys.user_token     
  
   -- Now get the data  
   SELECT AccountNumber, Name, CreditRating   
   FROM Purchasing.Vendor  
   WHERE CreditRating = 1  
END  
GO

이 코드를 실행하여 비밀번호를 사용하여 데이터베이스 인증서로 저장 프로시저에 서명합니다.

ADD SIGNATURE TO TestCreditRatingSP   
   BY CERTIFICATE TestCreditRatingCer  
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';  
GO

저장 프로시저에 대한 자세한 내용은 저장 프로시저(데이터베이스 엔진)를 참조하세요.

저장 프로시저 서명에 대한 자세한 내용은 서명 추가(Transact-SQL)를 참조하세요.

4. 인증서를 사용하여 인증서 계정 만들기

이 코드를 실행하여 인증서에서 데이터베이스 사용자(TestCreditRatingcertificateAccount)를 만듭니다. 이 계정에는 서버 로그인이 없으므로 해당 계정으로 기본 테이블에 대한 액세스가 제어됩니다.

USE AdventureWorks2022;  
GO  
CREATE USER TestCreditRatingcertificateAccount  
   FROM CERTIFICATE TestCreditRatingCer;  
GO

5. 인증서 계정에 데이터베이스 권한 부여

다음 코드를 실행하여 기본 테이블 및 저장 프로시저에 대한 권한을 TestCreditRatingcertificateAccount 에 부여합니다.

GRANT SELECT   
   ON Purchasing.Vendor   
   TO TestCreditRatingcertificateAccount;  
GO  
  
GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingcertificateAccount;  
GO

개체에 대한 권한 부여에 대한 자세한 내용은 권한 부여(Transact-SQL)를 참조하세요.

6. 액세스 컨텍스트 표시

저장 프로시저 액세스와 관련된 권한을 표시하려면 다음 코드를 실행하여 TestCreditRatingUser 사용자에게 저장 프로시저를 실행할 수 있는 권한을 부여합니다.

GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO

다음으로, 다음 코드를 실행하여 서버에서 사용한 dbo 로그인으로 저장 프로시저를 실행합니다. 사용자 컨텍스트 정보의 출력을 관찰합니다. 그룹 멤버십이 아닌 자체 권한이 있는 dbo 계정이 컨텍스트로 표시됩니다.

EXECUTE TestCreditRatingSP;  
GO

다음 코드를 실행하여 EXECUTE AS 문을 사용하여 TestCreditRatingUser 계정으로 전환하고 저장 프로시저를 실행합니다. 이번에는 사용자 컨텍스트가 USER MAPPED TO CERTIFICATE 컨텍스트로 설정되어 있는 것을 확인할 수 있습니다. 이 옵션은 포함된 데이터베이스 또는 Azure SQL 데이터베이스 또는 Azure Synapse Analytics에서는 지원되지 않습니다.

EXECUTE AS LOGIN = 'TestCreditRatingUser';  
GO  
EXECUTE TestCreditRatingSP;  
GO

저장 프로시저에 서명했기 때문에 사용 가능한 감사가 표시됩니다.

참고 항목

데이터베이스 내에서 컨텍스트를 전환하려면 EXECUTE AS를 사용합니다.

7. 환경 다시 설정

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

REVERT;  
GO  
DROP PROCEDURE TestCreditRatingSP;  
GO  
DROP USER TestCreditRatingcertificateAccount;  
GO  
DROP USER TestCreditRatingUser;  
GO  
DROP LOGIN TestCreditRatingUser;  
GO  
DROP CERTIFICATE TestCreditRatingCer;  
GO

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

완성된 예시

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

/* Step 1 - Open the AdventureWorks2022 database */  
USE AdventureWorks2022;  
GO  
-- Set up a login for the test user  
CREATE LOGIN TestCreditRatingUser  
   WITH PASSWORD = 'ASDECd2439587y'  
GO  
CREATE USER TestCreditRatingUser  
FOR LOGIN TestCreditRatingUser;  
GO  
  
/* Step 2 - Create a certificate in the AdventureWorks2022 database */  
CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/31/2021';   -- Error 3701 will occur if this date is not in the future
GO  
  
/* Step 3 - Create a stored procedure and  
sign it using the certificate */  
CREATE PROCEDURE TestCreditRatingSP  
AS  
BEGIN  
   -- Shows who is running the stored procedure  
   SELECT SYSTEM_USER 'system Login'  
   , USER AS 'Database Login'  
   , NAME AS 'Context'  
   , TYPE  
   , USAGE   
   FROM sys.user_token;     
  
   -- Now get the data  
   SELECT AccountNumber, Name, CreditRating   
   FROM Purchasing.Vendor  
   WHERE CreditRating = 1;  
END  
GO  
  
ADD SIGNATURE TO TestCreditRatingSP   
   BY CERTIFICATE TestCreditRatingCer  
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';  
GO  
  
/* Step 4 - Create a database user for the certificate.   
This user has the ownership chain associated with it. */  
USE AdventureWorks2022;  
GO  
CREATE USER TestCreditRatingcertificateAccount  
   FROM CERTIFICATE TestCreditRatingCer;  
GO  
  
/* Step 5 - Grant the user database rights */  
GRANT SELECT   
   ON Purchasing.Vendor   
   TO TestCreditRatingcertificateAccount;  
GO  
  
GRANT EXECUTE  
   ON TestCreditRatingSP   
   TO TestCreditRatingcertificateAccount;  
GO  
  
/* Step 6 - Test, using the EXECUTE AS statement */  
GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO  
  
-- Run the procedure as the dbo user, notice the output for the type  
EXEC TestCreditRatingSP;  
GO  
  
EXECUTE AS LOGIN = 'TestCreditRatingUser';  
GO  
EXEC TestCreditRatingSP;  
GO  
  
/* Step 7 - Clean up the example */  
REVERT;  
GO  
DROP PROCEDURE TestCreditRatingSP;  
GO  
DROP USER TestCreditRatingcertificateAccount;  
GO  
DROP USER TestCreditRatingUser;  
GO  
DROP LOGIN TestCreditRatingUser;  
GO  
DROP CERTIFICATE TestCreditRatingCer;  
GO

참고 항목

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