ADD SIGNATURE (Transact-SQL)

文章
10/16/2024

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

將數位簽章加入預存程序、函式、組件或 DML 觸發程序。也會將副署簽章加入預存程序、函式、組件或 DML 觸發程序。

語法

ADD [ COUNTER ] SIGNATURE TO module_class::module_name
    BY <crypto_list> [ ,...n ]
  
<crypto_list> ::=  
    CERTIFICATE cert_name  
    | CERTIFICATE cert_name [ WITH PASSWORD = 'password' ]
    | CERTIFICATE cert_name WITH SIGNATURE = signed_blob
    | ASYMMETRIC KEY Asym_Key_Name  
    | ASYMMETRIC KEY Asym_Key_Name [ WITH PASSWORD = 'password'.]
    | ASYMMETRIC KEY Asym_Key_Name WITH SIGNATURE = signed_blob

引數

module_class
這是要加入簽章之模組的類別。結構描述範圍模組的預設值是 OBJECT。

module_name
這是要簽署或反簽署的預存程序、函數、組件或觸發程序的名稱。

CERTIFICATE cert_name
這是用來預存程序、函數、組件或觸發程序要簽署或反簽署的憑證名稱。

WITH PASSWORD ='password'
這是要將憑證私密金鑰或非對稱金鑰解密所需的密碼。唯有當私密金鑰不受資料庫主要金鑰保護時，才需要這個子句。

SIGNATURE =signed_blob
指定模組已簽署的二進位大型物件 (BLOB)。如果您想要傳送模組但不傳送私密金鑰，則這個子句很有用。當您使用這個子句時，只需要模組、簽章和公開金鑰，即可將簽署的二進位大型物件加入資料庫中。 signed_blob 是十六進位格式的 Blob 本身。

ASYMMETRIC KEY Asym_Key_Name
這是預存程序、函數、組件或觸發程序用來簽署或副署的非對稱金鑰名稱。

備註

簽署或副署的模組及用來簽署的憑證或非對稱金鑰必須已存在。模組中的每個字元都會包含在簽章計算中。這包括開頭的歸位字元和換行字元。

可用任何數目的憑證和非對稱金鑰來簽署和副署模組。

當模組變更時，會卸除模組的簽章。

如果模組包含 EXECUTE AS 子句，則主體的安全性識別碼 (SID) 也會併入成為簽署程序的一部分。

警告

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

無法簽署資料定義語言 (Data Definition Language，DDL) 觸發程序和內嵌資料表值函數。

您可以在 sys.crypt_properties 目錄檢視中，看到有關簽章的資訊。

警告

當重新建立簽章的程序時，在原始批次的所有陳述式必須符合當重新建立批次。如果批次中有任何部分不同 (即使是在空格或註解)，產生的簽章會有所不同。

副署

當執行簽署的模組時，簽章會暫時新增至 SQL Token，但是如果此模組執行另一個模組或是此模組結束執行，簽章將會遺失。副署是一種特殊形式的簽章。副署簽章本身並不會授與任何權限，但是可允許在呼叫副署物件的期間，保留相同憑證或非對稱金鑰所做的簽章。

例如，假設使用者 Alice 呼叫 ProcForAlice 程序，這會呼叫 procSelectT1 程序，後者選取自資料表 T1。 Alice 擁有 ProcForAlice 和 ProcSelectT1 的 EXECUTE 權限，但是沒有 T1 的 SELECT 權限，所以這整個鏈結中不涉及任何擁有權鏈結。 Alice 無法直接存取資料表 T1，也不能透過 ProcForAlice 和 ProcSelectT1 存取。因為我們希望 Alice 一律使用 ProcForAlice 存取，所以不想授與她執行 ProcSelectT1 的權限。我們該怎麼完成呢？

如果我們簽署 ProcSelectT1，讓 ProcSelectT1 可以存取 T1，那麼 Alice 就可以直接叫用 ProcSelectT1，不必呼叫 ProcForAlice。
我們可拒絕 Alice 使用 ProcSelectT1 的 EXECUTE 權限，但這樣 Alice 就無法透過 ProcForAlice 呼叫 ProcSelectT1。
只簽署 ProcForAlice 是不可行的，因為在呼叫 ProcSelectT1 時可能會遺失簽章。

但是，使用簽署 ProcForAlice 的相同憑證副署 ProcSelectT1，SQL Server 會在整個呼叫鏈結中保留簽章，而且允許存取 T1。如果 Alice 嘗試直接呼叫 ProcSelectT1，並無法存取 T1，因為副署不會授與任何權限。以下的範例 C 示範 Transact-SQL 在此範例中發揮的作用。

signature example

權限

需要物件的 ALTER 權限，以及憑證或非對稱金鑰的 CONTROL 權限。如果相關聯的私密金鑰受到密碼保護，則使用者也必須有密碼。

範例

A. 使用憑證來簽署預存程序

下列範例以 HumanResources.uspUpdateEmployeeLogin 憑證簽署預存程序 HumanResourcesDP。

USE AdventureWorks2022;  
ADD SIGNATURE TO HumanResources.uspUpdateEmployeeLogin   
    BY CERTIFICATE HumanResourcesDP;  
GO

B. 使用簽署的 BLOB 來簽署預存程序

下列範例會建立新的資料庫，並建立要在此範例中使用的憑證。此範例會建立及簽署簡單預存程序，並從 sys.crypt_properties 中擷取 BLOB 簽章。然後卸除及重新加入簽章。這個範例會使用 WITH SIGNATURE 語法簽署此程序。

