教學課程：使用憑證簽署預存程序
適用於：
SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體
此教學課程說明如何使用由 SQL Server 產生的憑證來簽署預存程序。
注意
若要執行本教學課程中的程式碼，您必須已設定混合模式安全性，並已安裝 AdventureWorks2022 資料庫。
當您希望對預存程序加諸必要的權限，卻又不想明確將這些權限授與使用者時，使用憑證簽署預存程序就非常有用。 儘管透過其他方式也能達到這個目的 (比方使用 EXECUTE AS 陳述式)，使用憑證讓您得以利用追蹤找出預存程序的原始呼叫者。 這提供了更高層次的稽核，尤其是稽核安全性或資料定義語言 (DDL) 作業期間的一舉一動。
您可以在 master 資料庫中建立憑證而賦予伺服器層級權限，或在任何使用者資料庫中建立憑證而賦予資料庫層級權限。 在此案例中，對基底資料表不具有權限的使用者必須存取 AdventureWorks2022 資料庫中的預存程序，而您則是希望稽核物件存取記錄。 您將不會用到其他的擁有權鏈結方法，而是要建立無權存取基底物件的伺服器和資料庫使用者帳戶，以及有權存取資料表和預存程序的資料庫使用者帳戶。 預存程序和第二個資料庫使用者帳戶都將由憑證提供安全性。 第二個資料庫帳戶可以存取所有物件，第一個資料庫使用者帳戶則僅獲授與存取預存程序。
此案例會先建立資料庫憑證、預存程序和使用者，然後再測試程序，步驟依序如下：
此範例會在每個程式碼區塊中各行附上說明。 若要複製整個範例，請參閱本教學課程結尾處的＜ 完整範例 ＞一節。
Prerequisites
若要完成本教學課程，您需要 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 陳述式的詳細資訊，請參閱 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/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
如需預存程序的詳細資訊，請參閱預存程序 (資料庫引擎)。
如需簽署預存程序的詳細資訊，請參閱 ADD SIGNATURE (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
如需授與物件權限的詳細資訊，請參閱 GRANT (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 Database 或 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
另請參閱
意見反應
即將登場：在 2024 年，我們將逐步淘汰 GitHub 問題作為內容的意見反應機制，並將它取代為新的意見反應系統。 如需詳細資訊，請參閱：https://aka.ms/ContentUserFeedback。
提交並檢視相關的意見反應