共用方式為

  • 發行項

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

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

[!附註]

若要執行本教學課程中的程式碼，您必須設定使用混合模式安全性，並已安裝 AdventureWorks 資料庫。狀況

當您希望對預存程序加諸必要的權限，卻又不想明確將這些權限授與使用者時，使用憑證簽署預存程序就非常有用。儘管透過其他方式也能達到這個目的 (比方使用 EXECUTE AS 陳述式)，使用憑證讓您得以利用追蹤找出預存程序的原始呼叫者。這提供了更高層次的稽核，尤其是稽核安全性或資料定義語言 (DDL) 作業期間的一舉一動。

您可以在 master 資料庫中建立憑證而賦予伺服器層級權限，或在任何使用者資料庫中建立憑證而賦予資料庫層級權限。在此案例中，對基底資料表不具有權限的使用者必須存取 AdventureWorks 資料庫中的預存程序，而您則是希望稽核物件存取記錄。您將不會用到其他的擁有權鏈結方法，而是要建立無權存取基底物件的伺服器和資料庫使用者帳戶，以及有權存取資料表和預存程序的資料庫使用者帳戶。預存程序和第二個資料庫使用者帳戶都將由憑證提供安全性。第二個資料庫帳戶可以存取所有物件，第一個資料庫使用者帳戶則僅獲授與存取預存程序。

此案例會先建立資料庫憑證、預存程序和使用者，然後再測試程序，步驟依序如下：

  1. 設定環境。

  2. 建立憑證。

  3. 建立預存程序並使用憑證進行簽署。

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

  5. 授與憑證帳戶資料庫權限。

  6. 顯示存取內容。

  7. 重設環境。

此範例會在每個程式碼區塊中各行附上說明。若要複製整個範例，請參閱本教學課程結尾處的＜完整範例＞一節。

1. 設定環境

為了設定範例的初步內容，請在 SQL Server Management Studio 中開啟新查詢，然後執行下列程式碼以開啟 AdventureWorks 資料庫。這段程式碼將資料庫內容變更為 AdventureWorks，再建立新的伺服器登入和資料庫使用者帳戶 (TestCreditRatingUser)，並且使用了密碼。

USE AdventureWorks;
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/2010';
GO

3. 建立預存程序並使用憑證進行簽署

使用下列程式碼建立預存程序，從 Purchasing 資料庫結構描述內的 Vendor 資料表選取資料，同時僅限存取信用等級為 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)＞。

如需有關資料庫結構描述的詳細資訊，請參閱＜結構描述 (Database Engine)＞。

如需有關簽署預存程序的詳細資訊，請參閱＜ADD SIGNATURE (Transact-SQL)＞。

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

執行下列程式碼，經由憑證建立資料庫使用者 (TestCreditRatingcertificateAccount)。此帳戶不具有伺服器登入身分，最後將要用來控制基礎資料表的存取。

USE AdventureWorks;
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 帳戶身分來執行預存程序。這回您會看到使用者內容已設定為 USER MAPPED TO CERTIFICATE 內容。

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

這就達到了稽核目的，因為您已簽署過預存程序。

[!附註]

您有兩種方法可以讓使用者在資料庫中切換內容：SETUSER 或 EXECUTE AS。如需有關切換內容的詳細資訊，請參閱＜EXECUTE AS 與 SETUSER＞。

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