教程：使用证书对存储过程进行签名

2017-06-14

本教程演示如何使用 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

有关存储过程的详细信息，请参阅存储过程（数据库引擎）。

有关对存储过程进行签名的详细信息，请参阅 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 帐户，并执行存储过程。此时，你将看到用户上下文设置为 USER MAPPED TO CERTIFICATE 上下文。

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 数据库引擎和 Azure SQL 数据库安全中心

通过