ADD SIGNATURE (Transact-SQL)

發行項
03/11/2025

適用於：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）也會包含在簽署程式中。

警告

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

無法簽署資料定義語言（DDL）觸發程式和內嵌資料表值函式。

sys.crypt_properties 目錄檢視中會顯示簽章的相關信息。

警告

當您重新建立簽章的程式時，原始批次中的所有語句都必須符合重新建立的批次。如果批次的任何部分不同，即使在空格或批注中，結果簽章也不同。

副署

當您執行已簽署的模組時，簽章會暫時新增至 SQL 令牌，但如果模組執行另一個模組，或模組終止執行，簽章就會遺失。 反簽署 是一種特殊的簽章形式。反簽署本身不會授與任何許可權。不過，它可讓相同憑證或非對稱密鑰所建立的簽章在對反簽署物件的呼叫期間保留下來。

例如，假設使用者 Alice 會呼叫程式 ProcForAlice，它會呼叫程式 ProcSelectT1，它會從數據表 T1選取。 Alice 具有 ProcForAlice的 EXECUTE 許可權，但沒有 EXECUTE 許可權 ProcSelectT1 或 SELECTT1許可權，而且整個鏈結中沒有任何擁有權鏈結。 Alice 無法直接存取資料表 T1，或使用 ProcForAlice 和 ProcSelectT1。因為我們希望 Alice 一律使用 ProcForAlice 進行存取，所以我們不想授與她執行 ProcSelectT1的許可權。我們如何完成此案例？

如果我們簽署 ProcSelectT1，讓 ProcSelectT1 可以存取 T1，則 Alice 可以直接叫用 ProcSelectT1，而且她不需要呼叫 ProcForAlice。
我們可以拒絕對 Alice ProcSelectT1 的 EXECUTE 許可權，但接著 Alice 無法透過 ProcForAlice呼叫 ProcSelectT1。
簽署 ProcForAlice 本身將無法運作，因為簽章會在呼叫 ProcSelectT1中遺失。

不過，藉由使用用來簽署 ProcForAlice的相同憑證來反 ProcSelectT1，簽章會保留在呼叫鏈結中，並允許存取 T1。如果Alice嘗試直接呼叫 ProcSelectT1，她就無法存取 T1，因為反符號不會授與任何許可權。範例 C 顯示此範例的 Transact-SQL。

簽章範例的螢幕快照。

權限

需要物件的 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 basic procedure.
CREATE PROCEDURE [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
     INNER 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. 使用反符號存取程式

下列範例示範副署簽章如何控制物件的存取。您必須將 <password> 取代為適當的密碼。

-- 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 = '<password>'
    WITH SUBJECT = 'Certificate used to grant SELECT on T1';

CREATE USER ucsSelectT1 FOR CERTIFICATE csSelectT;
GRANT SELECT ON T1 TO ucsSelectT1;

-- Create a principal with low privileges
CREATE LOGIN Alice WITH PASSWORD = '<password>';
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
    EXECUTE ProcSelectT1;
END
GO

GRANT EXECUTE ON ProcForAlice TO PUBLIC;

-- Verify procedure works for a sysadmin user
EXECUTE ProcForAlice;

-- Alice still can't use the procedure yet
EXECUTE AS LOGIN = 'Alice';
EXECUTE ProcForAlice;
REVERT;

-- Sign procedure to grant it SELECT permission
ADD SIGNATURE TO ProcForAlice
BY CERTIFICATE csSelectT WITH PASSWORD = '<password>';

ADD COUNTER SIGNATURE TO ProcSelectT1
BY CERTIFICATE csSelectT WITH PASSWORD = '<password>';

-- Now the stored procedure works.   
-- Note that calling ProcSelectT1 directly still doesn't work.
EXECUTE AS LOGIN = 'Alice';
EXECUTE ProcForAlice;
EXECUTE ProcSelectT1;
REVERT;

-- Cleanup
USE master;
GO

DROP DATABASE testDB;
DROP LOGIN Alice;

共用方式為

ADD SIGNATURE (Transact-SQL)

語法

引數

module_class

module_name

CERTIFICATE cert_name

WITH PASSWORD = 'password'

SIGNATURE = signed_blob

ASYMMETRIC KEY Asym_Key_Name

備註

副署

權限

範例

A. 使用憑證簽署預存程式

B. 使用已簽署的 BLOB 簽署預存程式

C. 使用反符號存取程式

意見反應

其他資源

共用方式為

ADD SIGNATURE (Transact-SQL)

語法

引數

module_class

module_name

CERTIFICATE cert_name

WITH PASSWORD = 'password'

SIGNATURE = signed_blob

ASYMMETRIC KEY Asym_Key_Name

備註

副署

權限

範例

A. 使用憑證簽署預存程式

B. 使用已簽署的 BLOB 簽署預存程式

C. 使用反符號存取程式

相關內容

意見反應

其他資源