ADD SIGNATURE (Transact-SQL)
適用於:SQL Server
Azure SQL 資料庫 Azure SQL 受控執行個體
將數位簽章加入預存程序、函式、組件或 DML 觸發程序。 也會將副署簽章加入預存程序、函式、組件或 DML 觸發程序。
Syntax
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
注意
若要檢視 SQL Server 2014 (12.x) 和更早版本的 Transact-SQL 語法,請參閱舊版文件。
引數
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 在此範例中發揮的作用。
權限
需要物件的 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;