發行項
09/24/2010

模組簽署 (Database Engine)

資料庫應用程式通常會要求對應用程式結構描述中基礎資料表和物件的存取權要透過進入層級程序或檢視傳送。其目的是為了能夠授與使用者對進入層級物件的存取權，然後再代表使用者存取基礎物件。因此，使用者不需要獲得應用程式結構描述中所有物件的存取權。這個方法有兩種目的：

它可簡化權限管理，因為只需要管理少量物件的權限，而不需要管理應用程式結構描述中的所有物件。
它可讓使用者無法看見基礎結構描述配置，因為只會公開進入點。

MicrosoftSQL Server 具有一些可協助達到這些狀況的功能，例如擁有權鏈結和使用 EXECUTE AS 陳述式。從 SQL Server 2005 開始，SQL Server 也在資料庫中提供了簽署模組的功能。模組簽署會提供類似的功能，但不會變更執行內容。在這個內容中，模組是指預存程序、函數、觸發程序或組件。詳細資訊請參閱＜CREATE ROLE (Transact-SQL)＞和＜使用 EXECUTE AS 建立自訂權限集＞。

模組簽署

SQL Server 2005 導入在資料庫中簽署模組的功能，這些模組包括預存程序、函數、觸發程序或組件。請注意，資料定義語言 (DDL) 觸發程序無法進行簽署。數位簽章是指使用簽署人之私密金鑰加密的資料摘要。私密金鑰可確保數位簽章對於持有者或擁有者是唯一的。

若要簽署資料，簽署人會建立資料的摘要、使用私密金鑰加密，然後將加密的摘要值附加至此資料。若要驗證簽章，驗證器會使用簽署人的公開金鑰來解密附加至資料的加密摘要值。然後，驗證器會比較這個解密的摘要值與隨附資料上計算的摘要值。簽署人和驗證器一定要使用相同的雜湊函數來建立資料的摘要。

警告
模組簽署只能用來授與權限，絕對不能用來拒絕或撤銷權限。

狀況

假設對 sys.sysprocesses 檢視的存取權應該由 usp_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 作業至執行階段安全性 Token 中。一旦執行控制權傳回，就會從安全性 Token 中移除這些權限。因此，只有在模組執行的存留期間，您可以實際獲得額外的權限集。獲得此程序之 EXECUTE 權限的任何其他使用者或角色也會具有相同的能力。

範例

下列 Transact-SQL 指令碼會提供上述狀況的範例。它會根據金鑰組建立憑證，並將憑證對應至新的登入。您必須先使用 .NET Framework SDK 隨附的 MakeCert 工具來建立測試金鑰組。然後，sys.sysproceses 檢視的選取權限會授與和憑證相關的登入。它會在新的資料庫中建立 usp_sysprocesses Managed 預存程序並使用憑證加以簽署。接著，會建立 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 * 陳述式) 的原始程式碼。組件建立時，必須加以簽署。

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