CREATE DATABASE TestSignature ;  
GO  
USE TestSignature ;  
GO  
-- Create a CERTIFICATE to sign the procedure.  
CREATE CERTIFICATE cert_signature_demo   
    ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
    WITH SUBJECT = 'ADD SIGNATURE demo';  
GO  

-- Create a simple procedure.  
CREATE PROC [sp_signature_demo]  
AS  
    PRINT 'This is the content of the procedure.' ;  
GO  
-- Sign the procedure.  
ADD SIGNATURE TO [sp_signature_demo]   
    BY CERTIFICATE [cert_signature_demo]   
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y' ;  
GO  

-- Get the signature binary BLOB for the sp_signature_demo procedure.  
SELECT cp.crypt_property  
    FROM sys.crypt_properties AS cp  
    JOIN sys.certificates AS cer  
        ON cp.thumbprint = cer.thumbprint  
    WHERE cer.name = 'cert_signature_demo' ;  
GO

每次當您建立程序時，這個陳述式傳回的 crypt_property 簽章都會不同。請記下結果，稍後可用於此範例中。以此範例為例，示範的結果如下：0x831F5530C86CC8ED606E5BC2720DA835351E46219A6D5DE9CE546297B88AEF3B6A7051891AF3EE7A68EAB37CD8380988B4C3F7469C8EABDD9579A2A5C507A4482905C2F24024FFB2F9BD7A953DD5E98470C4AA90CE83237739BB5FAE7BAC796E7710BDE291B03C43582F6F2D3B381F2102EEF8407731E01A51E24D808D54B373。

-- Drop the signature so that it can be signed again.  
DROP SIGNATURE FROM [sp_signature_demo]   
    BY CERTIFICATE [cert_signature_demo];  
GO  

-- Add the signature. Use the signature BLOB obtained earlier.  
ADD SIGNATURE TO [sp_signature_demo]   
    BY CERTIFICATE [cert_signature_demo]  
    WITH SIGNATURE = 0x831F5530C86CC8ED606E5BC2720DA835351E46219A6D5DE9CE546297B88AEF3B6A7051891AF3EE7A68EAB37CD8380988B4C3F7469C8EABDD9579A2A5C507A4482905C2F24024FFB2F9BD7A953DD5E98470C4AA90CE83237739BB5FAE7BAC796E7710BDE291B03C43582F6F2D3B381F2102EEF8407731E01A51E24D808D54B373 ;  
GO

C. 使用副署簽章存取程序

下列範例示範副署簽章如何控制物件的存取。

-- Create tesT1 database  
CREATE DATABASE testDB;  
GO  
USE testDB;  
GO  
-- Create table T1  
CREATE TABLE T1 (c VARCHAR(11));  
INSERT INTO T1 VALUES ('This is T1.');  
  
-- Create a TestUser user to own table T1  
CREATE USER TestUser WITHOUT LOGIN;  
ALTER AUTHORIZATION ON T1 TO TestUser;  
  
-- Create a certificate for signing  
CREATE CERTIFICATE csSelectT  
  ENCRYPTION BY PASSWORD = 'SimplePwd01'  
  WITH SUBJECT = 'Certificate used to grant SELECT on T1';  
CREATE USER ucsSelectT1 FROM CERTIFICATE csSelectT;  
GRANT SELECT ON T1 TO ucsSelectT1;  
  
-- Create a principal with low privileges  
CREATE LOGIN Alice WITH PASSWORD = 'SimplePwd01';  
CREATE USER Alice;  
  
-- Verify Alice cannoT1 access T1;  
EXECUTE AS LOGIN = 'Alice';  
    SELECT * FROM T1;  
REVERT;  
GO  
-- Create a procedure that directly accesses T1  
CREATE PROCEDURE procSelectT1 AS  
BEGIN  
    PRINT 'Now selecting from T1...';  
    SELECT * FROM T1;  
END;  
GO  
GRANT EXECUTE ON ProcSelectT1 to public;  
GO
-- Create special procedure for accessing T1  
CREATE PROCEDURE  ProcForAlice AS  
BEGIN  
   IF USER_ID() <> USER_ID('Alice')  
    BEGIN  
        PRINT 'Only Alice can use this.';  
        RETURN  
    END  
   EXEC ProcSelectT1;  
END;  
GO;  
GRANT EXECUTE ON ProcForAlice TO PUBLIC;  
  
-- Verify procedure works for a sysadmin user  
EXEC ProcForAlice;  
  
-- Alice still can't use the procedure yet  
EXECUTE AS LOGIN = 'Alice';  
    EXEC ProcForAlice;  
REVERT;  
  
-- Sign procedure to grant it SELECT permission  
ADD SIGNATURE TO ProcForAlice BY CERTIFICATE csSelectT   
WITH PASSWORD = 'SimplePwd01';  
  
-- Counter sign ProcSelectT1, to make this work  
ADD COUNTER SIGNATURE TO ProcSelectT1 BY CERTIFICATE csSelectT   
WITH PASSWORD = 'SimplePwd01';  
  
-- Now the proc works.   
-- Note that calling ProcSelectT1 directly still doesn't work  
EXECUTE AS LOGIN = 'Alice';  
    EXEC ProcForAlice;  
    EXEC ProcSelectT1;  
REVERT;  
  
-- Cleanup  
USE master;  
GO  
DROP DATABASE testDB;  
DROP LOGIN Alice;

分享方式：

ADD SIGNATURE (Transact-SQL)

語法

引數

備註

副署

權限

範例

A. 使用憑證來簽署預存程序

B. 使用簽署的 BLOB 來簽署預存程序

C. 使用副署簽章存取程序

另請參閱

意見反映

更多資源