Dela via

LÄGG TILL SIGNATUR (Transact-SQL)

  • Artikel

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Lägger till en digital signatur i en lagrad procedur, funktion, sammansättning eller DML-utlösare. Lägger också till en motsignatur i en lagrad procedur, funktion, sammansättning eller DML-utlösare.

Transact-SQL syntaxkonventioner

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

Argument

module_class

Klassen för modulen som signaturen läggs till i. Standardvärdet för moduler med schemaomfattning är OBJECT.

module_name

Namnet på en lagrad procedur, funktion, sammansättning eller utlösare som ska signeras eller kontrasigneras.

CERTIFIKAT cert_name

Namnet på ett certifikat som den lagrade proceduren, funktionen, sammansättningen eller utlösaren ska signeras eller mottilldelas med.

MED LÖSENORD =lösenord

Lösenordet som krävs för att dekryptera den privata nyckeln för certifikatet eller den asymmetriska nyckeln. Den här satsen krävs bara om den privata nyckeln inte skyddas av databashuvudnyckeln.

SIGNATURE = signed_blob

Anger modulens signerade, binära stora objekt (BLOB). Den här satsen är användbar om du vill skicka en modul utan att skicka den privata nyckeln. När du använder den här satsen krävs endast modulen, signaturen och den offentliga nyckeln för att lägga till det signerade binära stora objektet i en databas. signed_blob är själva bloben i hexadecimalt format.

ASYMMETRISK NYCKEL Asym_Key_Name

Namnet på en asymmetrisk nyckel som den lagrade proceduren, funktionen, sammansättningen eller utlösaren ska signeras med eller motsignas med.

Anmärkningar

Modulen som signeras eller kontrasigneras och certifikatet eller den asymmetriska nyckel som används för att signera den måste redan finnas. Varje tecken i modulen ingår i signaturberäkningen. Detta inkluderar inledande vagnreturer och linjefeeds.

En modul kan signeras och kontrasigneras av valfritt antal certifikat och asymmetriska nycklar.

Signaturen för en modul tas bort när modulen ändras.

Om en modul innehåller en EXECUTE AS-sats inkluderas även säkerhets-ID (SID) för huvudkontot som en del av signeringsprocessen.

Försiktighet

Modulsignering bör endast användas för att bevilja behörigheter, aldrig för att neka eller återkalla behörigheter.

DDL-utlösare (Data Definition Language) och Inline table-valued functions kan inte signeras.

Information om signaturer visas i sys.crypt_properties katalogvyn.

Varning

När du återskapar en procedur för signatur måste alla instruktioner i den ursprungliga batchen matcha den återskapade batchen. Om någon del av batchen skiljer sig åt, även i blanksteg eller kommentarer, skiljer sig den resulterande signaturen.

Motsigneringar

När du kör en signerad modul läggs signaturerna tillfälligt till i SQL-token, men signaturerna går förlorade om modulen kör en annan modul eller om modulen avslutar körningen. En motsignering är en särskild form av signatur. I sig beviljar inte en motsignatur några behörigheter. Det gör dock att signaturer som görs av samma certifikat eller asymmetriska nyckel kan behållas under hela anropet till det motsignerade objektet.

Anta till exempel att användaren Alice anropar proceduren ProcForAlice, som anropar proceduren ProcSelectT1, som väljer från tabellen T1. Alice har EXECUTE behörighet på ProcForAlice, men har inte EXECUTE behörigheter för ProcSelectT1 eller SELECT behörighet på T1, och ingen ägarskapslänkning ingår i hela den här kedjan. Alice kan inte komma åt tabell T1, antingen direkt eller med hjälp av ProcForAlice och ProcSelectT1. Eftersom vi vill att Alice alltid ska använda ProcForAlice för åtkomst vill vi inte ge henne behörighet att köra ProcSelectT1. Hur kan vi åstadkomma det här scenariot?

  • Om vi signerar ProcSelectT1, så att ProcSelectT1 kan komma åt T1, kan Alice anropa ProcSelectT1 direkt och hon behöver inte anropa ProcForAlice.

  • Vi kan neka EXECUTE behörighet för ProcSelectT1 till Alice, men sedan kan Alice inte anropa ProcSelectT1 via ProcForAlice.

  • Att signera ProcForAlice fungerar inte på egen hand eftersom signaturen går förlorad i anropet till ProcSelectT1.

Men genom att kontrasignera ProcSelectT1 med samma certifikat som används för att signera ProcForAlicebehålls signaturen i anropskedjan och får åtkomst till T1. Om Alice försöker anropa ProcSelectT1 direkt kan hon inte komma åt T1eftersom motsignaturen inte beviljar några rättigheter. Exempel C visar Transact-SQL för det här exemplet.

Skärmbild av signaturexemplet.

Behörigheter

Kräver ALTER behörighet för objektet och CONTROL behörighet för certifikatet eller asymmetrisk nyckel. Om en associerad privat nyckel skyddas av ett lösenord måste användaren också ha lösenordet.

Exempel

A. Signera en lagrad procedur med hjälp av ett certifikat

I följande exempel signeras den lagrade proceduren HumanResources.uspUpdateEmployeeLogin med certifikatet HumanResourcesDP.

USE AdventureWorks2022;

ADD SIGNATURE TO HumanResources.uspUpdateEmployeeLogin
    BY CERTIFICATE HumanResourcesDP;
GO

B. Signera en lagrad procedur med hjälp av en signerad BLOB

I följande exempel skapas en ny databas och ett certifikat skapas som ska användas i exemplet. Exemplet skapar och signerar en grundläggande lagrad procedur och hämtar signaturbloben från sys.crypt_properties. Signaturen tas sedan bort och läggs till igen. Exemplet signerar proceduren med hjälp av syntaxen 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

Den crypt_property signatur som returneras av den här instruktionen skiljer sig åt varje gång du skapar en procedur. Anteckna resultatet för användning senare i det här exemplet. I det här fallet är resultatet som visas 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. Få åtkomst till en procedur med hjälp av en motsignering

I följande exempel visas hur motsignering kan hjälpa dig att styra åtkomsten till ett objekt. Du måste ersätta <password> med ett lämpligt lösenord.

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

Relaterat innehåll