模块签名（数据库引擎）

数据库应用程序通常需要通过入门级过程或视图来控制对应用程序架构内基础表和对象的访问。目的是能够授予最终用户访问入门级对象的权限，然后这些对象代表用户访问基础对象。因此，不必授予最终用户访问应用程序架构中所有对象的权限。这种方法具有两个用途：

• 它简化了权限管理，在权限管理中，只需针对较小的对象子集而不是应用程序架构中的所有对象进行权限管理。

• 它可以对最终用户隐藏基础架构布局，因为仅公开入口点。

Microsoft SQL Server 具有很多有助于实现这些方案的功能，例如建立所有权链以及使用 EXECUTE AS 语句。SQL Server 从 SQL Server 2005 开始，也提供对数据库内的模块进行签名的功能。模块签名提供类似的功能，但不会更改执行上下文。此上下文中的模块是指存储过程、函数、触发器或程序集。有关详细信息，请参阅 CREATE ROLE (Transact-SQL) 和使用 EXECUTE AS 创建自定义权限集。

模块签名

SQL Server 2005 引入了对数据库内的模块（例如存储过程、函数、触发器或程序集）进行签名的功能。注意，不能对数据定义语言 (DDL) 触发器进行签名。数字签名是指使用签名人私钥加密的数据摘要。私钥可确保数字签名是其持有人或所有者所特有的。

若要对数据进行签名，签名人应对数据进行摘要，并使用私钥加密摘要，然后将加密的摘要值附加到数据。若要验证签名，验证人可使用签名人的公钥对附加到数据的加密摘要值进行解密。然后，验证人将此解密的摘要值与根据附带数据计算的摘要值进行比较。签名人和验证人必须使用相同的哈希函数对数据进行摘要。

注意
模块签名应仅用于授予权限，从不用于拒绝或撤消权限。

应用场景

假设应通过 usp_sysprocesses 存储过程控制对 sys.sysprocesses 视图的访问。用户仅可通过执行 usp_sysprocesses 过程访问 sys.sysprocesses 信息。由于 usp_sysprocesses 对象和 sys.sysprocesses 对象具有不同的所有权，因此无法应用所有权链接。

首先，必须在服务器上使用 CREATE CERTIFICATE 语句根据密钥对来创建证书。然后，授予证书从 sys.sysprocesses 表中进行选择的权限。但是，由于 SQL Server 仅为主体授予权限，因此首先需要使用 CREATE LOGIN 语句根据证书创建登录名。此登录名不需要服务器连接权限，因为它只是权限占位符，并不用于连接到服务器实例。然后，可以使用 GRANT VIEW SERVER STATE TO 语句为此证书映射登录名授予对 sys.sysprocesses 表的 SELECT 权限。创建 usp_sysprocesses 存储过程之后，可以使用 ADD SIGNATURE 语句借助证书（实际上是对应于此证书的私钥）对存储过程进行签名。此时便创建了一个新的角色，并授予此角色对 usp_sysprocesses 存储过程的执行权限。于是，属于此角色成员的任何用户均具有执行 usp_sysprocesses 存储过程的权限，进而具有对 sys.sysprocess 视图的 SELECT 权限。执行签名模块时，使用 GRANT 语句授予主体的与签名证书关联的权限，在持续调用期间暂时通过 UNION 合并到运行时安全令牌中。一旦返回执行控制权，便立即从安全令牌中删除这些权限。因此，仅在模块执行的生存期内，才实际具有一组额外的权限。被授予对此过程的 EXECUTE 权限的任何其他用户或角色也具有相同的权限。

示例

以下 Transact-SQL 脚本提供上述方案的示例。证书根据密钥对创建并映射到新的登录名。首先必须使用 .NET Framework SDK 附带的 MakeCert 工具创建测试密钥对。然后，为与证书关联的登录名授予对 sys.sysproceses 视图的选择权限。usp_sysprocesses 托管存储过程在新的数据库中创建，并使用证书进行签名。这样便会创建 SysProcRole 角色，并为此角色授予对 usp_sysprocesses 存储过程的执行权限。还会创建测试用户并将其添加到 SysProcRole 角色。此测试用户对 sys.sysprocess 执行 SELECT 语句，然后执行 usp_sysprocesses 存储过程以进行比较。然后，脚本将清除测试环境。

