자습서: 인증서로 저장 프로시저 서명Tutorial: Signing Stored Procedures with a Certificate

이 자습서에서는 SQL ServerSQL Server에서 생성한 인증서를 사용하여 저장 프로시저에 서명하는 방법에 대해 설명합니다.This tutorial illustrates signing stored procedures using a certificate generated by SQL ServerSQL Server.

참고

이 자습서의 코드를 실행하려면 혼합 모드 보안을 구성하고 AdventureWorks2012AdventureWorks2012 데이터베이스를 설치해야 합니다.To run the code in this tutorial you must have both Mixed Mode security configured and the AdventureWorks2012AdventureWorks2012 database installed. 시나리오Scenario

저장 프로시저에 대한 사용 권한을 요구하려고 하지만 사용자에게 해당 권한을 명시적으로 부여하지 않으려는 경우 인증서를 사용하여 저장 프로시저에 서명하면 유용합니다.Signing stored procedures using a certificate is useful when you want to require permissions on the stored procedure but you do not want to explicitly grant a user those rights. EXECUTE AS 문을 사용하거나 다른 방법으로 이 태스크를 수행할 수도 있지만 인증서를 사용하면 추적을 통해 저장 프로시저를 원래 호출한 사용자를 찾을 수 있습니다.Although you can accomplish this task in other ways, such as using the EXECUTE AS statement, using a certificate allows you to use a trace to find the original caller of the stored procedure. 이를 통해 특히 보안 또는 DDL(데이터 정의 언어) 작업을 수행하는 동안 감사의 수준을 높일 수 있습니다.This provides a high level of auditing, especially during security or Data Definition Language (DDL) operations.

master 데이터베이스에서 인증서를 만들어 서버 수준 사용 권한을 허용하거나 사용자 데이터베이스에서 인증서를 만들어 데이터베이스 수준 사용 권한을 허용할 수 있습니다.You can create a certificate in the master database to allow server-level permissions, or you can create a certificate in any user databases to allow database-level permissions. 이 시나리오에서는 기본 테이블에 대한 권한이 없는 사용자가 AdventureWorks2012AdventureWorks2012 데이터베이스의 저장 프로시저에 액세스해야 하며 관리자는 개체 액세스 내역을 감사합니다.In this scenario, a user with no rights to base tables must access a stored procedure in the AdventureWorks2012AdventureWorks2012 database, and you want to audit the object access trail. 여기서는 다른 소유권 체인 방법을 사용하는 대신 기본 개체에 대한 권한이 없는 서버 및 데이터베이스 사용자 계정과 테이블 및 저장 프로시저에 대한 권한이 있는 데이터베이스 사용자 계정을 만듭니다.Rather than using other ownership chain methods, you will create a server and database user account with no rights to the base objects, and a database user account with rights to a table and a stored procedure. 저장 프로시저와 두 번째 데이터베이스 사용자 계정 모두 인증서를 사용하여 보안이 설정됩니다.Both the stored procedure and the second database user account will be secured with a certificate. 두 번째 데이터베이스 계정은 모든 개체에 대한 액세스 권한을 가지며 첫 번째 데이터베이스 사용자 계정에 저장 프로시저에 대한 액세스 권한을 부여합니다.The second database account will have access to all objects, and grant access to the stored procedure to the first database user account.

이 시나리오에서는 먼저 데이터베이스 인증서, 저장 프로시저 및 사용자를 만든 후 다음 단계를 따라 프로세스를 테스트합니다.In this scenario you will first create a database certificate, a stored procedure, and a user, and then you will test the process following these steps:

  1. 환경 구성Configure the environment.

  2. 인증서 만들기Create a certificate.

  3. 저장 프로시저를 만들고 여기에 인증서로 서명Create and sign a stored procedure using the certificate.

  4. 인증서를 사용하여 인증서 계정 만들기Create a certificate account using the certificate.

  5. 인증서 계정에 데이터베이스 권한 부여Grant the certificate account database rights.

  6. 액세스 컨텍스트 표시Display the access context.

  7. 환경 다시 설정Reset the environment.

