Share via

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

  • 项目

新建日期： 2006 年 12 月 12 日

本教程说明了如何使用由 SQL Server 生成的证书对存储过程进行签名。

Bb283630.note(zh-cn,SQL.90).gif注意：
若要在本教程中运行代码，必须已配置混合模式安全性并且已安装 AdventureWorks 数据库。有关混合模式安全性的详细信息，请参阅身份验证模式。有关安装 AdventureWorks 数据库的详细信息，请参阅安装 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

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

有关数据库架构的详细信息，请参阅架构。

有关对存储过程签名的详细信息，请参阅 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

此时将显示可用审核，因为您已对存储过程进行签名。

Bb283630.note(zh-cn,SQL.90).gif注意：
有两种方法允许用户在数据库中切换上下文：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

请参阅

其他资源

SQL Server 安全注意事项
数据库和数据库应用程序的安全注意事项

帮助和信息

获取 SQL Server 2005 帮助