use master
go

-- Create a test database.
CREATE DATABASE db_Demo
go

-- Create a certificate on the server. A test key pair can be created
-- using the MakeCert tool that ships with the .NET Framework SDK.
CREATE CERTIFICATE SysProcCert FROM FILE = 'e:\programming\testCert.cer'
go

-- Create a login and map it to the certificate.
CREATE LOGIN login_SysProcCert FROM CERTIFICATE SysProcCert
Go

-- Revoke the connect permission.
REVOKE CONNECT SQL FROM login_SysProcCert ;
go 
 
-- Grant the certificate, through the login, permission to select from sys.sysprocesses view.
GRANT VIEW SERVER STATE TO login_SysProcCert
go

-- Create a test login.
CREATE LOGIN bob WITH PASSWORD = '<enterStrongPasswordHere>'
go

-- Connect to the test database.
use db_Demo
go

-- Create the master key for the test database (used to protect 
-- private keys and certificates in the database).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<enterStrongPasswordHere>' 

-- Create a certificate from a private key.
CREATE CERTIFICATE SysProcCert FROM FILE = 'e:\programming\testCert.cer'
WITH PRIVATE KEY
(FILE = 'e:\programming\testCert.pvk', 
 DECRYPTION BY PASSWORD= '<enterStrongPasswordHere>', 
 ENCRYPTION BY PASSWORD='<enterStrongPasswordHere>')
go 

-- Create the assembly on the server. The assembly DLL must be signed.
CREATE ASSEMBLY SysStoredProcedures
FROM 'E:\programming\SysStoredProcs.dll'
WITH PERMISSION_SET = SAFE
go 

-- Create the managed stored procedure on the server.
CREATE PROCEDURE usp_sysprocesses
AS EXTERNAL NAME SysStoredProcedures.StoredProcedures.usp_sysprocesses
go 

-- Add the signature to the stored procedure.
ADD SIGNATURE TO [dbo].[usp_sysprocesses] 
BY CERTIFICATE SysProcCert WITH PASSWORD = '<enterStrongPasswordHere>'
go 

-- Create a role.
CREATE ROLE SysProcRole
go

-- Create a test user
CREATE USER bob
go

-- Add the test user to the role.
EXEC sp_addrolemember 'SysProcRole', 'bob'
go

-- Grant execute permissions on the stored procedure to the new role.
GRANT EXECUTE ON [dbo].[usp_sysprocesses] TO SysProcRole
go
 
-- Connect as the test user.
EXECUTE AS LOGIN = 'bob'
use db_Demo
go
 
-- User only has permission to see their own processes.
SELECT * FROM sys.sysprocesses
go

-- Execute the stored procedure, which has been signed.
exec usp_sysprocesses
go

-- REVERT
REVERT
----------------------------------------------------------------------
-- Cleanup

use db_Demo
go

use master
go

DROP DATABASE db_Demo
go 

DROP login login_SysProcCert
DROP login bob
go

DROP CERTIFICATE SysProcCert
go

以下是 usp_sysprocesses 存储过程的源代码，此存储过程可对 sys.sysprocesses 视图执行 SELECT * 语句。程序集在生成时必须进行签名。

C#

using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void usp_sysprocesses()
{
    using(SqlConnection connection = new SqlConnection("context connection=true"))
    {
        connection.Open();
        SqlCommand command = new SqlCommand("SELECT * FROM sys.sysprocesses", connection);
        SqlContext.Pipe.ExecuteAndSend(command);
    }
}
};

Visual Basic

Imports System
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub  usp_sysprocesses ()
    Using connection As New SqlConnection("context connection=true")
        connection.Open()

        Dim command As New SqlCommand("SELECT * FROM sys.sysprocesses", connection)
        SqlContext.Pipe.ExecuteAndSend(command)
    End Using
End Sub
End Class

请参阅

参考

CREATE CERTIFICATE (Transact-SQL)

CREATE LOGIN (Transact-SQL)

GRANT (Transact-SQL)

CREATE MASTER KEY (Transact-SQL)

ADD SIGNATURE (Transact-SQL)

CREATE ROLE (Transact-SQL)

概念

保护 SQL Server

SQL Server 证书和非对称密钥