이 예제의 각 코드 블록에 대한 설명도 함께 나와 있습니다.Each code block in this example is explained in line. 전체 예제를 복사하려면 이 자습서 끝에 있는 전체 예제 를 참조하세요.To copy the complete example, see Complete Example at the end of this tutorial.

1. 환경 구성1. Configure the Environment

예제의 초기 컨텍스트를 설정하려면 SQL Server Management StudioSQL Server Management Studio에서 새 쿼리를 열고 다음 코드를 실행하여 AdventureWorks2012AdventureWorks2012 데이터베이스를 엽니다.To set the initial context of the example, in SQL Server Management StudioSQL Server Management Studio open a new Query and run the following code to open the AdventureWorks2012AdventureWorks2012 database. 이 코드는 데이터베이스 컨텍스트를 AdventureWorks2012로 변경하고 암호를 사용하여 새 서버 로그인 및 데이터베이스 사용자 계정(TestCreditRatingUser)을 만듭니다.This code changes the database context to AdventureWorks2012 and creates a new server login and database user account (TestCreditRatingUser), using a password.

USE AdventureWorks2012;  
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 문에 대한 자세한 내용은 CREATE USER(Transact-SQL)를 참조하세요.For more information on the CREATE USER statement, see CREATE USER (Transact-SQL). CREATE LOGIN 문에 대한 자세한 내용은 CREATE LOGIN(Transact-SQL)을 참조하세요.For more information on the CREATE LOGIN statement, see CREATE LOGIN (Transact-SQL).

2. 인증서 만들기2. Create a Certificate

master 데이터베이스를 컨텍스트로 사용하거나, 사용자 데이터베이스를 사용하거나, 두 가지 방법을 모두 사용하여 서버에서 인증서를 만들 수 있습니다.You can create certificates in the server using the master database as the context, using a user database, or both. 인증서의 보안을 설정하는 옵션에는 여러 가지가 있습니다.There are multiple options for securing the certificate. 인증서에 대한 자세한 내용은 CREATE CERTIFICATE(Transact-SQL)를 참조하세요.For more information on certificates, see CREATE CERTIFICATE (Transact-SQL).

다음 코드를 실행하여 데이터베이스 인증서를 만들고 암호를 사용하여 이 인증서에 보안을 설정합니다.Run this code to create a database certificate and secure it using a password.

CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/05/2014';  
GO  

3. 저장 프로시저를 만들고 여기에 인증서로 서명3. Create and Sign a Stored Procedure Using the Certificate

다음 코드를 사용하여 Vendor 데이터베이스 스키마의 Purchasing테이블에서 데이터를 선택하는 저장 프로시저를 만들어 신용 등급이 1인 회사만 여기에 액세스할 수 있도록 제한할 수 있습니다.Use the following code to create a stored procedure that selects data from the Vendor table in the Purchasing database schema, restricting access to only the companies with a credit rating of 1. 저장 프로시저의 첫 번째 섹션은 저장 프로시저를 실행하는 사용자 계정의 컨텍스트를 표시합니다. 이 섹션은 개념을 보여 주기 위한 것으로,Note that the first section of the stored procedure displays the context of the user account running the stored procedure, which is to demonstrate the concepts only. 요구 사항을 만족할 필요는 없습니다.It is not required to satisfy the requirements.

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  

다음 코드를 실행하여 암호와 함께 데이터베이스 인증서로 저장 프로시저에 서명합니다.Run this code to sign the stored procedure with the database certificate, using a password.

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

저장 프로시저에 대한 자세한 내용은 저장 프로시저(데이터베이스 엔진)를 참조하세요.For more information on stored procedures, see Stored Procedures (Database Engine).

저장 프로시저에 서명하는 방법에 대한 자세한 내용은 ADD SIGNATURE(Transact-SQL)를 참조하세요.For more information on signing stored procedures, see ADD SIGNATURE (Transact-SQL).

