TAMBAHKAN TANDA TANGAN (Transact-SQL)

Artikel
12/22/2023

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance

Menambahkan tanda tangan digital ke prosedur tersimpan, fungsi, perakitan, atau pemicu DML. Juga menambahkan countersignature ke prosedur tersimpan, fungsi, rakitan, atau pemicu DML.

Konvensi sintaks transact-SQL

Sintaksis

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

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

module_class
Adalah kelas modul tempat tanda tangan ditambahkan. Default untuk modul dengan cakupan skema adalah OBJECT.

module_name
Adalah nama prosedur tersimpan, fungsi, perakitan, atau pemicu yang akan ditandatangani atau ditandatangani.

CERT_NAME SERTIFIKAT
Adalah nama sertifikat untuk menandatangani atau mengimbangi prosedur tersimpan, fungsi, perakitan, atau pemicu.

WITH PASSWORD ='password'
Apakah kata sandi yang diperlukan untuk mendekripsi kunci privat sertifikat atau kunci asimetris. Klausa ini hanya diperlukan jika kunci privat tidak dilindungi oleh kunci master database.

SIGNATURE =signed_blob
Menentukan objek besar biner (BLOB) modul yang ditandatangani. Klausa ini berguna jika Anda ingin mengirim modul tanpa mengirim kunci privat. Saat Anda menggunakan klausa ini, hanya modul, tanda tangan, dan kunci publik yang diperlukan untuk menambahkan objek besar biner yang ditandatangani ke database. signed_blob adalah blob itu sendiri dalam format heksadesimal.

Asym_Key_Name KUNCI ASIMETRIS
Adalah nama kunci asimetris untuk menandatangani atau mengimbangi prosedur tersimpan, fungsi, perakitan, atau pemicu.

Keterangan

Modul yang ditandatangani atau ditandatangani dan sertifikat atau kunci asimetris yang digunakan untuk menandatanganinya harus sudah ada. Setiap karakter dalam modul disertakan dalam perhitungan tanda tangan. Ini termasuk pengembalian pengangkutan terkemuka dan umpan baris.

Modul dapat ditandatangani dan ditandatangani oleh sejumlah sertifikat dan kunci asimetris.

Tanda tangan modul dihilangkan saat modul diubah.

Jika modul berisi klausul EXECUTE AS, ID keamanan (SID) prinsipal juga disertakan sebagai bagian dari proses penandatanganan.

Perhatian

Penandatanganan modul hanya boleh digunakan untuk memberikan izin, tidak pernah menolak atau mencabut izin.

Pemicu bahasa definisi data (DDL) dan fungsi bernilai tabel sebaris tidak dapat ditandatangani.

Informasi tentang tanda tangan terlihat dalam tampilan katalog sys.crypt_properties.

Peringatan

Saat membuat ulang prosedur untuk tanda tangan, semua pernyataan dalam batch asli harus cocok dengan batch rekreasi. Jika ada bagian dari batch yang berbeda, bahkan di spasi atau komentar, tanda tangan yang dihasilkan akan berbeda.

Countersignatures

Saat menjalankan modul yang ditandatangani, tanda tangan akan ditambahkan sementara ke token SQL, tetapi tanda tangan hilang jika modul menjalankan modul lain atau jika modul mengakhiri eksekusi. Countersignature adalah bentuk khusus tanda tangan. Dengan sendirinya, countersignature tidak memberikan izin apa pun, namun, itu memungkinkan tanda tangan yang dibuat oleh sertifikat yang sama atau kunci asimetris untuk disimpan selama durasi panggilan yang dilakukan ke objek yang ditandatangani.

Misalnya, anggap pengguna Alice memanggil prosedur ProcForAlice, yang memanggil prosedur ProcSelectT1, yang memilih dari tabel T1. Alice memiliki izin EXECUTE pada ProcForAlice dan ProcSelectT1, tetapi dia tidak memiliki izin SELECT pada T1, dan tidak ada rantai kepemilikan yang terlibat dalam seluruh rantai ini. Alice tidak dapat mengakses tabel T1, baik secara langsung, atau melalui penggunaan ProcForAlice dan ProcSelectT1. Karena kami ingin Alice selalu menggunakan ProcForAlice untuk akses, kami tidak ingin memberinya izin untuk menjalankan ProcSelectT1. Bagaimana kita bisa mencapai ini?

Jika kita menandatangani ProcSelectT1, sehingga ProcSelectT1 dapat mengakses T1, maka Alice dapat memanggil ProcSelectT1 secara langsung dan dia tidak perlu memanggil ProcForAlice.
Kami dapat menolak izin EXECUTE pada ProcSelectT1 ke Alice, tetapi kemudian Alice tidak akan dapat memanggil ProcSelectT1 melalui ProcForAlice.
Penandatanganan ProcForAlice tidak akan berfungsi dengan sendirinya, karena tanda tangan akan hilang dalam panggilan ke ProcSelectT1.

Namun, dengan meng-countersign ProcSelectT1 dengan sertifikat yang sama yang digunakan untuk menandatangani ProcForAlice, SQL Server tanda tangan akan disimpan di seluruh rantai panggilan dan akan memungkinkan akses ke T1. Jika Alice mencoba memanggil ProcSelectT1 secara langsung, dia tidak dapat mengakses T1, karena countersignature tidak memberikan hak apa pun. Contoh C di bawah ini, menunjukkan Transact-SQL untuk contoh ini.

signature example

Izin

Memerlukan izin UBAH pada objek dan izin CONTROL pada sertifikat atau kunci asimetris. Jika kunci privat terkait dilindungi oleh kata sandi, pengguna juga harus memiliki kata sandi.

Contoh

J. Menandatangani prosedur tersimpan dengan menggunakan sertifikat

Contoh berikut menandatangani prosedur HumanResources.uspUpdateEmployeeLogin tersimpan dengan sertifikat HumanResourcesDP.

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

B. Menandatangani prosedur tersimpan dengan menggunakan BLOB yang ditandatangani

Contoh berikut membuat database baru dan membuat sertifikat untuk digunakan dalam contoh. Contoh membuat dan menandatangani prosedur tersimpan sederhana dan mengambil BLOB tanda tangan dari sys.crypt_properties. Tanda tangan kemudian dihilangkan dan ditambahkan lagi. Contoh menandatangani prosedur dengan menggunakan sintaks 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

Tanda crypt_property tangan yang dikembalikan oleh pernyataan ini akan berbeda setiap kali Anda membuat prosedur. Catat hasil untuk digunakan nanti dalam contoh ini. Untuk contoh ini, hasil yang ditunjukkan adalah: 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. Mengakses prosedur menggunakan countersignature

Contoh berikut menunjukkan bagaimana penghitung dapat membantu mengontrol akses ke objek.

-- 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;

Bagikan melalui