教學課程：使用憑證簽署預存程式

此教學課程說明如何使用由 SQL Server 產生的憑證來簽署預存程序。

備註

若要執行本教學課程中的程序代碼，您必須同時設定混合模式安全性，以及已安裝 AdventureWorks2012 資料庫。情境

當您想要設定儲存程序的許可權，但您不想明確授與使用者這些權利時，使用憑證簽署儲存程序會很有用。雖然您可以使用其他方式來完成這項工作，例如使用 EXECUTE AS 語句，但使用憑證可讓您使用追蹤來尋找預存程式的原始呼叫端。這可提供高階的稽核，特別是在安全性或數據定義語言（DDL）作業期間。

您可以在 master 資料庫中建立憑證以允許伺服器層級許可權，也可以在任何用戶資料庫中建立憑證，以允許資料庫層級許可權。在此案例中，沒有基表許可權的用戶必須存取 AdventureWorks2012 資料庫中的預存程式，而且您想要稽核物件存取記錄。您將不會採用其他的所有權鏈結方法，而是會建立一個伺服器和一個沒有基本對象許可權的資料庫用戶帳戶，以及一個具備資料表和預存程序許可權的資料庫用戶帳戶。預存程式和第二個資料庫用戶帳戶都會使用憑證來保護。第二個資料庫帳戶可以存取所有物件，並將預存程式的存取權授與第一個資料庫用戶帳戶。

在此案例中，您將先建立資料庫憑證、預存程式和使用者，然後測試下列步驟的程式：

設定環境。
建立憑證。
使用憑證建立和簽署預存程式。
使用憑證建立憑證帳戶。
授與憑證帳戶資料庫許可權。
顯示存取內容。
重設環境。

此範例中的每個程式代碼區塊都會以行說明。若要複製完整的範例，請參閱本教學課程結尾的完整範例。

1.設定環境

若要設定範例的初始內容，請在 SQL Server Management Studio 中開啟新的查詢，然後執行下列程式代碼來開啟 AdventureWorks2012 資料庫。此程式代碼會使用密碼，將資料庫內容變更為 AdventureWorks2012 ，並建立新的伺服器登入和資料庫用戶帳戶。TestCreditRatingUser。

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）。如需 CREATE LOGIN 語句的詳細資訊，請參閱 CREATE LOGIN （Transact-SQL）。

2.建立憑證

您可以使用 master 資料庫作為內容、使用使用者資料庫或兩者，在伺服器中建立憑證。有多個選項可保護憑證。如需憑證的詳細資訊，請參閱 CREATE CERTIFICATE (Transact-SQL)。

執行此程式代碼來建立資料庫憑證，並使用密碼保護它。

CREATE CERTIFICATE TestCreditRatingCer  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
      WITH SUBJECT = 'Credit Rating Records Access',   
      EXPIRY_DATE = '12/05/2014';  
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

如需預存程式的詳細資訊，請參閱預存程式（Database Engine）。

如需簽署預存程式的詳細資訊，請參閱 ADD SIGNATURE （Transact-SQL）。

4.使用憑證建立憑證帳戶

執行此程式代碼以從憑證建立資料庫使用者（TestCreditRatingcertificateAccount）。此帳戶沒有伺服器登入，且最終會控制基礎表的存取權。

USE AdventureWorks2012;  
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

如需授與物件權限的詳細資訊，請參閱 GRANT （Transact-SQL）。

6.顯示存取內容

若要顯示與預存程式存取相關聯的許可權，請執行下列程式代碼，將執行預存程式的許可權授與 TestCreditRatingUser 使用者。

GRANT EXECUTE   
   ON TestCreditRatingSP   
   TO TestCreditRatingUser;  
GO

接下來，執行以下程式碼，以您在伺服器上使用的 dbo 登入身分執行預存程序。觀察使用者內容信息的輸出。它會在該上下文中顯示 dbo 帳戶及其自身的權限，而不是透過群組成員資格。

EXECUTE TestCreditRatingSP;  
GO

執行下列程式代碼，以使用 EXECUTE AS 語句來成為 TestCreditRatingUser 帳戶並執行預存程式。這次您會看到用戶內容設定為 [用戶對應至憑證] 內容。

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

另請參閱

SQL Server Database Engine 和 Azure SQL Database 的資訊安全中心

Last updated on 2017-06-14

共用方式為