4. 인증서를 사용하여 인증서 계정 만들기4. Create a Certificate Account Using the Certificate

다음 코드를 실행하여 인증서에서 데이터베이스 사용자(TestCreditRatingcertificateAccount)를 만듭니다.Run this code to create a database user (TestCreditRatingcertificateAccount) from the certificate. 이 계정에는 서버 로그인이 없으므로 해당 계정으로 기본 테이블에 대한 액세스가 제어됩니다.This account has no server login, and will ultimately control access to the underlying tables.

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

5. 인증서 계정에 데이터베이스 권한 부여5. Grant the Certificate Account Database Rights

다음 코드를 실행하여 기본 테이블 및 저장 프로시저에 대한 권한을 TestCreditRatingcertificateAccount에 부여합니다.Run this code to grant TestCreditRatingcertificateAccount rights to the base table and the stored procedure.

GRANT SELECT   
   ON Purchasing.Vendor   
   TO TestCreditRatingcertificateAccount;  
GO  

GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingcertificateAccount;  
GO  

개체에 사용 권한을 부여하는 방법에 대한 자세한 내용은 GRANT(Transact-SQL)를 참조하세요.For more information on granting permissions to objects, see GRANT (Transact-SQL).

6. 액세스 컨텍스트 표시6. Display the Access Context

저장 프로시저 액세스와 관련된 권한을 표시하려면 다음 코드를 실행하여 TestCreditRatingUser 사용자에게 저장 프로시저를 실행할 수 있는 권한을 부여합니다.To display the rights associated with the stored procedure access, run the following code to grant the rights to run the stored procedure to the TestCreditRatingUser user.

GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO  

그런 다음 아래 코드를 실행하여 서버에서 사용한 dbo 로그인으로 저장 프로시저를 실행합니다.Next, run the following code to run the stored procedure as the dbo login you used on the server. 사용자 컨텍스트 정보 출력을 살펴봅니다.Observe the output of the user context information. 그룹 멤버로서가 아니라 자체의 고유한 권한을 가진 컨텍스트로 dbo 계정이 표시되는 것을 확인할 수 있습니다.It will show the dbo account as the context with its own rights and not through a group membership.

EXECUTE TestCreditRatingSP;  
GO  

다음 코드를 실행하여 EXECUTE AS 계정이 되도록 TestCreditRatingUser 문을 사용하고 저장 프로시저를 실행합니다.Run the following code to use the EXECUTE AS statement to become the TestCreditRatingUser account and run the stored procedure. 이번에는 사용자 컨텍스트가 USER MAPPED TO CERTIFICATE 컨텍스트로 설정되어 있는 것을 확인할 수 있습니다.This time you will see the user context is set to the USER MAPPED TO CERTIFICATE context.

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

여기서는 저장 프로시저에 서명했기 때문에 사용 가능한 감사가 표시됩니다.This shows you the auditing available because you signed the stored procedure.

참고

데이터베이스 내에서 컨텍스트 전환에 EXECUTE를 사용합니다.Use EXECUTE AS to switch contexts within a database.

7. 환경 다시 설정7. Reset the Environment

다음 코드는 REVERT 문을 사용하여 현재 계정의 컨텍스트를 dbo로 되돌리고 환경을 다시 설정합니다.The following code uses the REVERT statement to return the context of the current account to dbo, and resets the environment.

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)를 참조하세요.For more information about the REVERT statement, see REVERT (Transact-SQL).

전체 예제Complete Example

이 섹션에서는 전체 예제 코드를 보여 줍니다.This section displays the complete example code.

/* Step 1 - Open the AdventureWorks2012 database */  
USE AdventureWorks2012;  
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 AdventureWorks2012 database */  
CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/05/2014';  
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 AdventureWorks2012;  
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  

참고 항목See Also

SQL Server 데이터베이스 엔진 및 Azure SQL Database에 대한 보안 센터Security Center for SQL Server Database Engine and Azure SQL Database